【ITニュース解説】Month-over-Month Growth in SQL: LAG, the Growth Formula, and the Traps

月次成長率の算出は、ビジネスの健全性を測る上で非常に重要な指標だが、SQLでこれを正確に計算するにはいくつかの落とし穴がある。システムエンジニアとしてデータ分析に携わる際、これらの罠を理解し、適切に対処することはデータの信頼性を保証するために不可欠である。

まず、月次成長率とは、今月の値から先月の値を引き、その結果を先月の値で割ることで求められる。例えば、1月の売上が250、2月の売上が300だった場合、(300 - 250) / 250 = 0.2、つまり20%の成長となる。この計算結果は「2月の売上は1月から20%成長し、1月は250、2月は300だった」のように、成長率だけでなく元の数値を併せて伝えることが重要だ。これは、割合だけでは規模感が伝わらなかったり、逆に変化の幅だけでは比較が難しかったりするためである。

SQLで月次成長率を計算する最初のステップは、データを「月ごとの売上」のように、分析したい粒度（これを「粒度」と呼ぶ）に集約することだ。例えば、個々の注文データがあるテーブルから月ごとの売上を計算するには、GROUP BY句とSUM関数を使って、order_dateから月を取り出し、その月のamount（金額）を合計する。strftime('%Y-%m', order_date)のような関数を使って日付から年と月を抽出し、それに基づいてグループ化すれば、各月の合計売上を1行で表現できる。

次に、この月ごとのデータに対して、前の月の売上を現在の月の行に持ってきたい。ここでLAG()関数が登場する。LAG(カラム名) OVER (ORDER BY カラム名)という形で使用するこの関数は、指定された順序（ORDER BY句で指定）で並べられた行の中で、「一つ前の行」の指定されたカラムの値を現在の行にコピーする。つまり、月ごとの売上が並んだテーブルがあれば、LAG(revenue) OVER (ORDER BY month)と記述することで、各月の売上行にその前の月の売上値を追加できるわけだ。

しかし、このLAG()関数を使う際に、いくつかの罠が存在する。 一つ目は「整数除算の罠」だ。多くのデータベースでは、整数同士の割り算は小数点以下を切り捨てる。例えば、50を250で割ると、数学的には0.2だが、整数除算では0になってしまう。これにより、成長率が実際には1%でも2%でも、全て0%と報告されてしまう可能性がある。この問題を回避するには、計算の途中で数値を浮動小数点数に変換する必要がある。最も簡単な方法は、計算式のどこかに100.0のような浮動小数点数を掛けることだ。100.0 * (revenue - prev_revenue) / prev_revenueのように記述すれば、結果は浮動小数点数として正確に計算される。

二つ目は「最初の月のNULLの罠」である。データ期間の最初の月は、その前に比較する月が存在しないため、LAG()関数はNULLを返す。このNULLを使って計算を行うと、結果もNULLになるのがSQLの仕様だ。これは実は正しい挙動であり、最初の月の成長率は不明であるという事実を正直に示している。これを無理に0%などと「修正」してしまうと、存在しない成長を主張することになるため、最初の月はNULLのままにしておくのが最も誠実な対応である。

そして、最も注意すべき三つ目の罠が「欠落した月の罠」だ。LAG()関数は「カレンダー上の前の月」ではなく、「データ上の前の行」を取得する。もし、ある月に注文が全くなく、GROUP BYで集約してもその月の行が生成されない場合、LAG()はカレンダー上では2ヶ月前、あるいはそれ以上前の月のデータを参照してしまう。例えば、1月、2月、3月とデータがあり、4月には注文が一つもなかったため行がなく、次に5月のデータがあるとする。この場合、5月の行に対するLAG()は、データ上で直前にある3月の売上を取得してしまう。これにより、5月の成長率が3月との比較で算出され、実際の4月の売上ゼロという状況が見過ごされ、誤った月次成長率が報告されることになる。記事の例では、この欠落によって5月の売上250は3月の売上360と比較され、-30.6%という誤った成長率が算出された。

この「欠落した月の罠」を解決するには、すべてのカレンダー上の月に対応する行を確実に存在させる必要がある。そのために「カレンダーテーブル」を生成し、元のデータと結合する。カレンダーテーブルは、分析対象期間のすべての月を網羅した一時的なテーブルとして作成できる。SQLITEではWITH RECURSIVE句を使い、指定した開始月から終了月までを連続的に生成する方法がある。PostgreSQLにはgenerate_seriesのような便利な関数もある。 このカレンダーテーブルと、先ほど月ごとに集約した売上データをLEFT JOINで結合する。LEFT JOINを使うことで、カレンダーテーブルのすべての月（行）が保持され、もし売上データがない月があったとしても、そのカレンダー月の行は残る。このとき、売上がなかった月では結合した結果のrevenueカラムがNULLになるため、COALESCE(m.revenue, 0)を使ってNULLを0に変換する。これで、データのない月も「売上0」の行として存在することになる。 さらに、売上0の月からの成長率を計算する際、分母が0になるとエラーになるため、NULLIF(LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month), 0)のようにNULLIF関数を組み合わせる。これは、分母となる前の月の売上が0の場合に、その値をNULLに置き換えることで、0による除算を回避し、結果的に成長率をNULLとして出力させるための工夫である。

これらの対策を施したクエリを実行することで、たとえ途中に注文が全くない月があっても、その月は「売上0」として正しく扱われ、次月との比較もカレンダー上の隣接する月と行われるようになる。これにより、例えば4月が売上0であれば、4月の成長率は-100%（360から0への下落）と正しく表示され、5月の売上250は4月の売上0と比較され、その成長率は「未定義（NULL）」と正確に報告されるようになる。

最後に、計算された成長率を解釈し、報告する際の追加の考慮事項を述べる。パーセンテージの変化は元の数値を伴って報告すべきだ。例えば、「30%減少」と「30%増加」は、開始点が同じでも最終的な数値が異なるため、元の数値を併記することで誤解を防ぐ。SQLでは計算結果を数値のまま保持し、小数点以下はROUND関数で適切な桁数に丸め、パーセント記号の追加や表示形式の調整はレポートツール側で行うのが良い習慣である。また、月次成長率は季節変動の影響を受けやすいため、長期的なトレンドを見る際には、前年同月比（YoY）の成長率も合わせて確認することが推奨される。

これらの罠とその対処法を理解し実践することは、システムエンジニアとして信頼性の高いデータ分析を行う上で不可欠なスキルだ。特に、LAG関数を使用する際には、データがカレンダー上のすべての期間を網羅しているかを常に確認する習慣を持つことが、誤った分析結果を防ぐための最も重要な心構えとなるだろう。