#author("2020-05-28T23:13:21+09:00","default:ryuichi","ryuichi")
#author("2020-05-28T23:16:47+09:00","default:ryuichi","ryuichi")
* PARTITION BYの複数の対象列を同等に評価してRANKを取る [#ad210eb0]
** 前提と目的 [#g4893cf7]
CREATE TABLE t1 ( id int, score int, grp1 text, grp2 text);
INSERT INTO t1 VALUES (1, 250, 'A', 'B'), (2, 200, 'B', 'C'), (3, 300, 'B', 'A'), (4, 150, 'D', 'B'), (5, 400, 'C', 'A');
sql=> SELECT * FROM t1;
id | score | grp1 | grp2
----+-------+------+------
1 | 250 | A | B
2 | 200 | B | C
3 | 300 | B | A
4 | 150 | D | B
5 | 400 | C | A
- grp1、grp2を合わせて、A、B、C、Dのグループがあるが、それぞれのグループ内での順位を取りたい
- すなわち、Aグループの1位はid:5の400、Bグループの1位はid:3の300、Cグループの1位はid:5の400、Dグループの1位はid:4の150（Dグループにはid:4しかない）
** PARTITION BYに対象列を並べて指定して集計する [#r0b5f6fc]
sql=> SELECT id, grp1, grp2, score, RANK() OVER (PARTITION BY grp1, grp2 ORDER BY score DESC) FROM t1;
id | grp1 | grp2 | score | rank
----+------+------+-------+------
1 | A | B | 250 | 1
3 | B | A | 300 | 1
2 | B | C | 200 | 1
5 | C | A | 400 | 1
4 | D | B | 150 | 1
- '''PARTITION BY''' に '''grp1, grp2''' を指定すると、順位が取れない（すべて1位になる）
** それぞれの対象列を取るインラインテーブルを作り、それをマージしてから、集計する [#j08bd2a1]
sql=> WITH t2 AS ( SELECT id, score, grp1 AS grp FROM t1 UNION SELECT id, score, grp2 AS grp FROM t1 )
SELECT id, grp, score, RANK() OVER (PARTITION BY grp ORDER BY score DESC) FROM t2;
id | grp | score | rank
----+-----+-------+------
5 | A | 400 | 1
3 | A | 300 | 2
1 | A | 250 | 3
3 | B | 300 | 1
1 | B | 250 | 2
2 | B | 200 | 3
4 | B | 150 | 4
5 | C | 400 | 1
2 | C | 200 | 2
4 | D | 150 | 1
- したがって、grp1を取るインラインテーブルとgrp2を取るインラインテーブルを作り、UNIONでマージする（この時、grp1とgrp2をgrpという別名にする）
- その後、'''PARTITION BY grp''' する
- すると、順位が取れる
