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], ... )                                                                                                                                                                              ※[ ]内は省略可能です。

確認の準備として、前回と同様に売上テーブルを作成して、データを挿入します。(売上テーブルが作成済の場合は、既存のデータを削除してから、下記の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;

blog15-02

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

(1)それでは、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」が見易いように先頭に半角スペースを連結しています。

(2)次に、売上日(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」が、売上日の昇順、部門コードの降順で採番されていることが確認できます。

(3) 続いて、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], ... )                                                                                                                                                                                                ※[ ]内は省略可能です。

(1) それでは、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」が、売上金額が同一の場合は同一番号が採番され、次のデータの番号はスキップされて採番されていることが確認できます。

(2)次に、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], ... )                                                                                                                                                                               ※[ ]内は省略可能です。

(1) それでは、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()関数とは違い、次のデータには次の番号が採番されていることが確認できます。

(2)次に、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つのウィンドウ関数の説明は以上となります。

おすすめの学習コンテンツ

本記事を読み終わって、さらに学びたい方、次は問題にチャレンジしてみたい方には、以下の無料コンテンツをご用意しておりますので、ぜひ活用してみてください!

SQLiteについて
・本記事で使用したデータベース「SQLite」について解説する資料です。
 SQLiteとは?他のデータベースとの違いも解説

SQLの教科書
 ビックデータ時代の必須科目「SQLの教科書」(初級編)
 ビックデータ時代の必須科目「SQLの教科書」(中級編 UPDATE文)NEW!!
 ビックデータ時代の必須科目「SQLの教科書」(中級編 テーブル結合)NEW!!

SQL無料トライアルテスト
・TOPSIC-SQLの無料トライアルテストをご受験していただけます。
 TOPSIC-SQL無料トライアルテスト

SQLコンテスト
・簡単な会員登録をするだけでSQLのオンラインコンテストに無料で参加できます。
    過去に開催されたコンテストの問題にもチャレンジできます。
 TOPSIC SQL CONTEST

SQL基礎解説YouTube
・「TOPSIC-SQL」を元にしたオンラインウェビナーです。
 SQL-BOOT CAMP #1 
 ビッグデータ時代の必須科目SQLを学ぶ「実戦で通用する武器を身につけよう!」

あとがき

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

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


RELATED POST関連記事


RECENT POST「SQL‐BOOTCAMP」の最新記事


SQL‐BOOTCAMP

SQLのテーブル結合(JOIN, UNION, NATURAL)を覚えよう!基本構文と使用例を解説

SQL‐BOOTCAMP

SQLのウィンドウ関数(OVER句でPARTITION BY、ROWS、RANGE)を使ってみよう!

SQL‐BOOTCAMP

SQLのウィンドウ関数(SUM関数とOVER句)を使ってみよう!

SQL‐BOOTCAMP

「TOPSIC SQL CONTEST」にチャレンジⅡ

SQLのウィンドウ関数(ROW_NUMBER、RANK、DENSE_RANK)を使ってみよう!

TOPSIC TOPへ

新規CTA

TOPSIC-SQL4コマ漫画

RANKING人気資料ランキング

RANKING人気記事ランキング

RECENT POST 最新記事