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

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

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

一、題目描述

求解用戶登陸信息表中，每個(gè)用戶連續(xù)登陸平臺(tái)的天數(shù)，連續(xù)登陸基礎(chǔ)為匯總?cè)掌诒仨毜顷懀碇忻刻熘挥幸粭l用戶登陸數(shù)據(jù)（計(jì)算中不涉及天內(nèi)去重）。

表描述：user_id：用戶的id；

sigin_date：用戶的登陸日期。

二、解法分析

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

思路：

該問(wèn)題的突破的在于登陸時(shí)間，計(jì)算得到連續(xù)登陸標(biāo)識(shí)，以標(biāo)識(shí)分組為過(guò)濾條件，得到連續(xù)登陸的天數(shù)，最后以u(píng)ser_id分組，以count()函數(shù)求和得到每個(gè)用戶的連續(xù)登陸天數(shù)。

連續(xù)登陸標(biāo)識(shí) =（當(dāng)日登陸日期 - 用戶的登陸日期）- 開(kāi)窗排序的順序號(hào)(倒序)

三、求解過(guò)程及結(jié)果展示

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

-- 1.建表語(yǔ)句
drop table if exists test_sigindate_cnt;
create table test_sigindate_cnt(
    user_id string
    ,sigin_date string 
)
;
-- 2.測(cè)試數(shù)據(jù)插入語(yǔ)句
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.計(jì)算過(guò)程

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
;