GROUP BY 句を含む PostgreSQL ウィンドウ関数を正しく使用するにはどうすればよいですか?

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

PostgreSQL で GROUP BY 句と組み合わせてウィンドウ関数を使用しようとする場合、これらの違いを理解することが重要です。二。ウィンドウ関数は、行を 1 つの値に要約する集計関数とは異なり、テーブル データのパーティションに対して動作し、すべての行を保持しながら行全体で計算を実行します。したがって、ウィンドウ関数を使用する場合は、GROUP BY 句にパーティション化された列を含めることが重要です。

問題の説明

提供されたクエリで、ユーザーが次のクエリを実行しようとするとエラーが発生します。コード:

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

ウィンドウ関数が sum(sp.payout) OVER (ORDER BY) であるため、エラーが発生します。 e.date) および sum(s.buyin) OVER (ORDER BY e.date) には、集計関数は伴いません。その結果、PostgreSQL は、パーティション化された列 sp.payout と s.buyin を GROUP BY 句に含めることを要求します。

解決策

この問題に対処するには、ユーザーは次のいずれかを行うことができます。

1. グループに sp.payout と s.buyin を含めるBY 句:

GROUP BY p.name, e.date, e.event_id

ただし、このアプローチでは、sp.payout または s.buyin の値が複数ある場合、プレーヤーおよびイベントごとに複数の行が作成される可能性があります。

2. ウィンドウ関数と集計関数を組み合わせて使用​​する関数:

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(s.buyin)) OVER w 式が外側のウィンドウを結合します。イベントごとの合計ペイアウトとバイインを計算する内部集計関数を使用した関数。

以上がGROUP BY 句を含む PostgreSQL ウィンドウ関数を正しく使用するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。