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

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

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

一、題目描述

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

表描述:user_id:用戶(hù)的id;

              sigin_date:用戶(hù)的登陸日期。

二、解法分析

注:求解過(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è)用戶(hù)的連續(xù)登陸天數(shù)。

連續(xù)登陸標(biāo)識(shí) =(當(dāng)日登陸日期 - 用戶(hù)的登陸日期)- 開(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
;

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

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

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

3.2 計(jì)算結(jié)果

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

相關(guān)文章

最新評(píng)論