检查这个材料
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 条注释。