ログインログからユーザーアクティビティを可視化：SQLによる連続アクセス日数分析

SQLで連続アクセス日数を最小値を求める方法

このチュートリアルでは、SQL Serverを使用して、ログデータなどの連続アクセス日数の最小値を計算する方法を説明します。この方法は、顧客のエンゲージメント分析、ログインアクティビティの監視、サブスクリプションの継続率追跡などに役立ちます。

要件

このチュートリアルを完了するには、以下の要件を満たす必要があります。

• SQL Serverデータベース
• テーブルにアクセス日時の列が含まれている

使用するデータ

この例では、次の構造を持つ user_logins テーブルを使用します。

方法

以下の手順で、連続アクセス日数の最小値を計算します。

1. 連続日数を示すフラグ列を作成する

最初のステップは、各ログインレコードの間にギャップがあるかどうかを示すフラグ列を作成することです。これを行うには、次のクエリを使用できます。

WITH cte_logins AS (
    SELECT
        user_id,
        login_date,
        LAG(login_date, 1) OVER (ORDER BY login_date) AS prev_login_date
    FROM user_logins
)
SELECT
    user_id,
    login_date,
    prev_login_date,
    CASE WHEN prev_login_date IS NULL OR login_date - prev_login_date > 1 THEN 1 ELSE 0 END AS is_gap
FROM cte_logins;

このクエリは、cte_logins というCTE（Common Table Expression）を作成します。このCTEには、元の user_logins テーブルのすべての列に加えて、prev_login_date という新しい列が含まれています。この列には、各レコードの前回のログイン日が入力されます。is_gap という新しい列も追加されます。この列には、現在のログイン日と前のログイン日の間にギャップがあるかどうかを示すフラグが格納されます。

1. 連続アクセス日数ブロックを識別する

次に、連続アクセス日数ブロックを識別する必要があります。これを行うには、次のクエリを使用できます。

WITH cte_logins_with_gaps AS (
    SELECT
        user_id,
        login_date,
        prev_login_date,
        is_gap,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS login_sequence
    FROM cte_logins
)
SELECT
    user_id,
    MIN(login_date) AS first_login_date,
    MAX(login_date) AS last_login_date,
    COUNT(*) AS consecutive_login_days
FROM cte_logins_with_gaps
WHERE is_gap = 0
GROUP BY user_id, login_sequence
ORDER BY user_id, first_login_date;

このクエリは、cte_logins_with_gaps という新しいCTEを作成します。このCTEには、元の cte_logins テーブルのすべての列に加えて、login_sequence という新しい列が含まれています。この列には、各ユーザーのログインシーケンス番号が入力されます。

次に、is_gap が 0 のレコードのみをフィルタリングし、user_id と login_sequence でグループ化して、各ユーザーの連続アクセス日数ブロックを計算します。

1. 最小連続アクセス日数を求める

最後に、各ユーザーの最小連続アクセス日数を求めます。これを行うには、次のクエリを使用できます。

SELECT
    user_id,
    MIN(consecutive_login_days) AS min_consecutive_login_days
FROM cte_logins_with_gaps
GROUP BY user_id;

このクエリは、user_id でグループ化し、各ユーザーの consecutive_login_days の最小値を計算します。

結果

このクエリを実行すると、次の結果が表示されます。

この結果、ユーザー1の最小連続アクセス日数は2日、ユーザー2の最小連続アクセス日数は5日、ユーザー3の最小連続アクセス日数は1日となります。

データセット

CREATE TABLE user_logins (
    user_id INT NOT NULL,
    login_date DATE NOT NULL
);

このテーブルには、ユーザーID (user_id) とログイン日時 (login_date) が格納されています。

クエリ

以下のクエリは、user_logins テーブルを使用して、各ユーザーの最小連続アクセス日数を計算します。

WITH cte_logins AS (
    SELECT
        user_id,
        login_date,
        LAG(login_date, 1) OVER (ORDER BY login_date) AS prev_login_date
    FROM user_logins
)
SELECT
    user_id,
    MIN(login_date) AS first_login_date,
    MAX(login_date) AS last_login_date,
    COUNT(*) AS consecutive_login_days
FROM cte_logins_with_gaps
WHERE is_gap = 0
GROUP BY user_id, login_sequence
ORDER BY user_id, first_login_date;

このクエリは、以下のステップを実行します。

1. cte_logins というCTEを作成します。このCTEには、元の user_logins テーブルのすべての列に加えて、prev_login_date という新しい列が含まれています。この列には、各レコードの前回のログイン日が入力されます。

解説

• CTE（Common Table Expression）を使用して、複雑なクエリをより読みやすく分割する方法
• ROW_NUMBER() 関数を使用して、各ユーザーのログインシーケンス番号を生成する方法
• WHERE 句を使用して、is_gap が 0 のレコードのみをフィルタリングする方法
• GROUP BY 句を使用して、user_id と login_sequence でレコードをグループ化する方法
• MIN() 関数を使用して、各ユーザーの consecutive_login_days の最小値を計算する方法

注意事項

窓関数は、特定の行のグループに対して集計計算を実行するために使用できます。この方法では、LAG() 関数と FIRST_VALUE() 関数を使用して、連続アクセス日数ブロックを識別し、最小値を計算できます。

SELECT
    user_id,
    MIN(login_date) OVER (PARTITION BY user_id ORDER BY login_date ROWS BETWEEN CURRENT ROW AND LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date)) AS first_login_date,
    MIN(login_date) OVER (PARTITION BY user_id ORDER BY login_date) + CAST(COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date) - 1 AS INT) AS last_login_date,
    COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date) AS consecutive_login_days
FROM user_logins
ORDER BY user_id, login_date;

1. PARTITION BY user_id ORDER BY login_date というウィンドウを定義します。このウィンドウは、各ユーザーのログインレコードをグループ化します。
2. LAG() 関数を使用して、現在のログインレコードの前回のログイン日を取得します。
3. FIRST_VALUE() 関数を使用して、現在のウィンドウ内の最初のログイン日を取得します。
4. COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date) を使用して、現在のウィンドウ内の連続ログイン日数をカウントします。
5. MIN(login_date) OVER (PARTITION BY user_id ORDER BY login_date) + CAST(COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date) - 1 AS INT) を使用して、現在のウィンドウ内の最後のログイン日を取得します。

方法 2: 再帰的CTEを使用する

再帰的CTEは、階層構造などの自己参照データクエリを処理するために使用できます。この方法では、再帰的CTEを使用して、連続アクセス日数ブロックをツリー構造として表現し、最小値を計算できます。

WITH RECURSIVE cte_logins_hierarchy AS (
    SELECT
        user_id,
        login_date,
        LAG(login_date, 1) OVER (ORDER BY login_date) AS prev_login_date,
        CASE WHEN prev_login_date IS NULL OR login_date - prev_login_date > 1 THEN 1 ELSE 0 END AS is_gap,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS login_sequence,
        1 AS level
    FROM user_logins
    UNION ALL
    SELECT
        cte_logins_hierarchy.user_id,
        cte_logins_hierarchy.login_date + 1,
        cte_logins_hierarchy.prev_login_date,
        cte_logins_hierarchy.is_gap,
        cte_logins_hierarchy.login_sequence,
        cte_logins_hierarchy.level + 1
    FROM cte_logins_hierarchy
    WHERE cte_logins_hierarchy.is_gap = 0
)
SELECT
    user_id,
    MIN(login_date) AS first_login_date,
    MAX(login_date) AS last_login_date,
    COUNT(*) AS consecutive_login_days
FROM cte_logins_hierarchy
GROUP BY user_id
ORDER BY user_id, first_login_date;

1. cte_logins_hierarchy という再帰的CTEを作成します。このCTEは、各ユーザーのログインレコードをツリー構造として表現します。
2. ベースステップでは、user_logins テーブルからすべてのログインレコードを選択します。
3. 再帰ステップでは、cte_logins_hierarchy テーブルから is_gap が 0 のレコードを選択し、ログイン日を1増やして再帰的に処理します。
4. user_id でグループ化し、各ユーザーの first_login_date、last_login_date、consecutive_login_days を計算します。