[sqlite3] 共通テーブル式(CTE)

[SQL] 共通テーブル式 (CTE)

今回はSQLの共通テーブル式（Common Table Expression, CTE）について解説する。 CTEは WITH 句を使って一時的な結果セットに名前を付け、それを後続のクエリで参照できる機能である。

複雑なサブクエリ（副問合せ）をCTEを使って書き直すことで、クエリの可読性が大幅に向上し、ロジックの再利用もしやすくなる。

今回は、サブクエリを使った場合とCTEを使った場合を比較し、そのメリットを体感してみる。

準備

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

データの準備

サンプルとして、売上データを格納した Sales テーブルを作成する。

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 Sales (
    id INTEGER PRIMARY KEY,
    sale_date TEXT,
    category TEXT,
    amount INTEGER
);

INSERT INTO Sales (sale_date, category, amount) VALUES
    ('2023-01-01', 'Electronics', 1000),
    ('2023-01-02', 'Clothing', 500),
    ('2023-01-03', 'Electronics', 1200),
    ('2023-01-04', 'Food', 300),
    ('2023-01-05', 'Clothing', 600),
    ('2023-01-06', 'Electronics', 1100),
    ('2023-01-07', 'Food', 400);

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





[]

%%sql
SELECT * FROM Sales;

 * sqlite:///data.db
Done.

id	sale_date	category	amount
1	2023-01-01	Electronics	1000
2	2023-01-02	Clothing	500
3	2023-01-03	Electronics	1200
4	2023-01-04	Food	300
5	2023-01-05	Clothing	600
6	2023-01-06	Electronics	1100
7	2023-01-07	Food	400

課題：カテゴリごとの売上合計が、全カテゴリの平均売上合計よりも高いカテゴリを抽出する

少し複雑な条件だが、これをSQLで表現してみる。

カテゴリごとの売上合計を計算する。
その中から、全カテゴリの平均売上合計よりも高いものをフィルタリングする。

方法1: サブクエリを使用する場合

まずはCTEを使わずに、サブクエリ（インラインビュー）を使って書いてみる。

query = """
SELECT
    category,
    total_sales
FROM (
    -- カテゴリごとの売上合計を計算
    SELECT
        category,
        SUM(amount) as total_sales
    FROM
        Sales
    GROUP BY
        category
) as CategorySales
WHERE
    total_sales > (
        -- 全カテゴリの平均売上合計を計算
        SELECT
            AVG(total_sales)
        FROM (
            SELECT
                category,
                SUM(amount) as total_sales
            FROM
                Sales
            GROUP BY
                category
        )
    );
"""

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

shape: (1, 2)

category	total_sales
“Electronics”	3300

同じ集計（GROUP BY category）が2回登場しており、ネストも深くて読みづらい。

方法2: CTEを使用する場合

次に、CTE（WITH 句）を使って書き直してみる。

query = """
WITH CategorySales AS (
    -- カテゴリごとの売上合計を計算（ここで定義して再利用）
    SELECT
        category,
        SUM(amount) as total_sales
    FROM
        Sales
    GROUP BY
        category
)
SELECT
    category,
    total_sales
FROM
    CategorySales
WHERE
    total_sales > (SELECT AVG(total_sales) FROM CategorySales);
"""

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