使用視窗函數時，為什麼我的 PostgreSQL 查詢會產生「列「sp.payout」必須出現在 GROUP BY 子句中」錯誤？

PostgreSQL 中的視窗函數和 Group By 異常

在此查詢中，目標是計算特定用戶隨時間的累積利潤/損失。然而，由於對 PostgreSQL 中視窗函數性質的誤解，最初的嘗試遇到了錯誤。

錯誤辨識的聚合函數

錯誤訊息「column 「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(sum(sp.payout)) OVER w) 是一個視窗函數，而內部的sum() 是一個聚合函數。聚合函數對每個事件中的支付值和買入值進行求和，然後視窗函數對指定範圍內的這些聚合結果進行求和。

關鍵點

• 視窗函數在指定範圍內聚合值指定範圍，保留操作後的所有行。
• 組合視窗和聚合時首先應用聚合函數函數。
• 使用正確的函數類型（視窗或聚合）對於正確執行查詢至關重要。

以上是使用視窗函數時，為什麼我的 PostgreSQL 查詢會產生「列「sp.payout」必須出現在 GROUP BY 子句中」錯誤？的詳細內容。更多資訊請關注PHP中文網其他相關文章！