分析関数とは
分析関数とは、標準SQLでいうウィンドウ関数の事をいいます。Oracleでは分析関数というみたいです。
またウィンドウ関数は、OLAP(Online Analytical Processing)関数ともいいます。
その名前の通り、データベース側でリアルタイムにデータ分析を行う処理をする関数です。
この記事では、標準SQLで定められている分析関数でかつ使用頻度が高い分析関数である「DENSE_RANK」関数を紹介します。ちなみに以下の分析関数の使用頻度が高いと思います。
- 「ROW_NUMBER」関数
- 「RANK」関数
- 「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^)/順位が飛ばされてないことを確認できますね。他の分析関数については以下の記事参考にしてください。
コメント