Postgres で GROUP BY を含むウィンドウ関数を正しく使用して累積ユーザー統計を計算する方法

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

問題

クエリは取得を求めていますユーザー統計は時間の経過とともに累積されますが、不正確になることがあります。イベント内に複数のゲームが存在する場合、クエリはさまざまな支払いについて複数の行を生成します。イベント ID によるグループ化という明らかな解決策は失敗し、次のエラーが表示されます。「列 "sp.payout" は GROUP BY 句に表示されるか、集計関数で使用される必要があります。」

解決策

このエラーは、クエリが集計関数ではなく、すべての行を保持しながらパーティションごとの値を集計するウィンドウ関数を使用しているために発生します。ウィンドウ関数では、引数を GROUP BY 句に含める必要があります。

この問題を解決するには、次のようにウィンドウ関数と集計関数を組み合わせることができます。

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() 関数の合計を計算するウィンドウ関数です。内部関数は、各イベント内の各プレーヤーの支払いとバイインを集計します。結果はプレーヤーおよびイベントごとに 1 行になり、累積損益が表示されます。

追加の考慮事項

• 複数のプレーヤーを含めるには、WHERE 句を次のように置き換えます。 : WHERE p.player_id
• p.name が一意でない場合は、正しい結果を得るために、さらに player_id でグループ化して並べ替えます。
• e.date と p.name でグループ化すると、パフォーマンスが向上する可能性があります。

以上がPostgres で GROUP BY を含むウィンドウ関数を正しく使用して累積ユーザー統計を計算する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。