如何在Postgres中正确使用窗口函数和GROUP BY来计算累积用户统计数据？

Postgres 窗口函数和 Group By 异常

问题

查询正在寻求检索随着时间的推移累积的用户统计数据，但会遇到不准确的情况。当一个事件中存在多个游戏时，查询会为不同的支出生成多行。按事件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() 函数的总和。内部函数汇总每个事件中每个玩家的支出和买入。结果是每个玩家和事件一行，显示累积利润或损失。

其他注意事项

• 要包含多个玩家，请将 WHERE 子句替换为: WHERE p.player_id
• 如果 p.name 不唯一，请另外按player_id 进行分组和排序以获得正确的结果。
• 按 e.date 和 p.name 分组可能会带来性能优势。

以上是如何在Postgres中正确使用窗口函数和GROUP BY来计算累积用户统计数据？的详细内容。更多信息请关注PHP中文网其他相关文章！