ZOZO Advent Calendar 2024 4日目の記事になります。
今年もAdvent Calenderの時期がやってきました！今年は4記事ほど書く予定なので頑張りたいと思います。

今回は連続したデータが続く際、その連続したデータの期間毎にまとめたい場合どういったSQLクエリを書けばよいか紹介します。言葉だと難しいので例を用いて紹介できればと思います。

はじめに

下記テーブルを例にどのような結果を得たいのか説明できればと思います。
(例1)

id	username	access_date
1	tom	2024-05-01
1	tom	2024-05-02
1	tom	2024-05-03
2	Thomas	2024-05-01
2	Thomas	2024-05-02
2	Thomas	2024-05-03
3	Sam	2024-05-01
3	Sam	2024-05-02
3	Sam	2024-05-03
4	Leo	2024-05-01
4	Leo	2024-05-02
4	Leo	2024-05-03

上記テーブルはユーザーがアクセスした日付を記録するテーブルです。access_dateカラムにログインした日付が保存されます。このテーブルに対してユーザーが連続何日アクセスしているか調べたいためSQLを用いて抽出します。
まず、どういう結果がほしいのか下記表にまとめます。 id毎にグルーピングを行い、各ユーザーの連続ログイン期間をaccess_start_dateとaccess_start_dateで表します。

id	username	access_start_date	access_end_date
1	tom	2024-05-01	2024-05-03
2	Thomas	2024-05-01	2024-05-03
3	Sam	2024-05-01	2024-05-03
4	Leo	2024-05-01	2024-05-03

クエリの作成

上記表の結果が得られるようなクエリを作成していきます。
まず簡単に思いつくクエリとしてMIN, MAX関数を利用しaccess_dateカラムに対してid毎の日付を抽出するようなクエリを例として作成してみます。下記クエリがMIN, MAX関数を利用したクエリになります。

SELECT
  id,
  username,
  MIN(access_date) AS access_start_date,
  MAX(access_date) AS access_end_date
FROM
  `sample_project.dataset.table`
GROUP BY
  id,
  username
ORDER BY id ASC

結果は下記スクリーンショットになります。先程提示した抽出結果と同じ結果になっています。

結果だけ見るとMIN, MAX関数を利用したクエリで良さそうですが、下記テーブルのような形になると得たい結果に対して差異が発生してしまいます。

(例2)

id	username	access_date
1	tom	2024-05-01
1	tom	2024-05-02
1	tom	2024-05-03
2	Thomas	2024-05-01
2	Thomas	2024-05-02
2	Thomas	2024-05-04
2	Thomas	2024-05-05
3	Sam	2024-05-02
3	Sam	2024-05-03
4	Leo	2024-05-01
4	Leo	2024-05-03

例1のテーブルと違い、同じidでも連続してアクセスした期間が分かれています。
上記テーブルに対して期待する結果は下記になります。id=2とid=4のユーザーは連続してアクセスしていない日付があるため、レコードが2つ存在します。

id	username	access_start_date	access_end_date
1	tom	2024-05-01	2024-05-03
2	Thomas	2024-05-01	2024-05-02
2	Thomas	2024-05-04	2024-05-05
3	Sam	2024-05-02	2024-05-03
4	Leo	2024-05-01	2024-05-01
4	Leo	2024-05-03	2024-05-03

例2のテーブルに対してMIN, MAX関数を利用したクエリを実行すると下記結果になります。途中で区切れた期間もまとまってしまっています。

上記の結果よりMIN, MAX関数のみでは連続した期間を抽出できないことがわかりました。このクエリを改良し、途中で区切れた期間に対しても考慮できるようにします。

改良を行い、途中で区切れた期間に対しても対応できるようになったクエリが下記になります。

WITH add_a_column_zero_if_the_record_matches_yesterday_otherwise_one as (
  SELECT
    *,
    CASE
      WHEN
          id = LAG(id) OVER (PARTITION BY id ORDER BY  access_date) AND
          username = LAG(username) OVER (PARTITION BY id ORDER BY  access_date) AND
          date_diff(access_date, LAG(access_date) OVER (PARTITION BY id ORDER BY access_date), DAY) = 1
      THEN 0 ELSE 1
    END AS zero_if_the_record_matches_yesterday_otherwise_one
  FROM `sample_project.dataset.table`
), same_record AS (
  SELECT
    *,
    SUM(zero_if_the_record_matches_yesterday_otherwise_one) OVER (PARTITION BY id ORDER BY access_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_value_with_the_same_id
  FROM add_a_column_zero_if_the_record_matches_yesterday_otherwise_one
)
SELECT
  id,
  username,
  MIN(access_date) AS access_start_date,
  MAX(access_date) AS access_end_date
FROM same_record
GROUP BY
  id,
  username,
  total_value_with_the_same_id
ORDER BY id ASC

クエリを実行した結果下記結果になります。例2のテーブルに対して期待した結果と同じ結果が得られることが確認できます。

クエリの説明

クエリをwith句単位で区切って説明します。
まず最初のクエリになります。

SELECT
    *,
    CASE
      WHEN
          id = LAG(id) OVER (PARTITION BY id ORDER BY  access_date) AND
          username = LAG(username) OVER (PARTITION BY id ORDER BY  access_date) AND
          date_diff(access_date, LAG(access_date) OVER (PARTITION BY id ORDER BY access_date), DAY) = 1
      THEN 0 ELSE 1
    END AS zero_if_the_record_matches_yesterday_otherwise_one
FROM `sample_project.dataset.table`

このクエリではwindow関数を利用し、PARTITION BYで区切ったレコードに対して現在のレコードに対する前のレコードの日付が昨日かどうか確認しています。前日と連続していれば0, 2日以上期間が空く場合は1が格納されるzero_if_the_record_matches_yesterday_otherwise_oneカラムを追加します。

このクエリの結果は下記になります。

次のクエリになります。

SELECT
    *,
    SUM(zero_if_the_record_matches_yesterday_otherwise_one) OVER (PARTITION BY id ORDER BY access_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_value_with_the_same_id
FROM add_a_column_zero_if_the_record_matches_yesterday_otherwise_one

このクエリでは前回のクエリで追加したzero_if_the_record_matches_yesterday_otherwise_oneカラムに対してwindow関数を利用し、現在行までの総和を格納するtotal_value_with_the_same_idカラムを追加しています。``zero_if_the_record_matches_yesterday_otherwise_one```カラムは2日以上期間が空く場合1が格納さるので、連続していないレコードがあった場合は+1されます。なので連続しているレコードに対して同じ数字を返します。

このクエリの結果は下記になります。

最後にtotal_value_with_the_same_idカラムを利用して連続した期間をまとめ上げます。

SELECT
  id,
  username,
  MIN(access_date) AS access_start_date,
  MAX(access_date) AS access_end_date
FROM same_record
GROUP BY
  id,
  username,
  total_value_with_the_same_id
ORDER BY id ASC

まとめ

うまく連続した期間をまとめることができました。テーブル内に途切れたレコードが発生せず、すべてのレコードが連続している場合はMIN, MAX関数のみで簡潔に書けますが、途切れたレコードを加味する場合はwindow関数を利用した複雑なクエリになってしまいました。より簡潔に書けるような方法がある場合は教えていただけると嬉しいです。

jongzの日記

BigQueryで連続した期間毎にまとめるSQLを作成する

はじめに

クエリの作成

クエリの説明

まとめ