按列结果对查询组

Question

目前，我的数据集由4列组成：id、status、user_id、created_date

所以过了一段时间，数据可以是这样的

(1, 'LOGIN', '2019-07-16 07:06:55', 'Bob')
(2, 'LOGOUT', '2019-07-16 07:29:13', 'Bob')
(3, 'LOGIN', '2019-07-16 07:30:31', 'Bob')
(4, 'LOGOUT', '2019-07-16 07:49:50', 'Bob')
(5, 'LOGIN', '2019-07-16 08:05:55', 'Tom')
(6, 'LOGOUT', '2019-07-16 08:15:13', 'Tom')
(7, 'LOGIN', '2019-07-16 09:13:55', 'John')
(8, 'LOGOUT', '2019-07-16 09:20:13', 'John')

我试着把它弄成这样

(1, '2019-07-16 07:06:55', '2019-07-16 07:29:13', 'Bob', 22.5800)
(2, '2019-07-16 07:30:31', '2019-07-16 07:49:50', 'Bob', 19.5800)
(3, '2019-07-16 08:05:55', '2019-07-16 08:15:13', 'Tom', 9.5800)
(4, '2019-07-16 09:13:55', '2019-07-16 09:20:13', 'John', 6.5800)

这就是我到目前为止想出的问题

SELECT
  max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END)               AS login_date,
  CASE WHEN max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END) < max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END)
    THEN max(CASE WHEN action = 'LOGOUT'
      THEN A.action_date END)
  ELSE max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END) END           AS logout_date,
  A.full_name,
  CASE WHEN timestamp(max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END)) < timestamp(max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END)) OR max(CASE WHEN action = 'LOGOUT'
    THEN A.action_date END) IS NULL
    THEN 0
  ELSE
    (timestamp(max(CASE WHEN action = 'LOGOUT'
      THEN A.action_date END)) - timestamp(max(CASE WHEN action = 'LOGOUT'
      THEN A.action_date END))) / 100 END AS session_time
FROM (
       SELECT
         timestamp(created_date) AS action_date,
         name as full_name,
         status as action
       FROM `training_ground`.session
       WHERE status = 'LOGIN' OR status = 'LOGOUT'
       GROUP BY action, cast(action_date AS DATE), name
       ORDER BY action_date DESC) AS A
GROUP BY A.full_name
ORDER BY A.action_date DESC;

我不知道如何区分第一次登录-注销到第二次登录-注销会话，使用这个查询，我只需要

(1, '2019-07-16 07:06:55', '2019-07-16 07:29:13', 'Bob', 22.5800)
(2, '2019-07-16 08:05:55', '2019-07-16 08:15:13', 'Tom', 9.5800)
(3, '2019-07-16 09:13:55', '2019-07-16 09:20:13', 'John', 6.5800)

是否有一种方法可以将登录-退出分组为一个集合，这样我就可以按顺序区分每一对登录-退出？

Answer 1

您可以使用左联接，然后减去以下两个日期：

select 
  t.id, 
  t.action_date login_date, tt.action_date logout_date,
  t.user_id,
  (tt.action_date - t.action_date) / 100 session_time
from (
  select * from session where status = 'LOGIN'  
) t left join (
  select * from session where status = 'LOGOUT'    
) tt on tt.user_id = t.user_id and 
  tt.action_date = (
    select min(action_date) from session
    where status = 'LOGOUT' and user_id = t.user_id and action_date > t.action_date
  )

见演示。

结果：

| id  | login_date          | user_id | logout_date         | session_time |
| --- | ------------------- | ------- | ------------------- | ------------ |
| 1   | 2019-07-16 07:06:55 | Bob     | 2019-07-16 07:29:13 | 22.58        |
| 3   | 2019-07-16 07:30:31 | Bob     | 2019-07-16 07:49:50 | 19.19        |
| 5   | 2019-07-16 08:05:55 | Tom     | 2019-07-16 08:15:13 | 9.58         |
| 7   | 2019-07-16 09:13:55 | John    | 2019-07-16 09:20:13 | 6.58         |

Answer 2

对提供的输入进行简单的实现。我有如下数据集：

select id, stat[status],dater[Date], name[Name] from tablelogin

​

数据提取：

Select t1.id, t1.dater[login], t2.dater[logout], t1.name[name], datediff(day,t1.dater, t2.dater)[days] from tablelogin t1, tablelogin t2 where t1.name=t2.name and t2.id>t1.id and t2.stat='logout'

​

​

您需要处理逻辑和控制，当记录增长时，上面的查询可能会产生不同的结果。