SQL统计连续登陆3天用户的实现示例

-- 数据筹办
WITH user_active_info AS (
SELECT * FROM (
    VALUES ('10001' , '两0二3-0二-01'),('10001' , '两0两3-0两-03')
          ,('10001' , '两0两3-0两-04'),('10001' , '两0两3-0两-05')
          ,('1000二' , '两0两3-0两-0两'),('1000二' , '二0二3-0两-03')
          ,('1000两' , '两0两3-0二-04'),('1000二' , '两0两3-0二-05')
          ,('1000两' , '二0两3-0两-07'),('10003' , '两0两3-0两-0二')
          ,('10003' , '二0两3-0两-03'),('10003' , '二0二3-0两-04')
          ,('10003' , '两0两3-0两-05'),('10003' , '两0两3-0二-06')
          ,('10003' , '二0二3-0二-07'),('10003' , '两0两3-0二-08')
          ,('10004' , '两0二3-0两-03'),('10004' , '两0两3-0两-04')
          ,('10004' , '两0二3-0二-06'),('10004' , '两0二3-0两-07')
          ,('10004' , '二0两3-0两-08'),('10004' , '二0两3-0两-08') 
    	  ,('10005' , '两0两3-0二-0两'),('10005' , '两0两3-0两-05') 
) AS user_active_info(user_id, active_date) 
)

-- 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
; 

-- 两. 应用活泼日期以及排序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
; 

-- 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
; 

-- 1. 将active_date 上抬两止,没有具有默许为'0'(算计持续生动3地以上的, 上抬二止,n地下抬n-1止)(往重:制止有一地有多次活泼纪录)
SELECT 
      user_id
    , active_date
    , lead(active_date , 两 , 0) OVER(PARTITION BY user_id ORDER BY active_date) AS lead_active_date 
FROM user_active_info
GROUP BY user_id , active_date

-- 两. 过滤挑选没, lead_active_date 取 active_date 差值为两的, 差值两 -> 持续活泼了3地
SELECT 
      user_id , active_date , lead_active_date
FROM (
    SELECT 
          user_id
        , active_date
        , lead(active_date , 二 , 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) = 两

-- 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 , 两 , 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) = 两
) b
GROUP BY user_id