MariaDB 강좌 / 조회 종합 실습: 월별 매출·고객별 구매·미판매 상품

월별 매출·고객별 구매·미판매 상품은 같은 데이터에서도 집계 기준이 다른 조회입니다. 결제 상태·금액 범위·미구매의 정의를 먼저 정하고, 월별 합계와 고객별 합계가 같은 전체 금액에 맞는지 교차 검증해야 합니다.

보고서 지표의 공통 기준 문장으로 정하기

이 실습에서 매출은 paid 주문 상세의 수량×주문 당시 단가이며 배송비·취소 주문은 제외합니다. 실제 회계상 수익·환불·세금 인식 기준을 구현하는 예제는 아닙니다. 미판매 상품은 모든 등록 상품 중 paid 주문에 등장한 적이 없는 상품으로 정합니다. 월·고객별 그룹 기준은 GROUP BY·HAVING으로 묶고 집계 결과 걸러내기, JOIN 뒤 금액이 중복되는 원인은 여러 테이블 JOIN하기: 중복 행과 집계 오류 확인에서 작은 결과로 먼저 확인할 수 있습니다.

검증 가능한 작은 주문 데이터 준비하기

학습용 MariaDB 서버의 shopcourse에 course_user로 접속합니다. 명령줄 클라이언트와 UTF-8 SQL 파일 실행 방법대로 코드를 UTF-8 파일에 저장하고 SOURCE C:/mariadb-lab/lesson.sql;처럼 실제 파일 경로로 실행할 수 있습니다. SQL 문은 운영체제 터미널이 아닌 MariaDB 연결 안에서 실행합니다.

먼저 SELECT VERSION(), DATABASE();와 SHOW TABLES;로 대상 서버·DB·이름 중복을 확인합니다. 아래 c42_customers, c42_products, c42_orders, c42_items는 이 글의 독립된 연습용 이름입니다. 이미 존재하면 정의와 행을 확인하고, 보관할 자료가 없는 이 글의 실습 테이블임이 확실할 때만 마지막 절의 정리 순서로 지운 뒤 다시 만듭니다. 기존 강좌의 동명 없는 테이블이나 운영 데이터는 삭제하지 않습니다.

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);

101번 상품 금액 11500, 102번 8000, 103번 19500이 paid입니다. 104번 1500은 취소되어 제외합니다. 이 값으로 전체 39000을 먼저 계산해 두고 각 보고서와 대조합니다. 독립 실습 테이블이므로 다른 강좌의 변경 상태에 영향을 받지 않습니다.

연·월과 주문 건수를 정확한 단위로 집계하기

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;

상세가 다섯 행이어도 주문 건수는 세 건이므로 COUNT(DISTINCT order_id)를 사용합니다. MONTH만으로 묶으면 여러 해의 같은 달이 섞일 수 있어 연·월을 함께 표시했습니다. 월별 결과가 없는 달을 자동으로 0으로 채우는 기능은 아니며 달력 목록과의 결합이 별도로 필요할 수 있습니다.

미구매 고객까지 포함해 고객별 금액 구하기

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;

고객 1·2의 금액은 각각 19500.00이고 고객 3·4는 0.00입니다. 기준 고객을 LEFT JOIN으로 유지하고 CASE로 paid 상세만 더했습니다. WHERE o.status='paid'로 바꾸면 주문이 없는 고객을 잃을 수 있으므로 지표 정의에 맞는 위치를 유지합니다.

주문 배송비를 이 JOIN 결과에서 바로 SUM하면 상세 수만큼 반복될 수 있습니다. 이 지표는 상세 금액만 합산하며 주문 단위 비용을 추가할 때는 주문별 선집계 등 별도 단계가 필요합니다. 같은 숫자라도 측정 단위를 섞지 않습니다.

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;

스탠드인 4번만 반환합니다. NOT EXISTS는 해당 상품에 연결된 paid 주문이 하나도 없는지를 검사합니다. 취소 주문에만 등장한 상품이 있다면 이 정의에서는 미판매에 포함됩니다. 모든 상태의 주문 이력을 기준으로 하려면 상태 조건을 바꾸어야 합니다. 존재·부재 조건와 그룹 집계 구문을 확인하세요.

세 보고서의 합계와 누락을 교차 검증하기

월별 두 금액의 합은 39000, 고객별 네 금액의 합도 39000이어야 합니다. 각각의 기본키·기간·상태·단가 기준이 같은지 확인하고, 값이 다르면 중복 JOIN이나 LEFT JOIN 뒤 필터 위치부터 살펴봅니다. 가장 큰 합계 하나만 보고 보고서 전체가 맞다고 판단하지 않습니다.

운영에서는 같은 기준 시점의 스냅샷·시간대·취소 및 환불 정책도 필요합니다. 이 글은 고정된 DATETIME 입력을 사용하므로 실행 날짜에 따라 월 경계가 움직이지 않습니다. 실제 요구에 맞춰 기간을 매개변수화하되 입력을 SQL 문자열로 이어 붙이지 않습니다.

반복하기 전 현재 DB와 실제 정의·행을 확인하고 필요한 자료를 백업합니다. 보관할 데이터가 없는 이 글의 전용 실습 객체만 아래 순서로 정리합니다. 외래키가 있으면 자식부터 지웁니다. DROP TABLE은 구조와 데이터를 모두 없애며 일반적인 ROLLBACK으로 복원되지 않으므로 취소 가능한 DML 실습과 분리합니다.

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