按列结果分组查询 [英] Query group by pair of column result
本文介绍了按列结果分组查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当前我的数据集由4列组成,分别为id
,status
,user_id
,created_date
Currently my dataset consist of 4 columns, id
, status
, user_id
, created_date
所以一段时间后数据可能是这样的
so after a while the data can be like this
(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)
这是我到目前为止提出的查询
So this is the query that I came up with so far
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;
我不知道如何将第一次登录注销与第二次登录注销会话区分开,通过此查询,我只能得到
I have no idea how to differentiate first login-logout to the second login-logout session, with this query, I am only getting
(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)
是否可以将登录注销分组为一组,以便我可以按顺序区分每一对登录注销?
Is there a way to group login-logout as a set, so that I can differentiate every single pair of login-logout sequentially?
推荐答案
您可以使用左联接来完成,然后减去2个日期:
You can do it with a left join and then subtract the 2 dates:
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
)
请参见演示.
结果:
See the demo.
Results:
| 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 |
这篇关于按列结果分组查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文