7

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?

13

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.

  • 1
    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. – dukedave Feb 8 '17 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.