0

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)

  • In the WHERE usr.user_id = user_id you wanted to use WHERE usr.user_id = cu.user_id ? – ypercubeᵀᴹ Nov 19 '15 at 11:07
  • yes you're right, I have edited...but I still get....'HINT: There is an entry for table "cu", but it cannot be referenced from this part of the query.' – DaveB Nov 19 '15 at 11:17
  • You can use a LATERAL join to overcome this issue. But I don't see why a simple join and using CASE in the OVER (PARTITION BY ...) wouldn't be simpler (and probably more efficient). – ypercubeᵀᴹ Nov 19 '15 at 11:19
  • hmm ok, do you mean adding the CASE into the PARTITION BY part? I didnt realise that was possible? – DaveB Nov 19 '15 at 11:26
  • Ah yes, you're right I can use the CASE in the PARTITION BY, however this would mean I have to duplicate the CASE definition to get the users Age Group in the select....I assume the LATERAL join solution overcomes this? – DaveB Nov 19 '15 at 11:32
3

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
  • thanks, I had not come across LATERAL before, I will use this since we are running 9.4, +1 for the alternative solution – DaveB Nov 19 '15 at 11:40
  • I made a small change/improvement in the 1st suggestion. – ypercubeᵀᴹ Nov 19 '15 at 11:41
  • In case you didn't notice, the FULL join is actually equivalent to a LEFT join in your query (due to the WHERE clause that has condition using columns of the left table). – ypercubeᵀᴹ Nov 19 '15 at 11:43

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.