SQL窗口函数是一种能在数据行的“动态窗口”内计算的高级函数，无需像GROUP BY那样折叠查询结果，可保留原始数据结构。SQL窗口函数能高效实现累计统计、排名对比、前后数据关联等复杂分析，广泛应用在报表生成（如：部门薪资占比）、趋势分析（如：7日销售额均值）、数据分段（如：学生成绩排名）等应用场景，大幅简化传统多表关联或子查询的复杂逻辑，提升数据分析效率与可读性。（所有代码块与表格均可左右滚动）

一、窗口函数语法结构模板

函数名([参数])  -- 核心计算函数，如：SUM(salary)、RANK()
OVER (
    -- 可选：按指定列分组，组内独立计算（类似GROUP BY但不折叠行）
    [PARTITION BY 分组列1, 分组列2, ...]  
    -- 可选：指定分组内数据的排序规则，影响窗口范围和排名结果
    [ORDER BY 排序列1 [ASC/DESC], 排序列2 [ASC/DESC], ...]  
    -- 可选：定义窗口的具体范围（物理行数或值范围），默认随排序动态变化
    [ROWS/RANGE 窗口起始位置 AND 窗口结束位置]  
) AS 结果列别名  -- 给窗口函数计算结果命名，便于后续引用

二、窗口函数语法元素详解

1、函数名([参数])：核心计算逻辑载体，不同函数实现不同需求，如：SUM()用于求和，LAG()用于获取前N行数据，无参数函数（如：ROW_NUMBER()）可省略括号。
2、OVER()子句：窗口函数的标志，用于定义“计算窗口”，所有窗口相关的配置（分区、排序、范围）均在此括号内声明，无配置时表示对全表数据计算。
3、PARTITION BY：数据分组依据，将全表数据按指定列拆分为多个独立分区，窗口函数仅在分区内计算。例如：PARTITION BY department表示按“部门”分组，分别计算每个部门的统计结果。
4、ORDER BY：分区内数据的排序规则，决定窗口内数据的顺序，影响排名函数的序号生成、取值函数的前后位置判断。例如：ORDER BY salary DESC表示在分区内按“薪资”降序排列。
5、ROWS/RANGE：窗口范围定义，控制计算时包含的行。ROWS基于物理行数（如：6 PRECEDING表示前6行），RANGE基于排序列的值范围（如：5 PRECEDING表示排序列值比当前行小5的范围）；常见范围值包括UNBOUNDED PRECEDING（分区起始行）、CURRENT ROW（当前行）、UNBOUNDED FOLLOWING（分区结束行）。

三、窗口函数的常见类型

1、聚合类窗口函数

对窗口内的数据进行统计聚合，保留原始行数据，不折叠结果。

核心函数：SUM()（求和）、AVG()（平均值）、COUNT()（计数）、MAX()（最大值）、MIN()（最小值）

示例：计算各部门员工薪资累计值与薪资占比

SELECT
    department,  -- 部门
    employee,    -- 员工姓名
    salary,      -- 员工薪资
    -- 按部门分组，按入职日期排序，累计部门薪资
    SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS cum_dept_salary,
    -- 按部门分组，计算个人薪资占部门总薪资的百分比
    ROUND(salary / SUM(salary) OVER (PARTITION BY department) * 100, 2) AS salary_percent
FROM employees;

输出片段：

department	employee	salary	cum_dept_salary	salary_percent
IT	Alice	7000	7000	38.89
IT	Bob	9000	16000	50.00

2、排名类窗口函数

为分区内的行生成排名序号，处理并列排名场景。

核心函数：

• ROW_NUMBER()：生成连续唯一序号（无并列，如：1,2,3）
• RANK()：并列时跳过后续序号（如：1,1,3）
• DENSE_RANK()：并列时不跳号（如：1,1,2）

示例：对各销售区域销售额排名

SELECT
    region,  -- 销售区域
    sales,   -- 销售额
    ROW_NUMBER() OVER (ORDER BY sales DESC) AS rn,  -- 唯一排名
    RANK() OVER (ORDER BY sales DESC) AS rk,        -- 并列跳号排名
    DENSE_RANK() OVER (ORDER BY sales DESC) AS dr   -- 并列不跳号排名
FROM regional_sales;

输出片段：

region	sales	rn	rk	dr
North	50000	1	1	1
South	50000	2	1	1
East	40000	3	3	2

3、取值类窗口函数

获取窗口内其他行的数据，实现前后数据关联。

核心函数：

• LAG(列, n)：取当前行前n行的指定列值，n默认1
• LEAD(列, n)：取当前行后n行的指定列值，n默认1
• FIRST_VALUE(列)：取窗口内第一行的指定列值
• LAST_VALUE(列)：取窗口内最后一行的指定列值

示例：计算月度销售额环比增长

SELECT
    month,  -- 月份
    revenue,  -- 当月销售额
    -- 获取上月销售额，无上月数据时显示0
    COALESCE(LAG(revenue, 1) OVER (ORDER BY month), 0) AS prev_month_revenue,
    -- 计算环比增长额
    revenue - COALESCE(LAG(revenue, 1) OVER (ORDER BY month), 0) AS mom_growth
FROM monthly_sales;

输出片段：

month	revenue	prev_month_revenue	mom_growth
2023-01	100000	0	100000
2023-02	120000	100000	20000

4、分布类窗口函数

计算数据在整体中的分布位置，用于分段分析。

核心函数：

• PERCENT_RANK()：计算百分比排名，公式为(当前排名-1)/(总行数-1)，范围[0,1]
• CUME_DIST()：计算累计分布，公式为≤当前值的行数/总行数，范围(0,1]

示例：分析学生考试成绩分布

SELECT
    student_id,  -- 学生ID
    score,       -- 考试分数
    -- 百分比排名（保留2位小数）
    ROUND(PERCENT_RANK() OVER (ORDER BY score), 2) AS score_percentile,
    -- 累计分布（保留2位小数）
    ROUND(CUME_DIST() OVER (ORDER BY score), 2) AS score_cum_dist
FROM exam_scores;

输出片段：

student_id	score	score_percentile	score_cum_dist
S01	85	0.00	0.33
S02	90	0.50	0.67
S03	95	1.00	1.00

5、分桶类窗口函数

将窗口内的数据均匀分配到指定数量的“桶”中，实现数据分组。

核心函数：NTILE(n)，n为桶的数量，若数据无法均分，前几个桶会多1行数据

示例：将销售区域按销售额分为4个等级（桶）

SELECT
    region,  -- 销售区域
    sales,   -- 销售额
    -- 按销售额降序，分4个桶
    NTILE(4) OVER (ORDER BY sales DESC) AS sales_quartile
FROM regional_sales;

输出片段：

region	sales	sales_quartile
North	50000	1
South	50000	1
East	40000	2
West	30000	2

四、窗口函数的高级应用与复合应用示例

1、多级分区计算

在多个维度下分组计算，如：“部门+职级”双维度的薪资排名。

SELECT
    department,  -- 部门
    job_level,   -- 职级
    employee,    -- 员工姓名
    salary,      -- 薪资
    -- 按部门和职级双分组，按薪资降序排名
    DENSE_RANK() OVER (
        PARTITION BY department, job_level
        ORDER BY salary DESC
    ) AS dept_level_sal_rank
FROM employees;

输出片段：

department	job_level	employee	salary	dept_level_sal_rank
IT	L3	Alice	9000	1
IT	L3	Bob	8500	2
HR	L2	Carol	7500	1

2、滑动窗口分析

动态调整窗口范围，如：计算7日销售额移动平均（消除短期波动）。

SELECT
    sale_date,  -- 销售日期
    amount,     -- 当日销售额
    -- 按日期排序，窗口范围为“前6行到当前行”（共7天）
    ROUND(AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS rolling_avg_7day
FROM sales;

输出片段：

sale_date	amount	rolling_avg_7day
2023-01-01	15000	15000.00
2023-01-02	18000	16500.00
...	...	...
2023-01-07	20000	17500.00

3、缺失值填充

利用前后行数据填补缺失值，如：补齐天气表中的温度缺失数据。

SELECT
    date,  -- 日期
    temperature,  -- 原始温度（含NULL）
    -- 若温度为NULL，用前1行的温度填充；仍为NULL则用后1行
    COALESCE(
        temperature,
        LAG(temperature, 1) OVER (ORDER BY date),
        LEAD(temperature, 1) OVER (ORDER BY date)
    ) AS filled_temperature
FROM weather;

输出片段：

date	temperature	filled_temperature
2023-01-01	15	15
2023-01-02	NULL	15
2023-01-03	18	18

五、窗口函数跨数据库语法兼容性参考

函数/特性	PostgreSQL	MySQL（8.0+）	SQL Server（2012+）
RANGE 子句	✅支持	✅支持	✅支持
GROUPS 子句	✅支持	❌不支持	❌不支持
NTILE(n)	✅支持	✅支持	✅支持
NTH_VALUE(列,n)	✅支持	✅支持	✅支持

MySQL示例（需8.0及以上版本，低版本不支持窗口函数）：

SELECT
    year,  -- 年份
    product,  -- 产品
    -- 按产品分组，按年份排序，累计产品销售额
    SUM(sales) OVER (
        PARTITION BY product
        ORDER BY year
        ROWS UNBOUNDED PRECEDING
    ) AS cum_product_sales
FROM product_sales;

六、窗口函数的常见误区与避坑指南

误区1：使用ROW_NUMBER()时未指定唯一排序字段，导致排名结果随机。

• 错误代码：ROW_NUMBER() OVER (ORDER BY sale_date)（若sale_date有重复，排名会随机）
• 避坑：添加唯一列（如：订单ID）确保排序唯一，修正代码：ROW_NUMBER() OVER (ORDER BY sale_date, order_id)

误区2：混淆ROWS与RANGE的范围逻辑，导致计算结果不符预期。

• 错误场景：用RANGE计算“前5行平均值”（RANGE BETWEEN 5 PRECEDING AND CURRENT ROW），实际会按排序列值范围计算，而非物理行数。
• 避坑：计算物理行数范围用ROWS，计算值范围用RANGE，修正代码：AVG(price) OVER (ORDER BY price ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)

误区3：忽略ORDER BY对窗口范围的影响，默认窗口范围为“分区起始到当前行”。更准确的表述是：当OVER()中无ORDER BY时，窗口函数默认范围是 “整个分区”（聚合类函数直接返回分区总结果）；仅当指定ORDER BY后，默认范围才是 “分区起始到当前行”，例如：SUM(salary) OVER (PARTITION BY dept)无ORDER BY，返回部门总薪资；SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date)默认返回累计值。

• 错误场景：想计算分区内全量数据的平均值，却因未指定范围导致结果为累计值。
• 避坑：需显式指定全量范围，代码：AVG(salary) OVER (PARTITION BY department ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

七、窗口函数的性能优化技巧

1、创建组合索引：在PARTITION BY和ORDER BY的列上创建组合索引，减少排序和分组的计算耗时。例如：CREATE INDEX idx_dept_sal ON employees(department, salary DESC);
2、剪裁窗口范围：避免使用UNBOUNDED PRECEDING（全表扫描），仅保留必要的窗口范围。例如：计算“近30天销售额”，用ROWS BETWEEN 29 PRECEDING AND CURRENT ROW而非全量范围。
3、拆分复杂查询：将多窗口函数的复杂查询拆分为CTE（公共表表达式）分步处理，降低单次查询的计算压力。示例：

WITH dept_sal AS (
    -- 第一步：计算部门总薪资
    SELECT department, SUM(salary) AS dept_total_sal FROM employees GROUP BY department
)
-- 第二步：关联原表计算薪资占比（避免重复聚合）
SELECT
    e.department, e.employee, e.salary,
    ROUND(e.salary / d.dept_total_sal * 100, 2) AS salary_percent
FROM employees e
JOIN dept_sal d ON e.department = d.department;

八、窗口函数典型面试题及参考答案

面试题1：用SQL计算每个员工的薪资在其部门内的排名（需处理并列，不跳号），并显示部门内薪资最高的员工姓名。

参考答案：

WITH dept_sal_rank AS (
    -- 第一步：计算每个员工的部门内薪资排名（不跳号）
    SELECT
        department,
        employee,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS sal_rank
    FROM employees
),
dept_top_sal AS (
    -- 第二步：获取每个部门的最高薪资
    SELECT
        department,
        MAX(salary) AS top_sal
    FROM employees
    GROUP BY department
),
dept_top_emp AS (
    -- 第三步：获取每个部门薪资最高的员工姓名（可能多个）
    SELECT
        dts.department,
        GROUP_CONCAT(dsr.employee SEPARATOR ', ') AS top_employees  -- 用GROUP_CONCAT拼接多个最高薪资员工姓名
    FROM dept_top_sal dts
    JOIN dept_sal_rank dsr ON dts.department = dsr.department 
                          AND dts.top_sal = dsr.salary
    GROUP BY dts.department
)
-- 第四步：关联结果，显示员工排名与部门最高薪资员工
SELECT
    dsr.department,
    dsr.employee,
    dsr.salary,
    dsr.sal_rank,
    dte.top_employees AS dept_top_sal_employees
FROM dept_sal_rank dsr
JOIN dept_top_emp dte ON dsr.department = dte.department
ORDER BY dsr.department, dsr.sal_rank;

输出逻辑：先通过DENSE_RANK()实现部门内不跳号的薪资排名，再通过子查询获取各部门最高薪资及对应员工，最后关联展示完整信息，解决“并列排名”和“部门最高员工识别”双重需求。

面试题2：计算每月销售额的3个月滑动平均（即当月+前2个月的平均值），并对比当月销售额与滑动平均的差异。
参考答案：

SELECT
    month,  -- 月份
    revenue AS monthly_revenue,  -- 当月销售额
    -- 滑动窗口：当前行+前2行（共3个月），按月份排序确保顺序正确
    ROUND(AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 窗口范围：前2个月到当月
    ), 2) AS rolling_avg_3month,
    -- 计算当月销售额与滑动平均的差异（保留2位小数）
    ROUND(revenue - AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS revenue_diff_from_avg
FROM monthly_sales
ORDER BY month;

说明：使用ROWS BETWEEN 2 PRECEDING AND CURRENT ROW明确3个月的物理窗口范围，避免RANGE因“月份值间隔不规律”导致的计算偏差；通过ORDER BY month确保窗口内数据按时间顺序排列，滑动平均结果符合业务逻辑。

输出（片段）：

month	monthly_revenue	rolling_avg_3month	revenue_diff_from_avg
2023-01	100000	100000.00	0.00
2023-02	120000	110000.00	10000.00
2023-03	130000	116666.67	13333.33
2023-04	110000	120000.00	-10000.00

面试题3：如何用窗口函数填充表中的缺失数据？请以“补齐用户连续登录记录中的缺失日期，并用前一次登录的设备信息填充”为例说明。

参考答案：

-- 步骤1：生成连续日期（假设需补齐2023-01-01至2023-01-10的登录记录）
WITH date_series AS (
    SELECT DATE_ADD('2023-01-01', INTERVAL num DAY) AS login_date
    FROM (
        SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
        SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
        SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS nums
),
-- 步骤2：关联用户登录表，获取原始数据（含缺失日期）
user_login_raw AS (
    SELECT
        ds.login_date,
        ul.user_id,
        ul.device_type  -- 登录设备（可能为NULL，即日期缺失）
    FROM date_series ds
    CROSS JOIN (SELECT DISTINCT user_id FROM user_logins) ul  -- 确保每个用户都有连续日期
    LEFT JOIN user_logins ul ON ds.login_date = ul.login_date 
                           AND ds.user_id = ul.user_id  -- 关联原始登录记录
)
-- 步骤3：用LAG()填充缺失的设备信息（取最近一次非NULL的设备值）
SELECT
    login_date,
    user_id,
    -- 若当前设备为NULL，取前一次非NULL的设备值；窗口范围为用户的所有历史记录
    LAST_VALUE(device_type) OVER (
        PARTITION BY user_id
        ORDER BY login_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 从用户第一条记录到当前行
        -- 注：部分数据库支持IGNORE NULLS，如：PostgreSQL；MySQL需用子查询辅助，此处简化示例
    ) AS filled_device_type
FROM user_login_raw
ORDER BY user_id, login_date;

避坑点：直接用LAG(device_type)仅能取前1行数据，若连续多日缺失会失效；通过LAST_VALUE()结合“全范围窗口”，可获取当前行之前最近一次非NULL的设备信息，更符合“连续缺失填充”需求（注意：MySQL无IGNORE NULLS时，可通过子查询标记非NULL值位置，再用窗口函数定位填充）。

输出（片段）（用户ID=U001）：

login_date	user_id	filled_device_type
2023-01-01	U001	iOS
2023-01-02	U001	iOS
2023-01-03	U001	Android
2023-01-04	U001	Android

面试题4：现有员工表（employees）含部门（department）、入职日期（hire_date）、薪资（salary），请计算每个部门内“入职时间最早的3名员工”的平均薪资。

参考答案：

WITH dept_emp_hire_rank AS (
    -- 第一步：按部门分组，按入职日期升序排名（最早入职为1）
    SELECT
        department,
        employee,
        hire_date,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY hire_date ASC  -- 入职早的排名靠前
        ) AS hire_rank
    FROM employees
),
-- 第二步：筛选每个部门入职最早的3名员工
dept_top3_early_hire AS (
    SELECT
        department,
        salary
    FROM dept_emp_hire_rank
    WHERE hire_rank <= 3  -- 保留排名前3的员工
)
-- 第三步：计算每个部门前3早入职员工的平均薪资
SELECT
    department,
    ROUND(AVG(salary), 2) AS avg_salary_top3_early_hire  -- 平均薪资保留2位小数
FROM dept_top3_early_hire
GROUP BY department
ORDER BY department;

说明：先用ROW_NUMBER()按“部门+入职日期”排序，确保每个部门内入职时间唯一排序（避免并列导致人数超3）；再筛选排名前3的员工，最后按部门聚合计算平均薪资，精准匹配“入职最早3人”的业务需求。

输出（片段）：

department	avg_salary_top3_early_hire
IT	8200.00
HR	6800.00
Sales	7500.00

面试题5：对比GROUP BY与窗口函数的核心差异，并用同一需求（计算部门总薪资）分别写出两种实现方式。

参考答案： 1、核心差异对比

对比维度	GROUP BY	窗口函数（如：SUM() OVER (PARTITION BY)）
结果行数	每组仅返回1行聚合结果	保留原始所有行，每行附带聚合结果
数据折叠	会折叠组内原始数据	不折叠原始数据，聚合结果作为新增列
多维度计算支持	单次查询仅支持1组聚合逻辑	可同时实现多维度聚合（如：同一行显示部门总薪资+公司总薪资）

2、实现方式对比（需求：计算每个员工薪资及所属部门的总薪资）

方式1：GROUP BY + 关联查询

-- 第一步：用GROUP BY计算部门总薪资
WITH dept_total_sal AS (
    SELECT
        department,
        SUM(salary) AS dept_total
    FROM employees
    GROUP BY department
)
-- 第二步：关联原表获取员工个人薪资与部门总薪资
SELECT
    e.department,
    e.employee,
    e.salary,
    dts.dept_total
FROM employees e
JOIN dept_total_sal dts ON e.department = dts.department
ORDER BY e.department, e.salary DESC;

方式2：窗口函数（PARTITION BY）

SELECT
    department,
    employee,
    salary,
    -- 按部门分组计算总薪资，直接作为新增列返回
    SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees
ORDER BY department, salary DESC;

结果一致性：两种方式最终都会返回“员工个人薪资+部门总薪资”，但窗口函数无需额外子查询和关联，代码更简洁；当需同时展示原始数据与聚合结果时，窗口函数效率更高。

面试题6：用窗口函数计算“每个用户连续2个月及以上销售额下降的时间段”（用户表：user_sales，含user_id、month、revenue）。

参考答案：

WITH user_sales_diff AS (
    -- 第一步：计算每个用户当月与上月销售额的差异（下降标记为1，否则为0）
    SELECT
        user_id,
        month,
        revenue,
        -- 获取上月销售额
        LAG(revenue, 1) OVER (
            PARTITION BY user_id
            ORDER BY month
        ) AS prev_month_revenue,
        -- 标记是否下降：当月销售额 < 上月则为1，否则为0
        CASE 
            WHEN revenue < LAG(revenue, 1) OVER (PARTITION BY user_id ORDER BY month) 
            THEN 1 
            ELSE 0 
        END AS is_decline
    FROM user_sales
),
-- 第二步：对连续下降的记录分配同一“下降组ID”（中断时重置）
user_decline_group AS (
    SELECT
        user_id,
        month,
        revenue,
        is_decline,
        -- 用累计求和生成组ID：非下降时累计值不变，下降时累计值+1（实现连续分组）
        SUM(CASE WHEN is_decline = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY user_id
            ORDER BY month
            ROWS UNBOUNDED PRECEDING
        ) AS decline_group_id
    FROM user_sales_diff
    WHERE prev_month_revenue IS NOT NULL  -- 排除无上月数据的首月
),
-- 第三步：筛选连续2个月及以上下降的组（组内记录数≥2）
user_continuous_decline AS (
    SELECT
        user_id,
        decline_group_id,
        MIN(month) AS start_decline_month,  -- 连续下降起始月
        MAX(month) AS end_decline_month,    -- 连续下降结束月
        COUNT(*) AS continuous_months       -- 连续下降月数
    FROM user_decline_group
    WHERE is_decline = 1  -- 仅保留下降记录
    GROUP BY user_id, decline_group_id
    HAVING COUNT(*) >= 2  -- 筛选连续2个月及以上的下降
)
-- 第四步：关联原始销售额，展示完整下降时间段信息
SELECT
    ucd.user_id,
    ucd.start_decline_month,
    ucd.end_decline_month,
    ucd.continuous_months,
    -- 获取起始月和结束月的销售额
    us_start.revenue AS start_month_revenue,
    us_end.revenue AS end_month_revenue
FROM user_continuous_decline ucd
JOIN user_sales us_start 
    ON ucd.user_id = us_start.user_id 
    AND ucd.start_decline_month = us_start.month
JOIN user_sales us_end 
    ON ucd.user_id = us_end.user_id 
    AND ucd.end_decline_month = us_end.month
ORDER BY ucd.user_id, ucd.start_decline_month;

思路：通过LAG()计算销售额环比差异，用SUM()窗口函数实现“连续下降分组”（同一连续下降段分配相同组ID），最后筛选组内记录数≥2的分组，精准定位连续下降时间段，解决“连续趋势识别”的典型分析需求。

输出（片段）（用户ID=U002）：

user_id	start_decline_month	end_decline_month	continuous_months	start_month_revenue	end_month_revenue
U002	2023-03	2023-04	2	90000	85000
U002	2023-06	2023-08	3	82000	75000

面试题7：现有订单表（orders）含订单ID（order_id）、用户ID（user_id）、订单金额（amount）、下单时间（order_time），请计算“每个用户的第3笔订单金额”以及“该用户前3笔订单的平均金额”。

参考答案：

WITH user_order_rank AS (
    -- 第一步：按用户分组，按下单时间升序给订单排名（第1笔为1，第3笔为3）
    SELECT
        user_id,
        order_id,
        amount,
        order_time,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY order_time ASC  -- 按下单时间确定订单顺序
        ) AS order_rank
    FROM orders
),
-- 第二步：计算每个用户前3笔订单的平均金额（无论是否有第3笔）
user_top3_avg AS (
    SELECT
        user_id,
        ROUND(AVG(amount), 2) AS avg_top3_amount  -- 前3笔订单平均金额
    FROM user_order_rank
    WHERE order_rank <= 3  -- 仅统计前3笔订单
    GROUP BY user_id
),
-- 第三步：提取每个用户的第3笔订单金额（无第3笔则为NULL）
user_third_order AS (
    SELECT
        user_id,
        amount AS third_order_amount  -- 第3笔订单金额
    FROM user_order_rank
    WHERE order_rank = 3
)
-- 第四步：关联结果，展示用户第3笔订单金额与前3笔平均金额
SELECT
    COALESCE(uto.user_id, uta.user_id) AS user_id,  -- 确保所有用户都显示
    uto.third_order_amount,
    uta.avg_top3_amount
FROM user_third_order uto
FULL JOIN user_top3_avg uta ON uto.user_id = uta.user_id
ORDER BY user_id;

细节：用ROW_NUMBER()确保每个用户的订单按时间唯一排序（避免同一时间多笔订单导致排名混乱）；通过FULL JOIN关联“第3笔订单”和“前3笔平均”结果，确保“无第3笔订单的用户”也能显示前3笔平均金额（若前3笔不足，按实际订单数计算平均）。

输出（片段）：

user_id	third_order_amount	avg_top3_amount
U001	280.00	250.00
U002	NULL	190.00
U003	450.00	380.00

面试题8：解释ROWS与RANGE的区别，并分别用两者实现“计算薪资不低于当前员工薪资10%范围内的平均薪资”。

参考答案：
1、ROWS与RANGE核心区别

对比维度	ROWS	RANGE
范围定义依据	物理行数（如：“前2行到当前行”）	排序列的数值范围（如：“薪资±10%”）
适用场景	固定行数的滑动计算（如：7日均值）	基于数值波动的范围计算（如：薪资区间统计）
结果一致性	行数固定，结果稳定	受排序列值分布影响，行数可能不固定

2、实现方式对比（需求：薪资±10%范围内的平均薪资）

前提：员工表（employees）含employee（姓名）、salary（薪资），需按薪资排序后计算范围平均。

方式1：用RANGE实现（推荐，精准匹配“数值范围”需求）

SELECT
    employee,
    salary,
    -- 排序列为salary，范围为“当前薪资*0.9 到 当前薪资*1.1”
    ROUND(AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 
            (salary * 0.9) PRECEDING  -- 下限：当前薪资的90%
            AND (salary * 1.1) FOLLOWING  -- 上限：当前薪资的110%
    ), 2) AS avg_salary_10pct_range
FROM employees
ORDER BY salary;

方式2：用ROWS实现（需先确定行数，仅适用于薪资连续且均匀的场景）

-- 注：此方式需先确认“薪资±10%对应固定行数”，实际业务中不推荐，仅作对比
WITH sal_sorted AS (
    -- 第一步：按薪资排序，给每行分配行号
    SELECT
        employee,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS sal_row_num
    FROM employees
)
SELECT
    employee,
    salary,
    -- 假设薪资±10%对应“前1行到后1行”（需根据实际数据调整行数）
    ROUND(AVG(salary) OVER (
        ORDER BY salary
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ), 2) AS avg_salary_10pct_rows
FROM sal_sorted
ORDER BY salary;

结果差异说明：若员工薪资分布不均（如：部分员工薪资差距大），ROWS方式会包含薪资超出±10%的行，导致结果偏差；RANGE方式直接基于薪资数值范围计算，结果更精准，符合业务需求。

修正：RANGE的PRECEDING/FOLLOWING仅支持常量（如：100 PRECEDING），不支持动态值（如：salary * 0.9），需先计算当前行薪资的 ±10% 阈值，再用RANGE结合子查询实现，例如：

WITH sal_threshold AS (
    SELECT employee, salary,
           salary * 0.9 AS lower_bound,
           salary * 1.1 AS upper_bound
    FROM employees
)
SELECT employee, salary,
       AVG(salary) OVER (
           ORDER BY salary
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
           -- 结合WHERE筛选范围（实际需用子查询关联）
       ) AS avg_salary_10pct_range
FROM sal_threshold;

说明：RANGE的动态值范围计算在SQL中需间接实现，直接写salary * 0.9会报错，需特别注意。

面试题9：用窗口函数实现“按季度统计每个产品的销售额，并对比当季销售额与上季度销售额的增长率”。

参考答案：

WITH product_quarterly_sales AS (
    -- 第一步：按产品和季度分组，计算季度销售额
    SELECT
        product_id,
        product_name,
        -- 提取年份和季度（如：2023Q1）
        CONCAT(YEAR(order_time), 'Q', QUARTER(order_time)) AS sale_quarter,
        SUM(amount) AS quarterly_revenue  -- 季度销售额
    FROM orders
    JOIN products ON orders.product_id = products.product_id
    GROUP BY product_id, product_name, YEAR(order_time), QUARTER(order_time)
)
-- 第二步：计算上季度销售额及增长率
SELECT
    product_id,
    product_name,
    sale_quarter,
    quarterly_revenue,
    -- 获取上季度销售额（按产品和季度排序）
    LAG(quarterly_revenue, 1) OVER (
        PARTITION BY product_id
        ORDER BY sale_quarter  -- 按季度排序，确保时间顺序正确
    ) AS prev_quarter_revenue,
    -- 计算增长率（保留2位小数，无上限时显示NULL）
    ROUND(
        (quarterly_revenue - LAG(quarterly_revenue, 1) OVER (
            PARTITION BY product_id ORDER BY sale_quarter
        )) / LAG(quarterly_revenue, 1) OVER (
            PARTITION BY product_id ORDER BY sale_quarter
        ) * 100,
        2
    ) AS revenue_growth_rate
FROM product_quarterly_sales
ORDER BY product_id, sale_quarter;

技巧：先通过YEAR()和QUARTER()函数聚合季度销售额，生成“产品-季度”维度的基础数据；再用LAG()按产品分组、按季度排序，获取上季度销售额，最终通过增长率公式计算同比变化，满足“季度对比”的业务分析需求。

输出（片段）（产品ID=P001）：

product_id	product_name	sale_quarter	quarterly_revenue	prev_quarter_revenue	revenue_growth_rate
P001	智能手机	2023Q1	500000	NULL	NULL
P001	智能手机	2023Q2	620000	500000	24.00
P001	智能手机	2023Q3	589000	620000	-5.00

面试题10：现有学生成绩表（exam_scores）含学生ID（student_id）、科目（subject）、分数（score），请找出“每科分数排名前20%的学生”，并标注其所在科目和排名。

参考答案：

SELECT
    student_id,
    subject,
    score,
    -- 计算百分比排名（(当前排名-1)/(总行数-1)），范围[0,1]
    ROUND(PERCENT_RANK() OVER (
        PARTITION BY subject
        ORDER BY score DESC  -- 分数降序，排名1为最高分
    ), 4) AS score_percent_rank,
    -- 标注是否为前20%（百分比排名≤0.2）
    CASE 
        WHEN PERCENT_RANK() OVER (
            PARTITION BY subject
            ORDER BY score DESC
        ) <= 0.2 
        THEN '是'
        ELSE '否'
    END AS is_top20_percent
FROM exam_scores
-- 筛选前20%的学生（也可在WHERE中过滤，此处用CASE标注更直观）
WHERE PERCENT_RANK() OVER (
    PARTITION BY subject
    ORDER BY score DESC
) <= 0.2
ORDER BY subject, score DESC;

说明：原真题WHERE PERCENT_RANK() OVER (...) <= 0.2窗口函数不能在 WHERE中直接使用，应改为（示例）：

SELECT * FROM (
  SELECT ...,
    PERCENT_RANK() OVER (...) AS pct_rank
  FROM exam_scores
) t WHERE pct_rank <= 0.2

函数选择理由：PERCENT_RANK()能直接计算学生成绩在科目内的相对排名比例，相比CUME_DIST()（累计分布），其“前20%”的判定更精准（如：科目有50人，前10人恰好为20%，PERCENT_RANK()≤0.2可准确筛选）；若用NTILE(5)分5个桶取第1桶，当人数无法均分（如：51人）时，第1桶会多1人，导致结果偏差。

输出片段（科目=数学）：

student_id	subject	score	score_percent_rank	is_top20_percent
S005	数学	98	0.0000	是
S012	数学	96	0.0204	是
...	...	...	...	...
S048	数学	85	0.1837	是
S033	数学	84	0.2041	否

面试题11：现有用户行为表（user_behavior）含用户ID（user_id）、行为类型（behavior_type，值为“浏览”、“加购”、“下单”）、行为时间（behavior_time），请计算“每个用户从‘加购’到‘下单’的平均间隔时间”（仅统计有完整加购-下单流程的用户）。

参考答案：

WITH user_cart AS (
    -- 第一步：提取用户所有“加购”行为及时间
    SELECT
        user_id,
        behavior_time AS cart_time,
        -- 给同一用户的加购行为按时间排序（避免多笔加购对应多笔下单的匹配混乱）
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY behavior_time ASC
        ) AS cart_rank
    FROM user_behavior
    WHERE behavior_type = '加购'
),
user_purchase AS (
    -- 第二步：提取用户所有“下单”行为及时间
    SELECT
        user_id,
        behavior_time AS purchase_time,
        -- 给同一用户的下单行为按时间排序
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY behavior_time ASC
        ) AS purchase_rank
    FROM user_behavior
    WHERE behavior_type = '下单'
),
-- 第三步：匹配加购与下单（按用户+排序序号一对一匹配，确保时间顺序）
cart_purchase_match AS (
    SELECT
        uc.user_id,
        uc.cart_time,
        up.purchase_time,
        -- 计算加购到下单的间隔时间（单位：分钟，可按需改为小时/天）
        TIMESTAMPDIFF(MINUTE, uc.cart_time, up.purchase_time) AS cart_to_purchase_minutes
    FROM user_cart uc
    JOIN user_purchase up 
        ON uc.user_id = up.user_id 
        AND uc.cart_rank = up.purchase_rank  -- 按序号匹配，确保“先加购后下单”
    WHERE uc.cart_time < up.purchase_time  -- 排除下单时间早于加购的异常数据
)
-- 第四步：计算每个用户的平均加购-下单间隔
SELECT
    user_id,
    COUNT(*) AS valid_cart_purchase_count,  -- 有效加购-下单次数
    ROUND(AVG(cart_to_purchase_minutes), 1) AS avg_cart_to_purchase_minutes  -- 平均间隔
FROM cart_purchase_match
GROUP BY user_id
ORDER BY avg_cart_to_purchase_minutes;

说明：通过ROW_NUMBER()给同一用户的加购、下单行为分别排序，实现“按时间顺序一对一匹配”（避免多笔加购与下单交叉匹配）；用TIMESTAMPDIFF()计算时间间隔，过滤异常数据后聚合平均，精准反映用户“加购到下单”的转化效率。

输出（片段）：

user_id	valid_cart_purchase_count	avg_cart_to_purchase_minutes
U001	3	45.2
U002	2	120.5
U003	5	30.8

面试题12：对比RANK()、DENSE_RANK()与ROW_NUMBER()的差异，并用同一组数据（学生成绩）展示三种函数的排名结果。

参考答案：
1、三种排名函数核心差异

函数	并列排名处理	序号连续性	适用场景
ROW_NUMBER()	不允许并列，强制生成唯一序号	连续（如：1,2,3,4）	需唯一排序的场景（如：订单编号排序）
RANK()	允许并列，但跳过后续序号	不连续（如：1,1,3,4）	需体现排名差距的场景（如：比赛排名）
DENSE_RANK()	允许并列，不跳过后续序号	连续（如：1,1,2,3）	需紧凑排名的场景（如：奖学金分级）

2、实例展示（学生成绩排名）
原始数据（学生成绩表exam_scores）：

student_id	score
S01	95
S02	92
S03	95
S04	88
S05	92

SQL实现与结果：

SELECT
    student_id,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
    RANK() OVER (ORDER BY score DESC) AS rk,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dr
FROM exam_scores
ORDER BY score DESC;

输出结果：

student_id	score	rn	rk	dr
S01	95	1	1	1
S03	95	2	1	1
S02	92	3	3	2
S05	92	4	3	2
S04	88	5	5	3

结果解读：S01和S03均为95分（并列第1），ROW_NUMBER()给两者分配1、2的唯一序号，RANK()跳过序号2直接到3，DENSE_RANK()则紧凑到2，清晰体现三种函数的逻辑差异。

面试题13：现有店铺销售表（store_sales）含店铺ID（store_id）、日期（sale_date）、销售额（amount），请计算“每个店铺每月的销售额同比增长率”（同比=（当月销售额-去年同月销售额）/去年同月销售额*100%）。

参考答案：

WITH store_monthly_sales AS (
    -- 第一步：按店铺和年月分组，计算月销售额
    SELECT
        store_id,
        YEAR(sale_date) AS sale_year,
        MONTH(sale_date) AS sale_month,
        SUM(amount) AS monthly_amount  -- 月销售额
    FROM store_sales
    GROUP BY store_id, YEAR(sale_date), MONTH(sale_date)
)
-- 第二步：计算同比增长率（获取去年同月销售额）
SELECT
    store_id,
    CONCAT(sale_year, '-', LPAD(sale_month, 2, '0')) AS sale_month,  -- 格式化年月（如：2023-05）
    monthly_amount,
    -- 获取去年同月销售额（按店铺+月份匹配，年份-1）
    LAG(monthly_amount, 12) OVER (
        PARTITION BY store_id
        ORDER BY sale_year, sale_month  -- 按年份+月份排序，确保时间顺序
    ) AS last_year_same_month_amount,
    -- 计算同比增长率（保留2位小数，无去年数据时显示NULL）
    ROUND(
        (monthly_amount - LAG(monthly_amount, 12) OVER (
            PARTITION BY store_id ORDER BY sale_year, sale_month
        )) / NULLIF(LAG(monthly_amount, 12) OVER (
            PARTITION BY store_id ORDER BY sale_year, sale_month
        ), 0) * 100,
        2
    ) AS yoy_growth_rate
FROM store_monthly_sales
ORDER BY store_id, sale_year, sale_month;

说明：NULLIF(LAG(...), 0)建议改为(monthly_amount - last_year) / NULLIF(last_year, 0) * 100，NULLIF应包裹分母避免除零错误。

技巧：用LAG(monthly_amount, 12)按店铺分组、按时间排序，直接获取“12行前”（即去年同月）的销售额；通过NULLIF()处理“去年销售额为0”的情况，避免除法报错；LPAD()函数格式化月份为两位数（如：5月→05），提升结果可读性。

输出片段（店铺ID=ST001）：

store_id	sale_month	monthly_amount	last_year_same_month_amount	yoy_growth_rate
ST001	2022-03	150000	NULL	NULL
ST001	2022-04	168000	NULL	NULL
...	...	...	...	...
ST001	2023-03	180000	150000	20.00
ST001	2023-04	193200	168000	15.00

面试题14：用窗口函数实现“查询每个部门薪资最高的2名员工，若有并列第2名则全部保留”。

参考答案：

WITH dept_sal_rank AS (
    -- 第一步：按部门分组，按薪资降序用DENSE_RANK()排名（并列不跳号）
    SELECT
        department,
        employee,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS sal_rank
    FROM employees
)
-- 第二步：筛选排名≤2的员工（保留并列第2名）
SELECT
    department,
    employee,
    salary,
    sal_rank
FROM dept_sal_rank
WHERE sal_rank <= 2
ORDER BY department, sal_rank, salary DESC;

函数选择理由：若用RANK()，当存在并列第1名时，会跳过第2名直接到第3名（如：薪资9000、9000、8500，排名为1、1、3），导致无法保留第2名；DENSE_RANK()会将8500排名为2，若有多个8500（如：8500、8500），则均排名2，满足“并列第2全部保留”的需求。

输出片段（部门=IT）：

department	employee	salary	sal_rank
IT	Alice	9500	1
IT	Bob	9000	2
IT	Charlie	9000	2
IT	Dave	8500	3

面试题15：现有物流表（logistics）含订单ID（order_id）、物流状态（status，值为“已发货”、“运输中”、“已签收”）、状态更新时间（update_time），请计算“每个订单从‘已发货’到‘已签收’的总运输时长”（仅统计已完成签收的订单）。

参考答案：

WITH order_logistics AS (
    -- 第一步：提取每个订单的“已发货”和“已签收”时间
    SELECT
        order_id,
        -- 获取“已发货”时间（FIRST_VALUE取状态排序后的首行，此处需先过滤状态）
        MAX(CASE WHEN status = '已发货' THEN update_time END) AS ship_time,
        -- 获取“已签收”时间
        MAX(CASE WHEN status = '已签收' THEN update_time END) AS sign_time
    FROM logistics
    -- 仅保留“已发货”和“已签收”的状态记录
    WHERE status IN ('已发货', '已签收')
    GROUP BY order_id
    -- 过滤已完成签收的订单（需同时有发货和签收时间）
    HAVING MAX(CASE WHEN status = '已发货' THEN update_time END) IS NOT NULL
       AND MAX(CASE WHEN status = '已签收' THEN update_time END) IS NOT NULL
)
-- 第二步：计算运输时长（单位：小时，按需调整）
SELECT
    order_id,
    ship_time,
    sign_time,
    TIMESTAMPDIFF(HOUR, ship_time, sign_time) AS transport_hours  -- 运输时长（小时）
FROM order_logistics
ORDER BY transport_hours DESC;

替代窗口函数实现（若需保留所有状态记录）：

WITH logistics_rank AS (
    SELECT
        order_id,
        status,
        update_time,
        -- 按订单分组，按更新时间排序
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY update_time ASC
        ) AS time_rank
    FROM logistics
)
SELECT
    lr1.order_id,
    lr1.update_time AS ship_time,
    lr2.update_time AS sign_time,
    TIMESTAMPDIFF(HOUR, lr1.update_time, lr2.update_time) AS transport_hours
FROM logistics_rank lr1
JOIN logistics_rank lr2 
    ON lr1.order_id = lr2.order_id 
    AND lr1.status = '已发货'
    AND lr2.status = '已签收'
ORDER BY lr1.order_id;

输出片段：

order_id	ship_time	sign_time	transport_hours
O001	2023-10-01 09:00:00	2023-10-02 14:30:00	29.5
O002	2023-10-01 11:00:00	2023-10-02 08:15:00	21.25

说明：HOUR单位仅返回整数部分，小数需通过分钟 / 秒换算，TIMESTAMPDIFF(HOUR, ...)返回整数（如：29小时30分钟会计算为29小时），若需小数精度，需用TIMESTAMPDIFF(MINUTE, ...)/60转换。

以上15道面试题覆盖了窗口函数的核心语法、分类应用、跨场景实战及与其他SQL功能的对比，可帮助我们全面掌握窗口函数在实际业务中的使用逻辑与避坑要点。

阅读原文：原文链接