ウィンドウ関数とWHERE句
前提
PSQL> SELECT * FROM t1;
id | type | num
----+------+-----
1 | 1 | 100
2 | 2 | 150
3 | 1 | 200
4 | 2 | 200
5 | 1 | 50
6 | 3 | 80
普通にウィンドウ関数でRANK()を実行
PSQL> SELECT *, RANK() OVER (PARTITION BY type ORDER BY num DESC) FROM t1;
id | type | num | rank
----+------+-----+------
3 | 1 | 200 | 1
1 | 1 | 100 | 2
5 | 1 | 50 | 3
4 | 2 | 200 | 1
2 | 2 | 150 | 2
6 | 3 | 80 | 1
WHERE句で条件を付けてRANK()を実行
PSQL=> SELECT *, RANK() OVER (PARTITION BY type ORDER BY num DESC) FROM t1 WHERE id % 2 = 0;
id | type | num | rank
----+------+-----+------
4 | 2 | 200 | 1
2 | 2 | 150 | 2
6 | 3 | 80 | 1
- WHERE id % 2 = 0で絞り込まれたレコードに対してRANK()が適用される