【Oracle】ROWNUMとROW_NUMBER()の使い方を知ろう!

Oracle

今回は、ROWNUMとROW_NUMBER()の使い方について説明します!

データ準備

ROWNUMとROW_NUMBER()を試すためにテーブルを作成し、データを追加します。

-- テーブルの作成
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50),
    department VARCHAR2(50),
    salary NUMBER
);

-- データの登録
INSERT
  ALL INTO employees
VALUES
  (1, '山田太郎', '営業', 500000)
INTO
  employees
VALUES
  (2, '佐藤花子', '営業', 550000)
INTO
  employees
VALUES
  (3, '鈴木一郎', '営業', 600000)
INTO
  employees
VALUES
  (4, '田中次郎', 'マーケティング', 450000)
INTO
  employees
VALUES
  (5, '渡辺三郎', 'マーケティング', 470000)
INTO
  employees
VALUES
  (6, '高橋四郎', 'マーケティング', 520000)
INTO
  employees
VALUES
  (7, '井上五郎', '開発', 650000)
INTO
  employees
VALUES
  (8, '小林六郎', '開発', 700000)
INTO
  employees
VALUES
  (9, '森田七郎', '開発', 750000)
INTO
  employees
VALUES
  (10, '山本八郎', '営業', 520000)
INTO
  employees
VALUES
  (11, '松本九郎', '営業', 580000)
INTO
  employees
VALUES
  (12, '加藤十郎', 'マーケティング', 480000)
INTO
  employees
VALUES
  (13, '中村十一', '開発', 800000)
INTO
  employees
VALUES
  (14, '石田十二', '営業', 530000)
INTO
  employees
VALUES
  (15, '小川十三', 'マーケティング', 460000)
SELECT
  *
FROM
  DUAL;
従業員ID姓名部門給料
1山田太郎営業500000
2佐藤花子営業550000
3鈴木一郎営業600000
4田中次郎マーケティング450000
5渡辺三郎マーケティング470000
6高橋四郎マーケティング520000
7井上五郎開発650000
8小林六郎開発700000
9森田七郎開発750000
10山本八郎営業520000
11松本九郎営業580000
12加藤十郎マーケティング480000
13中村十一開発800000
14石田十二営業530000
15小川十三マーケティング460000
従業員テーブル

ROWNUMの使い方

下記の記事で一度紹介したのですが、ROWNUMは行番号を取得するための擬似列で、特定の行数だけデータを取得することができます。

ROWNUMは下記のように記述します。

SELECT 列名… FROM テーブル名
  WHERE ROWNUM 行数

ROWNUM <= 5 と記述することで5レコードだけ取得することができます。

SELECT
  ROWNUM AS "擬似列(ROWNUM)",
  employee_id AS "従業員ID",
  employee_name AS "姓名",
  department AS "部門",
  salary AS "給料"
FROM
  employees
WHERE
  ROWNUM <= 5;
擬似列(ROWNUM)従業員ID姓名部門給料
11山田太郎営業500000
22佐藤花子営業550000
33鈴木一郎営業600000
44田中次郎マーケティング450000
55渡辺三郎マーケティング470000
ROWNUM <= 5 で5レコードを取得

ROWNUM = 5 で指定してもレコードの取得はできないので注意しましょう。

※ROWNUMは、各行が結果セットに追加されるその瞬間に番号が付けられるため

もし、5番目のレコードを取得したい場合は、サブクエリを使って全ての行を取得した後に 5番目 と指定する必要があります。

SELECT * FROM (
  SELECT 
    ROWNUM AS "擬似列(ROWNUM)",
    employee_id AS "従業員ID",
    employee_name AS "姓名",
    department AS "部門",
    salary AS "給料"
  FROM 
    employees
  WHERE 
    ROWNUM <= 5
)
WHERE "擬似列(ROWNUM)" = 5;
擬似列(ROWNUM)従業員ID姓名部門給料
55渡辺三郎マーケティング470000
サブクエリを使って5番目のレコードを取得

ROW_NUMBER()の使い方

ROW_NUMBER()はウィンドウ関数で、指定した順序に基づいて行番号を付与します。

ROW_NUMBER()は下記のように記述します。

ROW_NUMBER() OVER (
    PARTITION BY 列名1, 列名2, ... 
    ORDER BY 列名A ASC | DESC, ... 
)

PARTITION BY は特定の列を基にしてデータをグループ分けし、そのグループごとに行番号を1から付け直します。 ORDER BY は指定した列の値に基づき、各行の順序を決定します。

試しに、ROW_NUMBER() を使って部門ごとにグループ化し給料の高い順で並び替えてみます。

SELECT
  employee_id AS "従業員ID",
  employee_name AS "姓名",
  department AS "部門",
  salary AS "給料",
  ROW_NUMBER() OVER (
      PARTITION BY department  -- 部門ごとにグループ化
      ORDER BY salary DESC     -- 給料の降順で行番号を付与
  ) AS "ROW_NUMBER"
FROM
  employees;
従業員ID姓名部門給料ROW_NUMBER()
6高橋四郎マーケティング5200001
12加藤十郎マーケティング4800002
5渡辺三郎マーケティング4700003
15小川十三マーケティング4600004
4田中次郎マーケティング4500005
3鈴木一郎営業6000001
11松本九郎営業5800002
2佐藤花子営業5500003
14石田十二営業5300004
10山本八郎営業5200005
1山田太郎営業5000006
13中村十一開発8000001
9森田七郎開発7500002
8小林六郎開発7000003
7井上五郎開発6500004
各部門で給与の高い順に並び替え

ちゃんとに並び替えることができました。

ROWNUMとROW_NUMBER()の違い

特性ROWNUMROW_NUMBER()
種類擬似列分析関数(ウィンドウ関数)
動作の仕組みクエリの結果セットに基づいて番号を付与ORDER BYPARTITION BYで順序を制御可能
ORDER BY対応ORDER BYに関係なく番号を付与ORDER BYによる並び替えが可能
応用例最初の数行を取得するなど単純な用途ページネーションや詳細な並び替えに対応
ROWNUMとROW_NUMBER()の違い

まとめ

ROWNUMは、単純な行番号を付与できて並び替えできない。ROW_NUMBER()は特定の順序・条件で柔軟に行番号を付与できるということを覚えておきましょう!

コメント