深度解析：如何编写复杂SQL查询实现电商订单多维度统计

要编写出精准的复杂 SQL 查询，我们首先需要明确三个核心问题：第一，查询对应什么具体的业务场景，例如是电商销量统计还是企业库存盘点；第二，清楚了解表结构，包括字段名称、数据类型以及表之间的关联关系；第三，明确查询目标，比如是要进行多表关联统计、条件筛选，还是数据排名。

下面，我们将通过一个常见的“电商订单多维度统计”业务场景示例，来逐步拆解复杂 SQL 的构建过程。这个示例将综合运用多表关联、聚合计算、窗口函数、条件过滤等多种技术。

示例场景

统计2024年第一季度（1-3月）各省份的订单数据，需包含以下维度：省份、订单总数、支付订单数、退款订单数、总销售额、客单价、TOP3热销商品名称及销量。

一、创建模拟数据表及插入模拟数据

1、orders（订单表）
结构：order_id(订单ID)、user_id(用户ID)、province(省份)、order_time(下单时间)、pay_status(支付状态：1=已支付，0=未支付)、refund_status(退款状态：1=已退款，0=未退款)

order_id	user_id	province	order_time	pay_status	refund_status
1001	2001	广东省	2024-01-05 10:20:30	1	0
1002	2002	广东省	2024-02-12 15:40:10	1	1
1003	2003	浙江省	2024-03-01 09:15:20	1	0
1004	2004	广东省	2024-01-20 18:30:00	0	0
1005	2005	浙江省	2024-02-25 14:50:40	1	0
1006	2006	江苏省	2024-03-18 11:05:50	1	0
1007	2007	浙江省	2024-01-18 16:25:30	0	0
1008	2008	江苏省	2024-03-22 13:10:20	1	0

2、order_details（订单明细表）
结构：detail_id(明细ID)、order_id(订单ID)、product_id(商品ID)、quantity(购买数量)、unit_price(单价)

detail_id	order_id	product_id	quantity	unit_price
3001	1001	4001	2	199.00
3002	1001	4002	1	299.00
3003	1002	4001	1	199.00
3004	1003	4003	3	99.00
3005	1005	4002	2	299.00
3006	1006	4003	1	99.00
3007	1006	4004	2	159.00
3008	1008	4004	3	159.00

3、products（商品表）
结构：product_id(商品ID)、product_name(商品名称)

product_id	product_name
4001	无线蓝牙耳机
4002	智能手表
4003	便携充电宝
4004	高清数据线

二、复杂SQL查询语句

WITH
-- 子查询1：计算各订单的总金额（关联订单表和明细表）
order_amount AS (
    SELECT
        o.order_id, -- 订单ID
        o.province, -- 省份
        o.pay_status, -- 支付状态
        o.refund_status, -- 退款状态
        SUM(od.quantity * od.unit_price) AS order_total -- 计算单订单总金额
    FROM orders o
    -- 左连接订单明细表
    LEFT JOIN order_details od ON o.order_id = od.order_id
    -- 筛选出2024年第一季度（1-3月）的订单
    WHERE o.order_time BETWEEN '2024-01-01 00:00:00' AND '2024-03-31 23:59:59'
    -- 按照订单ID、省份、支付状态、退款状态进行分组
    GROUP BY o.order_id, o.province, o.pay_status, o.refund_status
),

-- 子查询2：计算各省份的基础订单统计（聚合计算）
province_order_stats AS (
    SELECT
        province, -- 省份
        COUNT(order_id) AS total_orders, -- 订单总数
        SUM(CASE WHEN pay_status = 1 THEN 1 ELSE 0 END) AS paid_orders, -- 支付订单数
        SUM(CASE WHEN refund_status = 1 THEN 1 ELSE 0 END) AS refunded_orders, -- 退款订单数
        SUM(CASE WHEN pay_status = 1 THEN order_total ELSE 0 END) AS total_sales, -- 总销售额（GMV口径）
        ROUND(
            SUM(CASE WHEN pay_status = 1 THEN order_total ELSE 0 END)
            / NULLIF(SUM(CASE WHEN pay_status = 1 THEN 1 ELSE 0 END), 0),
            2
        ) AS avg_transaction  -- 客单价
    FROM order_amount
    GROUP BY province
),

-- 子查询3：计算各省份的商品销量排名（窗口函数）
province_product_sales AS (
    SELECT
        o.province, -- 省份
        p.product_name, -- 商品名称
        SUM(od.quantity) AS product_sales, -- 商品销量
        ROW_NUMBER() OVER (PARTITION BY o.province ORDER BY SUM(od.quantity) DESC) AS sales_rank
    FROM orders o
    LEFT JOIN order_details od ON o.order_id = od.order_id
    LEFT JOIN products p ON od.product_id = p.product_id
    WHERE o.order_time BETWEEN '2024-01-01 00:00:00' AND '2024-03-31 23:59:59'
      AND o.pay_status = 1
    GROUP BY o.province, p.product_name
),

-- 子查询4：筛选各省份TOP3热销商品（行转列）
province_top3_products AS (
    SELECT
        province,
        CONCAT(
            '1.', MAX(CASE WHEN sales_rank = 1 THEN product_name END), '(', MAX(CASE WHEN sales_rank = 1 THEN product_sales END), '件); ',
            '2.', MAX(CASE WHEN sales_rank = 2 THEN product_name END), '(', MAX(CASE WHEN sales_rank = 2 THEN product_sales END), '件); ',
            '3.', MAX(CASE WHEN sales_rank = 3 THEN product_name END), '(', MAX(CASE WHEN sales_rank = 3 THEN product_sales END), '件)'
        ) AS top3_products
    FROM province_product_sales
    WHERE sales_rank <= 3
    GROUP BY province
)

-- 关联所有子查询，输出完整统计结果
SELECT
    pos.province,
    pos.total_orders,
    pos.paid_orders,
    pos.refunded_orders,
    pos.total_sales,
    pos.avg_transaction,
    ptp.top3_products
FROM province_order_stats pos
LEFT JOIN province_top3_products ptp ON pos.province = ptp.province
WHERE pos.paid_orders > 0
ORDER BY pos.total_sales DESC;

三、复杂逻辑说明

1. 多表关联：通过 orders.order_id 关联 order_details，再通过 order_details.product_id 关联 products，实现了“订单-明细-商品”的全链路数据获取。
2. CTE子查询（WITH子句）：将复杂逻辑拆分为4个独立的子查询（Common Table Expressions），降低了代码的耦合度，逻辑清晰且便于后期维护。
3. 条件聚合：使用 CASE WHEN 配合 SUM 函数，灵活地计算支付订单数、退款订单数及总销售额，仅对符合特定条件的数据进行统计。
4. 窗口函数：ROW_NUMBER() 函数按省份分区，并对商品销量进行降序排名，从而高效地筛选出“各省份TOP3商品”。
5. 特殊处理：使用 NULLIF 函数避免客单价计算时出现“除以0”的错误；用 ROUND 函数保留2位小数；用 CONCAT 函数拼接TOP3商品信息，将行数据转换为更易读的列展示。

四、查询结果（2024年Q1统计）

已按“总销售额”降序排序，并过滤掉无支付订单的省份：

province	total_orders	paid_orders	refunded_orders	total_sales	avg_transaction	top3_products
广东省	3	2	1	896.00	448.00	1.无线蓝牙耳机(3件); 2.智能手表(1件); 3.高清数据线(0件)
浙江省	3	2	0	895.00	447.50	1.便携充电宝(3件); 2.智能手表(2件); 3.无线蓝牙耳机(0件)
江苏省	2	2	0	894.00	447.00	1.高清数据线(5件); 2.便携充电宝(1件); 3.智能手表(0件)

结果逻辑验证（以广东省为例）：

1. 总订单数=3：订单1001、1002、1004（包含未支付的1004）。
2. 支付订单数=2：仅1001（已支付未退款）、1002（已支付已退款）。
3. 总销售额=896：1001金额=2*199 + 1*299 = 697，1002金额=1*199 = 199，合计 697+199=896。
4. TOP3商品：广东省销量最高的是“无线蓝牙耳机”（1001买2件+1002买1件=3件）；其次是“智能手表”（1001买1件）；第三名空缺，故显示0件。

五、业务语义优化：无第三名商品时，可将“0件”改为“无”

原查询在拼接 TOP3 商品时，对于排名不足3位的商品，会显示 0件，这在业务语义上不够友好。更佳的做法是将空缺的排名显示为“无”，以准确反映“该排名没有商品”的业务现状。

例如，广东省 TOP3 商品可优化为：1.无线蓝牙耳机(3件); 2.智能手表(1件); 3.无

实现方式（修改子查询4的 CONCAT 逻辑）：
在 province_top3_products 的 CONCAT 函数中，使用 COALESCE (或 IFNULL) 函数处理空值。

-- 子查询4：筛选各省份TOP3热销商品（优化业务语义版）
province_top3_products AS (
    SELECT
        province,
        CONCAT(
            '1.', COALESCE(MAX(CASE WHEN sales_rank = 1 THEN CONCAT(product_name, '(', product_sales, '件)') END), '无'), '; ',
            '2.', COALESCE(MAX(CASE WHEN sales_rank = 2 THEN CONCAT(product_name, '(', product_sales, '件)') END), '无'), '; ',
            '3.', COALESCE(MAX(CASE WHEN sales_rank = 3 THEN CONCAT(product_name, '(', product_sales, '件)') END), '无')
        ) AS top3_products
    FROM province_product_sales
    WHERE sales_rank <= 3
    GROUP BY province
)

修改后的查询结果：

province	total_orders	paid_orders	refunded_orders	total_sales	avg_transaction	top3_products
广东省	3	2	1	896.00	448.00	1.无线蓝牙耳机(3件); 2.智能手表(1件); 3.无
浙江省	3	2	0	895.00	447.50	1.便携充电宝(3件); 2.智能手表(2件); 3.无
江苏省	2	2	0	894.00	447.00	1.高清数据线(5件); 2.便携充电宝(1件); 3.无

六、如何处理“已支付但已退款”的订单？

在不同的业务场景下，对“销售额”的定义不同，这直接决定了我们是否应该将“已退款的支付订单”计入总销售额。主要有两种统计口径：

1. 口径一：GMV (Gross Merchandise Volume) / 成交总额

• 定义：只要订单支付成功，无论后续是否退款，都计入销售额。
• 优点：反映了平台前端的交易规模和运营能力。
• 缺点：与实际收入不符。
• 原查询使用的就是此口径：逻辑为 SUM(CASE WHEN pay_status = 1 THEN order_total ELSE 0 END)。它只判断“是否支付”，因此广东省订单1002（支付了199元但已退款）依然被计入了总销售额。

2. 口径二：净销售额 (Net Sales) / 实际收入

• 定义：只计入最终未退款的支付订单金额。已支付的款项如果后续被退回，则不应计入。
• 优点：更准确地反映公司的实际收入，是财务部门更关注的指标。
• 缺点：无法体现前端的交易流量。

3. 如何修改SQL以符合“净销售额”口径？

如果我们需要采用口径二（净销售额），只需修改 province_order_stats 子查询中的聚合条件。

将：

SUM(CASE WHEN pay_status = 1 THEN order_total ELSE 0 END) AS total_sales

修改为：

-- 计算总销售额（净销售额口径）
SUM(CASE WHEN pay_status = 1 AND refund_status = 0 THEN order_total ELSE 0 END) AS total_sales

这个修改意味着：只统计那些“已支付”且“未退款”的订单金额。

修改后的结果验证（以广东省为例）：

• 原GMV口径：总销售额 = 697 (1001) + 199 (1002) = 896元
• 净销售额口径：总销售额 = 697 (1001) + 0 (1002已退款不计) = 697元

此时，客单价也需要相应调整，分母应为“支付且未退款的订单数”。更严谨的写法是：

ROUND(
    SUM(CASE WHEN pay_status = 1 AND refund_status = 0 THEN order_total ELSE 0 END)
    / NULLIF(SUM(CASE WHEN pay_status = 1 AND refund_status = 0 THEN 1 ELSE 0 END), 0),
    2
) AS avg_transaction

在严谨的财务或业务分析中，选择哪种口径取决于统计目的。评估销售业绩或交易规模常用 GMV口径；分析实际营收或利润则必须使用 净销售额口径。因此，在编写任何数据查询前，一定要与需求方明确指标的具体统计口径。

掌握从业务场景分析到复杂查询构建的全过程，是每一位数据开发者和分析师的必备技能。通过本文的“电商订单多维度统计”示例，我们系统性地演练了多表关联、条件聚合、窗口函数等核心技巧。在实践中，我们应始终以需求为导向，精确地雕琢每一个 SQL 语句，从而更高效地挖掘数据价值。更多关于数据库和系统架构的深度讨论，欢迎访问 云栈社区 进行交流。