MySQLのユーザ定義変数を利用してOracleの分析関数RANKを実現する方法

7628 ワード
MySQL SQL Aurora MySQL テキストリンク

OracleにRANK()という分析関数がありますが、それをMySQL、Auroraにコンバージョンする方法をご紹介します。

RANK() OVER(ORDER BY)

Oracleで書く場合、こんな感じです。（※給料（salary）の降順でランク付け）

SELECT
    emp_id,dept_id,job_id,salary,
    RANK()OVER(ORDER BY salary DESC) AS rank 
FROM emp;

実行結果：

MySQLでコンバージョンする場合、ユーザ定義変数とIF(expr1,expr2,expr3)関数で実現します。
考え方は以下の通りです。
　１．salaryで降順ソートする。
　２．抽出レコードに連番を付ける。←tmp1
　３．カレント行のsalary = 一つ前のsalaryの場合、前のsalaryと同じランクにする。
　　　カレント行のsalary != 一つ前のsalaryの場合、ランクに連番を設定する。

SELECT
    emp_id, dept_id, job_id, salary,
    IF(salary=@_last_salary,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
    @_sequence:=@_sequence+1 as tmp1,
    @_last_salary:=salary as tmp2
FROM      emp a, (SELECT @curRank := 1, @_sequence:=1) r
ORDER BY  salary desc;

実行結果：

RANK() OVER(PARTITION BY ORDER BY)

Oracleで書く場合、こんな感じです。（※部門（dept_id）毎に給料（salary）の降順でランク付け）

SELECT 
    emp_id,dept_id,job_id,salary,
    RANK()OVER(PARTITION BY DEPT_ID ORDER BY salary DESC) AS rank
FROM emp;

実行結果：

MySQLで書き換える場合、考え方は基本的に上と一緒です。
違うところは、部門（dept_id）が変わったらランクを１から振り直しのことです。

SELECT
    emp_id, dept_id, job_id, salary,
    IF(dept_id=@_last_dept_id,
        IF(salary=@_last_salary,@curRank:=@curRank,@curRank:=@_sequence),
    @_sequence:=1) AS rank,
    @_sequence:=@_sequence+1 as tmp1,
    @_last_salary:=salary as tmp2,
    @_last_dept_id:=dept_id as tmp3
FROM      emp a, (SELECT @curRank := 1, @_sequence:=1) r
ORDER BY  dept_id asc, salary desc;

実行結果：