連番を振るROW_NUMBER関数

便利なんだけど、忘れてしまうので備忘録として残す

前提
対応したSQL
ROW_NUMBER() 窓関数の詳細解説
類似の窓関数との比較
対応DBについて
参考URL

前提

２つのテーブルがあります

CREATE TABLE [dbo].[Article](
    [art_id] [varchar](8) NOT NULL,
    [column_id] [varchar](2) NOT NULL,
    [title] [varchar](128) NOT NULL,
    [vol_no] [smallint] NOT NULL,
    [text] [varchar](max) NOT NULL,
    [start_date] [datetime] NOT NULL,
    [avail_f] [char](1) NOT NULL,
    [upd_date] [datetime] NOT NULL,
    [reg_date] [datetime] NULL
) 

CREATE TABLE [dbo].[ArticleImg](
    [img_id] [varchar](8) NOT NULL,
    [art_id] [varchar](6) NOT NULL,
    [title] [varchar](256) NULL,
    [start_date] [smalldatetime] NOT NULL,
    [disp_order] [smallint] NOT NULL,
    [avail_f] [char](1) NOT NULL,
    [reg_date] [datetime] NOT NULL,
    [upd_date] [datetime] NULL
)

PKとか詳しい部分は省いています
Articleに対してArticleImgは複数存在します。
リスト記事画像が表示（avail_f）が１でdisp_orderが最新のデータを１件だけ取りたい。 ArticleリストのSQLの中にArticleImgを記載すると「N+1」の問題がおきます

対応したSQL

SELECT
    uca.art_id,
    uca.column_id,
    uca.title,
    uca.vol_no,
    uca.start_date,
    img.img_id,
    img.title AS img_title,
    img.disp_order,
FROM
    Article uca 
    LEFT JOIN (
        SELECT 
            ucai.img_id,
            ucai.art_id,
            ucai.title,
            ucai.disp_order,
            ROW_NUMBER() OVER (PARTITION BY  ucai.art_id ORDER BY ucai.disp_order DESC) as rn
        FROM 
            ArticleImg ucai
        WHERE 
            ucai.avail_f = '1'
    ) img ON 
             AND uca.art_id = img.art_id 
             AND img.rn = 1
WHERE
    uca.start_date <= GETDATE()
    AND uca.avail_f = '1'
ORDER BY
    uca.vol_no DESC

このSQLの特徴：

LEFT JOIN を使用しているため、画像がない記事も結果に含まれます
ROW_NUMBER() 窓関数で各記事に対してdisp_orderの降順でランク付けし、最新の画像1件のみを取得
N+1問題を回避 - 1回のクエリですべてのデータを取得

ROW_NUMBER() 窓関数の詳細解説

基本構文

ROW_NUMBER() OVER (PARTITION BY カラム ORDER BY カラム)

今回のSQLでの使用例

ROW_NUMBER() OVER (PARTITION BY ucai.art_id ORDER BY ucai.disp_order DESC) as rn

各部分の説明

1. PARTITION BY ucai.art_id

意味: art_id の組み合わせごとに「グループ分け」する
動作: 同じart_idを持つレコードを1つのグループとして扱う

2. ORDER BY ucai.disp_order DESC

意味: 各グループ内で disp_order の降順（大きい順）に並び替える
動作: disp_orderが最も大きい（最新）ものが1番目に来る
DESC: 降順指定（大きい値から小さい値へ）

3. ROW_NUMBER()

意味: 各グループ内で1, 2, 3...と連番を振る
動作: ORDER BYで指定した順序で番号を付ける
結果: 最新のdisp_orderを持つレコードに「1」が付く

具体的な動作例

元データ（ArticleImg）

art_id	img_id	disp_order	avail_f
12345678	IMG001	1	1
12345678	IMG002	3	1
12345678	IMG003	2	1
12345679	IMG004	1	1
12345679	IMG005	2	1

ROW_NUMBER()適用後

corner_id	art_id	img_id	disp_order	rn
A001	12345678	IMG002	3	1
A001	12345678	IMG003	2	2
A001	12345678	IMG001	1	3
A001	12345679	IMG005	2	1
A001	12345679	IMG004	1	2

最終的にrn=1のみを取得

corner_id	art_id	img_id	disp_order
A001	12345678	IMG002	3
A001	12345679	IMG005	2

なぜこの方法を使うのか

❌ 悪い例（N+1問題が発生）

-- 記事ごとに別クエリを実行する必要がある
SELECT * FROM ArticleImg 
WHERE corner_id = ? AND art_id = ? AND avail_f = '1'
ORDER BY disp_order DESC 
LIMIT 1

✅ 良い例（今回の方法）

-- 1回のクエリですべての記事の最新画像を取得
ROW_NUMBER() OVER (PARTITION BY...) を使用

類似の窓関数との比較

関数	特徴	同値の扱い
ROW_NUMBER()	必ず連番	同値でも異なる番号
RANK()	同値は同順位、次は飛ぶ	1,1,3,4...
DENSE_RANK()	同値は同順位、次は連続	1,1,2,3...

今回はdisp_orderが同じ値の場合でも1件だけ取得したいため、ROW_NUMBER()が最適でした。

対応DBについて

・Oracle
・SQL Server
・PostgreSQL
・MySQL(8以降）

動作確認SQLの

-- 窓関数のテスト
SELECT ROW_NUMBER() OVER (ORDER BY 1) as test;

参考URL

https://style.potepan.com/articles/23566.html

masalibの日記

システム開発、運用と猫の写真ブログです