阿里巴巴大数据面试SQL-每个城市销售额排名及占比

⚠️ 待修正

一、题目背景

这道题来自阿里巴巴淘宝/天猫事业部的区域运营岗面试。阿里电商业务覆盖全国数百个城市，区域运营团队需要了解各城市的销售贡献度，以便合理分配营销预算、规划仓储物流和制定区域增长策略。

业务场景：大区运营总监看板的首页通常就是一个"城市销售排名+占比"的表格，一眼看出哪些城市是核心市场、哪些城市还有增长空间。这道题考察的正是窗口函数排名和占比计算的基本功。

二、题目

现有一张城市销售表 t7_city_sales，请计算每个城市的销售额排名及占总销售额的占比。

城市销售表 t7_city_sales：

+----------+-----------+
| city     | sales_amt |
+----------+-----------+
| 杭州     | 500000    |
| 北京     | 300000    |
| 上海     | 400000    |
| 深圳     | 200000    |
| 广州     | 150000    |
+----------+-----------+

三、思路分析

本题考察窗口函数的两个典型用法：

1. 排名计算：RANK() OVER (ORDER BY sales_amt DESC) 按销售额降序生成名次
2. 占比计算：SUM(sales_amt) OVER () 计算全局总量，各城市金额除以总量即是占比
3. 两个窗口函数可以并列写在同一 SELECT 中，因为窗口计算不缩减行数

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

四、逐步推导

完整SQL：一次查询同时计算排名和占比

窗口函数可以在同一 SELECT 中并列使用，互不干扰。RANK() OVER (ORDER BY sales_amt DESC) 按销售额降序排名，SUM(sales_amt) OVER() 不分组计算全表总销售额。

执行SQL

select city, sales_amt,
       rank() over (order by sales_amt desc) as city_rank,
       round(sales_amt / sum(sales_amt) over () * 100, 2) as sales_pct
from t7_city_sales
order by city_rank

执行结果

+--------+-----------+-----------+-----------+
| city   | sales_amt | city_rank | sales_pct |
+--------+-----------+-----------+-----------+
| 杭州   | 500000    | 1         | 32.26     |
| 上海   | 400000    | 2         | 25.81     |
| 北京   | 300000    | 3         | 19.35     |
| 深圳   | 200000    | 4         | 12.90     |
| 广州   | 150000    | 5         | 9.68      |
+--------+-----------+-----------+-----------+

杭州以 32.26% 的销售占比排名第一，前三个城市（杭州、上海、北京）合计贡献了 77.42% 的销售额，符合典型的"二八分布"。

五、常见坑点

坑1：RANK vs ROW_NUMBER 在并列时的差异

如果两个城市销售额相同，RANK() 会给出相同名次（1, 1, 3...），ROW_NUMBER() 会强行区分（1, 2, 3...）。在"城市排名"场景中，用 RANK 更合理——并列第1的两个城市应该都是第1名，而不是硬分出先后。

坑2：窗口函数中写 PARTITION BY 会改变占比的分母

如果加了 PARTITION BY region，SUM() OVER() 的分母就变成"每个区域内总销售额"而非全国总量，占比含义完全改变。不加 PARTITION BY 才是全局占比。

六、举一反三

1. 按区域分组排名+占比：RANK() OVER (PARTITION BY region ORDER BY sales_amt DESC) + SUM() OVER (PARTITION BY region) 实现各区域内城市的排名和区域内占比
2. 累计占比（帕累托分析）：SUM(sales_amt) OVER (ORDER BY sales_amt DESC) / SUM(sales_amt) OVER () 计算累计贡献度，画出二八曲线
3. 同比/环比增长率：LAG(sales_amt) OVER (PARTITION BY city ORDER BY month) 获取上月销售额，计算环比增长率
4. 销售额分层：结合 CASE WHEN，将城市按占比分为"核心城市（>20%）、重要城市（10-20%）、潜力城市（<10%）"三档

七、知识点总结

考点	说明
RANK() OVER	按销售额降序排名，处理并列值时不跳跃名次
SUM() OVER()	空括号 = 全表总和，作为占比计算的分母
ROUND + 百分比	格式化占比为两位小数，乘以100转为百分数
窗口函数并列使用	多个窗口函数可在同一 SELECT 中共存，互不干扰

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t7_city_sales (
    city      string COMMENT '城市',
    sales_amt decimal(10,2) COMMENT '销售额'
) COMMENT '城市销售表';

INSERT INTO t7_city_sales VALUES
('杭州',500000),('北京',300000),('上海',400000),('深圳',200000),('广州',150000);

📱关注公众号

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

💬加群交流

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