質問への回答

パレート原則は、イタリアにおける土地所有に関するヴィルフレド・パレートの観察から生まれ、後にジョセフ・ジュランの仕事を通じて品質管理や在庫管理の基盤となりました。リレーショナルデータベースにおいては、これはABC分析のニーズにつながります。アナリストは、外部の統計ツールに頼らずに、ビジネス価値の大半を生み出す重要な少数のレコードを特定する必要があります。

この問題は、降順に並べられたメトリックの累積割合を絶対総計に対して計算し、80%の閾値で切り捨てることを要求しています。ANSI SQLは、反復カーソルではなく、セット上で動作するため、ウィンドウ関数が宣言的なメカニズムを提供します。解決策は、全結果セットに渡ってパーティション化された累積和を利用し、値を降順に並べ、同じ行のコンテキスト内で合計を分割してパーセンタイル順位を導き出します。

重要なことに、フレーム仕様ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWは、行ごとの決定論的な累積を確保します。もし厳密にタイを処理する必要がある場合（境界値を共有するすべてのレコードをユニットとして含めるべきまたは除外すべき）、RANGEがROWSに置き換わります。最終的なフィルタリングは外部クエリで行われる必要があり、ウィンドウ関数はWHERE句の後に論理的に計算されます。

WITH ranked_products AS (
    SELECT 
        product_id,
        product_name,
        annual_revenue,
        SUM(annual_revenue) OVER (
            ORDER BY annual_revenue DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_revenue,
        SUM(annual_revenue) OVER () AS total_revenue
    FROM inventory
),
pareto_subset AS (
    SELECT 
        product_id,
        product_name,
        annual_revenue,
        CAST(cumulative_revenue AS DECIMAL) / total_revenue AS cumulative_pct
    FROM ranked_products
)
SELECT product_id, product_name, annual_revenue, cumulative_pct
FROM pareto_subset
WHERE cumulative_pct <= 0.80;

実生活からの状況

ある全国的な電子小売業者は、四半期ごとの在庫監査中にコストが急増し、高価値のSKUを抽出して倉庫資本の80%（4万アイテムで5,000万ドル）を占めるものに優先順位を付ける必要がありました。

解決策1: スプレッドシートの抽出では、アナリストがCSVファイルをExcelにエクスポートし、単価でソートし、閾値に達するまで手動で合計した。長所は開発時間ゼロでした。短所には、大きなデータセットでのアプリケーションのクラッシュ、時間ごとの再計算要件、倉庫管理システムとのリアルタイム統合の阻害が含まれました。

解決策2: アプリケーションレイヤー計算では、Pythonスクリプトを使用して行をストリーミングし、ランニングアキュムレーターを維持しました。長所としては柔軟なロジックと簡単なデバッグがありました。短所としては、数百万行を転送する際のネットワーク遅延、シングルスレッドの実行による分析ダッシュボードのブロック、およびクライアントマシン上のメモリ制約が導入されました。

解決策3: ANSI SQLセットベースアプローチでは、ウィンドウ関数クエリをPostgreSQLウェアハウス内に直接実装しました。長所にはミリ秒単位の遅延、データ移動の排除、夜間の更新による自動リフレッシュが含まれました。短所にはメンテナンスのための高度なSQL知識が必要でした。

選ばれた解決策と結果: 解決策3はビューとして導入され、わずか12%のSKUが80%の価値を占めることが明らかになりました。監査の範囲は88%減少し、四半期ごとに340の労働時間が節約されながら、物質的価値の完全なカバレッジが維持されました。

候補者が見落とすことが多い点

重複値が存在する場合、ROWSとRANGEフレーム仕様の選択が80%の閾値にどのように影響しますか？

RANGEは同じORDER BY値を持つピア行を単一のグループとして扱います。80%の境界がタイの内側に落ちると、RANGEはグループ全体を含むため、80%を超える可能性があります。ROWSはタイに関係なく物理的なオフセットを処理し、論理的なビジネスユニットを分割することがあります。候補者は、ANSI SQLがこの動作を明示的に調整できることを見落としがちです。財務報告のために、RANGEは一貫した期間が分割されないようにし、ROWSは異なるアイテムのためにより細かな粒度を提供します。

累積割合計算を直接WHERE句で行うのではなく、派生テーブルまたはCTEで行う必要があるのはなぜですか？

ウィンドウ関数はSELECTフェーズ中に論理的に評価され、このフェーズはWHERE句が行をフィルタリングした後に発生します。直接WHERE句でcumulative_revenue / total_revenue <= 0.8をフィルタリングしようとすると、ウィンドウ結果がまだ具現化されていないため、構文エラーが発生します。候補者は、ANSI SQLの論理的処理順序（FROM → WHERE → GROUP BY → HAVING → WINDOW → SELECT → ORDER BY）にしばしば苦労します。解決策は、ウィンドウ関数を内部クエリで計算し、外部クエリでその結果の列をフィルタリングするためにネストする必要があります。

もし在庫テーブルに数十億の行があり、80%のサブセットが非常に小さいと推定される場合、このクエリをどのように最適化しますか？

候補者はTop-N最適化パターンを見落としがちです。テーブル全体でウィンドウ関数を計算するのではなく、**DENSE_RANK()やNTILE()**を使用したサブクエリで事前フィルタリングすることで、ウィンドウ計算を最も重要な候補に制限できます。また、分析がカテゴリ別にセグメント化されている場合は、PARTITION BYを活用することでフルテーブルスキャンを防げます。ウィンドウ関数がソート操作を強制し、収益列を降順にインデックス付けすることがソートコストを排除できることを理解することが、スケールにとって重要です。

ANSI SQLのウィンドウ関数技術を使用して、累積貢献度が合計の80%を占める最小の順序付けられたレコードのサブセットを特定する方法を示してください。

質問への回答

実生活からの状況

候補者が見落とすことが多い点