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

2024-06-04 9231阅读

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

相关阅读：

1、VPS无法Ping通怎么办？解决指南与注意事项全解析

2、Linux系统VPS连接全攻略，一步步轻松上手

3、在 Linux 系统中，有多种工具可以创建 Wi-Fi 热点（将电脑作为无线接入点）以下是常见的几种方案，Linux 下哪个工具创建 Wi-Fi 热点最简单高效？，Linux下哪个工具创建Wi-Fi热点最简单高效？一键搞定无线共享！，在保留核心疑问的同时，通过一键搞定增强吸引力，并用无线共享替代部分专业术语提升普适性）

4、Linux 内核镜像是 Linux 操作系统的核心组件，通常以压缩文件形式分发，需解压后加载到内存中运行。以下是关于 Linux 内核镜像的详细说明，Linux内核镜像，为何它是操作系统的灵魂所在？，Linux内核镜像，为何它被称为操作系统的灵魂？

5、在Linux中，可以通过Shell脚本或命令行工具实现字符串与数字的相加操作。由于Shell中变量默认是字符串类型，直接使用 会导致字符串拼接而非数学运算，因此需要特殊处理。以下是几种常见方法，如何在Linux中正确实现字符串与数字的相加？，如何在Linux中让字符串与数字正确相加？5种实用方法揭秘！

    高速稳定云服务器25元起
    免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!

    相关阅读

    目录[+]