How to Correctly Use Window Functions with Aggregate Functions in Postgres to Calculate Cumulative Profit/Loss?

Postgres Window Functions and Group By Exception

Postgres provides window functions to analyze data across different partitions. However, combining them with aggregation functions can lead to confusion and errors.

In the given query, the user attempts to calculate cumulative profit/loss for a user over time, but encounters an error:

ERROR:  column "sp.payout" must appear in the GROUP BY clause or be used in an aggregate function

This error arises because the SUM functions applied to sp.payout and s.buyin are actually window functions due to the OVER clause. Window functions keep all rows while aggregating values within a partition.

To resolve this, aggregate the data first, then use the aggregated values in the window functions. In this case, we can calculate the sum of payouts and buyins for each event:

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 AS p
JOIN
  result AS r ON r.player_id = p.player_id
JOIN
  game AS g ON g.game_id = r.game_id
JOIN
  event AS e ON e.event_id = g.event_id
JOIN
  structure AS s ON s.structure_id = g.structure_id
JOIN
  structure_payout AS 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;

Here, the outer SUM functions aggregate values across all results within an event, and the window functions then calculate the cumulative profit/loss.

The above is the detailed content of How to Correctly Use Window Functions with Aggregate Functions in Postgres to Calculate Cumulative Profit/Loss?. For more information, please follow other related articles on the PHP Chinese website!