PARTITION BYの複数の対象列を同等に評価してRANKを取る
前提と目的
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
PARTITION BYに対象列を並べて指定して集計する
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
それぞれの対象列を取るインラインテーブルを作り、それをマージしてから、集計する
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
