あるユーザーの連続ログイン日数を求めるSQLクエリ

2022-01-05 18:39:05

I. タイトル説明

各ユーザーは、ユーザーログイン情報テーブルでプラットフォームにログインしている連続した日数を解く、要約日付に基づいてログインする必要があり、テーブル内の1日あたりの唯一のユーザーログイン（計算で関与日以内に重複していない）。

テーブルの説明：user_id：ユーザーのid。

sigin_date: ユーザーのログイン日。

II. ソリューションの分析

注：このプロセスを解決する方法はたくさんあります。以下の解決策は著者のアイデアであり、他の解決策はコメント欄で交換できます。

アイデア

この問題の突破口はログイン時間にあり、連続ログインIDを計算し、IDのグループ化をフィルターとして連続ログイン日数を取得し、最後にuser_idのグループ化を利用してcount()関数でユーザーごとに連続ログイン日数を集計します。

連続ログイン数 = (当日のログイン数 - ユーザーのログイン数) - 窓付きソート順 (逆順)

III. 解答プロセスと結果表示

1. データの準備

-- 1. Table creation statement
drop table if exists test_sigindate_cnt;
create table test_sigindate_cnt(
    user_id string
    sigin_date string 
)
;
-- 2. Test data insert statement
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. 計算結果と期待値の比較

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文の詳細については、スクリプトの家の他の関連記事に注意を払うしてください