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
  • 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に対象列を並べて指定して集計する

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 BYgrp1, grp2 を指定すると、順位が取れない(すべて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
  • したがって、grp1を取るインラインテーブルとgrp2を取るインラインテーブルを作り、UNIONでマージする(この時、grp1とgrp2をgrpという別名にする)
  • その後、PARTITION BY grp する
  • すると、順位が取れる

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS

Last-modified: 2020-05-28 (木) 23:16:47