一、題目描述
求解用戶登陸信息表中，每個用戶連續(xù)登陸平臺的天數(shù)，連續(xù)登陸基礎(chǔ)為匯總?cè)掌诒仨毜顷懀碇忻刻熘挥幸粭l用戶登陸數(shù)據(jù)（計算中不涉及天內(nèi)去重）。
表描述：user_id：用戶的id；
sigin_date：用戶的登陸日期。
二、解法分析
注：求解過程有多種方式，下述求解解法為筆者思路，其他解法可在評論區(qū)交流。
思路：
該問題的突破的在于登陸時間，計算得到連續(xù)登陸標識，以標識分組為過濾條件，得到連續(xù)登陸的天數(shù)，最后以user_id分組，以count()函數(shù)求和得到每個用戶的連續(xù)登陸天數(shù)。
連續(xù)登陸標識 =（當日登陸日期 - 用戶的登陸日期）- 開窗排序的順序號(倒序)
三、求解過程及結(jié)果展示
1.數(shù)據(jù)準備
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
-- 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.計算過程
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
select user_id ,count(1) as sigin_cntfrom ( 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) twhere data_diff - row_num = -1group 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)注服務(wù)器之家其它相關(guān)文章！
原文鏈接：https://blog.csdn.net/Heng_bigdate_yan/article/details/120643783
