SQL

SQL WITH句(CTE)の使い方【長いクエリを読みやすく組み立てる】

酒井 裕麻株式会社Bistro 代表取締役社長

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句で「段階ごとに名前を付けて分ける」ことを意識するだけで、自分にもチームのメンバーにも読みやすいクエリになります。

CONTACT

データ活用のお悩み、
直接相談しませんか。

「何から始めればいいかわからない」段階でも大丈夫です。課題の整理からお手伝いします。