欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQL統(tǒng)計連續(xù)登陸3天用戶的實(shí)現(xiàn)示例

 更新時間：2024年05月24日 09:55:35   作者：Taerge0110  
最近有個需求,求連續(xù)登陸的這一批用戶,本文就來介紹一下SQL統(tǒng)計連續(xù)登陸3天用戶的實(shí)現(xiàn)示例,具有一定的參考價值,感興趣的可以了解一下

1. 數(shù)據(jù)準(zhǔn)備

-- 數(shù)據(jù)準(zhǔn)備
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. 對用戶數(shù)據(jù)進(jìn)行分組,按照活躍日期進(jìn)行排序(去重:防止有一天有多次活躍記錄)
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進(jìn)行差值計算,得到的日期如果是相等的,就說明活躍日期是連續(xù)的
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 進(jìn)行分組求和,篩選出連續(xù)登陸天數(shù)大于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函數(shù)

-- 1. 將active_date 上抬2行,不存在默認(rèn)為'0'(計算連續(xù)活躍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 -> 連續(xù)活躍了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 去重, 得到連續(xù)活躍天數(shù)>=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

到此這篇關(guān)于SQL統(tǒng)計連續(xù)登陸3天用戶的實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)SQL統(tǒng)計連續(xù)登陸3天用戶內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論