SQLのウィンドウ関数（ROW_NUMBER、RANK、DENSE_RANK）を使ってみよう！

2024.05.23 株式会社システムインテグレータ

　今回の初心者訓練所#15は、指定した並び順で順番を求める3つのウィンドウ関数を説明していきます。前回紹介したOVER句も使用しますので、OVER句の復習も一緒にやっていきましょう。今回もデータベースは、いつもの「SQLite」を使用していきますので、実践しながら確認していきましょう。

ROW_NUMBER()

　ROW_NUMBER()関数は、ORDER BY句で指定された項目値の順序で1から順番に連番を取得します。また、PARTITION BY句が指定された場合は、指定された項目毎に区切って順番を取得します。
構文は下記になります。

ROW_NUMBER() OVER ( [ PARTITION BY 項目A, [項目B], ...]
ORDER BY 項目C, [項目D], ... [ASC|DESC])
※[ ]内は省略可能です。

確認の準備として、前回と同様に売上テーブルを作成して、データを挿入します。（売上テーブルが作成済の場合は、既存のデータを削除してから、下記のINSERT文を実行してください。）

【売上テーブルのCREATE文とINSERT文】

CREATE TABLE tbl_sales (
sales_no varchar not null
, sales_date varchar not null
, dept_code varchar not null
, sales_amt integer default 0 not null,
PRIMARY KEY( sales_no ) );

INSERT INTO tbl_sales ( sales_no,sales_date,dept_code,sales_amt ) VALUES
( 'S0001','2024-05-01','D001','1000' )
,( 'S0002','2024-05-01','D002','1000' )
,( 'S0003','2024-05-01','D003','1200' )
,( 'S0004','2024-05-02','D003','1200' )
,( 'S0005','2024-05-02','D002','2000' )
,( 'S0006','2024-05-02','D001','1000' )
,( 'S0007','2024-05-04','D002','2000' )
,( 'S0008','2024-05-04','D003','2400' )
,( 'S0009','2024-05-04','D001','1800' );

blog15-01
出力フォーマットを調整して、SELECT文で登録内容を確認してみましょう。

.headers on (ヘッダ情報を出力)
.mode column (カラム毎に左揃え出力)

SELECT * FROM tbl_sales;

INSERT文で作成した、9件のデータを確認できます。

（１）それでは、ORDER BY句に売上日(sales_date)の昇順を指定して実行してみましょう。

SELECT
ROW_NUMBER() OVER (ORDER BY sales_date ASC) AS NO
,sales_no,sales_date,dept_code
FROM tbl_sales;

blog15-03

「NO」が、売上日の昇順で採番されていることが確認できます。
※画像のSELECT文では、「NO」が見易いように先頭に半角スペースを連結しています。

（２）次に、売上日(sales_date)の昇順、部門コード(dept_code)の降順を指定して実行してみましょう。　　　

SELECT
ROW_NUMBER() OVER (ORDER BY sales_date ASC,dept_code DESC) AS NO
,sales_no,sales_date,dept_code
FROM tbl_sales;

blog15-04

「NO」が、売上日の昇順、部門コードの降順で採番されていることが確認できます。

（３）続いて、ORDER BY句とPARTITION BY句に売上日(sales_date)を指定して実行してみましょう。

SELECT
ROW_NUMBER() OVER (PARTITION BY sales_date ORDER BY sales_date ASC) AS NO
,sales_no,sales_date,dept_code
FROM tbl_sales;

blog15-05

PARTITION BYで指定した売上日(5/1、5/2、5/4)毎に、「NO」が1から連番で採番されることが確認できます。

RANK()

　RANK()関数は、ORDER BY句で指定された項目値の順序で1から順番に連番を取得しますが、項目値が同じ場合は、同一番号が設定されます。また、同一番号となった順位は欠番となり、次の行にはその数だけスキップして順位付けされます。
例えば、「1，2，2，4」といった順位となる場合があります。
PARTITION BY句が指定された場合は、ROW_NUMBER()関数と同様に指定された項目毎に区切って順番を取得します。
構文もROW_NUMBER()関数と同様で、下記になります。

RANK() OVER ( [ PARTITION BY 項目A, [項目B], ...]
ORDER BY 項目C, [項目D], ... [ASC|DESC])
※[ ]内は省略可能です。

（１）それでは、ORDER BY句に売上金額(sales_amt)の昇順を指定して実行してみましょう。

SELECT
RANK() OVER (ORDER BY sales_amt ASC) AS RANK
,sales_no,sales_date,dept_code,sales_amt
FROM tbl_sales;

blog15-06

「RANK」が、売上金額が同一の場合は同一番号が採番され、次のデータの番号はスキップされて採番されていることが確認できます。

（２）次に、PARTITION BY句に部門コード(dept_code)を指定して、売上金額(sales_amt)の降順で実行してみましょう。

SELECT
RANK() OVER (PARTITION BY dept_code ORDER BY sales_amt DESC) AS RANK
,sales_no,sales_date,dept_code,sales_amt
FROM tbl_sales;

blog15-07

「RANK」が、部門コード毎に売上金額の降順で1から採番されていることが確認できます。

DENSE_RANK()

　DENSE_RANK()関数は、RANK()関数と同様に項目値が同じ場合、同一番号が設定されますが、次の行には欠番なしで同一番号の次の番号が採番されます。
例えば、「1，2，2，3」といった順位となる場合があります。
PARTITION BY句が指定された場合は、前述の2つの関数と同様に指定された項目毎に区切って順番を取得します。
構文は同様に、下記になります。

DENSE_RANK() OVER ( [ PARTITION BY 項目A, [項目B], ...]
ORDER BY 項目C, [項目D], ... [ASC|DESC])
※[ ]内は省略可能です。

（１）それでは、ORDER BY句に売上金額(sales_amt)の昇順を指定して実行してみましょう。

SELECT
DENSE_RANK() OVER (ORDER BY sales_amt ASC) AS D_RANK
,sales_no,sales_date,dept_code,sales_amt
FROM tbl_sales;

blog15-08

「D_RANK」が、売上金額が同一の場合は同一番号が採番されますが、RANK()関数とは違い、次のデータには次の番号が採番されていることが確認できます。

（２）次に、PARTITION BY句に部門コード(dept_code)を指定して、売上金額(sales_amt)の降順で実行してみましょう。

SELECT
DENSE_RANK() OVER (PARTITION BY dept_code ORDER BY sales_amt DESC) AS D_RANK
,sales_no,sales_date,dept_code,sales_amt
FROM tbl_sales;

blog15-09

「D_RANK」が、部門コード毎に売上金額の降順で同一番号を含め1から連番で採番されていることが確認できます。

ウィンドウの連結

　ウィンドウの連結は、ウィンドウ関数で使用するOVER句の内容が同じ場合に、省略して記入できる方法です。具体的には、この省略方法でPARTITION BY句とORDER BY句の記入を省略することができます。たとえば、下記のようにOVER句が、同じ条件のウィンドウ関数を使用する場合を見てみましょう。
下記は、今回登場したROW_NUMBWE()、RANK()、DENSE_RANK()のORDER BY句が全て売上金額(sale_amt)の降順になっています。

SELECT
ROW_NUMBER() OVER (ORDER BY sales_amt DESC) AS NO
,RANK() OVER ( ORDER BY sales_amt DESC) AS RANK
,DENSE_RANK() OVER ( ORDER BY sales_amt DESC) AS D_RANK
,sales_no,sales_date,dept_code,sales_amt
FROM tbl_sales;

blog15-10

上記のSELECT文を、下記のように省略して記入することができます。

SELECT
ROW_NUMBER() OVER win AS NO
,RANK() OVER win AS RANK
,DENSE_RANK() OVER win AS D_RANK
,sales_no,sales_date,dept_code,sales_amt
FROM tbl_sales
WINDOW win AS ( ORDER BY sales_amt DESC) ;

OVER句の内容を、ウィンドウ連結で「win」という別名に定義して、各ウィンドウ関数では別名の「win」を記入しています。複数のウィンドウ関数を利用する場合は、便利な方法なので、是非、覚えておいてください。

blog15-11

結果は省略前と同じになります。

並び順を取得する、3つのウィンドウ関数の説明は以上となります。

あとがき

　「初心者訓練所」の第15弾では、並び順を取得する3つのウィンドウ関数について説明してきました。基本的な構文は同じなので、理解し易かったのではないでしょうか。場合に応じて今回覚えた関数を活用していきましょう。ウィンドウの連結も便利な方法なので、利用する機会があれば、是非、試してみてください。次回も引き続きウィンドウ関数を紹介する予定です。次回の記事も、どうぞお楽しみに！

(株)システムインテグレータ　TOPSICチーム　元鬼軍曹　久保　司