ウィンドウ関数を使用すると、PostgreSQL クエリで「列 'sp.payout' が GROUP BY 句に表示される必要があります」というエラーが生成されるのはなぜですか?

PostgreSQL のウィンドウ関数と Group By Exception

このクエリの目標は、特定のユーザーの経時的な累積損益を計算することです。ただし、最初の試行では、PostgreSQL のウィンドウ関数の性質に関する誤解が原因でエラーが発生します。

誤って識別された集計関数

エラー メッセージ「列 "sp.payout" は、 GROUP BY 句または集計関数で使用される」は、PostgreSQL が sp.payout 列と s.buyin 列が GROUP に含まれることを期待していることを示しますBY 句は、意図された集計関数 sum() で使用されるためです。ただし、これは誤認識です。

ウィンドウ関数の利用

このクエリでは、sum() が集計関数ではなくウィンドウ関数として使用されています。ウィンドウ関数は、集計関数とは異なり、指定された範囲内の値を集計しますが、操作後の個々の行はすべて保持されます。これは、集計関数に関するクエリの誤った仮定が見落としている重要な違いです。

ウィンドウ関数と集計関数の組み合わせ

解決策は、ウィンドウ関数を正しく識別して利用することです。 PostgreSQL では、最初に集約関数を適用して、ウィンドウ関数と集約関数を組み合わせることができます。

改訂されたクエリ

改訂されたクエリには、次の理解が組み込まれています。

SELECT p.name
     , e.event_id
     , e.date
     , sum(sum(sp.payout)) OVER w
     - sum(sum(s.buyin  )) OVER w AS "Profit/Loss" 
FROM   player            p
JOIN   result            r ON r.player_id     = p.player_id  
JOIN   game              g ON g.game_id       = r.game_id 
JOIN   event             e ON e.event_id      = g.event_id 
JOIN   structure         s ON s.structure_id  = g.structure_id 
JOIN   structure_payout sp ON sp.structure_id = g.structure_id
                          AND sp.position     = r.position
WHERE  p.player_id = 17 
GROUP  BY e.event_id
WINDOW w AS (ORDER BY e.date, e.event_id)
ORDER  BY e.date, e.event_id;

説明

この式 (sum(sum(sp.payout)) OVER w) の外側の sum() はウィンドウ関数ですが、内部の sum() は集計関数です。集計関数は各イベント内のペイアウト値とバイイン値を合計し、ウィンドウ関数は指定された範囲にわたってこれらの集計結果を合計します。

キー ポイント

• ウィンドウ関数は、指定された範囲にわたって値を集計します。指定された範囲、操作後のすべての行を保持します。
• ウィンドウと集計を組み合わせるときに、集計関数が最初に適用されます。関数。
• クエリを適切に実行するには、正しい関数タイプ (ウィンドウまたは集計) を使用することが重要です。

以上がウィンドウ関数を使用すると、PostgreSQL クエリで「列 'sp.payout' が GROUP BY 句に表示される必要があります」というエラーが生成されるのはなぜですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。