【2026年8月版】

Alopex DB v0.8.8チュートリアル第3回：CASE式・集合演算・WITH句・ウィンドウ関数

公開日: 2026/08/27
読了時間: 約 21分

Rust Python SQL データ

第2回では、SELECT・WHERE・JOIN・集約を扱いました。第3回で扱うのは、次の四つです。

v0.8.8では、これらのSQLをライブラリだけでなく、組み込み・サーバー・cluster-awareの各モードでも同じ結果として検証できます。ここでは問い合わせの書き方に集中します。

CASE式：条件で値を振り分ける
集合演算：二つの検索結果を組み合わせる
WITH句：問い合わせに名前を付ける
ウィンドウ関数：行ごとの順位や累計を出す

テーブルは第2回と同じarticlesを使います。

1. 環境と準備

項目	値
Alopex DB	`0.8.8`（PyPI / crates.io 公開版）
Python	`3.11.11`
Rust	`1.96.0`
OS	Linux（WSL2、glibc 2.35）
確認日	2026年8月17日

第2回と同じテーブルを使います。

from alopex import Database

db = Database.new()
db.execute_sql("""CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  views INTEGER,
  rating REAL
)""")

rows = [
    "(1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5)",
    "(2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5)",
    "(3, 'ベクトル検索の基礎', 'ren', 1500, 4.5)",
    "(4, '組み込みDBの選び方', 'ren', 600, 4.0)",
    "(5, 'WALとクラッシュ復旧', 'sora', 300, NULL)",
]
for row in rows:
    db.execute_sql(f"INSERT INTO articles VALUES {row}")

このデータには三つの特徴があります。rating = 4.5が2件あるので、順位を付けると同順位が生じます。id = 5のratingはNULLなので、集計から外れます。viewsが多い記事とratingが高い記事は一致しないので、二つの条件で絞ると別の集合になります。

2. CASE式で値を振り分ける

閲覧数で記事を分類します。

print(db.execute_sql("""
SELECT id,
       CASE WHEN views >= 1000 THEN '人気' ELSE '通常' END AS band
FROM articles
ORDER BY id
"""))

[{'id': 1, 'band': '人気'},
 {'id': 2, 'band': '通常'},
 {'id': 3, 'band': '人気'},
 {'id': 4, 'band': '通常'},
 {'id': 5, 'band': '通常'}]

CASE WHEN 条件 THEN 値 ELSE 値 ENDの形です。条件に合えばTHENの値、合わなければELSEの値が入ります。

WHENはいくつでも並べられ、上から順に評価して最初に一致したものが使われます。

ELSEを省略する

ELSEは省略できます。

print(db.execute_sql("""
SELECT id,
       CASE WHEN rating > 4.0 THEN '高評価' END AS top
FROM articles
ORDER BY id
"""))

[{'id': 1, 'top': None},
 {'id': 2, 'top': '高評価'},
 {'id': 3, 'top': '高評価'},
 {'id': 4, 'top': None},
 {'id': 5, 'top': None}]

どのWHENにも一致しない行はNULLになります。id = 5はratingがNULLなので、比較の結果が真にならず、こちらもNULLです。

分岐の型をそろえる

分岐ごとに違う型の値を書くと、共通の数値型へそろえます。

print(db.execute_sql("""
SELECT id,
       CASE WHEN views >= 1000 THEN 1 ELSE 0.5 END AS score
FROM articles
ORDER BY id
"""))

[{'id': 1, 'score': 1.0},
 {'id': 2, 'score': 0.5},
 {'id': 3, 'score': 1.0},
 {'id': 4, 'score': 0.5},
 {'id': 5, 'score': 0.5}]

THEN 1は整数ですが、結果は1.0です。ELSE 0.5が浮動小数点なので、列全体が浮動小数点になります。

数値どうしのようにそろえられない組み合わせは、実行前に断られます。

db.execute_sql("SELECT CASE WHEN TRUE THEN 1 ELSE 'text' END")

error[ALOPEX-T001]: type mismatch: expected Integer, found Text

整数と文字列に共通の型がないためです。

一致した分岐だけが評価される

CASEは、最初に一致したWHENで評価を打ち切ります。

print(db.execute_sql("SELECT CASE WHEN TRUE THEN 7 ELSE 1 / 0 END AS v"))

[{'v': 7}]

ELSE側にゼロ除算を書いていますが、エラーになりません。WHEN TRUEが一致した時点で、ELSEは評価されないためです。

3. SELECTで付けた別名を後ろの句から参照する

第2回でAS nのような別名を使いました。この別名は、ORDER BYとHAVINGから名前で参照できます。

print(db.execute_sql("""
SELECT author, SUM(views) AS total
FROM articles
GROUP BY author
HAVING total >= 2000
ORDER BY author
"""))

[{'author': 'mio', 'total': 2000},
 {'author': 'ren', 'total': 2100}]

HAVING total >= 2000はHAVING SUM(views) >= 2000と書いたのと同じ意味になるので、集約式を2回書かずに済みます。

参照できるのはORDER BYとHAVINGの二つで、WHEREとGROUP BYからは見えません。

db.execute_sql("SELECT views AS v FROM articles WHERE v > 1000")

error[ALOPEX-C003]: column 'v' not found in table 'articles'

SQL標準では、WHEREとGROUP BYはSELECTより前に評価されます。別名がまだ存在しない段階なので、名前を解決できません。

名前が衝突した場合は別名が優先されます。

print(db.execute_sql("SELECT views AS id FROM articles ORDER BY id"))

[{'id': 300}, {'id': 600}, {'id': 800}, {'id': 1200}, {'id': 1500}]

ORDER BY idは主キーのidではなく、別名を付けたviewsで並びます。元の列で並べたいときはarticles.idのようにテーブル名を付けます。

4. UNION・INTERSECT・EXCEPT

閲覧数の多い記事と、評価の高い記事を、それぞれ取り出します。

print(db.execute_sql("SELECT id FROM articles WHERE views >= 800 ORDER BY id"))
print(db.execute_sql("SELECT id FROM articles WHERE rating >= 4.0 ORDER BY id"))

[{'id': 1}, {'id': 2}, {'id': 3}]
[{'id': 2}, {'id': 3}, {'id': 4}]

左は{1, 2, 3}、右は{2, 3, 4}です。この二つを四つの演算子で組み合わせます。

left = "SELECT id FROM articles WHERE views >= 800"
right = "SELECT id FROM articles WHERE rating >= 4.0"

for op in ["UNION", "UNION ALL", "INTERSECT", "EXCEPT"]:
    print(op, db.execute_sql(f"{left} {op} {right} ORDER BY id"))

UNION [{'id': 1}, {'id': 2}, {'id': 3}, {'id': 4}]
UNION ALL [{'id': 1}, {'id': 2}, {'id': 2}, {'id': 3}, {'id': 3}, {'id': 4}]
INTERSECT [{'id': 2}, {'id': 3}]
EXCEPT [{'id': 1}]

それぞれの意味は次のとおりです。

演算子	結果	意味
`UNION`	`{1, 2, 3, 4}`	両方を合わせ、重複を除く
`UNION ALL`	6行	両方を合わせ、重複を残す
`INTERSECT`	`{2, 3}`	両方にあるものだけ
`EXCEPT`	`{1}`	左にあって右にないもの

EXCEPTは左右を入れ替えると結果が変わります。

print(db.execute_sql(f"{right} EXCEPT {left} ORDER BY id"))

[{'id': 4}]

閲覧数は少ないが評価は高い記事、つまりid = 4が残ります。

演算子を三つ以上つなぐ

INTERSECTがUNION・EXCEPTより強く結び付きます。同じ強さのものは左から順に評価され、ALLはそれを書いた段だけに効きます。

print(db.execute_sql("SELECT 1 AS v UNION ALL SELECT 1 UNION SELECT 1"))
print(db.execute_sql("SELECT 1 AS v UNION SELECT 1 UNION ALL SELECT 1"))
print(db.execute_sql("SELECT 1 AS v UNION SELECT 2 INTERSECT SELECT 2"))

[{'v': 1}]
[{'v': 1}, {'v': 1}]
[{'v': 1}, {'v': 2}]

一つ目は最後のUNIONが全体の重複を除くので1行です。二つ目は先にUNIONが1行へ減らしたあと、UNION ALLが重複を残すので2行です。三つ目は2 INTERSECT 2が先に評価されます。

NULLと形の不一致

NULLは一つの値として扱われます。

print(db.execute_sql("SELECT rating FROM articles UNION SELECT rating FROM articles"))

[{'rating': 4.5}, {'rating': 4.0}, {'rating': 3.5}, {'rating': None}]

rating = 4.5は2件ありますが、UNIONが重複を除いて1行にします。NULLどうしも重複と判定し、1行にまとめます。

左右で列の数や型が合わない場合は、実行前に断られます。

db.execute_sql("SELECT id FROM articles UNION SELECT id, title FROM articles")

error[ALOPEX-T008]: set operation column count mismatch: left 1, right 2

db.execute_sql("SELECT id FROM articles UNION SELECT title FROM articles")

error[ALOPEX-T001]: type mismatch: expected Integer, found Text

5. WITH句で問い合わせに名前を付ける

SELECT文の前にWITH句を書くと、副問い合わせに名前を付けて、後ろの本体から参照できます。

WITH 名前 AS (SELECT ...)
SELECT ... FROM 名前;

この名前 AS (...)のまとまりを、SQLの用語では共通テーブル式（CTE、Common Table Expression）と呼びます。以降はCTEと書きます。

閲覧数の多い記事に名前を付け、同じ著者の記事と突き合わせます。

print(db.execute_sql("""
WITH popular AS (
  SELECT id, author FROM articles WHERE views >= 800
)
SELECT articles.id AS aid, popular.id AS pid
FROM articles
JOIN popular ON articles.author = popular.author
ORDER BY aid, pid
"""))

[{'aid': 1, 'pid': 1}, {'aid': 1, 'pid': 2},
 {'aid': 2, 'pid': 1}, {'aid': 2, 'pid': 2},
 {'aid': 3, 'pid': 3}, {'aid': 4, 'pid': 3}]

CTEは実テーブルと同じように結合します。mioの記事が2件ずつあるので、その組み合わせで4行になります。CTEにまとめても重複は自動では消えないので、一意にしたい場合はDISTINCTかGROUP BYを自分で書きます。

複数の定義を並べることもできます。後ろのCTEから、先に定義したCTEを参照できます。

同じ名前を付けるとどうなるか

CTEの名前は、同じ名前のベーステーブルをその文の中だけ隠します。

print(db.execute_sql("""
WITH articles AS (SELECT id + 100 AS id FROM articles WHERE id = 1)
SELECT id FROM articles
"""))

[{'id': 101}]

CTEの本体にあるFROM articlesはベーステーブルを指し、外側のFROM articlesはCTEを指します。定義の内と外で同じ名前が別のものを指すので、隠す意図がないなら名前を分けます。

自分を参照するCTE

CTEには自分自身を参照する書き方もあり、WITH RECURSIVEと書きます。組織の上司を根まで辿る、部品表を展開するといった、階層の深さが事前に決まらない問い合わせで使います。

WITH RECURSIVEは現時点では未対応です。ALOPEX-F001を返し、未対応であることを明示します。RECURSIVEを無視して1回だけ実行することはありません。

なお、定義していないCTE名をFROMに書いた場合はALOPEX-C001になります。テーブル名の誤りと同じ扱いです。

6. ウィンドウ関数で順位と累計を出す

GROUP BYは行をまとめてしまうので、個々の行を残したまま集計値を並べることはできません。ウィンドウ関数を使うと、行を残したまま計算できます。

print(db.execute_sql("""
SELECT id,
       RANK() OVER (ORDER BY rating) AS rk,
       DENSE_RANK() OVER (ORDER BY rating) AS dk,
       SUM(views) OVER (ORDER BY id) AS running
FROM articles
ORDER BY id
"""))

[{'id': 1, 'rk': 1, 'dk': 1, 'running': 1200},
 {'id': 2, 'rk': 3, 'dk': 3, 'running': 2000},
 {'id': 3, 'rk': 3, 'dk': 3, 'running': 3500},
 {'id': 4, 'rk': 2, 'dk': 2, 'running': 4100},
 {'id': 5, 'rk': 5, 'dk': 4, 'running': 4400}]

5行がそのまま5行で返っています。GROUP BYと違い、行は減りません。

rating = 4.5のid = 2とid = 3が同順位になり、RANKとDENSE_RANKで違いが出ます。RANKは同順位の次を飛ばして5にし、DENSE_RANKは飛ばさず4にします。

runningはidの順に足し上げた累計です。使えるのはランキング関数のROW_NUMBER・RANK・DENSE_RANKと、集約関数のSUM・COUNT・AVG・MIN・MAXです。

実際にv0.8.8でCASEとROW_NUMBER()を組み合わせた結果を録画しています。条件分岐の結果と順位が同時に返り、行をまとめずに各行の情報を残せることが分かります。

v0.8.8で条件分岐と順位付けを同時に実行した結果

動画を開く

集計する範囲を決める

OVERの中身で、集計する範囲が決まります。

print(db.execute_sql("SELECT id, SUM(views) OVER () AS grand FROM articles ORDER BY id"))
print(db.execute_sql("SELECT id, SUM(views) OVER (PARTITION BY author) AS by_author FROM articles ORDER BY id"))

[{'id': 1, 'grand': 4400}, {'id': 2, 'grand': 4400}, {'id': 3, 'grand': 4400}, {'id': 4, 'grand': 4400}, {'id': 5, 'grand': 4400}]
[{'id': 1, 'by_author': 2000}, {'id': 2, 'by_author': 2000}, {'id': 3, 'by_author': 2100}, {'id': 4, 'by_author': 2100}, {'id': 5, 'by_author': 300}]

OVER ()は全行の合計、PARTITION BY authorは著者ごとの合計です。先ほどのOVER (ORDER BY id)は、先頭から現在行までを累計します。

範囲の決まり方は二つです。

OVERの中にORDER BYがない：範囲全体
OVERの中にORDER BYがある：先頭から現在行まで

同じSUM(views)でも、OVERの中身で意味が変わります。

NULLの扱い

ウィンドウ集約もNULLを無視します。

print(db.execute_sql("SELECT id, SUM(rating) OVER (PARTITION BY author) AS r FROM articles ORDER BY id"))

[{'id': 1, 'r': 8.0}, {'id': 2, 'r': 8.0},
 {'id': 3, 'r': 8.5}, {'id': 4, 'r': 8.5},
 {'id': 5, 'r': None}]

soraはratingがNULLの記事1件だけなので、合計する値がなくNULLになります。0にはなりません。

前の行の値を使いたい場合

前後の行を直接参照するLAG・LEADと、ROWS BETWEEN・RANGE BETWEENによる範囲の明示は、現時点では未対応です。前者はALOPEX-F001、後者はALOPEX-P001を返します。

前の行との差分を出したい場合は、当面は自己結合で書きます。

print(db.execute_sql("""
SELECT curr.id, curr.views - prev.views AS diff
FROM articles AS curr
LEFT JOIN articles AS prev ON prev.id = curr.id - 1
ORDER BY curr.id
"""))

[{'id': 1, 'diff': None},
 {'id': 2, 'diff': -400},
 {'id': 3, 'diff': 700},
 {'id': 4, 'diff': -900},
 {'id': 5, 'diff': -300}]

id = 1には前の行がないので、第2回のLEFT JOINと同じくNULLになります。

7. 型と値の扱い

集計の結果が想定と違うとき、原因が型にあることがあります。articlesのviewsはINTEGER、ratingはREALです。この二つの列で、型が結果に現れる場面を確かめます。

print(db.execute_sql("SELECT SUM(views) AS total FROM articles"))
print(db.execute_sql("SELECT id, views * 1.5 AS scaled FROM articles WHERE id <= 2 ORDER BY id"))
print(db.execute_sql("SELECT pg_typeof(views) AS iv, pg_typeof(rating) AS rv FROM articles WHERE id = 1"))
print(db.execute_sql("SELECT COUNT(rating) AS cnt, COUNT(*) AS all_rows FROM articles"))

[{'total': 4400}]
[{'id': 1, 'scaled': 1800.0}, {'id': 2, 'scaled': 1200.0}]
[{'iv': 'integer', 'rv': 'real'}]
[{'cnt': 4, 'all_rows': 5}]

整数の合計は整数のままです。SUM(views)は4400であり、4400.0にはなりません。
整数と浮動小数点を混ぜた計算は浮動小数点になります。views * 1.5は1800.0です。
pg_typeof()で列の型を確かめられます。viewsはinteger、ratingはrealです。REALはFLOATの別名で、f32の4バイトです。倍精度が要る列はDOUBLEで宣言します。
COUNT(列名)はNULLを数えません。COUNT(rating)が4、COUNT(*)が5です。行数を数えたいのか、値のある行を数えたいのかで書き分けます。

8. Rustからウィンドウ関数とWITH句を使う

ウィンドウ関数もWITH句も、Rustのexecute_sql()にそのまま渡せます。第2回で書いたshow関数を使い回します。

use alopex_embedded::{Database, SqlResult};
use std::path::Path;

fn show(db: &Database, sql: &str) -> Result<(), Box<dyn std::error::Error>> {
    if let SqlResult::Query(q) = db.execute_sql(sql)? {
        let names: Vec<&str> = q.columns.iter().map(|c| c.name.as_str()).collect();
        println!("{}", names.join(" | "));
        for row in &q.rows {
            let cells: Vec<String> = row.iter().map(|v| format!("{v:?}")).collect();
            println!("{}", cells.join(" | "));
        }
    }
    Ok(())
}

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let db = Database::open(Path::new("./notes-db"))?;
    db.execute_sql("CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, author TEXT, views INTEGER, rating REAL)")?;
    for sql in [
        "INSERT INTO articles VALUES (1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5)",
        "INSERT INTO articles VALUES (2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5)",
        "INSERT INTO articles VALUES (3, 'ベクトル検索の基礎', 'ren', 1500, 4.5)",
        "INSERT INTO articles VALUES (4, '組み込みDBの選び方', 'ren', 600, 4.0)",
        "INSERT INTO articles VALUES (5, 'WALとクラッシュ復旧', 'sora', 300, NULL)",
    ] {
        db.execute_sql(sql)?;
    }

    show(&db, "SELECT title, rating,
                      RANK() OVER (ORDER BY rating DESC) AS r,
                      DENSE_RANK() OVER (ORDER BY rating DESC) AS dr
               FROM articles")?;
    Ok(())
}

title | rating | r | dr
Text("Rustで書くLSMツリー") | Float(3.5) | BigInt(4) | BigInt(3)
Text("SQLパーサをNimで実装する") | Float(4.5) | BigInt(1) | BigInt(1)
Text("ベクトル検索の基礎") | Float(4.5) | BigInt(1) | BigInt(1)
Text("組み込みDBの選び方") | Float(4.0) | BigInt(3) | BigInt(2)
Text("WALとクラッシュ復旧") | Null | BigInt(5) | BigInt(4)

順位は2章から6章までのPythonの結果と同じです。ratingが4.5の2件が両方とも1位で、RANKは次を3位、DENSE_RANKは2位にします。ratingがNULLのid = 5は最後に来ます。

7章で扱った型は、Rustでは表示にそのまま現れます。ratingはREALで宣言したのでFloat、順位はBigIntです。同じ問い合わせをAVGとCOUNTに変えると、集約の結果の型が分かります。

show(&db, "SELECT AVG(rating) AS avg_rating, COUNT(rating) AS c_rating, COUNT(*) AS c_all
           FROM articles")?;

avg_rating | c_rating | c_all
Double(4.125) | BigInt(4) | BigInt(5)

ratingの列はFloat（32ビット）で、AVGの結果はDouble（64ビット）です。平均を64ビットで計算するためです。COUNT(rating)が4、COUNT(*)が5になるのも7章と同じで、NULLは数に入りません。

Pythonでは、この違いは4.125という数値として返るだけで見えません。Rustは値と一緒に型が返るので、FloatとDoubleのどちらで返ったかを実行結果から確かめられます。列の型が想定と違うときは、q.columnsのdata_typeも合わせて読みます。

9. ここまでで使ったSQL

構文	役割
`CASE WHEN ... THEN ... ELSE ... END`	条件で値を振り分ける
`UNION` / `UNION ALL`	二つの結果を合わせる
`INTERSECT` / `EXCEPT`	共通部分、差分を取る
`WITH 名前 AS (...)`	問い合わせに名前を付ける
`RANK()` / `DENSE_RANK()` / `ROW_NUMBER()`	順位を付ける
`SUM(...) OVER (...)`	行を残したまま集計する
`HAVING 別名` / `ORDER BY 別名`	`SELECT`で付けた名前を使う

次回は、ベクトル検索とDataFrameを扱います。

参照

v0.8.8 リリース確認レポート