Postgres - window function rank and count

Question

I have some tables that track athletes time around a track:

competitions, competition_users and competition_user_sessions

I am constructing a query that extracts the current rank and total no of competitors for each supplied 'competition_user', I can get rank ok, but my count (totalUsers) doesn't count all competitors in the competition, it only seems to count up to the supplied user (eg. gives the same as the rank)

SELECT compUserId, rank, totalUsers 
 FROM (
 SELECT cu.competition_user_id as compUserId, cu.user_id as userId, 
 count(*) OVER w as totalUsers, rank() OVER w as rank 
 FROM competition_users cu 
 LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id 
 LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id 
 WHERE cu.left_competition = false 
 AND cu.competition_id in (:compIds)
 WINDOW w AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC) 
 ) as sub 
WHERE compUserId in (:compUserIds)

My understanding was that the default frame for was the entire window, whereas this seems to be counting from frame start to current row?

• Erwin Brandstetter: Related answer on SO with quote from the manual documenting the behavior: stackoverflow.com/a/15839234/939860

Answer 1

Your issue appears to be that you are applying the same WINDOW (named w) for both your COUNT(*) and your rank().

When you use a WINDOW which contains an ORDER BY clause, and you then apply certain aggregations such as SUM or COUNT, it applies the aggregation continuously across the ordering, which is why your COUNT and rank() are identical.

If you modify your query have multiple windows as

SELECT compUserId, rank, totalUsers 
FROM (
 SELECT cu.competition_user_id as compUserId, cu.user_id as userId, 
 count(*) OVER (PARTITION BY cu.competition_id) as totalUsers, 
 rank() OVER (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC) as rank 
 FROM competition_users cu 
 LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id 
 LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id 
 WHERE cu.left_competition = false 
 AND cu.competition_id in (:compIds)
) as sub 
WHERE compUserId in (:compUserIds);

so that you are only applying the PARTITION BY to your COUNT(*) window, and have both PARTITION BY and ORDER BY clauses for your rank(), I believe you'll get the results you want.

Refer to this SQL FIDDLE as a reference, where I have a generic id field, a com_num field to represent the competition id, and a com_time field to represent a competitors time.

• davetapley: Perhaps mention window frame, and quote the specific line in the docs which clarifies this? Namely: By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to the ORDER BY clause. When ORDER BY is omitted the default frame consists of all rows in the partition.