一、題目描述
求解用戶登陸信息表中，每個用戶連續登陸平臺的天數，連續登陸基礎為匯總日期必須登陸，表中每天只有一條用戶登陸數據（計算中不涉及天內去重）。
表描述：user_id：用戶的id；
sigin_date：用戶的登陸日期。
二、解法分析
注：求解過程有多種方式，下述求解解法為筆者思路，其他解法可在評論區交流。
思路：
該問題的突破的在于登陸時間，計算得到連續登陸標識，以標識分組為過濾條件，得到連續登陸的天數，最后以user_id分組，以count()函數求和得到每個用戶的連續登陸天數。
連續登陸標識 =（當日登陸日期 - 用戶的登陸日期）- 開窗排序的順序號(倒序)
三、求解過程及結果展示
1.數據準備
|
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.測試數據插入語句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.計算結果及預期結果對比
3.1 預期結果
| 匯總日期 | 用戶id | 登陸天數 |
| 2021-08-06 | uid_1 | 6 |
| 2021-08-06 | uid_2 | 2 |
| 2021-08-06 | uid_3 | 1 |
3.2 計算結果
以上就是SQL查詢語句求出用戶的連續登陸天數的詳細內容，更多關于SQL語句求用戶的連續登陸天數的資料請關注服務器之家其它相關文章！
原文鏈接：https://blog.csdn.net/Heng_bigdate_yan/article/details/120643783
