Martin Martin - 1 year ago 77
SQL Question

Postgres window function and group by exception

I'm trying to put together a query that will retrieve the statistics of a user (profit/loss) as a cumulative result, over a period of time.

Here's the query I have so far:

sum(sp.payout) OVER (ORDER BY
- sum(s.buyin) OVER (ORDER BY 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,, e.event_id, sp.payout, s.buyin

The query will run. However, the result is slightly incorrect. The reason is that an
can have multiple games (with different
). Therefore, the above comes out with multiple rows if a user has 2 results in an event with different payouts (i.e. there are 4 games per event, and a user gets £20 from one, and £40 from another).

The obvious solution would be to amend the

GROUP BY,, e.event_id

However, Postgres complains at this as it doesn't appear to be recognizing that
are inside an aggregate function. I get the error:

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

I'm running 9.1 on Ubuntu Linux server.

Am I missing something, or could this be a genuine defect in Postgres?

Answer Source

You are not, in fact, using aggregate functions. You are using window functions. That's why PostgreSQL demands sp.payout and s.buyin to be included in the GROUP BY clause.

By appending an OVER clause, the aggregate function sum() is turned into a window function, which aggregates values per partition while keeping all rows.

You can combine window functions and aggregate functions. Aggregations are applied first. I did not understand from your description how you want to handle multiple payouts / buyins per event. As a guess, I calculate a sum of them per event. Now I can remove sp.payout and s.buyin from the GROUP BY clause and get one row per player and event:

     , e.event_id
     , 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.event_id)
ORDER  BY, e.event_id;

In this expression: sum(sum(sp.payout)) OVER w, the outer sum() is a window function, the inner sum() is an aggregate function.

Assuming p.player_id and e.event_id are PRIMARY KEY in their respective tables.

I added e.event_id to the ORDER BY of the WINDOW clause to arrive at a deterministic sort order. (There could be multiple events on the same date.) Also included event_id in the result to distinguish multiple events per day.

While the query restricts to a single player (WHERE p.player_id = 17), we don't need to add or p.player_id to GROUP BY and ORDER BY. If one of the joins would multiply rows unduly, the resulting sum would be incorrect (partly or completely multiplied). Grouping by could not repair the query then.

I also removed from the GROUP BY clause. The primary key e.event_id covers all columns of the input row since PostgreSQL 9.1.

If you change the query to return multiple players at once, adapt:

WHERE  p.player_id < 17  -- example - multiple players
GROUP  BY, p.player_id,, e.event_id  -- and redundant
WINDOW w AS (ORDER BY, p.player_id,, e.event_id)
ORDER  BY, p.player_id,, e.event_id;

Unless is defined unique (?), group and order by player_id additionally to get correct results in a deterministic sort order.

I only kept and in GROUP BY to have identical sort order in all clauses, hoping for a performance benefit. Else, you can remove the columns there. (Similar for just in the first query.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download