如何正確使用 GROUP BY 子句的 PostgreSQL 視窗函數？

PostgreSQL 視窗函數與 Group By 例外

當嘗試在 PostgreSQL 中將視窗函數與 GROUP BY 子句結合使用時，請理解二。視窗函數對錶資料的分區進行操作，跨行執行計算，同時保留所有行，這與將行匯總為單一值的聚合函數不同。因此，使用視窗函數時，必須在 GROUP BY 子句中包含分區列。

問題描述

在提供的查詢中，使用者在嘗試執行下列指令時遇到錯誤code:

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. 在GROUP BY 中包含sp.payout 和s.buyin子句：

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中文網其他相關文章！