複雑な分析要件に対応するSQLクエリは、しばしば長大化し、理解やメンテナンスが難しくなることがあります。しかし、SQLのWITH句（CTE: Common Table Expression、共通テーブル式とも呼ぶ）を効果的に活用し、処理を論理的なステップに分割することで、見通しが良く、変更にも強いクエリを構築できます。
本記事では、そのためのシンプルな3ステップについて解説します。
対象読者
この記事は、SQLを記述するだけでなく、その価値を最大限に引き出し、長期的に活用していきたいと考えている人におすすめです。
- 作成したSQLを再利用したり、条件を変えて複数の集計に用いたい
- SQLクエリを定期実行するなど、保守や運用を前提として記述している
- 過去の複雑なSQLを読み解き、改善する必要性を感じている
- 長大なSQLの記述を、より構造的で理解しやすい形にしたい
- SQLレビューの品質やチームの共通理解を高めたい
SQLクエリ設計の基本：WITH句による処理の段階的構築
メンテナンス性の高いSQLクエリでは、一連のデータ処理が、意味のある単位で区切られた複数のWITH句として段階的に構築されます。この段階を意識することで、複雑なロジックが整理され、可読性が向上します。
具体的には、以下3つの主要なステップを意識します。
1. ログテーブル：分析の材料を準備
まず、分析の出発点となる「ログテーブル」を準備します。ここでの「ログテーブル」とは、イベントログのような時系列データを指すのではなく、集計に必要な元データをそのままの粒度で保持した、一次加工済みのデータセットのことです。このステップのWITH句では、最終的に見たい指標を計算するために必要な情報を過不足なく集め、基本的な絞り込みや整形を行い、分析の「素材」となるクリーンなデータセットを作る役割を担います。
主な処理
- データソースから必要な列を切り出す（例：商品テーブルから商品IDと商品名、商品カテゴリを取り出す）
- WHERE 句で範囲を絞る（例：売上テーブルから直近2ヶ月の売上を絞り込む）
- 複数の材料を JOIN する（例：売上、商品、店舗テーブルを結合して、分析に必要な情報がすべて揃ったテーブルを作る）
2. サマリーテーブル：集計を行う
次に、ステップ1で準備したログテーブルに対し、分析目的に応じた特定の切り口（分析軸）でデータを集約します。
より具体的な分析ニーズに合わせた形にデータを変換し、最終的なレポート作成やアドホック分析を効率化する、中間集計結果としての役割を担います。
主な処理
- 分析軸に沿って GROUP BY を行う（例：商品×月ごとに、その月にその商品がいくら売れたのかを集計する）
- SUM() や AVG() などの集計処理を行う（例：月ごとの全店舗合計売上や1店舗あたりの平均売上を集計する）
- WINDOW を用いて、累積や移動平均を計算する（例：商品ごとに、発売してからの月ごとの累計売上を集計する）
3. アウトプットテーブル：ユースケースの要件を満たす
最後に、これまでのステップで準備・加工してきたデータを、レポートやダッシュボードなど、具体的なユースケースの要件に合わせて最終的な形に整えます。SQLクエリの最後に存在するWITH句が、この役割を担います。
主な処理
- 列名を日本語にする（例：列名 "avg_daily_sales" を「今月の商品日販」に書き換えて、わかりやすくする）
- WHERE や LIMIT を用いた、最終的なフィルタリング（例：売上が高い上位10商品に絞り込む）
- ORDER BY による並び替え（例：売上が高い順に商品を並べる）
- 数値の丸め処理や文字列の結合といった書式設定（例：数値「1.2345678」を「1.23倍」に書き換えて、意味を一目で読み取りやすくする）
具体例
具体的なシナリオで見ていきましょう。
「あるスーパーの千駄ヶ谷店について、生鮮食品に絞った売れ筋商品を知りたい」というニーズに応えるクエリを作成します。
具体的には、先月と比較して売上が10%以上増加しており、かつ1日平均で10万円以上売れている商品を「売れ筋商品」とします。
以下のテーブルを用います。
| テーブル名 | ラベル | 説明 |
|---|---|---|
| sales | 売上実績 | いつどこで何がどれだけ売れたか |
| products | 商品 | 商品のマスタ |
| stores | 店舗 | 店舗のマスタ |
ステップ1：分析用ログテーブル(sales_details)
分析に必要な情報を揃え、結合したログテーブルを作ります。
with import_sales as (
select
transaction_date,
store_id,
product_id,
quantity_sold,
unit_price
from
sales
where
transaction_date between date_trunc(date_sub(current_date(), interval 1 month), month)
and last_day(current_date()) -- 直近2ヶ月に絞る
)
,
import_products as (
select
product_id,
product_name,
category_name
from
products
where
category_name = "生鮮食品" -- 生鮮食品に絞る
)
,
import_stores as (
select
store_id
from
stores
where
store_name = "千駄ヶ谷店" -- 千駄ヶ谷店に絞る
)
,
logic_sales_details as (
select
transaction_id, -- 分析上は不要だが、あるとテーブルの粒度をイメージしやすい
transaction_date,
date_trunc(transaction_date, month) as target_month,
product_name,
quantity_sold * unit_price as sales_amount
from
import_sales
inner join import_products using(product_id) -- 生鮮食品の商品に絞る
inner join import_stores using(store_id) -- 千駄ヶ谷店に絞る
)
,
ログテーブルのイメージは以下です。
| transaction_id | transaction_date | target_month | product_name | sales_amount |
|---|---|---|---|---|
| TRN0000000123 | 2025-05-15 | 2025-05-01 | 国産朝採れ大玉トマト | 1,200 |
| TRN0000000124 | 2025-05-15 | 2025-05-01 | 新鮮！朝どれきゅうりパック | 700 |
| TRN0000000125 | 2025-05-15 | 2025-05-01 | 国産朝採れ大玉トマト | 600 |
| TRN0000000128 | 2025-05-16 | 2025-05-01 | 国産朝採れ大玉トマト | 800 |
| TRN0000000129 | 2025-05-16 | 2025-05-01 | 甘熟いちご（とちおとめ） | 2,500 |
| ... (5月の他の取引データ) ... | ... | ... | ... | ... |
| TRN0000000055 | 2025-04-10 | 2025-04-01 | 国産朝採れ大玉トマト | 1,000 |
| TRN0000000056 | 2025-04-10 | 2025-04-01 | 新鮮！朝どれきゅうりパック | 600 |
| TRN0000000059 | 2025-04-11 | 2025-04-01 | 甘熟いちご（とちおとめ） | 2,000 |
| ... (4月の他の取引データ) ... | ... | ... | ... | ... |
ステップ2：サマリーテーブル(logic_joined)
ログテーブルに対して、月×商品でデータを集約し、売上や前月比を計算します。「売れ筋」を定義します。
logic_monthly_sales as (
select
target_month,
product_name,
sum(sales_amount) as sales_amount, -- その月の売上
count(distinct transaction_date) as sales_days_in_month -- その月の販売日
from
logic_sales_details
group by
1, 2
)
,
logic_product_average_daily_sales as (
select
target_month,
product_name,
sales_amount,
sales_days_in_month,
safe_divide(sales_amount, sales_days_in_month) as avg_daily_sales
from
logic_monthly_sales
)
,
logic_current_month_sales as (
select * from logic_product_average_daily_sales
where target_month = date_trunc(current_date(), month)
)
,
logic_previous_month_sales as (
select * from logic_product_average_daily_sales
where target_month = date_trunc(date_sub(current_date(), interval 1 month), month)
)
,
logic_joined as (
select
logic_current_month_sales.product_name,
logic_current_month_sales.avg_daily_sales,
coalesce(previous_month_sales.avg_daily_sales, 0) as avg_daily_sales_previous_month,
safe_divide(
logic_current_month_sales.avg_daily_sales,
coalesce(previous_month_sales.avg_daily_sales, 0)
) as sales_ratio_to_previous_month -- 売上の先月比。先月の売上が無い場合はnullになる
from
logic_current_month_sales
inner join previous_month_sales using(product_name) -- 今月発売商品は除く
)
,
サマリーテーブルのイメージは以下です。
| product_name | avg_daily_sales | avg_daily_sales_previous_month | sales_ratio_to_previous_month |
|---|---|---|---|
| 国産朝採れ大玉トマト | 350,000.00 | 278,000.00 | 1.2589 |
| 新鮮！朝どれきゅうりパック | 280,000.00 | 243,000.00 | 1.1522 |
| 甘熟いちご（とちおとめ） | 250,000.00 | 192,000.00 | 1.3020 |
| 旬の特選キャベツ | 220,000.00 | 186,000.00 | 1.1827 |
| 有機栽培ほうれん草 | 190,000.00 | 130,000.00 | 1.4615 |
| シャキシャキレタス | 185,000.00 | 165,000.00 | 1.1212 |
| 産地直送 新じゃがいも | 170,000.00 | 138,000.00 | 1.2318 |
| 色鮮やかパプリカセット | 150,000.00 | 100,000.00 | 1.5000 |
| 泥付き新鮮ごぼう | 130,000.00 | 117,500.00 | 1.1063 |
| 旨味たっぷり生しいたけ | 110,000.00 | 81,000.00 | 1.3580 |
| ... (他の商品データ) ... | ... | ... | ... |
ステップ3：アウトプットテーブル(final)
「売れ筋」商品に絞り込み、売上が高い順に並び替え、列名を日本語にします。
final as (
select
product_name as `商品名`,
round(avg_daily_sales, 0) as `今月の平均日販`,
round(sales_ratio_to_previous_month * 100, 1) as `先月比(%)`
from
logic_joined
where
sales_ratio_to_previous_month >= 1.1 -- 先月比で日次売上が10ポイント以上伸びている
and avg_daily_sales >= 100000 -- 1日平均で10万円上売れている
)
select * from final order by avg_daily_sales desc limit 10
アウトプットテーブルのイメージは以下です。
| 商品名 | 今月の平均日販 | 先月比(%) |
|---|---|---|
| 国産朝採れ大玉トマト | 350,000 | 125.5 |
| 新鮮！朝どれきゅうりパック | 280,000 | 115.2 |
| 甘熟いちご（とちおとめ） | 250,000 | 130.1 |
| 旬の特選キャベツ | 220,000 | 118.0 |
| 有機栽培ほうれん草 | 190,000 | 145.3 |
| シャキシャキレタス | 185,000 | 112.0 |
| 産地直送 新じゃがいも | 170,000 | 122.8 |
| 色鮮やかパプリカセット | 150,000 | 150.0 |
| 泥付き新鮮ごぼう | 130,000 | 110.5 |
| 旨味たっぷり生しいたけ | 110,000 | 135.7 |
こうして、売れ筋商品が一目でわかるようになりましt。
まとめ
本記事では、SQLクエリの可読性とメンテナンス性を高めるための3ステップについて解説しました。
多くの分析用SQLクエリは、「準備：ログテーブル」「集計：サマリーテーブル」「表示：アウトプットテーブル」の3ステップに分割して考えることで、各処理の役割が明確になり、クエリ全体の見通しがよくなります。
SQLのレビューがしやすくなりますし、似たようなSQLを使い回す際も、各ステップのWITH句を部品として再利用したり、一部を差し替えて別の分析に展開したりすることが容易になります。
日々の分析業務でSQLを書く際に、この3ステップを意識することで、分析者自身にとっても、チームの他のメンバーにとっても、より扱いやすく、長期的に価値を生み出し続けるSQLクエリを作成できるはずです。