SQL查詢語句求出用戶的連續(xù)登陸天數(shù)

更新時間：2021年10月09日 15:01:50 作者：Heng_bigdate_yan

這篇文章主要是SQl語句的練習，本文教大家如何用SQL查詢語句求出用戶的連續(xù)登陸天數(shù)，有需要的朋友可以借鑒參考下，希望能夠有所幫助

一、題目描述

求解用戶登陸信息表中，每個用戶連續(xù)登陸平臺的天數(shù)，連續(xù)登陸基礎為匯總日期必須登陸，表中每天只有一條用戶登陸數(shù)據(jù)（計算中不涉及天內去重）。

表描述：user_id：用戶的id；

sigin_date：用戶的登陸日期。

二、解法分析

注：求解過程有多種方式，下述求解解法為筆者思路，其他解法可在評論區(qū)交流。

思路：

該問題的突破的在于登陸時間，計算得到連續(xù)登陸標識，以標識分組為過濾條件，得到連續(xù)登陸的天數(shù)，最后以user_id分組，以count()函數(shù)求和得到每個用戶的連續(xù)登陸天數(shù)。

連續(xù)登陸標識 =（當日登陸日期 - 用戶的登陸日期）- 開窗排序的順序號(倒序)

三、求解過程及結果展示

1.數(shù)據(jù)準備

-- 1.建表語句
drop table if exists test_sigindate_cnt;
create table test_sigindate_cnt(
    user_id string
    ,sigin_date string 
)
;
-- 2.測試數(shù)據(jù)插入語句
insert overwrite table test_sigindate_cnt 
    select 'uid_1' as user_id,'2021-08-03' as sigin_date        
    union all
    select 'uid_1' as user_id,'2021-08-04' as sigin_date 
    union all
    select 'uid_1' as user_id,'2021-08-01' as sigin_date        
    union all
    select 'uid_1' as user_id,'2021-08-02' as sigin_date        
    union all
    select 'uid_1' as user_id,'2021-08-05' as sigin_date       
    union all
    select 'uid_1' as user_id,'2021-08-06' as sigin_date        
    union all
    select 'uid_2' as user_id,'2021-08-01' as sigin_date        
    union all
    select 'uid_2' as user_id,'2021-08-05' as sigin_date        
    union all
    select 'uid_2' as user_id,'2021-08-02' as sigin_date         
    union all
    select 'uid_2' as user_id,'2021-08-06' as sigin_date        
    union all
    select 'uid_3' as user_id,'2021-08-04' as sigin_date     
    union all
    select 'uid_3' as user_id,'2021-08-06' as sigin_date        
    union all
    select 'uid_4' as user_id,'2021-08-03' as sigin_date        
    union all
    select 'uid_4' as user_id,'2021-08-02' as sigin_date              
;

2.計算過程

select  user_id
        ,count(1) as sigin_cnt
from    (
    select  
            user_id
            ,datediff('2021-08-06',sigin_date)  as data_diff
            ,row_number() over (partition by user_id order by sigin_date desc) as row_num
    from    test_sigindate_cnt
) t
where   data_diff - row_num = -1
group by 
        user_id
;