0Pricing
Coding Interview Prep · レッスン

ウィンドウフレームで累積合計を求める

順序付きフレームでSUM OVERを使い、累計を作成します。

「ウィンドウフレームで累積合計を求める」はCoddyKit上の無料Coding Interview Prepレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはCoding Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Coding Interview Prepコースには全4レッスンが含まれています。

累積合計に関する質問

ほぼすべてのアナリスト面接で、次のような質問が出ます。「時間の経過に沿った累積売上を表示してください。」 累積合計とは、先頭から現在の行までのすべてを積み上げ、行ごとに増えていく合計です。

ウィンドウ関数が存在する前は、受験者は低速な自己結合や相関サブクエリでこの問題を解いていました。現在期待される答えは SUM(...) OVER (ORDER BY ...) です。ウィンドウフレームを使った書き方を知っていれば、2012年頃以降の SQL を理解していることを示せます。

ORDER BY 付きウィンドウ合計の構造

累積合計は、集約関数をウィンドウ関数にしたものにすぎません。SUM(amount) はそのままにして、ORDER BY を含む OVER 句を追加します。

OVER 内の ORDER BY が累積にする要素です。この順序で行を積み上げるよう SQL に指示します。ORDER BY がなければ、SUM は行ごとに増えるのではなく、各行に対してパーティション全体の合計を返します。

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

ORDER BY がフレームを暗黙に指定する理由

ここは面接官が詳しく確認したがるポイントです。ウィンドウ集約関数に ORDER BY を追加すると、SQL は RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW というデフォルトのフレームを適用します。

このデフォルトこそが累積合計を作ります。パーティションの先頭から現在の行まで（現在の行を含む）のすべての行が対象になるためです。このデフォルトを理解していれば、累積合計が「そのまま機能する」理由も理解できます。

フレームを明示的に指定する

フレームは手動で書くこともできます。次の2つのクエリは同じ結果を返しますが、明示的に指定したバージョンなら、内部で何が起きているかを理解していることを面接官に示せます。

累積合計では、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW と書くのが最も安全な明示形式です。物理的な行を数えるため、RANGE による値のグループ化に起因する予期しない動作を避けられます（詳しくは次のレッスンで扱います）。

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

実践例：日別売上

月曜が100、火曜が50、水曜が200、木曜が75の4日間の売上を考えてみましょう。累積合計は左から右へ積み上がります。

  • 月曜：100
  • 火曜：100 + 50 = 150
  • 水曜：150 + 200 = 350
  • 木曜：350 + 75 = 425

最後の行は常に総合計と一致します。これは面接で伝えられる簡単な妥当性チェックです。最後の累積合計の値は、全体に対する SUM(amount) と一致しなければなりません。

PARTITION BY でグループごとにリセットする

実際の問題では、全体の累積合計ではなく、通常は顧客ごとまたは地域ごとの累積合計が求められます。PARTITION BY を追加すると、各パーティションの先頭で累積が再開されます。

イメージとしては、PARTITION BY が行を独立したバケットに分割し、ORDER BY とフレームが各バケット内で個別に実行されます。

SELECT
  customer_id,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date
  ) AS customer_running_total
FROM sales;

同順位による落とし穴

2つの行が同じ ORDER BY 値（同じ日に2件の売上など）を持つ場合、デフォルトの RANGE フレームはそれらをピアとして扱い、両方の金額を含めた同じ累積合計を返します。

同じ値の場合でも行ごとに厳密に増加させたいなら、ROWS フレームに切り替え、ORDER BY に sale_date, id のような一意のタイブレーカーを追加してください。面接官は、気付けるかどうかを見るために重複する日付を意図的に用意することがあります。

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

カウントの累積合計

累積の仕組みは SUM に限りません。どの集約関数でもウィンドウ関数として使えるため、累積カウント、累積平均、累積最大値を作成できます。

注文数の累積カウントは、ダッシュボードでよく使われる指標です。各日付の時点で、これまでに何件の注文を受けたかを表します。

SELECT
  order_date,
  COUNT(*) OVER (
    ORDER BY order_date
  ) AS orders_to_date
FROM orders;

従来の方法：相関サブクエリ

面接官は、理解の深さを確認するために、ウィンドウ関数を使わずに累積合計を求めるよう尋ねることがあります。ウィンドウ関数以前の典型的な解決策は、過去の各行を再集計する相関サブクエリです。

動作はしますが、計算量は O(n squared) です。行ごとにテーブルを再走査するためです。ウィンドウ関数がなぜ相関サブクエリに取って代わったのかを理解していると示すために、この点にも触れてください。

SELECT
  s.sale_date,
  s.amount,
  (SELECT SUM(s2.amount)
   FROM sales s2
   WHERE s2.sale_date <= s.sale_date) AS running_total
FROM sales s
ORDER BY s.sale_date;

フィルタリングとウィンドウ関数の結果

よくある追加質問は、「累積合計が1000を超えた日だけを表示してください」です。フレームの計算は WHERE の実行後に行われるため、ウィンドウ関数を WHERE に入れることはできません。

累積合計を CTE またはサブクエリで計算し、その後で外側のクエリに対してフィルタリングするのが解決策です。これは、すべてのウィンドウ関数に適用される、同じラッピングのルールです。

WITH t AS (
  SELECT
    sale_date,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
  FROM sales
)
SELECT *
FROM t
WHERE running_total >= 1000;

面接で説明すべきポイント

累積合計の答えを説明するときは、満点を取るために次の点を順に述べてください。

  • SUM OVER (ORDER BY ...) が累積形式です。
  • ORDER BY を追加すると、UNBOUNDED PRECEDING から CURRENT ROW までのデフォルトフレームが作られます。
  • グループごとにリセットするには PARTITION BY を使います。
  • 重複値による落とし穴を避けるには、一意のタイブレーカーと ROWS フレームを追加します。
  • 結果を条件で絞り込むには、CTE でラップします。

確認問題

デフォルトのフレームについて理解できているか確認しましょう。

まとめ：累積合計

累積合計は、順序付きのウィンドウ集約です。SUM(amount) OVER (ORDER BY sale_date) は、暗黙に適用される UNBOUNDED PRECEDING から CURRENT ROW までのフレームによって、パーティションの先頭から現在の行までを累積します。

PARTITION BY でグループごとにリセットし、並び順の重複値に対処するにはタイブレーカーと ROWS フレームを追加します。累積値でフィルタリングする必要がある場合は、必ず CTE でラップしてください。次は、このレッスンで触れた ROWS と RANGE の違いを詳しく見ていきます。

よくある質問

「ウィンドウフレームで累積合計を求める」レッスンは無料ですか？

はい。「ウィンドウフレームで累積合計を求める」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Coding Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Coding Interview Prepコースには全4レッスンが含まれています。

「ウィンドウフレームで累積合計を求める」で何を学びますか？

順序付きフレームでSUM OVERを使い、累計を作成します。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Coding Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのCoding Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。

「ウィンドウフレームで累積合計を求める」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このCoding Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのCoding Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. ウィンドウフレームで累積合計を求める
  2. ROWSとRANGEのフレーム指定
  3. スライディングウィンドウで移動平均を求める
  4. 累積分布と全体に占める割合
← Coding Interview Prepに戻る