连续登陆问题

问题描述

t_login 表记录了用户每次登陆的时间信息：

uid	login_time
1	2022-01-01 08:05:11
2	2022-01-01 10:00:00
3	2022-01-01 12:13:14
1	2022-01-01 19:30:00
...	...

如何通过 SQL 查询找出 2022年1月 连续登陆3天以上的用户？

SELECT t1.uid, t2.ymd, t3.ymd
FROM (SELECT DISTINCT uid, date(login_time) ymd
FROM t_login
WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND timestamp '2022-01-31 23:59:59') t1
JOIN (SELECT DISTINCT uid, date(login_time) ymd
FROM t_login
WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND timestamp '2022-01-31 23:59:59') t2
ON (t1.uid = t2.uid AND datediff(t2.ymd, t1.ymd)=1)
JOIN (SELECT DISTINCT uid, date(login_time) ymd
FROM t_login
WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND timestamp '2022-01-31 23:59:59') t3
ON (t2.uid = t3.uid AND datediff(t3.ymd, t2.ymd)=1);

WITH t1 AS (SELECT DISTINCT uid, date(login_time) ymd
FROM t_login
WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND timestamp '2022-01-31 23:59:59'),
t2 AS (SELECT uid, ymd,
			 date_sub(ymd, INTERVAL ROW_NUMBER() OVER (PARTITION BY uid ORDER BY ymd) day) sub_date
FROM t1)
SELECT uid, min(ymd), max(ymd), count(*)
FROM t2
GROUP BY uid, sub_date
HAVING count(*) >= 3;

WITH t1 AS (SELECT DISTINCT uid, date(login_time) ymd
FROM t_login
WHERE login_time BETWEEN timestamp '2022-01-01 00:00:00' AND timestamp '2022-01-31 23:59:59'),
t2 AS (
SELECT uid, ymd,
			 dateiff(ymd, lag(ymd, 2) OVER (PARTITION BY uid ORDER BY ymd)) diff
FROM t1)
SELECT uid, ymd, diff
FROM t2
WHERE diff = 2;