SQL统计连续登陆3天的用户(连续活跃超3天用户)
创始人
2025-01-15 15:33:32
0

SQL统计连续登陆3天的用户(连续活跃超3天用户)

目录

    • SQL统计连续登陆3天的用户(连续活跃超3天用户)
      • 1. 数据准备
      • 2. 方法一: 差值计算
      • 3. 方法二: lead或lag函数
        • end

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(PARTITION BY user_id ORDER BY active_date) AS rn  FROM user_active_info GROUP BY user_id , active_date ;  
user_idactive_datern
100012023-02-011
100012023-02-032
100012023-02-043
100012023-02-054
100022023-02-021
100022023-02-032
100022023-02-043
100022023-02-054
100022023-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(PARTITION BY user_id ORDER BY active_date) AS rn      FROM user_active_info      GROUP BY user_id , active_date     ) a ;  
user_idactive_daternsub_date
100012023-02-0112023-01-31
100012023-02-0322023-02-01
100012023-02-0432023-02-01
100012023-02-0542023-02-01
100022023-02-0212023-02-01
100022023-02-0322023-02-01
100022023-02-0432023-02-01
100022023-02-0542023-02-01
100022023-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(PARTITION BY user_id ORDER BY active_date) AS rn          FROM user_active_info          GROUP BY user_id , active_date     ) a ) b GROUP BY user_id , sub_date HAVING login_duration >= 3 ;  
user_idbegin_dateend_datelogin_duration
100012023-02-032023-02-053
100022023-02-022023-02-054
100032023-02-022023-02-087
100042023-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(PARTITION BY user_id ORDER BY active_date) AS lead_active_date  FROM user_active_info GROUP BY user_id , active_date 
user_idactive_datelead_active_date
100012023-02-012023-02-04
100012023-02-032023-02-05
100012023-02-040
100012023-02-050
100022023-02-022023-02-04
100022023-02-032023-02-05
100022023-02-042023-02-07
100022023-02-050
100022023-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(PARTITION BY user_id ORDER BY active_date) AS lead_active_date     FROM user_active_info     GROUP BY user_id , active_date ) a  WHERE  lead_active_date != '0'     AND DATEDIFF(lead_active_date , active_date) = 2 
user_idactive_datelead_active_date
100012023-02-032023-02-05
100022023-02-022023-02-04
100022023-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(PARTITION BY user_id ORDER BY active_date) AS lead_active_date          FROM user_active_info         GROUP BY user_id , active_date     ) a      WHERE  lead_active_date != '0'         AND DATEDIFF(lead_active_date , active_date) = 2 ) b GROUP BY user_id 
user_id
10001
10002
10003
10004
end

相关内容

热门资讯

一分钟内幕!科乐吉林麻将系统发... 一分钟内幕!科乐吉林麻将系统发牌规律,福建大玩家确实真的是有挂,技巧教程(有挂ai代打);所有人都在...
一分钟揭秘!微扑克辅助软件(透... 一分钟揭秘!微扑克辅助软件(透视辅助)确实是有挂(2024已更新)(哔哩哔哩);1、用户打开应用后不...
五分钟发现!广东雀神麻雀怎么赢... 五分钟发现!广东雀神麻雀怎么赢,朋朋棋牌都是是真的有挂,高科技教程(有挂方法)1、广东雀神麻雀怎么赢...
每日必看!人皇大厅吗(透明挂)... 每日必看!人皇大厅吗(透明挂)好像存在有挂(2026已更新)(哔哩哔哩);人皇大厅吗辅助器中分为三种...
重大科普!新华棋牌有挂吗(透视... 重大科普!新华棋牌有挂吗(透视)一直是有挂(2021已更新)(哔哩哔哩)1、完成新华棋牌有挂吗的残局...
二分钟内幕!微信小程序途游辅助... 二分钟内幕!微信小程序途游辅助器,掌中乐游戏中心其实存在有挂,微扑克教程(有挂规律)二分钟内幕!微信...
科技揭秘!jj斗地主系统控牌吗... 科技揭秘!jj斗地主系统控牌吗(透视)本来真的是有挂(2025已更新)(哔哩哔哩)1、科技揭秘!jj...
1分钟普及!哈灵麻将攻略小,微... 1分钟普及!哈灵麻将攻略小,微信小程序十三张好像存在有挂,规律教程(有挂技巧)哈灵麻将攻略小是一种具...
9分钟教程!科乐麻将有挂吗,传... 9分钟教程!科乐麻将有挂吗,传送屋高防版辅助(总是存在有挂)1、完成传送屋高防版辅助透视辅助安装,帮...
每日必看教程!兴动游戏辅助器下... 每日必看教程!兴动游戏辅助器下载(辅助)真是真的有挂(2025已更新)(哔哩哔哩)1、打开软件启动之...