Oracle 一意な連番を付与する「ROW_NUMBER」関数分析関数(ウィンドウ関数)

2019.10.142019.11.14

分析関数とは
1. 「ROW_NUMBER」関数の基本構文
2. 「ROW_NUMBER」関数の実行例

分析関数とは

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

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

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

この記事では、標準SQLで定められている分析関数でかつ使用頻度が高い分析関数である「ROW_NUMBER」関数を紹介します。ちなみに分析関数でよく使用される関数を以下に列挙します。その中でも今回紹介する「ROW_NUMBER」関数はよく使用される関数です。

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

「ROW_NUMBER」関数の基本構文

「ROW_NUMBER」関数は、一意な連番を付与することができる関数です。

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

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

「ROW_NUMBER」関数の実行例

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

id	name	address
001	いるか	海
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('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('001', 'かめ', '海')
  4  INTO animal1 VALUES('002', 'ぺんぎん', '海')
  5  INTO animal1 VALUES('003', 'あざらし', '海')
  6  INTO animal1 VALUES('004', 'うさぎ', '空')
  7  INTO animal1 VALUES('005', 'ふくろう', '空')
  8  SELECT * FROM dual;

6行が作成されました。

以下のSQLで分析関数を検証します。「PARTITON BY」で連番を付与するための範囲を設定しています。今回は、動物たちが住んでいる場所ごとに連番を付与するために「address」を指定します。

さらに「ORDER BY」でどの順序で連番をつけるか指定します。今回は「name」順で一意な連番を付与します。

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

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

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

6行が選択されました。

「address」を一つのグループとしてレコードごとに一意な連番を付与することができました＼(^o^)／他の分析関数は以下の記事を参考にしてください。