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

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

 更新時間:2021年10月09日 15:01:50   作者:Heng_bigdate_yan  
這篇文章主要是SQl語句的練習(xí),本文教大家如何用SQL查詢語句求出用戶的連續(xù)登陸天數(shù),有需要的朋友可以借鑒參考下,希望能夠有所幫助

一、題目描述

求解用戶登陸信息表中,每個用戶連續(xù)登陸平臺的天數(shù),連續(xù)登陸基礎(chǔ)為匯總?cè)掌诒仨毜顷?,表中每天只有一條用戶登陸數(shù)據(jù)(計算中不涉及天內(nèi)去重)。

表描述:user_id:用戶的id;

              sigin_date:用戶的登陸日期。

二、解法分析

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

思路:

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

連續(xù)登陸標(biāo)識 =(當(dāng)日登陸日期 - 用戶的登陸日期)- 開窗排序的順序號(倒序)

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

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

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

3.計算結(jié)果及預(yù)期結(jié)果對比

 3.1 預(yù)期結(jié)果 

匯總?cè)掌?/td> 用戶id 登陸天數(shù)
2021-08-06 uid_1 6
2021-08-06 uid_2 2
2021-08-06 uid_3 1

3.2 計算結(jié)果

以上就是SQL查詢語句求出用戶的連續(xù)登陸天數(shù)的詳細內(nèi)容,更多關(guān)于SQL語句求用戶的連續(xù)登陸天數(shù)的資料請關(guān)注腳本之家其它相關(guān)文章!

相關(guān)文章

最新評論