SQL统计连续登陆3天的用户(连续活跃超3天用户)
目录
1. 数据准备
-- 数据准备WITH user_active_info AS(SELECT*FROM(VALUES('10001','2023-02-01'),('10001','2023-02-03'),('10001','2023-02-04'),('10001','2023-02-05'),('10002','2023-02-02'),('10002','2023-02-03'),('10002','2023-02-04'),('10002','2023-02-05'),('10002','2023-02-07'),('10003','2023-02-02'),('10003','2023-02-03'),('10003','2023-02-04'),('10003','2023-02-05'),('10003','2023-02-06'),('10003','2023-02-07'),('10003','2023-02-08'),('10004','2023-02-03'),('10004','2023-02-04'),('10004','2023-02-06'),('10004','2023-02-07'),('10004','2023-02-08'),('10004','2023-02-08'),('10005','2023-02-02'),('10005','2023-02-05'))AS user_active_info(user_id, active_date))
2. 方法一: 差值计算
-- 1. 对用户数据进行分组,按照活跃日期进行排序(去重:防止有一天有多次活跃记录)SELECT
user_id
, active_date
, ROW_NUMBER()OVER(PARTITIONBY user_id ORDERBY active_date)AS rn
FROM user_active_info
GROUPBY user_id , active_date
;
user_idactive_datern100012023-02-011100012023-02-032100012023-02-043100012023-02-054100022023-02-021100022023-02-032100022023-02-043100022023-02-054100022023-02-075………
-- 2. 使用活跃日期和排序rn进行差值计算,得到的日期如果是相等的,就说明活跃日期是连续的SELECT
user_id
, active_date
, rn
, DATE_SUB(active_date,rn)AS sub_date
FROM(SELECT
user_id
, active_date
, ROW_NUMBER()OVER(PARTITIONBY user_id ORDERBY active_date)AS rn
FROM user_active_info
GROUPBY user_id , active_date
) a
;
user_idactive_daternsub_date100012023-02-0112023-01-31100012023-02-0322023-02-01100012023-02-0432023-02-01100012023-02-0542023-02-01100022023-02-0212023-02-01100022023-02-0322023-02-01100022023-02-0432023-02-01100022023-02-0542023-02-01100022023-02-0752023-02-02…………
-- 3. 按照user_id和sub_date 进行分组求和,筛选出连续登陆天数大于3天的用户SELECT
user_id
,MIN(active_date)AS begin_date
,MAX(active_date)AS end_date
,COUNT(1)AS login_duration
FROM(SELECT
user_id
, active_date
, rn
, DATE_SUB(active_date,rn)AS sub_date
FROM(SELECT
user_id
, active_date
, ROW_NUMBER()OVER(PARTITIONBY user_id ORDERBY active_date)AS rn
FROM user_active_info
GROUPBY user_id , active_date
) a
) b
GROUPBY user_id , sub_date
HAVING login_duration >=3;
user_idbegin_dateend_datelogin_duration100012023-02-032023-02-053100022023-02-022023-02-054100032023-02-022023-02-087100042023-02-062023-02-083
3. 方法二: lead或lag函数
-- 1. 将active_date 上抬2行,不存在默认为'0'(计算连续活跃3天以上的, 上抬2行,n天上抬n-1行)(去重:防止有一天有多次活跃记录)SELECT
user_id
, active_date
, lead(active_date ,2,0)OVER(PARTITIONBY user_id ORDERBY active_date)AS lead_active_date
FROM user_active_info
GROUPBY user_id , active_date
user_idactive_datelead_active_date100012023-02-012023-02-04100012023-02-032023-02-05100012023-02-040100012023-02-050100022023-02-022023-02-04100022023-02-032023-02-05100022023-02-042023-02-07100022023-02-050100022023-02-070………
-- 2. 过滤筛选出, lead_active_date 与 active_date 差值为2的, 差值2 -> 连续活跃了3天SELECT
user_id , active_date , lead_active_date
FROM(SELECT
user_id
, active_date
, lead(active_date ,2,0)OVER(PARTITIONBY user_id ORDERBY active_date)AS lead_active_date
FROM user_active_info
GROUPBY user_id , active_date
) a
WHERE lead_active_date !='0'AND DATEDIFF(lead_active_date , active_date)=2
user_idactive_datelead_active_date100012023-02-032023-02-05100022023-02-022023-02-04100022023-02-032023-02-05………
-- 3. user_id 去重, 得到连续活跃天数>=3天的用户SELECT
user_id
FROM(SELECT
user_id , active_date , lead_active_date
FROM(SELECT
user_id
, active_date
, lead(active_date ,2,0)OVER(PARTITIONBY user_id ORDERBY active_date)AS lead_active_date
FROM user_active_info
GROUPBY user_id , active_date
) a
WHERE lead_active_date !='0'AND DATEDIFF(lead_active_date , active_date)=2) b
GROUPBY user_id
user_id10001100021000310004
end
版权归原作者 Taerge0110 所有, 如有侵权,请联系我们删除。