综合练习：分析一家网店

第 21 课，共 21 课 · 第 5 部分 · 改数据和建表 · 约 20 分钟

除了「每月订单数」里取年月的 DATE_FORMAT，这一课没有新语法。我们像网店的数据分析员一样，提一串问题，每个问题写一条查询来回答，从简单的到难的。

先定两条规矩，后面每条查询都照着来：

卖出去的只算没取消的订单：status <> '已取消'。
一行明细的金额是 quantity * unit_price（下单时的单价，不是商品现在的价格）。

每一条都可以先自己试着写，再看我们的写法。写法不止一种，结果对就行。

热身：库存

哪些商品库存不多了？用 CASE 给它们标上状态：

main.sql

1SELECT name, stock,2    CASE3        WHEN stock = 0 THEN '缺货'4        WHEN stock < 20 THEN '快没了'5        ELSE '还够'6    END AS level7FROM products8WHERE stock < 309ORDER BY stock;

输出

name	stock	level
台灯	0	缺货
耳机	10	快没了
双肩包	15	快没了
帆布袋	25	还够

4 行

一共卖了多少

订单都是什么状态？

main.sql

1SELECT status, COUNT(*) AS orders2FROM orders3GROUP BY status4ORDER BY orders DESC;

输出

status	orders
已发货	8
待发货	3
已取消	1

3 行

一共卖了多少钱？要把订单和明细接起来，去掉取消的：

main.sql

1SELECT SUM(oi.quantity * oi.unit_price) AS revenue2FROM orders AS o3JOIN order_items AS oi ON oi.order_id = o.id4WHERE o.status <> '已取消';

输出

revenue
418.70

1 行

卖得最好的商品

每件商品卖了几件、卖了多少钱，按件数排名，取前三名：

main.sql

1WITH sales AS (2    SELECT p.id, p.name,3        SUM(oi.quantity) AS sold,4        SUM(oi.quantity * oi.unit_price) AS revenue5    FROM products AS p6    JOIN order_items AS oi ON oi.product_id = p.id7    JOIN orders AS o ON o.id = oi.order_id8    WHERE o.status <> '已取消'9    GROUP BY p.id, p.name10),11ranked AS (12    SELECT id, name, sold, revenue,13        RANK() OVER (ORDER BY sold DESC) AS place14    FROM sales15)16SELECT place, name, sold, revenue17FROM ranked18WHERE place <= 319ORDER BY place, id;

输出

place	name	sold	revenue
1	笔记本	10	45.00
2	咖啡杯	6	59.40
3	中性笔（10 支）	3	18.00
3	水杯	3	36.00
3	数据线	3	25.50

5 行

「前三名」查出了 5 件：有三件都卖了 3 件，并列第 3。用 ORDER BY sold DESC LIMIT 3 的话，这三件里会随便丢掉两件；RANK() 把并列的都留了下来。

反过来，哪些商品一件都没卖出去？LEFT JOIN 之后配不上的就是：

main.sql

1SELECT p.id, p.name2FROM products AS p3LEFT JOIN order_items AS oi ON oi.product_id = p.id4WHERE oi.product_id IS NULL5ORDER BY p.id;

输出

id	name
6	台灯

1 行

每个城市的销售额

客户 → 订单 → 明细，三张表接起来，按城市分组：

main.sql

1SELECT c.city, SUM(oi.quantity * oi.unit_price) AS revenue2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4JOIN order_items AS oi ON oi.order_id = o.id5WHERE o.status <> '已取消'6GROUP BY c.city7ORDER BY revenue DESC;

输出

city	revenue
上海	162.50
北京	101.80
广州	98.60
成都	55.80

4 行

先猜一猜

客户来自 5 个城市（这条查询会查出 5 行），上面按城市算销售额的结果却只有 4 行。少了的那个城市是怎么回事？

1SELECT DISTINCT city FROM customers;

杭州只有郑雪一个客户，她没下过单。JOIN orders 时她配不上任何订单，这一行就没了。想让杭州显示 0，要用 LEFT JOIN，再用 COALESCE 把 NULL 换成 0。

回头客

买过两次以上的客户叫回头客。每个客户没取消的订单数，留下至少 2 单的：

main.sql

1SELECT c.id, c.name, COUNT(*) AS orders2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4WHERE o.status <> '已取消'5GROUP BY c.id, c.name6HAVING COUNT(*) >= 27ORDER BY orders DESC, c.id;

输出

id	name	orders
1	林晓	3
2	王磊	2
5	刘洋	2

3 行

张伟（4 号）其实下过两单，可其中一单取消了，所以不算。WHERE 先去掉取消的订单，HAVING 再去掉单数不够的客户：两个一起用，正好是第 9 课讲的。

每月订单数

要按月份分组，得先从日期里取出「年-月」。MySQL 用 DATE_FORMAT(order_date, '%Y-%m')，2025-10-15 就成了 2025-10（别的数据库写法不同：PostgreSQL 是 TO_CHAR(order_date, 'YYYY-MM')，SQLite 是 strftime('%Y-%m', order_date)）。先算出每个订单的金额，再按月汇总：

main.sql

1WITH totals AS (2    SELECT o.id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total3    FROM orders AS o4    JOIN order_items AS oi ON oi.order_id = o.id5    WHERE o.status <> '已取消'6    GROUP BY o.id, o.order_date7)8SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,9    COUNT(*) AS orders,10    SUM(total) AS revenue,11    ROUND(AVG(total), 2) AS avg_order12FROM totals13GROUP BY DATE_FORMAT(order_date, '%Y-%m')14ORDER BY month;

输出

month	orders	revenue	avg_order
2025-09	3	135.40	45.13
2025-10	8	283.30	35.41

2 行

10 月的订单比 9 月多了一倍多，平均每单的金额却低了一些。

大客户

每个客户一共花了多少，占全部销售额的百分之几？「全部销售额」用 SUM() OVER () 写在每一行旁边，就能直接相除：

main.sql

1WITH spending AS (2    SELECT c.id, c.name, SUM(oi.quantity * oi.unit_price) AS spent3    FROM customers AS c4    JOIN orders AS o ON o.customer_id = c.id5    JOIN order_items AS oi ON oi.order_id = o.id6    WHERE o.status <> '已取消'7    GROUP BY c.id, c.name8)9SELECT name, spent,10    ROUND(100 * spent / SUM(spent) OVER (), 1) AS percent11FROM spending12ORDER BY spent DESC;

输出

name	spent	percent
林晓	116.50	27.8
王磊	101.80	24.3
孙强	59.00	14.1
刘洋	55.80	13.3
张伟	39.60	9.5
周婷	23.50	5.6
陈静	22.50	5.4

7 行

前两位客户加起来就占了一半多。

练一练

写好查询，点检查。提示随便看，实在想不出来再看答案。三道题一道比一道难。

练习 1

还没下过单的客户

查出从没下过单的客户的 name 和 city，按客户 id 排。

要显示

name	city
赵敏	北京
吴昊	成都
郑雪	杭州

3 行

exercise1.sql

1SELECT c.name, c.city2FROM customers AS c;

输出

点「运行」或按 Ctrl+Enter

练习 2

每个分类的销售额

算出每个分类卖了多少钱（不算取消的订单）：查出 category 和 revenue 两列，按 revenue 从高到低排。

要显示

category	revenue
数码	158.50
箱包	101.80
厨房	95.40
文具	63.00

4 行

exercise2.sql

1SELECT category2FROM products;

输出

点「运行」或按 Ctrl+Enter

练习 3

每个城市花钱最多的客户

在每个城市里，找出花钱最多的那位客户（不算取消的订单）：查出 city、name 和花的钱 spent，按 city 排。

要显示

city	name	spent
上海	林晓	116.50
北京	王磊	101.80
广州	孙强	59.00
成都	刘洋	55.80

4 行

exercise3.sql

1SELECT c.city, c.name2FROM customers AS c3ORDER BY c.city;

输出

点「运行」或按 Ctrl+Enter

这一课学会了

先把规矩定清楚（什么算「卖出去」、金额怎么算），每条查询都照着来。
「每个……的……」通常是 JOIN 接起来，再 GROUP BY；「至少……」用 HAVING。
「从没……过」用 LEFT JOIN 加 IS NULL；JOIN 会悄悄丢掉配不上的行。
「前几名」有并列时用 RANK()，比 LIMIT 更老实；「每组第一」用 PARTITION BY 再取第 1 名。
长的分析用 WITH 一步一步搭；占比可以用 SUM() OVER () 算总数。
DATE_FORMAT(日期, '%Y-%m') 取出年月（MySQL 的写法）。