[sqlite3] CASE式の活用

[SQL] CASE式の活用

今回はSQLの CASE 式について解説する。 CASE 式は、プログラミング言語における if-then-else のような条件分岐をSQL内で実現する機能である。

データの分類や、集計時の条件付き計算など、非常に幅広い用途で利用される。

準備

本サイトでは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'

データの準備

サンプルとして、学生のテスト点数を格納した Students テーブルを作成する。

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 Students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    score INTEGER
);

INSERT INTO Students (name, score) VALUES
    ('Aさん', 95),
    ('Bさん', 85),
    ('Cさん', 75),
    ('Dさん', 65),
    ('Eさん', 55),
    ('Fさん', 45);

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





[]

%%sql
SELECT * FROM Students;

 * sqlite:///data.db
Done.

id	name	score
1	Aさん	95
2	Bさん	85
3	Cさん	75
4	Dさん	65
5	Eさん	55
6	Fさん	45

例1: 点数に応じたランク付け

点数に応じて、以下のようにランク（成績）を付与してみる。

90点以上: S
80点以上: A
70点以上: B
60点以上: C
それ以外: D

query = """
SELECT
    name,
    score,
    CASE
        WHEN score >= 90 THEN 'S'
        WHEN score >= 80 THEN 'A'
        WHEN score >= 70 THEN 'B'
        WHEN score >= 60 THEN 'C'
        ELSE 'D'
    END as grade
FROM
    Students;
"""

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

shape: (6, 3)

name	score	grade
“Aさん”	95	“S”
“Bさん”	85	“A”
“Cさん”	75	“B”
“Dさん”	65	“C”
“Eさん”	55	“D”
“Fさん”	45	“D”

このように、SELECT 句の中で CASE 式を使うことで、既存のカラムの値に基づいた新しいカラムを動的に生成できる。

例2: クロス集計（条件付き集計）

次に、ランクごとの人数を集計してみる。通常は GROUP BY を使うが、CASE 式と集約関数（SUM や COUNT）を組み合わせることで、横持ちの集計表（ピボットテーブルのような形）を作ることができる。

query = """
SELECT
    SUM(CASE WHEN score >= 80 THEN 1 ELSE 0 END) as Excellent_Count,
    SUM(CASE WHEN score >= 60 AND score < 80 THEN 1 ELSE 0 END) as Good_Count,
    SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) as Poor_Count
FROM
    Students;
"""

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

shape: (1, 3)

Excellent_Count	Good_Count	Poor_Count
2	2	2

解説

CASE WHEN 条件 THEN 1 ELSE 0 END で、条件に合致する場合に 1、そうでない場合に 0 を返す。
これを SUM で合計することで、条件に合致する行の数をカウントできる。

このテクニックは、特定の条件ごとのKPIを一度のクエリで算出したい場合に非常に便利である。

まとめ

今回は CASE 式の基本的な使い方と応用例を紹介した。

条件分岐: データの値を条件に応じて変換できる。
クロス集計: 集約関数と組み合わせることで、柔軟な集計が可能になる。

CASE 式を使いこなすことで、SQLでのデータ加工の幅が大きく広がる。