SQL查詢語句求出用戶的連續(xù)登陸天數(shù)
一、題目描述
求解用戶登陸信息表中，每個(gè)用戶連續(xù)登陸平臺(tái)的天數(shù)，連續(xù)登陸基礎(chǔ)為匯總?cè)掌诒仨毜顷?，表中每天只有一條用戶登陸數(shù)據(jù)（計(jì)算中不涉及天內(nèi)去重）。
表描述：user_id：用戶的id；
sigin_date：用戶的登陸日期。
二、解法分析
注：求解過程有多種方式，下述求解解法為筆者思路，其他解法可在評(píng)論區(qū)交流。
思路：
該問題的突破的在于登陸時(shí)間，計(jì)算得到連續(xù)登陸標(biāo)識(shí)，以標(biāo)識(shí)分組為過濾條件，得到連續(xù)登陸的天數(shù)，最后以u(píng)ser_id分組，以count()函數(shù)求和得到每個(gè)用戶的連續(xù)登陸天數(shù)。
連續(xù)登陸標(biāo)識(shí) =（當(dāng)日登陸日期 - 用戶的登陸日期）- 開窗排序的順序號(hào)(倒序)
三、求解過程及結(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.測(cè)試數(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.計(jì)算過程
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> | 用戶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ù)登陸天數(shù)的詳細(xì)內(nèi)容，更多關(guān)于SQL語句求用戶的連續(xù)登陸天數(shù)的資料請(qǐng)關(guān)注本站其它相關(guān)文章！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信