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

スポンサーリンク
スポンサーリンク

分析関数とは

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

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

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

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

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

「DENSE_RANK」関数の基本構文

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

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

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

「DENSE_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,
       DENSE_RANK() OVER (PARTITION BY address ORDER BY id) AS RANKING
  FROM animal1;
SQL> SELECT id, name, address,
  2         DENSE_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」関数と結果が一緒になります。

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

先述したとおり同順位がある場合の動作は、後続の順位は飛ばされません。

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

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

1行が作成されました。

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

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

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

6行が選択されました。

想定どおりの結果が得られました\(^o^)/順位が飛ばされてないことを確認できますね。他の分析関数については以下の記事参考にしてください。

Oracle 一意な連番を付与する 「ROW_NUMBER」関数 分析関数(ウィンドウ関数)
分析関数とは 分析関数とは、標準SQLでいうウィンドウ関数の事をいいます。Oracleでは分析関数というみたいです。 またウィンドウ関数は、OLAP(Online Analytical Processing)関数ともいいます。 その名前の通...
Oracle ランキング作成 「RANK」関数 分析関数(ウィンドウ関数)
分析関数とは 分析関数とは、標準SQLでいうウィンドウ関数の事をいいます。Oracleでは分析関数というみたいです。 またウィンドウ関数は、OLAP(Online Analytical Processing)関数ともいいます。 その名前の通...

コメント

タイトルとURLをコピーしました