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

今回は、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;

ROWNUMの使い方

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

【Oracle】OFFSET/FETCHで行数を限定し取得！

前回は、検索結果を並び替える ORDER BY について説明しました。今回は、行数を限定しデータを取得できる OFFSET ~ FETCH とROWNUMについて説明します！

gptblog.jp

2024.03.26

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

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

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

SELECT
  ROWNUM AS "擬似列(ROWNUM)",
  employee_id AS "従業員ID",
  employee_name AS "姓名",
  department AS "部門",
  salary AS "給料"
FROM
  employees
WHERE
  ROWNUM <= 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;

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;

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

ROWNUMとROW_NUMBER()の違い

まとめ

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