歴史的背景
ANSI SQL:2011規格は、ウィンドウ関数構文にフレーム排除句を導入し、ウィンドウフレームが現在の行を必ず含むという制限に対処しました。この強化がされる前、開発者は現在の値を総計から引くなどの冗長な自己結合や代数操作に頼らなければなりませんでした。この規格は4つの排除オプションを定義しています：EXCLUDE NO OTHERS、EXCLUDE CURRENT ROW、EXCLUDE GROUP、およびEXCLUDE TIESで、順序付けられたパーティション内の集合操作に対して確定的な意味論を提供します。
問題
競争指標を分析する際（例えば、自身を除外したその他の同類製品の平均販売価格を計算する場合）、クエリは現在の行を除くすべての関連行を含むウィンドウを定義する必要があります。従来のウィンドウ関数（例えば AVG() OVER (PARTITION BY category)）は現在の行を含むため、結果が歪みます。これをサブクエリや結合を使用して実装すると、特に大規模なパーティションデータセットを扱う際に不要な複雑さとパフォーマンス低下を引き起こします。
解決策
ウィンドウ仕様内のフレーム排除句を利用します：AVG(price) OVER (PARTITION BY category ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW)。この構文は、SQLエンジンに最初に完全なパーティションフレームを確立させ、その後で現在の行を論理的に取り除いて集計を計算させます。すべての同点（例えば、同じ価格ポイントのすべての製品）を排除する必要があるシナリオでは、EXCLUDE GROUPは現在の行およびその順序付けされたピアを両方とも取り除き、EXCLUDE TIESは現在の行を保持しながら重複する順序付け値を取り除きます。
Eコマース分析チームは、マーケットポジションレポートを生成する必要があります。各売り手の電子機器リストについて、その売り手の価格と同じモデルを提供しているすべての他の売り手の平均価格を表示する必要があります。
自己結合アプローチが最初にプロトタイプされ、リスティングテーブルがmodel_idで自己結合され、マッチする主キーを除外しました。利点：すべてのSQL方言で普遍的にサポートされており、概念的に明確です。欠点：最悪の場合、実行はO(n²)の複雑さを示し、数百万行にわたる場合は指数関数的な遅延を引き起こします。さらに、クエリオプティマイザーは不均一な結合述語に苦しむことが多く、ハッシュスピルやネストループ結合を伴う非効率的な実行計画が生成されます。
代数的なワークアラウンドも評価され、各モデルごとのグローバル合計とカウントを計算し、次に他の製品の平均を (SUM(price) - current_price) / (COUNT(*) - 1) を通じて導出します。利点：結合を回避し、単一のウィンドウ関数のスキャンのみを必要とします。欠点：COUNT(*) = 1（ゼロでの除算）または価格がNULLの場合に壊滅的に失敗し、冗長なCASEガードが必要です。また、MEDIANやMODEなどの非代数的集計には適用できません。
チームは最終的にEXCLUDE CURRENT ROWフレーム仕様を選択しました。理由：宣言的であり、空のフレームに対して自然にNULLを返すことでNULLチェックのCASE式の必要がなく、最小限のメモリオーバーヘッドで単一のソート済みパスを使用してO(n)時間で実行します。その結果、レポート生成は12分から10秒未満に短縮されました。
結果：生産レポートは、毎日5000万のリスティングに対して競合他社の基準を正確に計算し、単一の売り手による稀なアイテムを優雅に処理し、エラーやゼロ値ではなくNULL（「競争なし」と解釈される）を表示します。
EXCLUDE CURRENT ROWは、特にピアグループに関してRANGEベースとROWSベースのウィンドウフレームと 함께使用した場合どのように動作しますか？
ウィンドウフレームがROWSを利用している場合、EXCLUDE CURRENT ROWは現在の行を1つ正確に取り除きます。一方、RANGE（例えば、RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING）を使用する場合、"現在の行"は指定された範囲内で現在の行と同じ順序値を共有するすべての行を概念的に表します。この文脈では、EXCLUDE CURRENT ROWは特定の行インスタンスのみを取り除き、他のピア（同点）をフレーム内に残します。対照的に、EXCLUDE GROUPは現在の行とすべてのピアをフレームユニットに関係なく取り除き、EXCLUDE TIESは現在の行を除いてすべてのピアを取り除きます。候補者はしばしばこれらを混同し、RANGEでのEXCLUDE CURRENT ROWがEXCLUDE GROUPのように動作することを想定し、重複する順序キーが存在する場合に誤った集計結果を引き起こします。
単一行パーティションに対してEXCLUDE CURRENT ROWを使用するクエリがNULLを返すのはなぜですか、そしてこれは手動の減算方法とどのように異なりますか？
ANSI SQL規格は、空の集合に対して集計がNULLを返すと定義しています。EXCLUDE CURRENT ROWが1行のみのパーティションに適用されると、フレームは空になり、AVG、SUM、またはCOUNTは自動的にNULLを生成します。対照的に、(SUM(col) - col) / (COUNT(*) - 1)のような手動の方法では、ゼロでの除算やNULL伝播の問題が生じ、単一のパーティションを安全に処理するために明示的なCASE文が必要になります。候補者はこの自動的なNULL処理の動作を見落とし、ゼロまたは現在の値を期待し、境界条件に対するEXCLUDEが優れたヌル安全性を提供することを理解できません。
EXCLUDEはスライディングウィンドウのような任意のフレーム範囲（例：ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING）と組み合わせることができますか、そしてそのパフォーマンスの影響は何ですか？
はい、EXCLUDE句は、BETWEENスライディングウィンドウを含む任意のフレーム範囲で有効です。例えば、AVG(val) OVER (ORDER BY time ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW)は、現在のポイントを中心にしながらそのポイントを除外して、2つの前の値と2つの後の値の平均を計算します。パフォーマンス的には、現代のオプティマイザーはこれをリングバッファやデックを使用してストリーミングアルゴリズムで実装し、各パーティションでO(n)の複雑さを維持します。候補者はしばしば、EXCLUDEがパーティションの完全なマテリアライゼーションを必要とするか、UNBOUNDEDフレームでのみ機能することを想定し、中央のローリング相関や外れ値堅牢スムージングのような計算において限定された移動ウィンドウとシームレスに統合されることを見落とします。