SQLite | ゼロからはじめるSQL、30日で習得するSQLite：実践 － Day27 実践クエリ

SQL SQLite
スポンサーリンク

Day27 後半のゴール

「“条件付きの複雑クエリ”を、自分で組み立てて説明できるようになる」

前半では、
FROM〜JOIN → GROUP BY → SELECT の順で読むことで、
「顧客×商品ごとの売上」などを分解して理解するところまで行きました。

後半では、そこにさらに

期間条件（いつのデータか）
ステータス条件（有効・キャンセルなど）
サブクエリ（“一度まとめてから”さらに集計）
ビュー（よく使う複雑クエリに名前をつける）

を絡めて、
「現場でそのまま使えるレベルの複雑クエリ」を組み立てていきます。

条件付きの複雑クエリの基本形

「JOIN で世界を作り、WHERE で“対象”を絞る」

まずは、前半で扱った「顧客×商品ごとの売上」に
期間とステータスの条件を足してみます。

前半のベースはこれでした。

SELECT
  c.name,
  p.name,
  SUM(oi.quantity * oi.unit_price) AS total_amount
FROM customers c
JOIN orders o
  ON c.id = o.customer_id
JOIN order_items oi
  ON o.id = oi.order_id
JOIN products p
  ON oi.product_id = p.id
GROUP BY
  c.name,
  p.name;
SQL

ここに、「2025年5月以降の、有効な注文だけ」という条件を足します。

SELECT
  c.name,
  p.name,
  SUM(oi.quantity * oi.unit_price) AS total_amount
FROM customers c
JOIN orders o
  ON c.id = o.customer_id
JOIN order_items oi
  ON o.id = oi.order_id
JOIN products p
  ON oi.product_id = p.id
WHERE
  o.status = 1
  AND o.ordered_at >= '2025-05-01 00:00:00'
GROUP BY
  c.name,
  p.name;
SQL

ここで意識してほしいのは順番です。

FROM〜JOIN で「顧客×注文×明細×商品」の世界を作る
WHERE で「その中から“有効な注文かつ2025年5月以降”だけを残す」
GROUP BY で「顧客×商品ごとにまとめる」

この流れが見えていれば、
条件が増えても「やっていることは同じ」と感じられるはずです。

「売上トップN顧客」を出す

「まず顧客ごとに集計してから、“上位だけ”を取る」

次に、「売上トップ3の顧客」を出してみます。
ここで初めて、サブクエリを絡めます。

まず、「顧客ごとの売上合計」を出すクエリを作ります。

SELECT
  c.id   AS customer_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS total_amount
FROM customers c
JOIN orders o
  ON c.id = o.customer_id
JOIN order_items oi
  ON o.id = oi.order_id
WHERE
  o.status = 1
GROUP BY
  c.id,
  c.name;
SQL

これを、そのまま「仮のテーブル」として扱います。

SELECT
  customer_id,
  customer_name,
  total_amount
FROM (
  SELECT
    c.id   AS customer_id,
    c.name AS customer_name,
    SUM(oi.quantity * oi.unit_price) AS total_amount
  FROM customers c
  JOIN orders o
    ON c.id = o.customer_id
  JOIN order_items oi
    ON o.id = oi.order_id
  WHERE
    o.status = 1
  GROUP BY
    c.id,
    c.name
) AS customer_sales
ORDER BY
  total_amount DESC
LIMIT 3;
SQL

内側の SELECT で「顧客ごとの売上合計」を作り、
外側の SELECT で「売上順に並べて上位3件だけ取る」という構造です。

ここでの重要ポイントは、

「一度まとめてから、さらに絞る／並べる」というときに
サブクエリが“中間テーブル”として使える

という感覚です。

「商品別売上ランキング」を作る

「起点を products に変えても、考え方は同じ」

今度は、「商品ごとの売上ランキング」を作ってみます。
やることは、さっきの顧客版とほぼ同じです。

まず、「商品ごとの売上合計」を出すクエリを作ります。

SELECT
  p.id   AS product_id,
  p.name AS product_name,
  SUM(oi.quantity * oi.unit_price) AS total_amount
FROM products p
JOIN order_items oi
  ON p.id = oi.product_id
JOIN orders o
  ON oi.order_id = o.id
WHERE
  o.status = 1
GROUP BY
  p.id,
  p.name;
SQL

これをサブクエリにして、ランキングにします。

SELECT
  product_id,
  product_name,
  total_amount
FROM (
  SELECT
    p.id   AS product_id,
    p.name AS product_name,
    SUM(oi.quantity * oi.unit_price) AS total_amount
  FROM products p
  JOIN order_items oi
    ON p.id = oi.product_id
  JOIN orders o
    ON oi.order_id = o.id
  WHERE
    o.status = 1
  GROUP BY
    p.id,
    p.name
) AS product_sales
ORDER BY
  total_amount DESC;
SQL

顧客版との違いは、「起点が customers か products か」だけです。

このあたりで、

起点テーブルを変えるだけで、
「顧客別」「商品別」「日別」など、
いろいろな切り口の集計が書ける

という感覚が、少しずつ体に入ってきているはずです。

「日別売上推移」を出す

「日時から“日付だけ”を取り出して集計する」

次は、「日別の売上推移」を出してみます。
ここでは、ordered_at から日付部分だけを取り出して使います。

SQLite では、date() 関数で日付部分を取り出せます。

SELECT
  date(o.ordered_at) AS order_date,
  SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN order_items oi
  ON o.id = oi.order_id
WHERE
  o.status = 1
GROUP BY
  date(o.ordered_at)
ORDER BY
  order_date;
SQL

このクエリの流れはこうです。

FROM〜JOIN
orders と order_items をつなぎ、「注文×明細」の世界を作る

WHERE
有効な注文だけに絞る

GROUP BY
date(o.ordered_at)、つまり「日付ごと」にまとめる

SELECT
日付ごとに、売上合計を出す

ここでのポイントは、

GROUP BY に「生のカラム」ではなく「関数で加工した値」を使っている

というところです。

「日付ごと」「月ごと」「年ごと」など、
時間軸の集計ではよく出てくるパターンなので、
date() や strftime() と GROUP BY の組み合わせは、
一度手を動かして慣れておくと強いです。

よく使う複雑クエリは VIEW にしてしまう

「“長いけどよく使う”ものには名前をつける」

ここまでのクエリ、正直なところ長いです。
毎回これをコピペしていると、どこかでミスります。

そこで出てくるのが VIEW です。

例えば、「顧客ごとの売上合計」をよく使うなら、
こんな VIEW を作ってしまえます。

CREATE VIEW customer_sales AS
SELECT
  c.id   AS customer_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS total_amount
FROM customers c
JOIN orders o
  ON c.id = o.customer_id
JOIN order_items oi
  ON o.id = oi.order_id
WHERE
  o.status = 1
GROUP BY
  c.id,
  c.name;
SQL

こうしておけば、
「顧客別売上ランキング」はこう書けます。

SELECT
  customer_id,
  customer_name,
  total_amount
FROM customer_sales
ORDER BY
  total_amount DESC
LIMIT 10;
SQL

VIEW のポイントは、

複雑な JOIN＋GROUP BY を“1つの名前”に隠せる
定義を1か所にまとめておけるので、仕様変更に強い

というところです。

「このビューは、こういう前提で集計した数字です」と
コメントやドキュメントに残しておけば、
チーム全体で同じ意味の数字を使い回せます。

サブクエリと VIEW の使い分け

「一時的に使うならサブクエリ、何度も使うなら VIEW」

ここまでで、

サブクエリ
VIEW

という、似たような“まとめ方”が出てきました。

ざっくりした使い分けの感覚はこうです。

その場限りの中間結果を作りたい
→ サブクエリで十分

同じ集計ロジックを何度も使う
→ VIEW にして名前をつける

例えば、「この画面だけで使う一時的なランキング」ならサブクエリでよくて、
「レポート・ダッシュボード・バッチ処理など、あちこちで使う顧客別売上」なら
VIEW にしておいた方が安全です。

どちらも、「複雑な JOIN＋集計を“ひとまとまり”として扱う」ための道具です。
道具の選び方を知っていると、SQL がだいぶ書きやすくなります。

セキュリティ・監査の視点から見た“集計ロジックの共通化”

「“どの数字も同じ定義で出しているか”は、かなり重要」

売上や顧客数のような数字は、
部署やシステムをまたいで使われます。

もし、

画面Aでは「顧客別売上」をこう計算している
画面Bでは「顧客別売上」を別の条件で計算している

という状態になっていると、
「どの数字が正しいのか」が分からなくなります。

VIEW や共通のサブクエリ定義を使って、

「顧客別売上は、この定義で計算する」
「キャンセルは含めない」
「期間条件はこう」

といったルールを一元化しておくことは、
セキュリティ・監査の観点からも重要です。

数字の定義がバラバラだと、
「このレポートの数字は、あのレポートと何が違うのか？」
という説明ができなくなり、
結果として信頼を失います。

Day27 後半のまとめ

複雑な JOIN に期間条件やステータス条件を足すときも、流れは「JOIN で世界を作る → WHERE で対象を絞る → GROUP BY で単位を決める」という一貫したものとして捉えられる。
「売上トップN顧客」「商品別売上ランキング」のようなクエリは、一度「顧客ごと」「商品ごと」に集計するサブクエリを作り、その結果に対して ORDER BY・LIMIT をかける構造にするとスッキリ書ける。
日別売上推移などの時間軸の集計では、date() や strftime() で日時から日付・月などを取り出し、それを GROUP BY のキーにするのが定番パターンになる。
よく使う複雑な JOIN＋集計は VIEW にして名前をつけておくと、同じ定義を何度も使い回せて、仕様変更やバグ修正のときに「直す場所が1か所」で済む。
サブクエリは「その場限りの中間結果」、VIEW は「何度も使う共通ロジック」として使い分けると、集計ロジックの一貫性を保ちやすくなり、レポートや監査の場面で「この数字はこういう前提で出しています」と胸を張って説明できる。

ここまで来たあなたは、
「複雑な JOIN と集計を怖がる側」から、
「分解して設計し、必要なら名前をつけて再利用する側」に回りつつあります。
この感覚は、SQL を“道具”として本気で使っていくうえでの大きな転換点です。

前へ
12
スポンサーリンク
タイトルとURLをコピーしました