分析SQLとウィンドウ関数

なぜ集約だけでは分析クエリが書きにくいのか

「地域ごとの売上合計」なら GROUP BY region で済みます。しかし実務の分析要件はもう一段ひねられている。「各注文が、その地域の中で売上何位か」「直近7日移動平均」「月初からの累積和」「前月比」——これらに共通するのは、畳んだ集計値と、畳む前の個々の行を同時に必要とすることです。

集約（GROUP BY）は本質的に複数行を1行へ畳んで行数を減らす演算です。地域ごとに1行の合計だけが残り、個々の注文行は消えます。だから「各注文の値」と「その地域の合計」を並べて比較したいと、集約結果を元の表へ自己結合して戻すか、相関サブクエリを行ごとに再評価する羽目になる。前者は同じデータを2回スキャンし、後者は行数ぶんサブクエリが走って計算量が跳ね上がります。ランキングに至っては、素のSQLでは「自分より大きい行を数える」自己結合（COUNT(*) WHERE b.val > a.val）になり、O(N²) に近い比較が発生します。

集約とウィンドウ関数：畳むか、横に付けるか

両者は同じ集計関数（SUM・AVG・COUNT・MIN／MAX）を使いますが、出力の行数が決定的に違います。集約は入力N行をグループ数の行へ減らす。ウィンドウ関数は入力N行に対して必ずN行を返し、各行に窓の集計値を付け足すだけです。構文上の目印は OVER (...) 句で、これが付いた集計関数はウィンドウ関数として振る舞います。

-- 集約: 地域ごとに1行へ畳む（出力は地域数ぶん）
SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region;

-- ウィンドウ: 各注文行を残し、地域合計を横に付ける（出力は注文数ぶん）
SELECT order_id, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount / SUM(amount) OVER (PARTITION BY region) AS share
FROM orders;

後者のウィンドウ版は、自己結合なしで「各注文の額」と「地域合計」を同じ行に並べ、構成比 share まで一発で出します。これが集約では書けない領域です。

評価順序も要点です。ウィンドウ関数は論理的に GROUP BY・HAVING の後、ORDER BY（最終並べ替え）の前に評価されます。したがって同じクエリで先に GROUP BY で畳み、その集約結果に対してさらにウィンドウ関数を掛けることもできます（例：日別合計を出してから、その日別合計の累積を取る）。逆に、ウィンドウ関数の結果で WHERE フィルタはできません（評価が後だから）。「順位が K 未満の行だけ」を取るには、ウィンドウ関数をサブクエリ／CTEで計算し、外側で絞る二段構えが必要です。

窓を決める3要素：PARTITION BY・ORDER BY・フレーム

OVER (...) の中身は3つの独立した部品でできています。ここを正確に分けて理解することが、ウィンドウ関数を使いこなす核心です。

• PARTITION BY — 窓を区切るグループを決める。GROUP BY に相当するが行は畳まない。省略すると全行が一つの窓になる。
• ORDER BY — 窓の内部での並び順を決める。順位・累積・前後参照はこの順序が基準になる。
• フレーム（frame） — 並べた窓の中で、現在行から見てどこからどこまでを集計対象にするかを指定する。ROWS／RANGE BETWEEN ... AND ... で書く。

SELECT ...,
  AVG(amount) OVER (
    PARTITION BY region          -- 窓を地域で区切る
    ORDER BY order_date          -- 窓内を日付順に並べる
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 現在行と直前6行＝7日移動平均
  )
FROM orders;

ここで見落としやすい罠が既定フレームです。ORDER BY を書くと、フレームを省略しても既定で RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（窓の先頭から現在行まで）が適用され、集計は累積になります。つまり SUM(amount) OVER (PARTITION BY region ORDER BY order_date) は地域合計ではなく累積和です。地域全体の合計が欲しければ ORDER BY を外すか、フレームを ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING（窓全体）と明示します。

ランキング・移動平均・累積：代表パターン

窓の3要素の組み合わせで、実務の主要パターンが素直に書けます。まずランキング系は専用のウィンドウ関数があり、ORDER BY の順序に沿って番号を振ります。

SELECT order_id, region, amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn,   -- 一意な連番（同値でも別番号）
  RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,  -- 同値は同順位、次は飛ぶ（1,1,3）
  DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS drnk  -- 同値は同順位、次は飛ばさない（1,1,2）
FROM orders;

3者の違いは同値（タイ）の扱いです。ROW_NUMBER は必ず一意な連番を振る（同値でも順序は不定に決まる）。RANK は同値に同順位を与え、次の順位を件数ぶん飛ばす。DENSE_RANK は飛ばさない。「地域ごと売上トップ3」は ROW_NUMBER() <= 3 をCTEの外側で絞って実現します（前述のとおりウィンドウ結果は WHERE で直接絞れないため）。

移動平均・累積・前後比較はフレームと前後参照関数の出番です。

SELECT order_date, amount,
  -- 7日移動平均（物理6行前～現在）
  AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7,
  -- 月初からの累積和（既定フレーム＝先頭～現在）
  SUM(amount) OVER (PARTITION BY date_trunc('month', order_date) ORDER BY order_date) AS mtd,
  -- 前日との差（LAGで1行前の値を参照）
  amount - LAG(amount, 1) OVER (ORDER BY order_date) AS diff_prev
FROM daily_sales;

LAG／LEAD は窓内で N 行前／後の値を直接引き、前期比・差分・在庫の繰越計算を自己結合なしで書けます。FIRST_VALUE／LAST_VALUE／NTH_VALUE は窓内の特定位置の値を、NTILE(n) は窓を n 等分した分位（四分位など）を返します。いずれも共通の骨格は「PARTITION BY で区切り、ORDER BY で並べ、フレームで範囲を切る」——これを押さえれば関数名は用途で選ぶだけです。この「イベントを順序づけて窓で畳む」発想は、ストリーム処理の時間窓（/data-engineering/stream-watermarks-windowing/）とも通じますが、SQLのウィンドウ関数は有限の確定データに対する行ベースの窓である点が異なります。

分析基盤での実行コスト：シャッフルとソートが効く

ウィンドウ関数は表現力の代償として、分散データ処理では重い演算子になります。実行の内訳を分解すると、コストの出どころがはっきりします。1つの OVER (PARTITION BY p ORDER BY o ...) は、実行エンジンから見ると次の段階に落ちます。

1) PARTITION BY p で再分散（シャッフル）: 同じ p の行を同一ノードへ集める
2) パーティション内で ORDER BY o のソート
3) 各パーティションを順に走査し、フレームの範囲で集計を計算

第一段のシャッフルが最大のコストです。ウィンドウの計算は「同じパーティションの行が同じノードに揃っている」ことを前提とするため、MPP（/data-engineering/mpp-data-warehouse/）では PARTITION BY キーでのデータ再分散が走り、Spark では PARTITION BY キーがシャッフル境界となって新しいステージが切られます（DAG実行の仕組みは /data-engineering/spark-rdd-dag/）。ネットワークを介した全行の配り直しは、分析基盤で最も帯域を食う操作です。ここで JOIN と同じスキューが牙をむく——PARTITION BY キーの値が偏ると、特定ノードに行が集中して最遅ノードが全体を支配します。

第三段のフレーム計算量も見落とせません。累積和や ROWS UNBOUNDED PRECEDING のような単調に伸びる窓、および固定幅の移動平均は、多くのエンジンがrunning aggregate（前の結果に増分を足し引きする逐次計算） で O(N) に落とします。ところが RANGE フレームで同値ピアが巨大な塊になったり、フレーム境界が行ごとに大きく動く指定だと、増分計算が効かず各行でフレーム全体を舐め直す O(N × 窓幅) に膨らみます。数億行での分析では、この差がクエリ時間を桁で変えます。列指向スキャン＋ベクトル化（/data-engineering/vectorized-query-execution/）でスキャンは速くなっても、シャッフルとソートは別コストとして残る点に注意が必要です。

まとめ

• 集約（GROUP BY）は複数行を1行へ畳んで行数を減らす。ウィンドウ関数（OVER）は行を畳まずN行のまま各行に窓の集計を横付けし、ランキング・移動平均・累積・前期比を自己結合や相関サブクエリなしで1パスに書ける。
• 窓は PARTITION BY（区切るグループ）・ORDER BY（窓内の並び）・フレーム（ROWS／RANGE BETWEEN） の3要素で決まる。ORDER BY を付けると既定フレームは累積になり、全体合計とは別物。
• ROWS は物理行数、RANGE は値範囲で同値ピアをまとめる。移動平均のように件数を固定したいときは必ず ROWS。ランキングは ROW_NUMBER／RANK／DENSE_RANK のタイ処理の違いで選ぶ。
• ウィンドウ結果は GROUP BY の後に評価されるため WHERE で直接フィルタできない。トップNなどは CTE／サブクエリで計算し外側で絞る。
• 分析基盤では PARTITION BY キーでのシャッフル＋パーティション内ソートが主コスト。キーのスキューが最遅ノードを生み、フレーム指定次第で計算量が O(N) から O(N×窓幅) に悪化する。同一窓定義はシャッフルを共有して効率化できる。