MariaDB講座 / 月次売上・顧客合計・未販売商品を作成して照合する

月次売上、顧客購入額、未販売商品は、同じ表でも一行の単位が違います。状態、金額範囲と未販売の意味を定め、月別・顧客別の合計を同じ全体商品額へ照合します。

共通の指標を文章で定義する

ここで売上はpaid明細の数量×購入時単価で、送料とcanceledは除外します。会計上の収益、税、返金をすべて扱う規則ではありません。未販売は、登録商品でpaid明細に一度も出ていないものです。

月次は2026年の年月一行、顧客別は非購入者も0で一行、未販売は一商品一行です。グループの単位、JOINの増殖を共通に確認します。

手計算できる独立したデータを用意する

course_userでshopcourseに接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で対象を確認します。以下は独立した実習表の作成・追加を含む完全な例で、UTF-8ファイルとして実行できます。同名表があれば定義と行を調べ、必要なものを保存してから判断します。

USE shopcourse;
CREATE TABLE c42_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c42_order_customer FOREIGN KEY (customer_id)
  REFERENCES c42_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c42_item_order FOREIGN KEY (order_id)
  REFERENCES c42_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c42_item_product FOREIGN KEY (product_id)
  REFERENCES c42_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c42_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c42_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c42_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c42_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

文字列は原文の韓国語サンプルを維持しています。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。UTF-8で保存・接続し、安定したIDと数値で照合します。

paid商品額は101が11500、102が8000、103が19500で計39000です。canceledの104の1500は除きます。記事専用の表なので、他のトランザクション実験はこの初期値へ影響しません。

年月でまとめ、注文を正しい単位で数える

年の期間も指定します。

SELECT DATE_FORMAT(o.ordered_at,'%Y-%m') AS month_label,
 COUNT(DISTINCT o.order_id) AS paid_orders,
 SUM(i.quantity*i.unit_price) AS goods_sales
FROM c42_orders o JOIN c42_items i ON i.order_id=o.order_id
WHERE o.status='paid' AND o.ordered_at>='2026-01-01' AND o.ordered_at<'2027-01-01'
GROUP BY DATE_FORMAT(o.ordered_at,'%Y-%m') ORDER BY month_label;

2026-01は二注文19500、2026-02は一注文19500です。明細は五行、注文は三件なので件数にはDISTINCT order_idを使います。月番号だけでは異なる年を混ぜ得ます。注文なしの月を0行として生成する処理ではないので、必要なら明示的なカレンダーを用意します。

非購入者を0で残す

顧客を左側にしてpaidだけを計算します。

SELECT c.customer_id,c.customer_name,
 COALESCE(SUM(CASE WHEN o.status='paid' THEN i.quantity*i.unit_price ELSE 0 END),0) AS paid_goods
FROM c42_customers c LEFT JOIN c42_orders o ON o.customer_id=c.customer_id
LEFT JOIN c42_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name ORDER BY c.customer_id;

ID1～4は19500・19500・0・0です。paid条件をWHEREへ移すと母集団が消える場合があるので、配置を保ちます。送料をこの明細上で直接足すと繰り返されるため、注文単位の費用は別計算や事前集計を行います。

paid履歴のない商品を選ぶ

存在しない関連を検査します。

SELECT p.product_id,p.product_name FROM c42_products p
WHERE NOT EXISTS(SELECT 1 FROM c42_items i JOIN c42_orders o ON o.order_id=i.order_id
 WHERE i.product_id=p.product_id AND o.status='paid') ORDER BY p.product_id;

ライトID4だけが該当します。キャンセルだけの商品も、この定義では未販売です。全状態の履歴を基準にするなら別条件になります。NOT EXISTSで不在の意味を確認できます。

合計・欠落・時点を照合する

月別二額も顧客別四額も39000になります。合わなければキー、期間、状態、単価、LEFT JOIN後の条件と行増殖を調べます。一つの総額がもっともらしいだけで、全報告が正しいとは言えません。

本番は同じ観測時点、タイムゾーン、取消・返金の扱いも必要です。この固定DATETIME例は実行日に依存しない境界です。実際の依頼期間はSQL文字列へ連結せず、パラメーターとして渡します。

やり直すときは、実際のDB・定義・行を確認し、必要なデータを保存して、この記事の削除可能な表だけを子から親の順に消します。DROP TABLEは定義とデータを削除し、通常のROLLBACKでは戻せません。

DROP TABLE shopcourse.c42_items;
DROP TABLE shopcourse.c42_orders;
DROP TABLE shopcourse.c42_products;
DROP TABLE shopcourse.c42_customers;

関連する公式リファレンス

• group by（公式ドキュメント）
• subqueries and exists（公式ドキュメント）