Postgresql – postgres window functions: using CASE to partition by age group

postgresqlpostgresql-9.4window functions

I am using postgres window functions to get a list of users taking part in a competition and their corresponding ranks based on a number of columns, all good so far….now I need to get rank based on 'age group' which are a number of pre-defined categories (eg. 0-15 years, 15+ years etc…), where age is calculated based on a timestamp column 'dob'.

I am pretty sure I can do this using CASE statements but I am unable to get the query structured correctly, I have written the query below which defines the 'age groups' in a separate FROM subquery so I can reference it in the WINDOW definition, but this doesn't work as the two FROM's are independent

SELECT 
cu.* as compUser, 
cus.time_in_seconds as timeInSeconds,
rank() OVER allTimes as overallRank,
u.gender as gender,
rank() OVER genderTimes as genderRank,
ageGroup as ageGroup,
rank() OVER ageGroupTimes as ageGroupRank,
FROM 
competition_users cu, 
(SELECT CASE WHEN usr.dob>'2000-01-01' AND usr.dob<now() THEN '0-15' ELSE '15+' END FROM users usr WHERE usr.user_id = cu.user_id) ageGroup
LEFT JOIN users u ON cu.user_id = u.user_id
FULL JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id 
WHERE cu.left_competition = false 
AND cu.competition_id = :compId
WINDOW 
allTimes AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC),
genderTimes AS (PARTITION BY u.gender ORDER BY cus.time_in_seconds ASC),
ageGroupTimes AS (PARTITION BY ageGroup ORDER BY cus.time_in_seconds ASC)

(above produces: ERROR: invalid reference to FROM-clause entry for table "cu"…HINT: There is an entry for table "cu", but it cannot be referenced from this part of the query.)

Can someone point me in the right direction? (sample query above is simplified, there are many age categories)

Best Answer

You could use a LATERAL join for this. And no need for joining the users table twice:

FROM 
    competition_users AS cu
  LEFT JOIN users AS u
    ON cu.user_id = u.user_id
  LEFT JOIN LATERAL
    ( SELECT CASE WHEN u.dob > '2000-01-01' AND u.dob < now() 
                 THEN '0-15' 
                 ELSE '15+' 
             END AS ageGroup
    ) AS age
    ON TRUE
  LEFT JOIN current_competition_sessions AS ccs 
    ON cu.competition_user_id = ccs.competition_user_id 
WHERE cu.left_competition = FALSE 
  AND cu.competition_id = :compId

and then the WINDOW would be left (almost) unchanged:

WINDOW
    ageGroupTimes AS (PARTITION BY age.ageGroup ORDER BY cus.time_in_seconds ASC)

Alternatively, a simple CTE or derived table with a normal LEFT JOIN could be used, without the need for the (9.3+ available only) LATERAL. With the following you'll only need to slightly modify your upper-level SELECT and WINDOW (removing table aliases mainly):

FROM
  ( SELECT 
        cu.*, 
        cus.time_in_seconds,
        u.gender,
        CASE WHEN u.dob > '2000-01-01' AND u.dob < now() 
            THEN '0-15' 
            ELSE '15+' 
        END AS ageGroup
    FROM 
        competition_users AS cu
      LEFT JOIN users AS u
        ON cu.user_id = u.user_id
      LEFT JOIN current_competition_sessions AS ccs 
        ON cu.competition_user_id = ccs.competition_user_id 
    WHERE cu.left_competition = FALSE 
      AND cu.competition_id = :compId
  ) AS d
Related Question