檢查這份材料
3 月各商品的銷售,把定義寫進查詢裡
材料: sql · 6 則註解
1WITH orders(order_id, customer_id, order_ts, status, coupon,2 shipping_fee) AS (3 VALUES4 (101, 1, '2025-02-28 21:15', 'paid', NULL, 5),5 (102, 1, '2025-03-02 10:05', 'paid', 'SPRING', 0),6 (103, 2, '2025-03-09 14:30', 'paid', NULL, 5),7 (104, 4, '2025-03-12 11:00', 'refunded', NULL, 5),8 (105, 3, '2025-03-15 09:40', 'cancelled', NULL, 5),9 (106, 3, '2025-03-21 16:10', 'paid', 'SPRING', 0),10 (107, 2, '2025-03-28 12:00', 'paid', NULL, 5),11 (108, 4, '2025-03-31 18:20', 'paid', NULL, 5),12 (109, 1, '2025-04-01 08:00', 'paid', NULL, 5)13),14order_lines(line_id, order_id, product, quantity, unit_price) AS (15 VALUES16 ( 1, 101, 'Mug', 2, 8),17 ( 2, 102, 'Kettle', 1, 30),18 ( 3, 102, 'Tea tin', 2, 12),19 ( 4, 103, 'Mug', 1, 8),20 ( 5, 103, 'Tea tin', 1, 12),21 ( 6, 103, 'Filter', 3, 4),22 ( 7, 104, 'Kettle', 1, 30),23 ( 8, 105, 'Tea tin', 3, 12),24 ( 9, 106, 'Mug', 4, 8),25 (10, 106, 'Filter', 2, 4),26 (11, 107, 'Tea tin', 1, 12),27 (12, 107, 'Mug', 1, 8),28 (13, 108, 'Kettle', 1, 30),29 (14, 108, 'Filter', 1, 4),30 (15, 109, 'Tea tin', 2, 12)31),32paid_march AS (33 SELECT order_id, customer_id34 FROM orders35 WHERE order_ts >= '2025-03-01' AND order_ts < '2025-04-01'36 AND status = 'paid'37),38sold AS (39 SELECT l.product,40 l.quantity,41 l.quantity * l.unit_price AS line_total,42 o.order_id,43 o.customer_id44 FROM order_lines AS l45 JOIN paid_march AS o ON o.order_id = l.order_id46)47SELECT product,48 SUM(quantity) AS items_sold,49 SUM(line_total) AS revenue,50 COUNT(DISTINCT order_id) AS orders,51 COUNT(DISTINCT customer_id) AS customers52FROM sold53GROUP BY product54UNION ALL55SELECT 'All products',56 SUM(quantity),57 SUM(line_total),58 COUNT(DISTINCT order_id),59 COUNT(DISTINCT customer_id)60FROM sold61ORDER BY revenue DESC, product;
- 第 32–37 行
完全照定義寫:3 月 1 日起、4 月 1 日之前的已付款訂單。只留下 order_id 和 customer_id。shipping_fee 是刻意不放的:它不屬於銷售,而且會在每一筆明細上重複。
- 第 38–46 行
把明細聯結到範圍:3 月已付款訂單的每一筆明細一列,共 11 列。明細小計在這裡只算一次,來自比較細的那張表。
- 第 47–53 行
定義裡的粒度。items_sold 和 revenue 是對明細的加總。
- 第 50–51 行
一筆訂單出現在好幾筆明細上,一位顧客也出現在好幾筆訂單上,所以兩者都用 DISTINCT 計數。普通的 COUNT 數出來會是明細數。
- 第 55–60 行
同樣的指標,對 sold 的所有列計算,不分組。不重複的計數在這裡重新計算,而不是把上面各列加起來。
- 第 61 行
營收最大的排最前面,並列的依商品名稱排,輸出每次都一樣。總計列的營收最大,所以排在第一。
預期輸出: product items_sold revenue orders customers
------------ ---------- ------- ------ ---------
All products 18 180 5 4
Kettle 2 60 2 2
Mug 6 48 3 2
Tea tin 4 48 3 2
Filter 6 24 3 3
材料包含 61 行和 6 則註解。