SQL窗口函数面试必考题（高频考点+真题详解）

第一章：SQL窗口函数面试必考题（高频考点+真题详解）

SQL窗口函数是数据分析和数据库开发岗位面试中的核心考察点，尤其在处理排序、累计、分组统计等复杂查询场景时表现出色。掌握其语法结构与典型应用模式，是通过技术面试的关键。

窗口函数基本语法结构

窗口函数的核心在于在不改变原始行数的前提下，为每一行计算一个基于“窗口”的聚合值。基本语法如下：

SELECT 
    column1,
    column2,
    FUNCTION(column3) OVER (
        [PARTITION BY partition_expression]
        [ORDER BY sort_expression]
        [frame_clause]
    ) AS result_column
FROM table_name;

其中：

• PARTITION BY：将数据分组，类似 GROUP BY，但每行仍保留
• ORDER BY：定义窗口内行的排序方式
• frame_clause：如 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW，定义窗口范围

常见窗口函数分类

类型	函数示例	用途说明
排序函数	RANK(), DENSE_RANK(), ROW_NUMBER()	对组内数据进行排名，处理并列情况不同
分析函数	LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()	访问前后行数据，常用于趋势分析
聚合函数	SUM(), AVG(), MAX(), MIN() over()	在窗口内做累计或移动平均

经典面试真题示例

求每个部门工资最高的员工信息（包括姓名、部门、工资）：

SELECT 
    name, 
    department, 
    salary
FROM (
    SELECT 
        name, 
        department, 
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rk
    FROM employee
) t
WHERE rk = 1; -- 使用RANK可保留并列最高工资的员工

该查询通过子查询为每位员工在各自部门内按工资降序打上排名，外层筛选排名为1的记录，准确识别出各部最高薪人员。

第二章：窗口函数核心概念与语法解析

2.1 窗口函数基本结构与OVER子句深入剖析

窗口函数的核心在于其独特的执行逻辑：在不改变原始行数的前提下，为每一行计算一个基于“窗口”的聚合值。其基本语法结构由函数名、OVER关键字及括号内的子句构成。

基本语法结构

SELECT 
    column, 
    AVG(value) OVER (
        PARTITION BY category 
        ORDER BY date 
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM table;

该语句中， OVER 子句定义了窗口的范围： PARTITION BY 将数据分组， ORDER BY 确定窗口内行的顺序， ROWS 限定物理行边界。

OVER子句三要素

• PARTITION BY：划分逻辑分区，类似GROUP BY，但保留每行输出；
• ORDER BY：指定窗口内排序方式，影响累计或移动计算方向；
• Window Frame：如ROWS/RANGE，精确控制参与计算的行集合。

2.2 分区（PARTITION BY）与排序（ORDER BY）的协同机制

在SQL窗口函数中， PARTITION BY 和 ORDER BY 共同构建了数据处理的上下文环境。分区子句将数据集划分为多个逻辑组，而排序子句则在每个分区内定义行的执行顺序。

执行优先级与作用域

PARTITION BY 优先于 ORDER BY 执行，先分组后排序。每个分区独立进行排序，确保聚合计算在局部范围内有效。

典型应用场景

例如，计算每位员工在其部门内的薪资排名：

SELECT 
  dept, 
  salary,
  RANK() OVER (
    PARTITION BY dept 
    ORDER BY salary DESC
  ) AS rank_in_dept
FROM employees;

上述语句中， PARTITION BY dept 将员工按部门分割， ORDER BY salary DESC 在每部门内按薪资降序排列， RANK() 基于此顺序生成排名。这种协同机制广泛应用于排名、移动平均和累计求和等分析场景。

2.3 ROWS/RANGE模式下的窗口帧定义与应用场景

在SQL窗口函数中，ROWS和RANGE是定义窗口帧的两种核心模式。ROWS基于物理行数进行边界划分，适用于按固定数量前后行计算的场景；而RANGE则依据逻辑值范围确定帧边界，常用于处理时间序列或连续数值。

ROWS模式示例

SELECT 
  order_date, 
  revenue,
  AVG(revenue) OVER (
    ORDER BY order_date 
    RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
  ) AS avg_revenue_7d
FROM sales;

该查询使用RANGE模式计算过去7天内的平均收入，适合日期间隔不规则的数据集，确保时间范围内的所有记录都被纳入统计。

应用场景对比

• ROWS：适用于滑动平均、移动总和等需精确控制行数的场景
• RANGE：更适合基于值域的聚合，如“工资高于当前员工1000以内的平均绩效”

通过合理选择模式，可精准控制分析范围，提升查询语义准确性。

2.4 聚合类窗口函数与普通聚合函数的本质区别

普通聚合函数（如 SUM、 COUNT、 AVG）会将多行数据归约为单行结果，通常需配合 GROUP BY 使用。而聚合类窗口函数在保留原始行的基础上，为每一行计算一个聚合值，不减少行数。

核心差异对比

特性	普通聚合函数	聚合类窗口函数
输出行数	减少（每组一行）	保持不变
是否需要 GROUP BY	必须（除非全局聚合）	不需要
支持细粒度分析	否	是

示例：计算每个部门员工薪资占比

SELECT 
  name, 
  dept, 
  salary,
  SUM(salary) OVER() AS total_company_salary,
  AVG(salary) OVER(PARTITION BY dept) AS dept_avg
FROM employees;

该查询中， SUM(...) OVER() 计算全公司总薪资， AVG(...) OVER(PARTITION BY dept) 计算各部门平均薪资，每行数据均被保留，实现细粒度分析。

2.5 窗口函数执行顺序与SQL语句逻辑流程分析

在标准SQL执行流程中，窗口函数的计算发生在`FROM`、`JOIN`、`WHERE`、`GROUP BY`和`HAVING`之后，但在`ORDER BY`之前。这意味着窗口函数可基于已分组和过滤的数据进行行间计算。

SQL逻辑执行顺序

1. FROM / JOIN：加载表并关联数据
2. WHERE：过滤原始行
3. GROUP BY：分组聚合
4. HAVING：过滤分组结果
5. SELECT：计算包括窗口函数在内的表达式
6. ORDER BY：最终排序

示例代码

SELECT 
  name,
  dept,
  salary,
  AVG(salary) OVER (PARTITION BY dept) AS avg_dept_salary
FROM employees 
WHERE hire_date > '2020-01-01';

该查询首先筛选入职时间，然后在每条保留记录上计算其部门的平均薪资。`OVER(PARTITION BY dept)`定义了窗口范围，确保平均值按部门隔离计算，体现窗口函数在逻辑流程中的延迟执行特性。

第三章：常见窗口函数分类与实战应用

3.1 排名函数ROW_NUMBER、RANK、DENSE_RANK对比与去重策略

在SQL中， ROW_NUMBER、 RANK和 DENSE_RANK是常用的窗口函数，用于对结果集进行排序并分配排名值。

核心差异对比

• ROW_NUMBER：为每行分配唯一序号，即使排序字段相同也连续编号；
• RANK：相同值并列排名，但会跳过后续名次（如1,1,3）；
• DENSE_RANK：相同值并列排名，不跳过名次（如1,1,2）。

数据	A	A	B	C
ROW_NUMBER	1	2	3	4
RANK	1	1	3	4
DENSE_RANK	1	1	2	3

去重策略实现

使用 ROW_NUMBER()可高效去重：

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY update_time DESC) AS rn
  FROM users
) t WHERE rn = 1;

该语句按 id分组，以最新更新时间保留唯一记录，实现去重。

3.2 数值分布函数PERCENT_RANK、CUME_DIST在业务指标中的运用

在分析员工绩效或销售排名时，PERCENT_RANK与CUME_DIST能有效衡量数值相对位置。

函数定义与差异

• PERCENT_RANK：计算 (当前行排名 - 1) / (总行数 - 1)，结果范围 [0, 1]
• CUME_DIST：计算 小于等于当前值的行数 / 总行数，体现累积分布

实际SQL示例

SELECT 
  name, 
  score,
  PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,
  CUME_DIST() OVER (ORDER BY score) AS cum_dist
FROM employee_scores;

上述代码中， PERCENT_RANK反映个体在整体中的相对排序百分比，常用于绩效分档； CUME_DIST则适用于判断某成绩是否进入前80%等业务场景，两者结合可精准划分用户等级区间。

3.3 前后行访问函数LAG、LEAD实现同比环比分析

在时间序列分析中， LAG和 LEAD函数能够高效实现同比、环比计算。它们通过偏移行位置获取前后数据，适用于连续周期对比。

核心函数说明

• LAG(col, n)：获取当前行向前第 n 行的值
• LEAD(col, n)：获取当前行向后第 n 行的值

同比环比SQL示例

SELECT 
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS last_month,        -- 环比
  LAG(revenue, 12) OVER (ORDER BY month) AS last_year_same_month, -- 同比
  (revenue - LAG(revenue, 1) OVER (ORDER BY month)) / LAG(revenue, 1) OVER (ORDER BY month) AS mom_growth,
  (revenue - LAG(revenue, 12) OVER (ORDER BY month)) / LAG(revenue, 12) OVER (ORDER BY month) AS yoy_growth
FROM sales_data;

上述代码中， LAG(revenue, 1)获取上月收入用于环比， LAG(revenue, 12)获取去年同期数据用于同比，结合窗口排序实现精准对齐。

第四章：典型面试真题深度解析

4.1 连续登录问题：使用窗口函数识别用户行为周期

在用户行为分析中，识别连续登录周期是衡量活跃度的关键。通过SQL窗口函数，可以高效计算用户连续登录的起止时间。

核心思路：日期差与分组识别

利用用户每日登录记录，将登录日期与行号做差，相同“日期差”值构成同一连续周期。

SELECT 
    user_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(*) AS consecutive_days
FROM (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() 
            OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
    FROM user_logins
) t
GROUP BY user_id, grp
HAVING consecutive_days >= 3;

上述代码中， ROW_NUMBER() 按用户和登录日期排序生成序列， DATE_SUB 计算“理论起始日”。连续登录时，该差值恒定，从而实现分组聚合。此方法可精准识别≥3天的连续行为周期，为留存分析提供数据支撑。

4.2 Top-N 每组记录查询：结合PARTITION BY与排序函数

在处理分组内排名问题时，常需获取每组前N条记录。通过窗口函数结合 PARTITION BY 与排序函数可高效实现。

核心函数应用

常用排序函数包括 ROW_NUMBER()、 RANK() 和 DENSE_RANK()，其中 ROW_NUMBER() 确保每行唯一序号，适合严格取Top-N场景。

SELECT *
FROM (
    SELECT 
        category, 
        product, 
        price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
) ranked
WHERE rn <= 3;

上述语句按类别分组，组内按价格降序编号，外层筛选每组前三。PARTITION BY 实现分组独立排序，ORDER BY 控制组内顺序。

性能优化建议

• 为排序字段建立索引以提升窗口函数效率
• 避免在大结果集上使用复杂排序逻辑
• 合理选择排序函数类型，防止重复排名导致数据溢出

4.3 分组最新数据提取：ROW_NUMBER经典应用案例

在处理时间序列数据时，常需从每组记录中提取最新的状态。`ROW_NUMBER()` 窗口函数为此类场景提供了高效解决方案。

核心逻辑解析

通过按分组字段分区并按时间降序排序，为每条记录分配行号，筛选行号为1的记录即可获取每组最新数据。

SELECT 
    user_id, 
    login_time, 
    ip_address
FROM (
    SELECT 
        user_id, 
        login_time, 
        ip_address,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY login_time DESC
        ) AS rn
    FROM user_logins
) t
WHERE rn = 1;

上述查询中，`PARTITION BY user_id` 将数据按用户分组，`ORDER BY login_time DESC` 确保最新登录排在首位，外层过滤 `rn = 1` 提取唯一最新记录。

性能优化建议

• 在分组和排序字段上建立复合索引
• 避免在子查询中使用复杂计算
• 结合分区表提升大规模数据处理效率

4.4 滑动平均与累计求和：时间序列数据分析实战

在处理时间序列数据时，滑动平均和累计求和是两种核心的平滑技术，能够有效揭示趋势并抑制噪声。

滑动平均的应用

滑动平均通过计算窗口内数据的均值来平滑短期波动。以下为Python实现示例：

import pandas as pd

# 模拟时间序列数据
data = pd.Series([10, 12, 14, 13, 15, 18, 20, 19])
window_size = 3

# 计算滑动平均
rolling_mean = data.rolling(window=window_size).mean()
print(rolling_mean)

该代码使用 pandas 的 rolling() 方法，设定窗口大小为3，逐窗计算均值，适用于趋势识别。

累计求和的趋势追踪

累计求和反映数据的累积效应，常用于监控总量增长：

• 适用于销售、访问量等累加型指标
• 能快速识别增长拐点
• 配合滑动平均可增强分析鲁棒性

第五章：总结与高阶学习路径建议

构建可扩展的微服务架构

在现代云原生系统中，掌握微服务设计模式至关重要。例如，使用 Go 实现基于 gRPC 的服务间通信时，可结合中间件实现熔断与限流：

func RateLimit(next grpc.UnaryServerInterceptor) grpc.UnaryServerInterceptor {
    limiter := rate.NewLimiter(10, 50) // 每秒10个请求，突发50
    return func(ctx context.Context, req interface{}, info *grpc.UnaryServerInfo, handler grpc.UnaryHandler) (interface{}, error) {
        if !limiter.Allow() {
            return nil, status.Errorf(codes.ResourceExhausted, "rate limit exceeded")
        }
        return handler(ctx, req)
    }
}

深入性能调优实战

生产环境中，Go 程序的 pprof 分析是必备技能。通过以下步骤可快速定位内存瓶颈：

1. 启用 HTTP Profiler：import _ "net/http/pprof"
2. 访问 /debug/pprof/heap 获取堆快照
3. 使用 go tool pprof 分析调用树
4. 识别高频分配对象并优化结构体对齐

推荐的学习路线图

阶段	核心技术栈	实践项目建议
中级进阶	context、sync.Pool、unsafe	实现轻量级协程池
高级架构	Kubernetes Operator SDK	开发自定义CRD控制器

[客户端] → [API网关] → [认证服务] ↘ [订单服务] → [消息队列] → [库存服务]