以一道题为例讲清楚 窗口函数是什么，什么时候使用，怎么使用

在数据处理和分析中，我们经常会遇到类似 “给员工按薪水排名”、“计算每个产品的销售额占比” 这样的需求。这些需求如果只用 GROUP BY 和普通的聚合函数来实现，会非常复杂，甚至需要编写繁琐的子查询。而窗口函数的出现，正是为了解决这类问题。

场景引入：员工薪水排名

假设我们有一个 salaries 表，存储了员工的编号（emp_no）和他们的薪水（salary）。

salaries 表：

需求： 对所有员工的薪水按照 salary 降序进行排名，如果 salary 相同，再按照 emp_no 升序排列，并给出一个排名列 t_rank。

预期结果：

为什么普通 SQL 难以实现？

如果我们尝试用 ORDER BY，虽然可以将结果排序，但无法生成一个 t_rank 列来显示具体的名次。

如果我们尝试用 GROUP BY，它会将相同薪水的员工合并为一行，这显然不符合我们需要展示所有员工信息的要求。

这时，窗口函数就成了完美的解决方案。

一、什么是窗口函数？

窗口函数（Window Function）是一种特殊的 SQL 函数，它能在不折叠行（即不减少结果集行数）的前提下，对一组行（称为一个 “窗口”）进行聚合计算。

你可以把它想象成：

1. 数据库先按照你的要求对数据进行排序。
2. 然后，它会为每一行数据 “画一个框”（这个框就是 “窗口”）。
3. 最后，它会对这个 “框” 内的数据应用聚合函数（如排名、求和、求平均等），并将计算结果作为一个新列附加到该行。

核心特点： 窗口函数不会像 GROUP BY 那样改变原始表的行数，每一行都会保留，只是多了一个计算后的列。

二、什么时候使用窗口函数？

当你遇到以下场景时，就应该考虑使用窗口函数：

1. 排名与排序：需要为每一行数据提供一个基于特定规则的排名。
   • 示例：按销量给商品排名、按分数给学生排名。
2. 聚合分析：需要在保留所有行的同时，显示某列的总计、平均值、最大值等。
   • 示例：显示每个员工的薪水，以及他所在部门的平均薪水和公司的总薪水。
3. 移动计算：需要在一个滑动的窗口内进行计算。
   • 示例：计算股票的 5 日移动平均线、计算每个月的累计销售额。
4. 分组内的 Top N：需要从每个分组中找出排名靠前或靠后的几条记录。
   • 示例：找出每个部门薪水最高的 3 名员工。

我们今天的例子就属于第一种场景：排名与排序。

三、怎么使用窗口函数？（以本题为例）

窗口函数的基本语法如下：

<窗口函数> OVER (
    [PARTITION BY <列名>]
    [ORDER BY <列名> [ASC|DESC]]
)

• <窗口函数>：可以是排名函数（RANK, DENSE_RANK, ROW_NUMBER）、聚合函数（SUM, AVG, COUNT）等。
• OVER (...)：这是窗口函数的标志，定义了 “窗口” 的规则。
  • PARTITION BY <列名>（可选）：将数据按照指定的列进行分组，窗口函数将在每个分组内独立计算。本题不需要分组，所以省略。
  • ORDER BY <列名>（常用）：定义了窗口内数据的排序规则，这是排名函数的关键。

针对本题的具体实现

要实现我们的排名需求，最合适的窗口函数是 DENSE_RANK()。

• DENSE_RANK()：密集排名。如果有相同的值，它们会获得相同的排名，并且下一个排名会连续递增，不会跳跃。

select
    emp_no,
    salary,
    dense_rank() over (
        order by
            salary desc,
            emp_no asc
    ) as t_rank
from
    salaries
order by
    t_rank asc,
    emp_no asc;

代码解析：

1. dense_rank() over (...) as t_rank:

   • 我们调用 dense_rank() 函数，并通过 OVER 子句来定义排名的规则。
   • as t_rank 为这个新生成的排名列指定一个别名 t_rank。

2. order by salary desc, emp_no asc:

   • 这是 OVER 子句的核心。它告诉数据库：
     • 首先，按照 salary 列进行降序（desc）排列。薪水越高，排名越靠前。
     • 如果 salary 相同，则按照 emp_no 进行升序（asc）排列。员工编号越小，排名越靠前。
   • DENSE_RANK() 函数会根据这个排序后的顺序来计算每一行的排名。

3. 最终的 order by t_rank asc, emp_no asc:

   • 虽然窗口函数已经计算出了排名，但为了确保最终输出结果是严格按照我们预期的 t_rank 和 emp_no 排序的（不同数据库可能有微小差异），在查询的最后加上一个显式的 ORDER BY 是一个好习惯。

为什么是 DENSE_RANK() 而不是其他？

SQL 提供了多种排名函数，它们的行为略有不同：

• ROW_NUMBER()：为每一行分配一个唯一的、连续的整数。即使值相同，排名也不同。
  • 应用到本题，10002 和 10004 的排名会是 2 和 3。
• RANK()：相同值会有相同的排名，但下一个排名会跳过。
  • 应用到本题，10002 和 10004 的排名都是 2，但 10003 的排名会是 4。
• DENSE_RANK()：相同值会有相同的排名，下一个排名连续递增。
  • 应用到本题，结果正是我们预期的：1, 2, 2, 3。

根据题目的要求（salary 相同则排名相同，且下一个排名不跳跃），DENSE_RANK() 是最精确的选择。

四、常见的窗口函数有哪些？

窗口函数功能强大，种类繁多，我们可以将常见的窗口函数分为以下几大类，方便理解和记忆：

1. 排名函数 (Ranking Functions)

这类函数专门用于对数据进行排名，是最常用的窗口函数之一。

• ROW_NUMBER()

  • 作用：为窗口内的每一行分配一个从 1 开始的唯一连续整数。即使值相同，也会得到不同的排名。
  • 示例（应用于我们的薪水表）：

    sql

    SELECT 
        emp_no, 
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC, emp_no ASC) AS row_num_rank
    FROM salaries;
    

  • 结果：

    emp_no	salary	row_num_rank
    10001	88958	1
    10002	72527	2
    10004	72527	3	-- 注意，这里是 3，而不是 2
    10003	43311	4

• RANK()

  • 作用：对窗口内的数据进行排名。如果有相同值，它们会获得相同的排名，下一个排名会跳过前面相同排名的数量。
  • 示例：

    sql

    SELECT 
        emp_no, 
        salary,
        RANK() OVER (ORDER BY salary DESC) AS rank_rank
    FROM salaries;
    

  • 结果：

    emp_no	salary	rank_rank
    10001	88958	1
    10002	72527	2
    10004	72527	2
    10003	43311	4	-- 注意，这里是 4，而不是 3

• DENSE_RANK()

  • 作用：对窗口内的数据进行密集排名。如果有相同值，它们会获得相同的排名，下一个排名会连续递增，不会跳跃。这是我们解决上述问题的关键函数。
  • 示例：

    sql

    SELECT 
        emp_no, 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_rank
    FROM salaries;
    

  • 结果：

    emp_no	salary	dense_rank_rank
    10001	88958	1
    10002	72527	2
    10004	72527	2
    10003	43311	3	-- 正确，这里是 3

• NTILE(n)

  • 作用：将有序的窗口内的数据平均分配到 n 个桶（group）中，并为每一行分配一个桶号。如果无法平均分配，前面的桶会比后面的桶多一行。
  • 示例：NTILE(2)会将数据分为两组。

    sql

    SELECT 
        emp_no, 
        salary,
        NTILE(2) OVER (ORDER BY salary DESC) AS tile_rank
    FROM salaries;
    

  • 结果：

    emp_no	salary	tile_rank
    10001	88958	1
    10002	72527	1
    10004	72527	2
    10003	43311	2

2. 聚合函数 (Aggregate Functions)

我们常用的聚合函数，如 SUM, AVG, COUNT, MAX, MIN 等，都可以作为窗口函数使用。

• 作用：在不折叠行的情况下，计算窗口内的聚合值。这对于计算累计总和、移动平均值等非常有用。
• 示例：计算每个员工的薪水以及所有员工的平均薪水。

  sql

  SELECT 
      emp_no, 
      salary,
      AVG(salary) OVER () AS company_avg_salary
  FROM salaries;
  

• 结果：

  emp_no	salary	company_avg_salary
  10001	88958	69330.75
  10002	72527	69330.75
  10004	72527	69330.75
  10003	43311	69330.75

3. 偏移函数 (Offset Functions)

这类函数用于获取窗口内当前行相对于其他行（如前一行、后一行、第一行、最后一行）的值。

• LAG(expr, n)

  • 作用：获取当前行之前第 n 行的 expr 值。n 默认为 1。如果没有，则返回 NULL。
  • 示例：获取每个员工的薪水及其前一个员工的薪水。

    sql

    SELECT 
        emp_no, 
        salary,
        LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary
    FROM salaries;
    

  • 结果：

    emp_no	salary	prev_salary
    10001	88958	NULL	-- 第一行没有前一行
    10002	72527	88958
    10004	72527	72527
    10003	43311	72527

• LEAD(expr, n)

  • 作用：获取当前行之后第 n 行的 expr 值。n 默认为 1。如果没有，则返回 NULL。

• FIRST_VALUE(expr)

  • 作用：获取窗口内第一行的 expr 值。

• LAST_VALUE(expr)

  • 作用：获取窗口内最后一行的 expr 值。

4. 分布函数 (Distribution Functions)

这类函数用于计算数据在整个数据集中的分布情况。

• PERCENT_RANK()

  • 作用：计算百分比排名。排名计算公式为 (rank - 1) / (total_rows - 1)。返回值范围在 0 到 1 之间。

• CUME_DIST()

  • 作用：计算累积分布。返回值为小于或等于当前行值的行数除以总行数。返回值范围在 (1/total_rows) 到 1 之间。

总结

窗口函数是 SQL 中一个极其强大的工具集，它极大地扩展了 SQL 的数据分析能力。

• 它是什么？ 它是一种在不折叠行的情况下，对一组数据（窗口）进行计算的函数。
• 常见的有哪些？ 主要分为排名函数（如 DENSE_RANK）、聚合函数（如 SUM）、偏移函数（如 LAG）和分布函数（如 PERCENT_RANK）。
• 何时用？ 当你需要进行排名、聚合分析、移动计算或分组 Top N 查询时。
• 怎么用？ 使用 <函数名> OVER (ORDER BY ...) 的语法，并根据具体需求选择合适的窗口函数（如本题用 DENSE_RANK）和排序规则。

掌握了窗口函数，你就能更优雅、更高效地解决许多复杂的 SQL 查询问题。

希望这一篇文章对你有帮助！！