Oracle ランキング作成「RANK」関数分析関数(ウィンドウ関数)

分析関数とは

分析関数とは

分析関数とは、標準SQLでいうウィンドウ関数の事をいいます。Oracleでは分析関数というみたいです。

またウィンドウ関数は、OLAP(Online Analytical Processing)関数ともいいます。

その名前の通り、データベース側でリアルタイムにデータ分析を行う処理をする関数です。

この記事では、標準SQLで定められている分析関数でかつ使用頻度が高い分析関数である「RANK」関数を紹介します。ちなみに以下の分析関数の使用頻度が高いと思います。

「ROW_NUMBER」関数
「RANK」関数　←この記事で解説します
「DENS_RANK」関数

「RANK」関数の基本構文

「RANK」関数は、レコードのランキングを算出することができる関数です。また、同順位がある場合の動作は、後続の順位が飛ばされてカウントされます。
例）1位が2レコードある場合、「1位、1位、3位」みたいな感じで2位が飛ばされてカウントされます。

基本構文は以下のとおりです。

RANK() OVER ([PARTITION BY <列名>] ORDER BY <ソート用列名>)

「RANK」関数の実行例

以下のテーブルとデータを使って検証をしていきます。

id	name	address
001	いるか	海
002	ぺんぎん	海
003	あざらし	海
004	うさぎ	空
005	ふくろう	空

CREATE TABLE animal1
(
    id       VARCHAR2(10)
    ,name    VARCHAR2(20)
    ,address VARCHAR2(20)
);

INSERT ALL
INTO animal1 VALUES('001', 'いるか', '海')
INTO animal1 VALUES('002', 'ぺんぎん', '海')
INTO animal1 VALUES('003', 'あざらし', '海')
INTO animal1 VALUES('004', 'うさぎ', '空')
INTO animal1 VALUES('005', 'ふくろう', '空')
SELECT * FROM dual;

SQL> CREATE TABLE animal1
  2  (
  3      id       VARCHAR2(10)
  4      ,name    VARCHAR2(20)
  5      ,address VARCHAR2(20)
  6  );

表が作成されました。

SQL> INSERT ALL
  2  INTO animal1 VALUES('001', 'いるか', '海')
  3  INTO animal1 VALUES('002', 'ぺんぎん', '海')
  4  INTO animal1 VALUES('003', 'あざらし', '海')
  5  INTO animal1 VALUES('004', 'うさぎ', '空')
  6  INTO animal1 VALUES('005', 'ふくろう', '空')
  7  SELECT * FROM dual;

5行が作成されました。

以下のSQLで分析関数を検証します。「PARTITON BY」でランキングを算出するための範囲を設定しています。今回は、動物たちが住んでいる場所ごとに順位を出すために「address」を指定します。

さらに「ORDER BY」でどの順序でランキングをつけるか指定します。今回は「id」順でランキングを作成します。

--分析関数
SELECT id, name, address,
       RANK() OVER (PARTITION BY address ORDER BY id) AS RANKING
  FROM animal1;

SQL> SELECT id, name, address,
  2         RANK() OVER (PARTITION BY address ORDER BY id) AS RANKING
  3    FROM animal1;

ID    NAME                 ADDRE    RANKING
----- -------------------- ----- ----------
001   いるか               海             1
002   ぺんぎん             海             2
003   あざらし             海             3
004   うさぎ               空             1
005   ふくろう             空             2

簡単にランキングを作成することができました＼(^o^)／便利ですね分析関数！

「RANK」関数の実行例　同順位がある場合

先述したとおり同順位がある場合の動作は、後続の順位が飛ばされてカウントされます。

先程のテーブルとデータに以下のデータを追加して検証していきましょう。

INSERT INTO animal1 VALUES('001', 'かめ', '海');

SQL> INSERT INTO animal1 VALUES('001', 'かめ', '海');

1行が作成されました。

さきほど検証した分析関数のSQLを使用して実行してみましょう。

SQL> SELECT id, name, address,
  2         RANK() OVER (PARTITION BY address ORDER BY id) AS RANKING
  3    FROM animal1;

ID    NAME                 ADDRE    RANKING
----- -------------------- ----- ----------
001   いるか               海             1
001   かめ                 海             1
002   ぺんぎん             海             3
003   あざらし             海             4
004   うさぎ               空             1
005   ふくろう             空             2

6行が選択されました。

想定どおりの結果が得られました＼(^o^)／他の分析関数については以下の記事を参考にしてください。