使用窗口函数时，为什么我的 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中文网其他相关文章！