mysql题解 | 查询连续登陆的用户 (步步为赢)

-- 问题提炼：连续登陆 不少于3天

WITH 
login_tb2 AS (
    SELECT DISTINCT  -- 该题忽略单日多次登录情况
		user_id
		,DATE(log_time) AS log_time
	FROM login_tb
	ORDER BY user_id, log_time
),
-- user_id|log_time  |
-- -------+----------+
--    1003|2022-02-09|
--    1003|2022-02-10|
--    1003|2022-02-11|
--    1101|2022-02-09|
--    1102|2022-02-09|
--    1102|2022-02-11|
--    1102|2022-02-12|
login_tb3 AS (
	SELECT 
		user_id
       ,log_time
       ,ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY log_time) AS r
    FROM login_tb2
),
-- user_id|log_time  |r|
-- -------+----------+-+
--    1003|2022-02-09|1|
--    1003|2022-02-10|2|
--    1003|2022-02-11|3|
--    1101|2022-02-09|1|
--    1102|2022-02-09|1|
--    1102|2022-02-11|2|
--    1102|2022-02-12|3|
login_tb4 AS (  -- 日期 减 序号
	SELECT 
		user_id
       ,log_time
       ,r
       ,DAY(log_time) - r AS sub
    FROM login_tb3
    GROUP BY user_id, log_time
),
-- user_id|log_time  |r|sub|
-- -------+----------+-+---+
--    1003|2022-02-09|1|  8|
--    1003|2022-02-10|2|  8|
--    1003|2022-02-11|3|  8|
--    1101|2022-02-09|1|  8|
--    1102|2022-02-09|1|  8|
--    1102|2022-02-11|2|  9|
--    1102|2022-02-12|3|  9|
login_tb5 AS (   -- 同时按user_id, sub分组，计算sub列相同的行
	SELECT 
		user_id
       ,sub
       ,COUNT(sub) AS n
    FROM login_tb4
    GROUP BY user_id, sub
)
-- user_id|sub|n|
-- -------+---+-+
--    1003|  8|3|
--    1101|  8|1|
--    1102|  8|1|
--    1102|  9|2|
--    1104|  9|2|
--    1106|  9|3|

SELECT 
	user_id 
FROM login_tb5 
WHERE n >= 3
    AND user_id IN (SELECT user_id FROM register_tb)
;

-- 因为我在DBeaver里修改了题目给的数据实例，所以你能看懂语句就行，语句的输出数据可能和你的不一样。
-- 这样一来，公司让你统计 连续登录不少于一周，半月，类似的问题都能使用这个SQL

一开始我没有思路，看了一些题解，有些题解使用LEAD()函数，但如果让我统计 连续不少于7天的用户，多累啊。

END