SQL WITH句(CTE)の使い方【長いクエリを読みやすく組み立てる】
SQLで少し複雑な集計をしようとすると、サブクエリの中にサブクエリが入り、カッコだらけで読みにくいクエリになりがちです。そんなときに使うのが WITH句(CTE:共通テーブル式) です。
WITH句を使うと、処理を段階ごとに分けて、名前を付けながら組み立てられます。この記事では、WITH句の書き方と、実務で役立つ使い方を、BigQueryでの実行結果付きで解説します。
参考記事
SQL JOINの使い方【INNER・LEFT・FULL OUTERの違いを実行結果で解説】
SQL CASE式の使い方【条件で値を分ける・集計をクロス表にする】
SQLウィンドウ関数の使い方【ROW_NUMBER・RANK・LAG・累計・移動平均】
この記事で使うデータ
JOINの記事と同じ、架空のお店の顧客テーブル(shop.customers)・注文テーブル(shop.orders)・日別売上テーブル(shop.daily_sales)を使います。BigQueryで同じデータを作る場合は、サンプルデータのSQL(shop_sample.sql)を実行してください。
WITH句の基本の書き方
WITH 名前 AS (
SELECT ...
)
SELECT
...
FROM
名前
WITH 名前 AS ( … ) で、カッコの中のSELECTの結果に名前を付けます。その後のSQLでは、その名前を普通のテーブルと同じように使えます。この一時的な表は、そのクエリの中だけで使えて、実行が終わると消えます。
顧客ごとの売上を集計して、4,000円以上の顧客だけを取り出してみます。
WITH customer_sales AS (
SELECT
customer_id
, COUNT(*) AS orders
, SUM(amount) AS sales
FROM
shop.orders
GROUP BY
customer_id
)
SELECT
*
FROM
customer_sales
WHERE
sales >= 4000
ORDER BY
sales DESC
実行結果
| customer_id | orders | sales |
|---|---|---|
| C05 | 2 | 12200 |
| C01 | 3 | 8100 |
| C03 | 2 | 4400 |
| C02 | 2 | 4000 |
サブクエリの入れ子との違い
次の2つのSQLは、どちらも「顧客を『未購入・1回・リピーター』に分けて、区分ごとの人数と売上を出す」ものです。
サブクエリの入れ子で書いた場合
SELECT
segment
, COUNT(*) AS customers
, SUM(sales) AS sales
, ROUND(AVG(sales)) AS avg_sales
FROM (
SELECT
*
, CASE
WHEN orders = 0 THEN '未購入'
WHEN orders = 1 THEN '1回'
ELSE 'リピーター'
END AS segment
FROM (
SELECT
c.customer_id
, c.rank
, COUNT(o.order_id) AS orders
, IFNULL(SUM(o.amount), 0) AS sales
FROM
shop.customers AS c
LEFT JOIN shop.orders AS o
ON c.customer_id = o.customer_id
GROUP BY
c.customer_id
, c.rank
)
)
GROUP BY
segment
ORDER BY
sales DESC
WITH句で書いた場合
WITH
-- ① 顧客ごとの注文数と売上
customer_sales AS (
SELECT
c.customer_id
, c.rank
, COUNT(o.order_id) AS orders
, IFNULL(SUM(o.amount), 0) AS sales
FROM
shop.customers AS c
LEFT JOIN shop.orders AS o
ON c.customer_id = o.customer_id
GROUP BY
c.customer_id
, c.rank
),
-- ② 注文数で区分を付ける
segmented AS (
SELECT
*
, CASE
WHEN orders = 0 THEN '未購入'
WHEN orders = 1 THEN '1回'
ELSE 'リピーター'
END AS segment
FROM
customer_sales
)
-- ③ 区分ごとに集計する
SELECT
segment
, COUNT(*) AS customers
, SUM(sales) AS sales
, ROUND(AVG(sales)) AS avg_sales
FROM
segmented
GROUP BY
segment
ORDER BY
sales DESC
実行結果(どちらも同じ)
| segment | customers | sales | avg_sales |
|---|---|---|---|
| リピーター | 4 | 28700 | 7175 |
| 1回 | 2 | 5400 | 2700 |
| 未購入 | 2 | 0 | 0 |
入れ子のサブクエリは、内側から外側へ読んでいかないと処理の流れがわかりません。WITH句なら、①→②→③と上から下へ順番に読めます。
複数の一時的な表を作るときは、WITH は最初に1回だけ書き、2つ目からは ,(カンマ)で区切って並べます。後ろの表は、前の表を参照できます。
途中の結果を確かめやすい
WITH句のもう1つの大きなメリットは、途中の段階の結果を確かめやすいことです。最後のSELECTを、たとえば SELECT * FROM segmented に書き換えて実行すれば、②の段階の結果がそのまま見られます。
集計の数字がおかしいときに、どの段階で間違えているかを1つずつ確かめられるので、デバッグが楽になります。
同じ集計を2回使う
WITH句で作った表は、そのあとのSQLで何回でも参照できます。顧客ごとの売上(customer_sales)と、その平均(overall)を作り、各顧客が平均より上かどうかを判定してみます。
WITH
customer_sales AS (
SELECT
customer_id
, SUM(amount) AS sales
FROM
shop.orders
GROUP BY
customer_id
),
overall AS (
SELECT
AVG(sales) AS avg_sales
FROM
customer_sales
)
SELECT
cs.customer_id
, cs.sales
, ROUND(o.avg_sales) AS avg_sales
, cs.sales > o.avg_sales AS above_avg
FROM
customer_sales AS cs
CROSS JOIN overall AS o
ORDER BY
cs.sales DESC
実行結果
| customer_id | sales | avg_sales | above_avg |
|---|---|---|---|
| C05 | 12200 | 4957 | true |
| C01 | 8100 | 4957 | true |
| C03 | 4400 | 4957 | false |
| C02 | 4000 | 4957 | false |
| C07 | 3000 | 4957 | false |
| C06 | 2400 | 4957 | false |
| C09 | 600 | 4957 | false |
overall は1行だけの表なので、CROSS JOIN ですべての行に平均値をくっつけています。平均(約4,957円)を超えているのは、C05とC01の2人だけでした。
実践:週ごとに集計して前週比を出す
WITH句とウィンドウ関数を組み合わせると、「まず週ごとに集計し、その結果で前週比を出す」という2段階の処理も読みやすく書けます。
WITH weekly AS (
SELECT
DATE_TRUNC(sales_date, WEEK(MONDAY)) AS week_start
, store
, SUM(sales) AS sales
FROM
shop.daily_sales
WHERE
sales_date BETWEEN '2026-03-02' AND '2026-03-22'
GROUP BY
week_start
, store
)
SELECT
week_start
, store
, sales
, LAG(sales) OVER (PARTITION BY store ORDER BY week_start) AS prev_week
, ROUND((sales / LAG(sales) OVER (PARTITION BY store ORDER BY week_start) - 1) * 100, 1) AS wow_pct
FROM
weekly
ORDER BY
store
, week_start
実行結果
| week_start | store | sales | prev_week | wow_pct |
|---|---|---|---|---|
| 2026-03-02 | 横浜店 | 974200 | NULL | NULL |
| 2026-03-09 | 横浜店 | 1026900 | 974200 | 5.4 |
| 2026-03-16 | 横浜店 | 980200 | 1026900 | -4.5 |
| 2026-03-02 | 渋谷店 | 1385200 | NULL | NULL |
| 2026-03-09 | 渋谷店 | 1378500 | 1385200 | -0.5 |
| 2026-03-16 | 渋谷店 | 1398000 | 1378500 | 1.4 |
DATE_TRUNC(日付, WEEK(MONDAY)) は、日付を「その週の月曜日」にそろえる関数です。月曜〜日曜がそろった3週間(3月2日〜22日)だけに絞っているのは、途中から始まる週や途中で終わる週を含めると、前週比が不自然に大きく動いてしまうからです。
読みやすいSQLを書くコツ
- 一時的な表には、中身がわかる名前を付ける:
t1、tmpではなく、customer_sales、weeklyのように - 1つの表で1つのことだけをする:「つなぐ」「区分を付ける」「集計する」を、それぞれ別の表に分ける
- コメントで段階を説明する:
-- ① 顧客ごとの注文数と売上のように、各表の上に一言書いておく - 最初に「元データを絞る」表を作る:期間や必要な列を最初に絞っておくと、後の処理がシンプルになり、BigQueryの料金も抑えられる
まとめ
- WITH句は
WITH 名前 AS (SELECT …)で、クエリの中だけで使える一時的な表を作る - 2つ目以降はカンマで区切って並べ、後ろの表から前の表を参照できる
- 入れ子のサブクエリと違い、上から下へ順番に読める
- 途中の段階の結果を確かめやすく、同じ表を何回でも使える
- ウィンドウ関数などと組み合わせて、複数段階の集計を読みやすく書ける
SQLが長くなってきたら、WITH句で「段階ごとに名前を付けて分ける」ことを意識するだけで、自分にもチームのメンバーにも読みやすいクエリになります。
NEWSLETTER
Data Schoolの新着記事をメールで受け取る
新しい記事が公開されたら、メールでお知らせします。配信はいつでも停止できます。
