#author("2020-05-19T14:49:24+09:00","default:ryuichi","ryuichi")
* ウィンドウ関数とWHERE句 [#s79001d6]
** 前提 [#r56cdc57]
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()を実行 [#z4e0b33e]
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()を実行 [#z868ac59]
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()が適用される