MySQLで、Oracleのrank関数っぽいことを、SELECT文だけで頑張ってみた。テーブルの読み込みは1箇所だけだから、副問い合わせとかで複雑なSELECT文になっていても応用できそうな気がする。
考え方
別項目にして出現順でも上手くいくこともありますが、リファレンスマニュアルには次の記載がありました。
SELECT などのほかのステートメントでは、予想した結果が得られることもありますが、これは保証されません。次のステートメントでは、MySQL が最初に @a を評価し、続いて 2 番目の割り当てを行います。
SELECT @a, @a:=@a+1, ...;
ただし、ユーザー変数を含む式の評価の順序は、定義されていません。
https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html より引用。
複数のユーザー定義変数を1項目でまとめて変更するには、上記ページの User Comments に CONCAT のトリックがあったので、それを発展させ、CONCATと、IFもしくはCASE文を使ってみます。
関数によるユーザー変数を含む式の評価
SELECT
IF( CONCAT( @idx := @idx + 1, @a1 := c1, @a2 := c1, ... ) = '', null, @idx ) AS rank
FROM t, (SELECT @idx := 0, @a1 := '', @a2 := '', ...) AS rowInfo
ORDER BY
c1 ASC
次の3点に注目してください。
- 対象となるテーブル(例では t)は、1度しか読まない。
- ユーザー定義変数への設定を、CONCATの引数として複数指定可能。
- ORDER BYでランクの順序を指定。
実践
元となるテーブルの作成
テーブルの作成とデータ投入
-- DROP TABLE hoge_table;
CREATE TABLE hoge_table (
id INT NOT NULL AUTO_INCREMENT,
syurui VARCHAR(45) NOT NULL,
namae VARCHAR(45) NOT NULL,
atai INT NOT NULL,
PRIMARY KEY (id));
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類A', '名前1', '1');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類A', '名前5', '5');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類A', '名前9', '9');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類B', '名前2', '2');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類B', '名前3', '8');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類C', '名前6', '6');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類C', '名前6', '66');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類C', '名前7', '7');
INSERT INTO hoge_table (syurui, namae, atai) VALUES ('種類D', '名前4', '4');
データ投入の確認
SELECT * FROM hoge_table;
id | syurui | namae | atai |
---|---|---|---|
1 | 種類A | 名前1 | 1 |
2 | 種類A | 名前5 | 5 |
3 | 種類A | 名前9 | 9 |
4 | 種類B | 名前2 | 2 |
5 | 種類B | 名前3 | 8 |
6 | 種類C | 名前6 | 6 |
7 | 種類C | 名前6 | 66 |
8 | 種類C | 名前7 | 7 |
9 | 種類D | 名前4 | 4 |
同値は別ランクを振る場合
syurui毎にnamaeの昇順でランクを取得。同値はataiの昇順で別ランク。
同値は別のランク
SELECT
syurui
,namae
,atai
,IF(
@befor_syurui = syurui
,IF( CONCAT(@befor_syurui := syurui, @idx := @idx + 1) = '', null, @idx)
,IF( CONCAT(@befor_syurui := syurui, @idx := 1 ) = '', null, @idx)
) AS rank
FROM
(SELECT
@befor_syurui := ''
,@idx := 0
) AS rowinfo
,hoge_table
ORDER BY
syurui ASC
,namae ASC
,atai ASC
;
syurui | namae | atai | rank |
---|---|---|---|
種類A | 名前1 | 1 | 1 |
種類A | 名前5 | 5 | 2 |
種類A | 名前9 | 9 | 3 |
種類B | 名前2 | 2 | 1 |
種類B | 名前3 | 8 | 2 |
種類C | 名前6 | 6 | 1 |
種類C | 名前6 | 66 | 2 |
種類C | 名前7 | 7 | 3 |
種類D | 名前4 | 4 | 1 |
同値は同ランクを振る場合
syurui毎にnamaeの昇順でランクを取得。同値は同ランク。
もう少しスマートに書けそうな気もする・・・。
同値は同ランク
SELECT
syurui
,namae
,atai
,IF(
CONCAT(
CASE
WHEN @befor_syurui = syurui AND @befor_namae = namae THEN CONCAT( @idx := @idx + 1 )
WHEN @befor_syurui = syurui THEN CONCAT( @idx := @idx + 1, @rank := @idx )
ELSE CONCAT( @idx := 1 , @rank := 1 )
END, CONCAT(@befor_syurui := syurui, @befor_namae := namae)
) = ''
, null, @rank ) AS rank
FROM
(SELECT
@befor_syurui := ''
,@befor_namae := ''
,@idx := 0
,@rank := 0
) AS rowinfo
,hoge_table
ORDER BY
syurui ASC
,namae ASC
;
syurui | namae | atai | rank |
---|---|---|---|
種類A | 名前1 | 1 | 1 |
種類A | 名前5 | 5 | 2 |
種類A | 名前9 | 9 | 3 |
種類B | 名前2 | 2 | 1 |
種類B | 名前3 | 8 | 2 |
種類C | 名前6 | 6 | 1 |
種類C | 名前6 | 66 | 1 |
種類C | 名前7 | 7 | 3 |
種類D | 名前4 | 4 | 1 |