SQLite | ゼロからはじめるSQL、30日で習得するSQLite：実践 － Day25 ミニ課題②

SQL SQLite
スポンサーリンク

Day25 後半のゴール

「このスキーマで“売上らしいクエリ”をちゃんと書けるようになる」

前半で作ったスキーマをもう一度整理します。

CREATE TABLE customers (
  id            INTEGER PRIMARY KEY,
  name          TEXT    NOT NULL,
  email         TEXT    NOT NULL UNIQUE,
  phone         TEXT,
  registered_at TEXT    NOT NULL,
  status        INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE products (
  id      INTEGER PRIMARY KEY,
  name    TEXT    NOT NULL,
  price   INTEGER NOT NULL,
  status  INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  ordered_at  TEXT    NOT NULL,
  status      INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE order_items (
  id         INTEGER PRIMARY KEY,
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity   INTEGER NOT NULL,
  unit_price INTEGER NOT NULL
);
SQL

後半では、このスキーマに実際にデータを入れて、

顧客ごとの売上合計
商品ごとの売上数・売上金額
特定期間の売上集計

といった「売上管理らしいクエリ」を、JOIN と集計を使って組み立てていきます。
同時に、「どのテーブルを起点に考えるか」という視点も意識してほしいところです。

サンプルデータを入れて“絵”をはっきりさせる

「頭の中で動かせるくらい、具体的なデータを置く」

まずは、簡単なサンプルデータを入れておきます。

顧客を入れます。

INSERT INTO customers (name, email, registered_at)
VALUES
  ('山田太郎', 'taro@example.com',   '2025-05-01'),
  ('佐藤花子', 'hanako@example.com', '2025-05-02');
SQL

商品を入れます。

INSERT INTO products (name, price)
VALUES
  ('ノートPC', 100000),
  ('マウス',     2000),
  ('キーボード', 5000);
SQL

注文ヘッダを入れます。

INSERT INTO orders (customer_id, ordered_at)
VALUES
  (1, '2025-05-10 10:00:00'),
  (1, '2025-05-11 15:30:00'),
  (2, '2025-05-12 09:15:00');
SQL

注文明細を入れます。

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
  (1, 1, 1, 100000),  -- 山田さんがノートPCを1台
  (1, 2, 1,   2000),  -- 山田さんがマウスを1個
  (2, 2, 2,   2000),  -- 山田さんがマウスを2個
  (3, 3, 1,   5000);  -- 佐藤さんがキーボードを1個
SQL

ここまで入れると、頭の中でこういう絵が描けるはずです。

山田太郎（id=1）は、2回注文している。
1回目の注文ではノートPC1台とマウス1個。
2回目の注文ではマウス2個。
佐藤花子（id=2）は、1回の注文でキーボード1個。

この「具体的なストーリー」があると、JOIN と集計の意味が一気に分かりやすくなります。

顧客ごとの売上合計を出す

「customers → orders → order_items を順番にたどる」

「顧客ごとの売上合計」を出したいとします。
売上金額は「数量 × 単価」の合計です。

まず、顧客と注文を JOIN します。

SELECT
  c.id,
  c.name,
  o.id AS order_id
FROM customers c
JOIN orders o
  ON c.id = o.customer_id;
SQL

これで「顧客×注文」の組み合わせが取れます。
次に、注文と注文明細を JOIN します。

SELECT
  c.id          AS customer_id,
  c.name        AS customer_name,
  oi.quantity,
  oi.unit_price
FROM customers c
JOIN orders o
  ON c.id = o.customer_id
JOIN order_items oi
  ON o.id = oi.order_id;
SQL

ここまで来ると、「顧客ごとの全ての明細」が行として並びます。
あとは、quantity * unit_price を計算して、顧客ごとに合計します。

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
GROUP BY
  c.id,
  c.name;
SQL

このクエリのポイントは三つです。

FROM から順に「どのテーブルを起点にしているか」を意識すること。
quantity * unit_price で「1明細あたりの金額」を作り、それを SUM していること。
GROUP BY で「顧客単位」にまとめていること。

サンプルデータで計算してみると、

山田太郎
1回目：100000×1 + 2000×1 = 102000
2回目：2000×2 = 4000
合計：106000

佐藤花子
1回目：5000×1 = 5000

となり、クエリの結果と頭の計算が一致するはずです。
この「自分の頭の計算と SQL の結果が一致する感覚」が、すごく大事です。

商品ごとの売上数・売上金額を出す

「起点を products に変えるだけで考え方は同じ」

次は、「商品ごとの売上数・売上金額」を出してみます。
今度は products を起点に考えます。

まず、商品と注文明細を JOIN します。

SELECT
  p.id,
  p.name,
  oi.quantity,
  oi.unit_price
FROM products p
JOIN order_items oi
  ON p.id = oi.product_id;
SQL

ここで「商品×その商品が含まれる明細」が並びます。
あとは、商品ごとに数量と金額を集計します。

SELECT
  p.id   AS product_id,
  p.name AS product_name,
  SUM(oi.quantity)                    AS total_quantity,
  SUM(oi.quantity * oi.unit_price)    AS total_amount
FROM products p
JOIN order_items oi
  ON p.id = oi.product_id
GROUP BY
  p.id,
  p.name;
SQL

サンプルデータで確認すると、

ノートPC（id=1）
数量：1
金額：100000

マウス（id=2）
数量：1 + 2 = 3
金額：2000×3 = 6000

キーボード（id=3）
数量：1
金額：5000

となり、やはり頭の計算と一致します。

ここでの重要ポイントは、「起点を変えるだけで考え方は同じ」ということです。
顧客ごとの集計では customers を起点にした。
商品ごとの集計では products を起点にした。
どちらも、「起点テーブル → 関連テーブル → 明細 → 集計」という流れは同じです。

特定期間の売上集計をする

「orders.ordered_at に条件をかける」

次は、「特定期間の売上合計」を出してみます。
例えば、「2025-05-11 以降の売上合計」を知りたいとします。

顧客単位ではなく、全体の売上合計だけを出すなら、こう書けます。

SELECT
  SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN order_items oi
  ON o.id = oi.order_id
WHERE o.ordered_at >= '2025-05-11 00:00:00';
SQL

ここでは、orders を起点にして、
ordered_at に期間条件をかけています。

顧客ごとに「その期間の売上」を出したいなら、
さっきの顧客別集計に WHERE を足すだけです。

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.ordered_at >= '2025-05-11 00:00:00'
GROUP BY
  c.id,
  c.name;
SQL

このように、「どのテーブルのどのカラムに条件をかけるか」を意識すると、
期間・状態・顧客・商品など、いろいろな切り口で集計できるようになります。

注文ステータスを考慮した集計

「キャンセル分を除外する、という現実的な一歩」

前半で、orders に status を持たせました。
例えば、1=有効、0=キャンセルとします。

売上集計では、通常「キャンセルされた注文は売上に含めない」必要があります。
その場合、WHERE に o.status = 1 を足します。

顧客ごとの売上合計なら、こうなります。

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

期間も合わせて考慮するなら、条件を増やします。

WHERE o.status = 1
  AND o.ordered_at >= '2025-05-11 00:00:00'
SQL

ここでの大事な感覚は、「ビジネスルールを WHERE に落とす」ということです。
「キャンセルは売上に含めない」というルールを、
status カラムと WHERE 条件で表現しているわけです。

セキュリティ・監査の視点から見た“売上クエリ”

「何を集計しているかを、SQL から説明できるか」

売上集計は、お金に直結するので、
セキュリティや監査の観点でも非常に重要です。

その意味で、次のようなことを意識しておくと良いです。

どのテーブルを起点にしているか
顧客別なら customers、商品別なら products、期間別なら orders。
起点が変わると意味も変わるので、SQL を見て説明できるようにする。

どの状態の注文を含めているか
status = 1 だけなのか、キャンセルも含めているのか。
WHERE 条件を見れば分かるようにしておく。

どの期間を集計しているか
ordered_at の条件が「日付だけ」なのか「日時まで」なのか。
境界条件（>= / > / <= / <）を意識する。

こういう「説明可能性」は、
不正やミスを疑われたときに自分を守る武器にもなります。

小さな思考トレーニング

頭の中で、次のような問いを自分に投げてみてください。

「顧客ごとの売上合計」を出すとき、
FROM の最初に書くべきテーブルはどれか。
なぜ customers から始めるのか。

「商品ごとの売上数」を出すとき、
GROUP BY に書くべきカラムはどれか。
なぜ order_items ではなく products.id / name でまとめるのか。

「キャンセルを除いた売上」を出すとき、
WHERE に書くべき条件はどれか。
なぜ order_items ではなく orders.status に条件を書くのか。

このあたりを自分の言葉で説明できるようになると、
もう「なんとなく JOIN している人」ではなく、
「設計を理解して JOIN している人」になっています。

Day25 後半のまとめ

顧客ごとの売上合計は、「customers → orders → order_items」を JOIN し、quantity * unit_price を顧客単位で SUM することで求められる。
商品ごとの売上数・売上金額は、「products → order_items」を JOIN し、商品単位で数量と金額を集計するだけで、考え方は顧客別集計と同じ。
特定期間の売上集計は、orders.ordered_at に期間条件をかけ、必要に応じて顧客や商品で GROUP BY することで柔軟に表現できる。
注文ステータス（status）を WHERE で絞ることで、「キャンセルを除外する」といったビジネスルールを SQL に落とし込める。
どのテーブルを起点にし、どの条件で絞り、どの単位で GROUP BY しているかを自分の言葉で説明できるようになると、売上管理の SQL は“暗記”ではなく“設計として理解している”状態に近づく。

ここまで来たあなたは、
「顧客×商品×注文」という現実的な世界を、
自分の手で設計し、数字として引き出せるところまで来ています。
この感覚は、どんな業種のシステムでも通用する“コアスキル”です。

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