Filter Postgres percent_rank before calculation but show all results?

user_1655861 0
Viewed 141

I am trying to figure out how to run a percent_rank on a table, but filter which records the percent_rank is run on, but still include those rows that were filtered out, but give them a 0 percent_rank

For example, I have a users table and everyone has a point value assigned to them. I only want to percent_rank on people with >= 20 points, but not exclude them from the results. Meaning if they have 19 points I can still see their record, but their rank is 0

For example:

SELECT name,points,PERCENT_RANK() OVER (ORDER BY points) 
FROM users 
WHERE points >= 20;

But keep the people with less than 20 points in the results.

postgresql sql
2 Answers

You could use a union here:

SELECT name, points, 0 AS pct_rank FROM users WHERE points < 20
UNION ALL
SELECT name, points, PERCENT_RANK() OVER (ORDER BY points) FROM users WHERE points >= 20;
user_18632290

You can do this with case:

SELECT u.name, u.points,
       (CASE WHEN u.points >= 20
             THEN PERCENT_RANK() OVER (PARTITION BY u.points >= 20 ORDER BY u.points) 
             ELSE 0
        END) as rank
FROM users u;

If you don't want to repeat the condition, you can use a lateral join:

SELECT u.name, u.points,
       (CASE WHEN v.cond
             THEN PERCENT_RANK() OVER (PARTITION BY v.cond ORDER BY points) 
             ELSE 0
        END) as rank
FROM users u CROSS JOIN LATERAL
     (VALUES (u.points >= 20)) v(cond);
user_11440350
Related