如何正确使用带有 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中文网其他相关文章！