SQL Tips: WINDOW関数で月次売上と前月売上を正確に比較する際によくある落とし穴とその回避方法

こんにちは、マネーフォワードケッサイ株式会社でデータ周りをいろいろごにょごにょやっている tamiya です。

この記事では、SQL の小ネタとして月次売り上げテーブルで各月とその前月などの値を比較する際にやってしまいがちなミスと、想定通りの集計を行うためのクエリの書き方を解説します。

なお、集計例は全て BigQuery を用いたものを紹介しています。

問題設定

以下のような月毎の売り上げが記録されたテーブルを考えます。

WITH
  sample_monthly_payments AS (
  SELECT
    DATE("2023-04-01") AS payment_month,
    300 AS payment_amount
  UNION ALL
  SELECT
    DATE("2023-05-01") AS payment_month,
    500 AS payment_amount
  UNION ALL
  SELECT
    DATE("2023-07-01") AS payment_month,
    200 AS payment_amount
  UNION ALL
  SELECT
    DATE("2023-08-01") AS payment_month,
    700 AS payment_amount)
SELECT
  *
FROM
  sample_monthly_payments

ただし、レコードは売上のあった月のみ記録されるものとします。
上記のサンプルでは、6月分（2023-06-01）は売り上げがなかったため記録がありません。

このような設定下で、各月についてもう1列追加して「前月の売り上げ」と比較できるようにしたいと思います。

よくある誤り

パッと思いつくのは、WINDOW 関数を用いた以下のようなクエリです。
LAG関数を用いて1行前の売り上げ（payment_amount）を取ってくるものです。

# NG例
SELECT
  payment_month,
  payment_amount,
  LAG(payment_amount) OVER(ORDER BY payment_month) AS payment_amount_previous_month
FROM
  sample_monthly_payments

しかし、これでは想定していたような集計はできません。
なぜなら、上記の3行目 7月の部分を見ていただければわかるように、6月ではなく5月の売り上げである500円を取ってきてしまうからです。

このことから分かるように、売り上げ0円の時でも欠損なく毎月レコードが入っていないと、LAG関数を用いて単純に集計することができないのです。

したがって、今回のようにレコードが記録されない月があるケースでは、少し工夫が必要になります。

正解例

では、どうすればいいでしょうか？
無理やり6月分の行を追加して連番テーブルのようにしてしまうのも良いですが、データが大きくなったときに処理が大変になるので、もう少しシンプルに以下のようなクエリを書いてみました：

SELECT
  payment_month,
  payment_amount,
  IF(
    DATE_DIFF(payment_month, LAG(payment_month) OVER(ORDER BY payment_month), MONTH) = 1, -- 1行前に記録されているのが1ヶ月前の内容かを判定
    LAG(payment_amount) OVER(ORDER BY payment_month), -- 1行前が1ヶ月前のレコードなら、その行の売り上げを取ってくる
    0 -- 1行前が1ヶ月前のレコードでないなら、前月は売り上げがなかったことを意味するため0円
    ) AS payment_amount_previous_month
FROM
  sample_monthly_payments

これは何をしているかというと、1つ前の行に記録されているのが1ヶ月前のレコードかどうかを IF 文で判定し分岐を行っています。

もう少し詳しく書くと、

• もし1行前が1ヶ月前のものならば、1行前のレコードをを取ってくる

• もし1行前が1ヶ月前のものでないならば、0円とする

  • … 1ヶ月前は売り上げがなかったためレコードがないと考える

のように処理を行い集計しています。

発展（おまけ）： 2ヶ月前までの売り上げ総額を求める

同様にして、「2ヶ月前までの売り上げ総額を求めたい」という場合にはどうすればいいでしょう？

これもまた先ほどと同じように、1つ前の行に記録されているのが1ヶ月前のレコードかどうかで処理を分岐させます。

• 1行前のレコードが1ヶ月前の内容 → 2行前までの合計を取る

• 1行前のレコードが1ヶ月前の内容でない → 1行前までの合計を取る

  • … 1行前は2ヶ月前以前のものであるため

SELECT
  payment_month,
  payment_amount,
  IF(
    DATE_DIFF(payment_month, LAG(payment_month) OVER(ORDER BY payment_month), MONTH) = 1,
    LAG(payment_amount) OVER(ORDER BY payment_month),
    0
    ) AS payment_amount_previous_month,
  IF(
    DATE_DIFF(payment_month, LAG(payment_month) OVER(ORDER BY payment_month), MONTH) = 1, -- 1行前に記録されているのが1ヶ月前の内容かを判定
    SUM(payment_amount) OVER(ORDER BY payment_month ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING), -- 1行前が1ヶ月前のレコードなら、2行前までの売り上げを足し合わせる
    SUM(payment_amount) OVER(ORDER BY payment_month ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) -- 1行前が1ヶ月前のレコードでなければ、1行前は2ヶ月より前のレコードなので、1行前までの売り上げを足し合わせる
    ) AS total_payment_upto_2months_before
FROM
  sample_monthly_payments

まとめ

SQLで「行ごとに 1ヶ月前の値を取得する」などと言ったとき、慣れている人だとつい WINDOW関数の利用を思いつきますが、データの欠損がある場合などは注意が必要です。
そのようなケースでは IF 文で処理を分けるなどして気をつけてクエリを書きましょう。