[sqlite3] Window関数

[SQL] Window関数

今回はSQLのWindow関数（ウィンドウ関数）について解説する。Window関数は、集約関数（SUM, AVGなど）とは異なり、行を集約せずに行ごとの値を計算しつつ、他の行の値（順位や累積和など）を参照できる強力な機能である。

特にランキングを作成する際によく使われる以下の3つの関数について、違いと使い方を見ていく。

1. RANK(): 同順位がある場合、次の順位を飛ばす（例: 1, 2, 2, 4…）。
2. DENSE_RANK(): 同順位がある場合、次の順位を飛ばさない（例: 1, 2, 2, 3…）。
3. ROW_NUMBER(): 同順位があっても、行ごとに連番を振る（例: 1, 2, 3, 4…）。

準備

本サイトではpythonのライブラリであるsqlite3とipython-sqlを利用して、Jupyter Notebook上でSQLを実行・解説する。 また、データフレームの表示には Polars を利用する。

github

• githubのjupyter notebook形式のファイルはこちら

google colaboratory

• google colaboratory で実行する場合はこちら

筆者の環境

筆者の環境である。

!sw_vers

ProductName:		macOS
ProductVersion:		15.5
BuildVersion:		24F74

!python -V

Python 3.12.12

必要なライブラリを読み込む。

import sqlite3
import polars as pl

%load_ext sql
%config SqlMagic.feedback = True

%config SqlMagic.style = '_DEPRECATED_DEFAULT'

データの準備

サンプルとして、あるクラスのテストの点数データを格納した Scores テーブルを作成する。

import os

if os.path.exists("data.db"):
    os.remove("data.db")

os.system("touch data.db")
os.system("chmod 664 data.db")

# データベース接続
%sql sqlite:///data.db

'Connected: @data.db'

%%sql
CREATE TABLE Scores (
    id INTEGER PRIMARY KEY,
    student_name TEXT,
    score INTEGER
);

INSERT INTO Scores (student_name, score) VALUES
    ('Aさん', 100),
    ('Bさん', 90),
    ('Cさん', 90),
    ('Dさん', 80),
    ('Eさん', 70),
    ('Fさん', 90);

 * sqlite:///data.db
Done.
6 rows affected.





[]

%%sql
SELECT * FROM Scores;

 * sqlite:///data.db
Done.

Window関数の実行

それでは、RANK(), DENSE_RANK(), ROW_NUMBER() を使って点数順に順位をつけてみる。 ORDER BY score DESC で点数の高い順に並べ替えて順位を付与する。

結果は Polars の DataFrame として取得して表示する。

query = """
SELECT
    student_name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank_val,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_val,
    ROW_NUMBER() OVER (ORDER BY score DESC) as row_num_val
FROM
    Scores;
"""

with sqlite3.connect("data.db") as conn:
    df = pl.read_database(query, connection=conn)
    display(df)

shape: (6, 5)

結果の解説

• rank_val (RANK): 90点の人が3人いますが、全員2位となり、次の80点の人は5位になっている（3, 4位が飛んでいる）。
• dense_rank_val (DENSE_RANK): 90点の人は全員2位ですが、次の80点の人は3位になっている（順位が詰まっている）。
• row_num_val (ROW_NUMBER): 点数が同じでも、ユニークな連番（2, 3, 4）が振られている（順序はDBの実装や読み込み順に依存する場合がある）。

このように、用途（同順位をどう扱うか）によって関数を使い分けることが重要である。

まとめ

今回はSQLのWindow関数による順位付けの違いを紹介した。

• RANK: 順位を飛ばす（オリンピック方式）
• DENSE_RANK: 順位を飛ばさない
• ROW_NUMBER: 単なる連番

これらを適切に使い分けることで、分析の幅が広がる。