窗口函数的使用
一、排名与去重类
这是窗口函数最基础、最高频的考点，常用于 TopN、去重、取最新记录等场景。
1. ROW_NUMBER、RANK、DENSE_RANK 有什么区别？
答案
三者都可以用于排名，但处理并列名次时规则不同。
| 函数 | 是否允许并列 | 是否跳过名次 | 特点 |
|---|---|---|---|
ROW_NUMBER() |
否 | 否 | 每行唯一编号 |
RANK() |
是 | 是 | 并列后跳号 |
DENSE_RANK() |
是 | 否 | 并列后不跳号 |
示例数据
| name | score |
|---|---|
| A | 100 |
| B | 90 |
| C | 90 |
| D | 80 |
SELECT
name,
score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rn,
RANK() OVER(ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rnk
FROM student;
结果
| name | score | rn | rnk | dense_rnk |
|---|---|---|---|---|
| A | 100 | 1 | 1 | 1 |
| B | 90 | 2 | 2 | 2 |
| C | 90 | 3 | 2 | 2 |
| D | 80 | 4 | 4 | 3 |
面试表达
ROW_NUMBER 适合取唯一一条记录，比如每个用户最近一次登录。
RANK 适合保留并列名次，但并列后会跳号。
DENSE_RANK 适合业务上的连续排名，比如查询“前 3 名且包含并列”。
2. 查询每个部门薪资排名前 3 的员工，包含并列第 3 名
考察点
是否知道“包含并列”时不能用 ROW_NUMBER()，通常应使用 RANK() 或 DENSE_RANK()。
推荐写法
WITH t AS (
SELECT
emp_id,
emp_name,
dept_id,
salary,
DENSE_RANK() OVER(
PARTITION BY dept_id
ORDER BY salary DESC
) AS salary_rank
FROM employee
)
SELECT
emp_id,
emp_name,
dept_id,
salary
FROM t
WHERE salary_rank <= 3;
注意
如果业务说的是“薪资档位前 3”，用 DENSE_RANK() 更合适。
如果业务说的是“排名前 3，排名规则允许跳号”，可以用 RANK()。
如果业务说的是“每个部门最多返回 3 个人”，才用 ROW_NUMBER()。
3. 查询每个用户最近一次登录记录
考察点
典型的 Top1 问题，使用 ROW_NUMBER()。
WITH t AS (
SELECT
user_id,
login_time,
device,
ip,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY login_time DESC, id DESC
) AS rn
FROM user_login
)
SELECT
user_id,
login_time,
device,
ip
FROM t
WHERE rn = 1;
为什么加 id DESC？
如果同一个用户有多条登录记录的 login_time 完全一样，只按 login_time DESC 排序会导致结果不稳定。
因此要加一个唯一字段作为二级排序条件，例如：
ORDER BY login_time DESC, id DESC
这可以保证每次查询结果确定。
4. 删除重复数据，只保留每组 ID 最大的一条
假设表 orders 中，按照 user_id, order_no 判断重复，只保留 id 最大的一条。
查询重复数据
WITH t AS (
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY user_id, order_no
ORDER BY id DESC
) AS rn
FROM orders
)
SELECT *
FROM t
WHERE rn > 1;
删除重复数据
不同数据库写法略有不同。
MySQL 8+
DELETE FROM orders
WHERE id IN (
SELECT id
FROM (
SELECT
id,
ROW_NUMBER() OVER(
PARTITION BY user_id, order_no
ORDER BY id DESC
) AS rn
FROM orders
) t
WHERE rn > 1
);
PostgreSQL
WITH t AS (
SELECT
id,
ROW_NUMBER() OVER(
PARTITION BY user_id, order_no
ORDER BY id DESC
) AS rn
FROM orders
)
DELETE FROM orders
WHERE id IN (
SELECT id
FROM t
WHERE rn > 1
);
面试加分回答
相比 GROUP BY + MAX(id)，窗口函数可以保留整行数据，并且逻辑更清晰，适合复杂去重场景。
5. 如果排序字段有 NULL，窗口函数如何处理？
答案
不同数据库对 NULL 的默认排序规则可能不同。
常见处理方式是显式指定：
ORDER BY salary DESC NULLS LAST
部分数据库支持 NULLS FIRST / NULLS LAST，如 PostgreSQL、Oracle。
MySQL 不直接支持该语法，可以使用表达式处理：
ORDER BY salary IS NULL, salary DESC
含义是：
salary IS NULL = 0
的非空数据排前面，NULL 排后面。
面试表达
为了保证窗口函数排序结果稳定，排序字段中如果可能有 NULL，应显式定义 NULL 的排序规则，避免依赖数据库默认行为。
二、偏移分析类：LAG / LEAD
主要用于时序分析，比如相邻订单间隔、环比、前后状态变化。
6. 不使用自连接，如何计算当前记录和上一条记录的差值？
示例：计算每日销售额与前一天销售额的差值
SELECT
dt,
sales,
sales - LAG(sales) OVER(ORDER BY dt) AS diff
FROM daily_sales;
带默认值
SELECT
dt,
sales,
sales - LAG(sales, 1, 0) OVER(ORDER BY dt) AS diff
FROM daily_sales;
解释
LAG(sales, 1, 0)
含义是：
当前行往前取 1 行的 sales，如果没有上一行，则返回默认值 0。
7. 计算用户相邻两次下单的时间间隔
假设订单表为 orders(user_id, order_id, order_time)。
WITH t AS (
SELECT
user_id,
order_id,
order_time,
LAG(order_time) OVER(
PARTITION BY user_id
ORDER BY order_time
) AS prev_order_time
FROM orders
)
SELECT
user_id,
order_id,
order_time,
prev_order_time,
DATEDIFF(order_time, prev_order_time) AS interval_days
FROM t;
注意
不同数据库日期函数不同：
MySQL
DATEDIFF(order_time, prev_order_time)
PostgreSQL
order_time::date - prev_order_time::date
Spark SQL
datediff(order_time, prev_order_time)
面试加分点
如果一个用户一天内有多笔订单，排序最好加上订单 ID：
ORDER BY order_time, order_id
避免相同时间导致顺序不确定。
8. 计算每日销售额的环比增长率
WITH t AS (
SELECT
dt,
sales,
LAG(sales) OVER(ORDER BY dt) AS prev_sales
FROM daily_sales
)
SELECT
dt,
sales,
prev_sales,
CASE
WHEN prev_sales IS NULL OR prev_sales = 0 THEN NULL
ELSE (sales - prev_sales) * 1.0 / prev_sales
END AS growth_rate
FROM t;
易错点
不能直接写：
(sales - LAG(sales)) / LAG(sales)
虽然有些数据库允许，但可读性差，而且没有处理 NULL 和除零问题。
面试表达
环比计算要注意两类边界：
-
第一行没有上一期数据。
-
上一期销售额为 0，不能直接作为分母。
9. 找出状态发生变化的记录
例如用户状态表：
| user_id | dt | status |
|---|---|---|
| 1 | 2024-01-01 | active |
| 1 | 2024-01-02 | active |
| 1 | 2024-01-03 | inactive |
查询状态变化的日期。
WITH t AS (
SELECT
user_id,
dt,
status,
LAG(status) OVER(
PARTITION BY user_id
ORDER BY dt
) AS prev_status
FROM user_status
)
SELECT
user_id,
dt,
status,
prev_status
FROM t
WHERE prev_status IS NOT NULL
AND status <> prev_status;
考察点
LAG() 不只可以计算数值差值，也可以比较前后状态变化。
三、聚合窗口类：SUM / COUNT / AVG OVER
主要考察累计值、总计占比、滑动窗口、窗口帧。
10. SUM(salary) OVER() 和 SUM(salary) OVER(PARTITION BY dept_id) 有什么区别？
示例
SELECT
emp_id,
dept_id,
salary,
SUM(salary) OVER() AS total_salary,
SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary
FROM employee;
区别
| 写法 | 含义 |
|---|---|
SUM(salary) OVER() |
全表总薪资，每行相同 |
SUM(salary) OVER(PARTITION BY dept_id) |
每个部门总薪资，同部门相同 |
SUM(salary) OVER(ORDER BY hire_date) |
按入职时间累计求和 |
11. 计算每个用户的累计消费金额
SELECT
user_id,
order_id,
order_time,
amount,
SUM(amount) OVER(
PARTITION BY user_id
ORDER BY order_time, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_amount
FROM orders;
为什么建议显式写窗口帧？
因为默认窗口帧在不同数据库、不同排序字段下可能产生不符合预期的结果。
尤其当 ORDER BY 字段有重复值时，RANGE 和 ROWS 的结果可能不同。
12. 计算每个用户订单金额占其总消费金额的比例
SELECT
user_id,
order_id,
amount,
amount * 1.0 / SUM(amount) OVER(PARTITION BY user_id) AS amount_ratio
FROM orders;
面试表达
窗口函数可以在保留明细行的同时计算分组汇总值。
如果用 GROUP BY user_id，会把明细行聚合掉；而窗口函数不会减少行数。
13. 计算最近 7 天移动平均销售额
如果数据是每天一行：
SELECT
dt,
sales,
AVG(sales) OVER(
ORDER BY dt
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM daily_sales;
重要陷阱
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 表示当前行以及前 6 行，不一定是真实的最近 7 个自然日。
如果数据缺日期，比如周末没有数据，那么它计算的是最近 7 条记录，而不是最近 7 天。
14. ROWS 和 RANGE 有什么区别？
答案
| 类型 | 含义 | 适合场景 |
|---|---|---|
ROWS |
按物理行数计算窗口 | 最近 N 条记录 |
RANGE |
按排序字段的值范围计算窗口 | 最近 N 天、金额范围、时间范围 |
示例
AVG(sales) OVER(
ORDER BY dt
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
表示最近 7 行。
AVG(sales) OVER(
ORDER BY dt
RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
)
表示最近 7 个自然日。
面试表达
如果业务要求“最近 7 条记录”，用 ROWS。
如果业务要求“最近 7 天”，更应该考虑 RANGE 或先补齐日期表后再用 ROWS。
四、连续性问题：岛屿与间隙问题
这是中高级面试的重点，常见于连续签到、连续登录、连续上涨、连续异常等场景。
15. 找出连续登录 3 天及以上的用户
假设表为：
user_login(user_id, login_date)
同一用户一天可能登录多次，需要先去重。
WITH login_distinct AS (
SELECT DISTINCT
user_id,
login_date
FROM user_login
),
t AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY login_date
) AS rn
FROM login_distinct
),
g AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS grp
FROM t
)
SELECT
user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS continuous_days
FROM g
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;
解题思路
连续日期有一个特点：
login_date - row_number
结果是恒定的。
例如：
| login_date | rn | login_date - rn |
|---|---|---|
| 2024-01-01 | 1 | 2023-12-31 |
| 2024-01-02 | 2 | 2023-12-31 |
| 2024-01-03 | 3 | 2023-12-31 |
所以可以按这个差值分组，得到连续区间。
16. 找出每个用户最长连续登录天数
WITH login_distinct AS (
SELECT DISTINCT
user_id,
login_date
FROM user_login
),
t AS (
SELECT
user_id,
login_date,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY login_date
) AS rn
FROM login_distinct
),
g AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS grp
FROM t
),
periods AS (
SELECT
user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS continuous_days
FROM g
GROUP BY user_id, grp
),
r AS (
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY continuous_days DESC, start_date
) AS rn
FROM periods
)
SELECT
user_id,
start_date,
end_date,
continuous_days
FROM r
WHERE rn = 1;
易错点
同一个用户一天多次登录，必须先 DISTINCT，否则连续天数会被重复登录次数干扰。
17. 查找股票连续上涨的区间
假设表为：
stock_price(stock_id, dt, price)
思路：
-
用
LAG()找出当天是否上涨。 -
只保留上涨的日期。
-
用
ROW_NUMBER()差值法找连续上涨区间。
WITH t AS (
SELECT
stock_id,
dt,
price,
LAG(price) OVER(
PARTITION BY stock_id
ORDER BY dt
) AS prev_price
FROM stock_price
),
up_days AS (
SELECT
stock_id,
dt,
price
FROM t
WHERE price > prev_price
),
r AS (
SELECT
stock_id,
dt,
price,
ROW_NUMBER() OVER(
PARTITION BY stock_id
ORDER BY dt
) AS rn
FROM up_days
),
g AS (
SELECT
stock_id,
dt,
price,
DATE_SUB(dt, INTERVAL rn DAY) AS grp
FROM r
)
SELECT
stock_id,
MIN(dt) AS start_date,
MAX(dt) AS end_date,
COUNT(*) AS up_days
FROM g
GROUP BY stock_id, grp;
面试追问
这里的 up_days 统计的是“上涨的交易日数量”，不是完整行情区间的自然天数。
如果要表达“连续上涨 3 天”，需要先明确：
-
是连续 3 个自然日？
-
还是连续 3 个交易日？
-
是 3 次上涨？
-
还是包含起点在内的 4 天价格序列？
这个澄清很加分。
18. 合并重叠的时间区间
假设有会议室预订表：
booking(room_id, start_time, end_time)
目标：合并重叠或相连的时间段。
WITH t AS (
SELECT
room_id,
start_time,
end_time,
MAX(end_time) OVER(
PARTITION BY room_id
ORDER BY start_time, end_time
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS prev_max_end_time
FROM booking
),
flagged AS (
SELECT
room_id,
start_time,
end_time,
CASE
WHEN prev_max_end_time IS NULL
OR start_time > prev_max_end_time
THEN 1
ELSE 0
END AS new_group_flag
FROM t
),
grouped AS (
SELECT
room_id,
start_time,
end_time,
SUM(new_group_flag) OVER(
PARTITION BY room_id
ORDER BY start_time, end_time
) AS grp
FROM flagged
)
SELECT
room_id,
MIN(start_time) AS merged_start_time,
MAX(end_time) AS merged_end_time
FROM grouped
GROUP BY room_id, grp
ORDER BY room_id, merged_start_time;
核心思路
判断当前区间是否开启新组：
start_time > 前面所有区间的最大 end_time
如果是，则新开一个区间组；否则归入当前组。
易错点
不能只比较上一行的 end_time，因为可能存在嵌套区间。
例如：
| start_time | end_time |
|---|---|
| 09:00 | 12:00 |
| 10:00 | 10:30 |
| 11:00 | 13:00 |
第三行和第二行看似不重叠，但它和第一行重叠。因此要比较“前面所有区间的最大结束时间”。
五、分组取值与首尾值类
这一类经常考 FIRST_VALUE、LAST_VALUE、NTH_VALUE。
19. 查询每个用户第一笔订单和最后一笔订单金额
SELECT
user_id,
order_id,
order_time,
amount,
FIRST_VALUE(amount) OVER(
PARTITION BY user_id
ORDER BY order_time, order_id
) AS first_order_amount,
LAST_VALUE(amount) OVER(
PARTITION BY user_id
ORDER BY order_time, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_order_amount
FROM orders;
重要陷阱
LAST_VALUE() 默认窗口帧通常截止到当前行，所以如果不写：
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
LAST_VALUE() 很可能返回当前行的金额，而不是分组内最后一行金额。
这是非常高频的坑。
20. 每个部门薪资最高员工的姓名
可以用 FIRST_VALUE()：
SELECT DISTINCT
dept_id,
FIRST_VALUE(emp_name) OVER(
PARTITION BY dept_id
ORDER BY salary DESC, emp_id
) AS highest_salary_emp
FROM employee;
也可以用 ROW_NUMBER()：
WITH t AS (
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY dept_id
ORDER BY salary DESC, emp_id
) AS rn
FROM employee
)
SELECT
dept_id,
emp_name,
salary
FROM t
WHERE rn = 1;
面试建议
实际开发中更推荐 ROW_NUMBER()，因为它可以保留完整员工信息，并且逻辑更清晰。
六、分桶与百分位类
常见于用户分层、成绩分布、商品价格分档。
21. NTILE 是什么？如何把用户按消费金额分成 4 档？
SELECT
user_id,
total_amount,
NTILE(4) OVER(
ORDER BY total_amount DESC
) AS level
FROM user_amount;
含义
NTILE(4) 会尽量把数据均匀分成 4 组。
例如可以理解为：
| level | 含义 |
|---|---|
| 1 | 高消费用户 |
| 2 | 中高消费用户 |
| 3 | 中低消费用户 |
| 4 | 低消费用户 |
易错点
NTILE(4) 是按照人数均分，不是按照金额区间均分。
也就是说，每档人数尽量接近，但金额范围不一定均匀。
22. PERCENT_RANK 和 CUME_DIST 有什么区别？
答案
| 函数 | 含义 |
|---|---|
PERCENT_RANK() |
当前行排名在整体中的相对位置 |
CUME_DIST() |
小于等于当前值的行数占比 |
示例：
SELECT
user_id,
score,
PERCENT_RANK() OVER(ORDER BY score) AS percent_rank_value,
CUME_DIST() OVER(ORDER BY score) AS cume_dist_value
FROM user_score;
面试表达
PERCENT_RANK() 更关注排名位置，CUME_DIST() 更关注累计分布比例。
在用户分层、成绩分布、风控评分中比较常见。
七、执行顺序与 SQL 原理类
这是高阶岗位、大数据岗位、数仓开发岗位常问内容。
23. 窗口函数在 SQL 中的逻辑执行顺序是什么？
标准逻辑顺序
FROM
WHERE
GROUP BY
HAVING
SELECT
窗口函数
ORDER BY
LIMIT
更准确地说，窗口函数是在 WHERE、GROUP BY、HAVING 之后计算的，因此它不能直接写在 WHERE 条件中。
24. 为什么窗口函数不能直接写在 WHERE 里？
错误写法：
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY login_time DESC
) AS rn
FROM user_login
WHERE rn = 1;
这是错误的，因为 WHERE 执行时，rn 还没有被计算出来。
正确写法
WITH t AS (
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY login_time DESC
) AS rn
FROM user_login
)
SELECT *
FROM t
WHERE rn = 1;
或者：
SELECT *
FROM (
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY login_time DESC
) AS rn
FROM user_login
) t
WHERE rn = 1;
特殊情况
有些数据库支持 QUALIFY，例如 BigQuery、Snowflake、Databricks SQL 等：
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY login_time DESC
) AS rn
FROM user_login
QUALIFY rn = 1;
QUALIFY 的作用类似于专门过滤窗口函数结果的 WHERE。
25. 窗口函数和 GROUP BY 有什么区别？
答案
| 对比项 | GROUP BY | 窗口函数 |
|---|---|---|
| 是否减少行数 | 会 | 不会 |
| 是否保留明细 | 不保留 | 保留 |
| 适合场景 | 分组汇总 | 明细 + 分组指标 |
| 示例 | 每个部门平均工资 | 每个员工工资与部门平均工资对比 |
示例
查询每个员工工资与本部门平均工资的差值：
SELECT
emp_id,
emp_name,
dept_id,
salary,
AVG(salary) OVER(PARTITION BY dept_id) AS dept_avg_salary,
salary - AVG(salary) OVER(PARTITION BY dept_id) AS diff_from_avg
FROM employee;
如果用 GROUP BY dept_id，就无法直接保留每个员工的明细信息。
26. 窗口函数和自连接相比有什么优势？
答案
窗口函数通常有三个优势：
-
SQL 更简洁，可读性更好。
-
可以避免复杂的自连接逻辑。
-
在很多数据库执行引擎中，窗口函数可以复用排序和分区结果，性能更稳定。
示例
计算每条订单与上一条订单的间隔，用自连接会很复杂，而窗口函数只需要：
LAG(order_time) OVER(
PARTITION BY user_id
ORDER BY order_time
)
八、性能优化类
适合数仓开发、大数据开发、后端数据查询优化岗位。
27. 窗口函数的性能瓶颈通常在哪里？
答案
窗口函数的主要性能开销通常来自：
-
PARTITION BY带来的数据分区。 -
ORDER BY带来的排序。 -
大数据场景下的 Shuffle。
-
多个不同窗口定义导致多次排序。
-
分区倾斜导致单个分区过大。
面试表达
窗口函数本质上需要在分区内排序，所以性能瓶颈通常不是函数计算本身，而是排序、分区和数据重分布。
28. 一个 SQL 中有多个窗口函数，会不会重复排序？
例如：
SELECT
user_id,
order_time,
amount,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY order_time
) AS rn,
SUM(amount) OVER(
PARTITION BY user_id
ORDER BY order_time
) AS cum_amount
FROM orders;
答案
如果多个窗口函数的 PARTITION BY 和 ORDER BY 完全相同，很多主流数据库或执行引擎可以复用排序结果。
但如果窗口定义不同，例如：
PARTITION BY user_id ORDER BY order_time
和：
PARTITION BY product_id ORDER BY order_time
通常需要不同的分区和排序，性能开销会明显增加。
29. 如何优化窗口函数？
常见优化思路
| 优化方向 | 说明 |
|---|---|
| 减少数据量 | 先用 WHERE 过滤无关数据 |
| 减少字段 | 避免 SELECT * |
| 统一窗口定义 | 尽量让多个窗口函数复用同一组 PARTITION BY + ORDER BY |
| 控制分区大小 | 避免某个 key 数据极度倾斜 |
| 建索引 | OLTP 数据库中可对分区字段、排序字段建联合索引 |
| 预聚合 | 先把明细压缩到天、用户、商品等粒度 |
| 分桶/分区表 | 大数据场景下利用表分区、桶表、Z-Order 等 |
示例
低效：
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time) AS rn
FROM orders;
优化：
WITH filtered AS (
SELECT
user_id,
order_id,
order_time,
amount
FROM orders
WHERE order_time >= '2024-01-01'
)
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY order_time, order_id
) AS rn
FROM filtered;
30. 窗口函数适合在哪些业务场景中使用？
答案
窗口函数适合“既要保留明细，又要计算分组或前后关系”的场景。
常见场景包括：
| 场景 | 典型函数 |
|---|---|
| TopN 排名 | ROW_NUMBER、RANK、DENSE_RANK |
| 去重 | ROW_NUMBER |
| 最近一次记录 | ROW_NUMBER |
| 环比同比 | LAG、LEAD |
| 累计值 | SUM OVER |
| 移动平均 | AVG OVER |
| 用户分层 | NTILE |
| 连续登录 | ROW_NUMBER 差值法 |
| 首尾值 | FIRST_VALUE、LAST_VALUE |
九、综合业务题
下面这些更接近真实面试题。
31. 查询每个用户消费金额最高的订单
WITH t AS (
SELECT
user_id,
order_id,
amount,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY amount DESC, order_id
) AS rn
FROM orders
)
SELECT
user_id,
order_id,
amount
FROM t
WHERE rn = 1;
如果要保留并列最高金额订单
WITH t AS (
SELECT
user_id,
order_id,
amount,
RANK() OVER(
PARTITION BY user_id
ORDER BY amount DESC
) AS rnk
FROM orders
)
SELECT
user_id,
order_id,
amount
FROM t
WHERE rnk = 1;
32. 查询每个班级成绩排名第二的学生
如果只返回一个人
WITH t AS (
SELECT
class_id,
student_id,
score,
ROW_NUMBER() OVER(
PARTITION BY class_id
ORDER BY score DESC, student_id
) AS rn
FROM student_score
)
SELECT *
FROM t
WHERE rn = 2;
如果保留并列第二
WITH t AS (
SELECT
class_id,
student_id,
score,
DENSE_RANK() OVER(
PARTITION BY class_id
ORDER BY score DESC
) AS rnk
FROM student_score
)
SELECT *
FROM t
WHERE rnk = 2;
33. 查询每个商品最近 30 天销量排名前 10 的日期
WITH daily AS (
SELECT
product_id,
dt,
SUM(quantity) AS sales_qty
FROM order_detail
WHERE dt >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY product_id, dt
),
r AS (
SELECT
product_id,
dt,
sales_qty,
ROW_NUMBER() OVER(
PARTITION BY product_id
ORDER BY sales_qty DESC, dt
) AS rn
FROM daily
)
SELECT
product_id,
dt,
sales_qty
FROM r
WHERE rn <= 10;
考察点
这道题结合了：
-
预聚合。
-
分组排名。
-
TopN。
-
时间过滤。
34. 计算用户每笔订单是该用户的第几单
SELECT
user_id,
order_id,
order_time,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY order_time, order_id
) AS order_seq
FROM orders;
扩展
找出用户首单：
WITH t AS (
SELECT
user_id,
order_id,
order_time,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY order_time, order_id
) AS order_seq
FROM orders
)
SELECT *
FROM t
WHERE order_seq = 1;
找出用户复购订单：
WITH t AS (
SELECT
user_id,
order_id,
order_time,
ROW_NUMBER() OVER(
PARTITION BY user_id
ORDER BY order_time, order_id
) AS order_seq
FROM orders
)
SELECT *
FROM t
WHERE order_seq >= 2;
35. 计算每个用户从首单到当前订单的累计消费
SELECT
user_id,
order_id,
order_time,
amount,
SUM(amount) OVER(
PARTITION BY user_id
ORDER BY order_time, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_amount
FROM orders;
十、面试回答模板
最后给你一套回答窗口函数题的通用模板。
1. 遇到 TopN 问题
可以这样说：
这个问题本质是分组内排序。
我会使用窗口函数，按照业务维度PARTITION BY，按照排名指标ORDER BY。
如果只需要固定返回 N 条，用ROW_NUMBER()；如果要保留并列名次，用RANK()或DENSE_RANK()。
2. 遇到最近一次记录问题
可以这样说：
这是典型的分组取最新记录问题。
我会用ROW_NUMBER()，按照用户分区，按时间倒序排序。
为了保证结果确定性，如果时间字段可能重复，会补充主键作为二级排序条件。
3. 遇到环比问题
可以这样说：
环比问题需要获取当前行的上一期数据。
使用LAG()可以避免自连接。
同时需要处理第一行没有上一期数据，以及上一期指标为 0 的边界情况。
4. 遇到累计问题
可以这样说：
累计指标可以用聚合窗口函数实现。
关键是定义好分区、排序和窗口帧。
我通常会显式写出ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，避免默认窗口帧带来的歧义。
5. 遇到连续性问题
可以这样说：
连续性问题属于岛屿与间隙问题。
常见解法是先对日期去重，然后用ROW_NUMBER()编号。
对连续日期来说，日期 - 序号是恒定值，因此可以按该值分组，得到连续区间。
高频易错点总结
| 易错点 | 正确做法 |
|---|---|
用 ROW_NUMBER() 查并列 TopN |
应使用 RANK() 或 DENSE_RANK() |
| 排序字段重复导致结果不稳定 | 增加主键作为二级排序字段 |
| 忽略 NULL 排序 | 显式指定 NULLS FIRST/LAST 或用表达式处理 |
| 直接在 WHERE 中过滤窗口函数 | 用 CTE、子查询或 QUALIFY |
LAST_VALUE() 结果异常 |
显式指定完整窗口帧 |
| 移动 7 行误认为移动 7 天 | 区分 ROWS 和 RANGE |
| 连续登录未先去重 | 先按用户、日期去重 |
| 环比未处理除零 | 使用 CASE WHEN prev_value = 0 THEN NULL |
| 多个窗口定义不一致 | 尽量复用相同 PARTITION BY + ORDER BY |
| 大表直接窗口排序 | 先过滤、预聚合、减少字段 |
最后浓缩版背诵口诀
排名用 Rank，唯一用 Row。
并列要保留，别忘 Dense。
前后看 Lag Lead，累计看 Sum Over。
连续问题日期减序号。
WHERE 不能直接筛窗口，CTE 包一层。
排序要稳定，主键来兜底。
LAST_VALUE 要小心，窗口帧要写全。
浙公网安备 33010602011771号