SQL 各国家地区GMV排名：GROUP BY + RANK窗口函数（SHEIN面试题）

一、题目

统计6月各国家/地区的GMV（GMV = 单价 × 销量），按GMV降序排名输出。GMV相同时并列排名（后续名次跳过），输出字段：country、gmv、rk。

示例数据：

+-----------+----------+---------+--------+-----------+-------------------+
| order_id  | country  | sku_id  | price  | sale_qty  |    order_time     |
+-----------+----------+---------+--------+-----------+-------------------+
| 1001      | US       | SKU001  | 15.00  | 2         | 2025-06-01 10:00  |
| 1002      | UK       | SKU002  | 25.00  | 1         | 2025-06-02 11:00  |
| 1003      | US       | SKU003  | 10.00  | 3         | 2025-06-03 09:00  |
| 1004      | JP       | SKU001  | 15.00  | 1         | 2025-06-04 12:00  |
| 1005      | US       | SKU002  | 25.00  | 2         | 2025-06-05 14:00  |
| 1006      | UK       | SKU003  | 10.00  | 5         | 2025-06-06 10:00  |
| 1007      | FR       | SKU001  | 15.00  | 1         | 2025-06-07 08:00  |
| 1008      | DE       | SKU002  | 8.00   | 1         | 2025-06-08 09:00  |
+-----------+----------+---------+--------+-----------+-------------------+

二、思路分析

1. 按国家/地区分组，计算每个国家/地区的GMV = sum(price * sale_qty)，使用coalesce防止NULL值导致的计算偏差；
2. 使用 RANK() 或 DENSE_RANK() 窗口函数按GMV降序排名，需根据业务需求选择并列处理策略；
3. 使用 substr(order_time, 1, 7) 过滤出2025年6月的数据。

维度	评分
题目难度	⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

三、逐步推导

1. 按国家统计6月GMV

按country分组，使用sum(price * sale_qty)计算各国GMV，coalesce确保NULL转为0，substr过滤6月数据。

执行SQL

select country,
       round(coalesce(sum(price * sale_qty), 0), 2) as gmv
from t1_shein_orders
where substr(order_time, 1, 7) = '2025-06'
group by country

查询结果

+----------+---------+
| country  |   gmv   |
+----------+---------+
| US       | 110.00  |
| UK       | 75.00   |
| JP       | 15.00   |
| DE       | 8.00    |
| FR       | 15.00   |
+----------+---------+
5 rows selected (0.916 seconds)(https://www.dwsql.com)

2. 使用RANK窗口函数按GMV降序排名

在外层使用窗口函数对GMV降序排名。JP和FR的GMV相同（15.00），并列第3名，下一个国家DE跳至第5名。

执行SQL

select country,
       gmv,
       rank() over (order by gmv desc) as rk
from (
    select country,
           round(coalesce(sum(price * sale_qty), 0), 2) as gmv
    from t1_shein_orders
    where substr(order_time, 1, 7) = '2025-06'
    group by country
) t
order by rk

查询结果

+----------+---------+-----+
| country  |   gmv   | rk  |
+----------+---------+-----+
| US       | 110.00  | 1   |
| UK       | 75.00   | 2   |
| JP       | 15.00   | 3   |
| FR       | 15.00   | 3   |
| DE       | 8.00    | 5   |
+----------+---------+-----+
5 rows selected (0.623 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：RANK vs DENSE_RANK的选择 — 这两个函数对并列值的处理不同，直接影响排名结果。RANK在并列后跳过名次，DENSE_RANK保持连续。本例中JP和FR的GMV相同并列第3名，DE的GMV最低——使用RANK时DE排第5（跳过了第4名），使用DENSE_RANK时DE排第4。需根据业务需求明确：排名并列时后续名次是否跳过。

坑2：price*sale_qty的NULL值导致GMV偏差 — 当price或sale_qty任一字段为NULL时，乘法结果也为NULL，而sum聚合函数会跳过NULL值，导致对应订单不被计入GMV，最终该国家/地区的GMV被低估。应使用coalesce(sum(price * sale_qty), 0)确保NULL情况转为0，避免统计遗漏。

坑3：substr提取月份依赖日期格式一致性 — substr(order_time, 1, 7)提取前7个字符作为月份标识，这要求order_time严格遵循'yyyy-MM-dd HH:mm'格式。如果数据中混入不同日期格式（如'2025/06/01'或'20250601'），过滤条件将失效，可能导致漏数据或误包含其他月份的数据。建议在开发前先确认日期字段格式统一，或使用更稳健的日期解析方式（如date_format + to_date）。

五、知识点总结

考点	说明
GROUP BY + sum聚合	分组聚合是数据分析基础，配合where过滤和coalesce处理NULL值
RANK / DENSE_RANK / ROW_NUMBER	排名函数三剑客，并列处理方式不同，需按业务场景选择
NULL值在聚合中的行为	sum等聚合函数跳过NULL，coalesce可设默认值防止统计偏差
substr日期过滤	简单高效，但强依赖日期格式统一，生产环境建议使用date类型函数

六、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
create table t1_shein_orders (
    order_id   bigint comment '订单id',
    country    string comment '国家/地区',
    sku_id     string comment 'sku编码',
    price      decimal(10,2) comment '商品单价',
    sale_qty   int comment '销售数量',
    order_time string comment '下单时间'
) comment 'shein订单表';

-- 插入数据
insert into t1_shein_orders(order_id, country, sku_id, price, sale_qty, order_time) values
(1001, 'US', 'SKU001', 15.00, 2, '2025-06-01 10:00'),
(1002, 'UK', 'SKU002', 25.00, 1, '2025-06-02 11:00'),
(1003, 'US', 'SKU003', 10.00, 3, '2025-06-03 09:00'),
(1004, 'JP', 'SKU001', 15.00, 1, '2025-06-04 12:00'),
(1005, 'US', 'SKU002', 25.00, 2, '2025-06-05 14:00'),
(1006, 'UK', 'SKU003', 10.00, 5, '2025-06-06 10:00'),
(1007, 'FR', 'SKU001', 15.00, 1, '2025-06-07 08:00'),
(1008, 'DE', 'SKU002', 8.00, 1, '2025-06-08 09:00');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题