エムスリーテックブログ

エムスリー(m3)のエンジニア・開発メンバーによる技術ブログです

SQL課題：月の集合を連続した期間の集合にまとめてください

BigQuery SQL

データ基盤チーム & Unit9（エビデンス創出プロダクトチーム）ブログリレー 4日目は、SQLプログラミングのお話をお届けします。Unit9エンジニアの三浦[記事一覧 ]です。昨日は木田さんの『巨大テーブルにインデックスを追加したい、Flywayで』でした。

今回の課題はタイトル通り、月の集合（たとえば、ユーザーがアクティブであった月の集合）から「何年何月から何年何月まで連続していた（アクティブだった）」という期間の集合に変換せよというものです。連続した期間ってのがまとまっていると判定で便利な局面があるんですよね。具体的にどう役に立つかは後述しますが、大変に実用を意識したデータ基盤プログラミング課題であります。

さて私自身がややこしいコードの書かれた記事を見るとコード部分を読み流してしまいがちなので偉そうなことが言えないのですが、やはりプログラミング課題の解説はコードを精読してこそです！　もし今精読する時間が取れないということでしたら、「後で読む」ブックマークしていただけるのももちろん嬉しいのですが紙にプリントアウトする方をよりおすすめしておきます。

STORY...

ユーザー数が伸びているサービスでは、ある期間のユーザー全体の何かしらコンバージョン状況をチャートにしようとすると、ある問題があります。ユーザーの総数が増えているわけですから、コンバージョン数が右肩上がりだったとしてもそれは単にユーザー数が増えた結果なのと施策に効果があったのと見分けがつかないという点です。

そうしたら、チャートの対象期間開始時点ですでに存在していたユーザーだけに絞り込んで集計しては？　はい、それでだいたいバイアスははずせるのですが、それでも今度は対象期間中に休眠してしまったユーザーの存在が見かけ上の右肩下がりを生んでしまいかねません。チャートの対象期間中、ずっとアクティブだったユーザーだけに絞り込みたいのです。

ここで、ユーザーごとに「月単位で連続でアクティブであった期間」のリストが存在すれば、チャートの対象期間を含むような連続アクティブ期間を持つユーザーという条件で絞るのが非常に容易になります。これが、月の集合を連続した期間の集合に変換したいという動機になります。

さて月の集合を連続した期間に変換するという処理は、手続き型プログラミングであればとても書きやすいものです。いったんソートしてからループでスキャンし、前のループ回の月と連続していなければそこで期間を区切る、さもなければカレント期間を延ばしていくというだけです。とはいえデータパイプラインをDataformやdbtのようなSQLベースのツールで書いている場合、この変換だけのためにわざわざ別ミドルウェアを導入というのも大げさです。SQLで書きたい。そこで今回の課題とあいなります。

課題の確認

問題の定義に進みますと、INPUTはこう。user_idは文字列型で、year_monthは月初日付を表すDATE型とします（ただ、読みやすさのためにYYYY-MM形式で表記して-01を省略しています）。DATE型・月初日付であるゆえ、INTERVAL 1 MONTHを足したり引いたりすることで前月や翌月という日付を得られるものとなります。

user_id year_month
桃太郎 2025-01
桃太郎 2025-02
桃太郎 2025-03
桃太郎 2025-05
かぐや姫 2024-12
かぐや姫 2025-01

OUTPUTはこう

user_id start_month end_month
桃太郎 2025-01 2025-03
桃太郎 2025-05 2025-05
かぐや姫 2024-12 2025-01

としたいということになります。桃太郎さんは2025年4月にアクティブではなかったので、2025年1月から3月までの期間と2025年5月の期間に分かれていますね。BigQueryのようなと言いましたことですし、ターゲットDB製品はBigQueryとしましょう。

手元でBigQueryを動かしながら試したい人のためのWITH句もご用意しておきます。

WITH active_months AS (
  SELECT * FROM UNNEST(
    ARRAY<STRUCT<user_id STRING, year_month DATE>>[
      ('桃太郎', '2025-01-01'),
      ('桃太郎', '2025-02-01'),
      ('桃太郎', '2025-03-01'),
      ('桃太郎', '2025-05-01'),
      ('かぐや姫', '2024-12-01'),
      ('かぐや姫', '2025-01-01')
    ])
)

解法のアプローチ

「連続して存在していること」をどうやって判定しましょう？　連続して存在する月をたどることができれば期間の開始月やそこから始まって連続が終わる終了月も求まるわけなのですが。

SQL書くときはこう発想します：「過去で、前月がアクティブでなかった月のうち最も新しい月」

この発想でいくと次のような式により、各アクティブ月について過去方向に連続している範囲（「start_month」と名付けましょう）を付けることができます。

SELECT
  this.user_id,
  this.year_month,
  MAX(CASE WHEN prev.year_month IS NULL THEN this.year_month END)
    OVER user_history AS start_month,
FROM
  active_months this
  LEFT JOIN active_months prev
    ON this.user_id = prev.user_id 
      AND prev.year_month = this.year_month - INTERVAL 1 MONTH
WINDOW user_history AS (
  PARTITION BY this.user_id
  ORDER BY this.year_month
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

クエリ式の読解が難しいかもしれませんが、まずはちょっと実行してみましょう。するとこうなります。

user_id year_month start_month
桃太郎 2025-01 2025-01
桃太郎 2025-02 2025-01
桃太郎 2025-03 2025-01
桃太郎 2025-05 2025-05
かぐや姫 2024-12 2024-12
かぐや姫 2025-01 2024-12

なるほど、INPUTのテーブルにstart_month列を付け加えることができています。これは、名前の通りyear_monthの月につながる連続が始まった月ですね。たしかに桃太郎さんの1月から3月までのアクティビティはすべて1月から始まったということになっており、5月のアクティビティは5月から始まったということになっています。

読解はおいておいて、ここまでできればゴールはもう見えています。あとはstart_monthごとにグルーピングして、year_monthの最大値を求めればそれが期間の終わり、end_monthとなります。次のような形。

WITH start_month_appended AS (
  /*
   上記の、start_monthを付与するクエリ式
  */
)
-- start_monthごとにグルーピングすればyear_monthの最大値が期間の終了月になる
SELECT
    user_id,
    start_month,
    MAX(year_month) AS end_month,
FROM
    start_month_appended
GROUP BY
    user_id, start_month
ORDER BY
    user_id, start_month

では、ゴールへつながるとわかったところで先程のクエリを読めるようになってみましょう。

ウィンドウ関数のあるクエリを読む

さて問題の、start_monthを付与するクエリ式をもう一度見てみます。

まずSELECT句に

MAX(...) OVER ...

という記述が見えます。これがウィンドウ関数です。ウィンドウ関数ってなんだったかおさらいしましょう。

SQLでの計算は通常、SELECT句でもWHERE句でも【いま注目している行】の中のカラム同士でだけ行えます。他の行の値は参照できません。これでは今回のように「前の月から連続している」ような判定が必要な場合には手も足も出ませんね。ウィンドウ関数とは、集約キーを指定したうえで、同じ集約キーを持つ行全体を「ウィンドウ」としそのウィンドウ全体を計算対象にできるというものです。なるほど、ウィンドウ関数を使えば今回のように前後の行の値に影響される計算もできそうですね！

今回で言えば集約キーをuser_idとしてウィンドウにすることになりそうです。そしてウィンドウは日付昇順で並べたものである必要がありますね。こういうウィンドウを定義するには

(PARTITION BY user_id ORDER BY year_month)

と書きます。実際のコードではまたちょっと違う書き方をしていますが、これは後ほど説明します。

FROMのあとの

  active_months this
  LEFT JOIN active_months prev
    ON this.user_id = prev.user_id 
      AND prev.year_month = this.year_month - INTERVAL 1 MONTH

ここはいわゆる自己結合ですね。同じユーザーの前月分レコードをprevとして結合しようとしています。LEFT JOINですから、アクティブな前月が存在しなければprev.year_monthはNULLになります。さて問題はそのあと。なんだこの記法は。

WINDOW user_history AS (
  PARTITION BY this.user_id
  ORDER BY this.year_month
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

ウィンドウ関数を書き慣れている方でも意外にこの書き方は初見かもしれません。WINDOW 【ウィンドウ名】 AS ...記法はウィンドウ定義に名前をつけてSELECT句内で利用できるようにするものです。これをしない場合は、SELECT句内でMAX(...) OVER (PARTITION BY ...)のように書きますよね。この(PARTITION BY ...)部分を繰り返し書かないといけないようなケースで威力を発揮する記法です。

えっ今回は一回しかウィンドウ関数を呼んでいないではないかと？　はい、その通りなんですがSELECT句をトップヘビーにせず少しでも読みやすく保ちたいのと、あとはWINDOW句をご紹介したくてでした！

さてウィンドウ定義だとわかったところでまだ読み慣れない文字列が。ウィンドウ定義内の最終行、

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

これですね。えっ何これって方も、なんとなく知っているけどすぐ忘れちゃうんだよねって方もご安心を。これは、「どこからどこまでをウィンドウとするか」の指定です。ORDER BYとあわせると、「日付昇順で、最初の行（もっとも古い日付）UNBOUNDED PRECEDING以降、この行CURRENT ROWまで」をウィンドウとする、という意味になります。これは実はウィンドウ定義のデフォルトの指定なので書かなくてもこの通り動くのですが、今回のようにウィンドウ範囲が意味上重要であるなら明示しておくことで後から読む人の混乱を防ぐという意味合いが大きいです。

このウィンドウ内での「アクティブな前月がない月」の最大値だったら、たしかに当該月から連続する最も古い月、すなわちstart_monthです。アクティブな前月がない月というのは、次のCASE式で書けますから

CASE WHEN prev.year_month IS NULL THEN this.year_month END

これをMAXで集約すればよい、というわけです。もちろんウィンドウuser_historyを指定して。

すべてあわせますと、再掲になりますが次のSQLでstart_monthを付与できると読めました。

SELECT
  this.user_id,
  this.year_month,
  MAX(CASE WHEN prev.year_month IS NULL THEN this.year_month END)
    OVER user_history AS start_month,
FROM
  active_months this
  LEFT JOIN active_months prev
    ON this.user_id = prev.user_id 
      AND prev.year_month = this.year_month - INTERVAL 1 MONTH
WINDOW user_history AS (
  PARTITION BY this.user_id
  ORDER BY this.year_month
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

GROUP BYをいらなくする

さて最初に、start_monthを付けることさえできればあとはGROUP BY start_monthでまとめて終了とできる、と解説したのですがこのGROUP BYってなくせないでしょうか。上で書いた通り、GROUP BYするためにサブクエリ化しないといけませんし。

要は、start_monthごとに期間末がとれればいいのですから、集約して最大値を求めるとかではなくて期間末の月だけに最初から絞ってしまえば目的が達成できるんでは？　と思いますよね。

WHERE 【期間末である】

とするということ。もう少し具体的には、「次の月」を表す自己結合nextをLEFT JOINしてこんな感じに

  ...
  LEFT JOIN active_months next
    ON this.user_id = next.user_id
      AND next.year_month = this.year_month + INTERVAL 1 MONTH
WHERE
  next.year_month IS NULL

ところが残念、この式だと正しく算出できません。WHEREで期間最終月にだけ絞ると、その絞り込み結果を対象にウィンドウ関数が動くことになってしまうからです。それではアクティブ月の連続性がぐちゃぐちゃですよね。

SQLの一般的な答えとしては、サブクエリ化して次段でWHERE句を書くことになります。ただ、サブクエリ化をしないでもウィンドウ関数計算後に行を絞り込みたいというニーズは今回に限らず幅広くあることですから、それ用の拡張構文がBigQueryには、いえRedshiftやSnowflakeなど分析用DBには広く用意されています。それがQUALIFY句です。

QUALIFY句はWHERE句と同様に行を絞り込むのですが、ウィンドウ関数の計算後に働くという点が異なります。つまり先程のうまく動かないという式はWHEREをQUALIFYに変えるだけで意図通り動くようになり、全体として次のようにサブクエリのない形に書き上がりました。

SELECT
  this.user_id,
  MAX(CASE WHEN prev.year_month IS NULL THEN this.year_month END)
    OVER user_history AS start_month,
  this.year_month AS end_month,
FROM
  active_months this
  LEFT JOIN active_months prev
    ON this.user_id = prev.user_id 
      AND prev.year_month = this.year_month - INTERVAL 1 MONTH
  LEFT JOIN active_months next
    ON this.user_id = next.user_id
      AND next.year_month = this.year_month + INTERVAL 1 MONTH   
QUALIFY
  next.year_month IS NULL
WINDOW user_history AS (
  PARTITION BY this.user_id
  ORDER BY this.year_month
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

さあこれを実行すれば

user_id start_month end_month
桃太郎 2025-01 2025-03
桃太郎 2025-05 2025-05
かぐや姫 2024-12 2025-01

この通り、目的通りのOUTPUTを得ることができました。

まとめ

今回の課題でこんなことがわかりました

  • 手続き型プログラミング的な要件をSQLで実現するには、「〇〇順で最後の〇〇な要素」のような宣言的な条件に読み替える発想が出発点になります
  • 多くの場合、手続き的要件をSQL化するときにはウィンドウ関数が欠かせません
  • そうしたプログラミングでウィンドウ内ORDER BYを書く際には範囲指定（ROWS BETWEEN）が重要になってきます
  • SQL非標準の拡張構文QUALIFY句は便利。ここ一番でQUALIFYが決まると最高に気持ちいいです

We are Hiring!

一緒に楽しくSQL開発やりませんか。分析DBなんて全然経験がなくてもそこは大丈夫。人は生まれながらにしてデータエンジニアであるのではなく、アプリ開発でRDBやっていた人が運用保守しているうちにデータ基盤に足突っ込んでデータエンジニアになってしまうことが往々にしてあるのです。ああ、それは私のことでした。カジュアル面談でお話しましょう。

jobs.m3.com