环境
阿里云MaxCompute
初始化数据
CREATE TABLE user_login
(
user_id BIGINT COMMENT '用户ID'
,login_date STRING COMMENT '登录日期'
)
COMMENT '用户登录日志'
;
INSERT OVERWRITE TABLE user_login
SELECT 1
,'2023-08-02'
UNION ALL
SELECT 1
,'2023-08-03'
UNION ALL
SELECT 1
,'2023-08-04'
UNION ALL
SELECT 2
,'2023-11-02'
UNION ALL
SELECT 2
,'2023-12-09'
UNION ALL
SELECT 3
,'2023-01-01'
UNION ALL
SELECT 3
,'2023-04-23'
UNION ALL
SELECT 3
,'2023-09-10'
UNION ALL
SELECT 3
,'2023-09-11'
UNION ALL
SELECT 3
,'2023-09-12'
UNION ALL
SELECT 4
,'2023-08-02'
UNION ALL
SELECT 4
,'2023-08-04'
UNION ALL
SELECT 4
,'2023-08-05'
UNION ALL
SELECT 4
,'2023-08-07'
;
方法一
SELECT user_id
,COUNT(1) AS cnt
FROM (
SELECT user_id
,login_date
,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date ) AS rk
FROM user_login
) t
GROUP BY user_id
,DATE_SUB(login_date, t.rk)
HAVING COUNT(1) >= 3;
--或者 HAVING cnt >= 3;
方法二
SELECT a.user_id
FROM (
SELECT user_id
,login_date
,LEAD(login_date,1) OVER (PARTITION BY user_id ORDER BY login_date ) AS lead_1day
,LEAD(login_date,2) OVER (PARTITION BY user_id ORDER BY login_date ) AS lead_2day
FROM user_login
) AS a
WHERE DATEDIFF(lead_2day, lead_1day) = 1
AND DATEDIFF(lead_1day, login_date) = 1
;