KnowForge/Apache DataFusion 简体中文
SQL 参考

窗口函数

师成师成· 更新于 2026-09-28· 阅读 30 分钟· 0 次阅读
登录后可跨设备保存划线和私人笔记登录

窗口函数

窗口函数会在与当前行存在某种关联的一组表行上执行计算。这与聚合函数所能完成的计算类似。不过，窗口函数并不会像非窗口的聚合调用那样，把多行归并为单个输出行，而是让每一行保留各自独立的身份。在底层实现中，窗口函数能够访问的不只是查询结果中的当前行。

下面的示例展示了如何将每位员工的薪资与其所在部门的平均薪资进行比较：

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

+-----------+-------+--------+-------------------+
| depname   | empno | salary | avg               |
+-----------+-------+--------+-------------------+
| personnel | 2     | 3900   | 3700.0            |
| personnel | 5     | 3500   | 3700.0            |
| develop   | 8     | 6000   | 5020.0            |
| develop   | 10    | 5200   | 5020.0            |
| develop   | 11    | 5200   | 5020.0            |
| develop   | 9     | 4500   | 5020.0            |
| develop   | 7     | 4200   | 5020.0            |
| sales     | 1     | 5000   | 4866.666666666667 |
| sales     | 4     | 4800   | 4866.666666666667 |
| sales     | 3     | 4800   | 4866.666666666667 |
+-----------+-------+--------+-------------------+

窗口函数调用总会在窗口函数名称与参数之后紧跟一个 OVER 子句。这正是它在语法上区别于普通函数或非窗口聚合函数的地方。OVER 子句精确地决定了查询的行如何被拆分以便窗口函数进行处理。OVER 中的 PARTITION BY 子句将行划分为若干组（即分区），同一分区内的行具有相同的 PARTITION BY 表达式值。对于每一行，窗口函数会在与当前行处于同一分区的所有行上进行计算。上一个示例展示了如何按分区计算某列的平均值。

你还可以通过 OVER 中的 ORDER BY 来控制窗口函数处理行的顺序。（窗口的 ORDER BY 甚至不必与输出行的顺序一致。）示例如下：

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

+-----------+-------+--------+--------+
| depname   | empno | salary | rank   |
+-----------+-------+--------+--------+
| personnel | 2     | 3900   | 1      |
| develop   | 8     | 6000   | 1      |
| develop   | 10    | 5200   | 2      |
| develop   | 11    | 5200   | 2      |
| develop   | 9     | 4500   | 4      |
| develop   | 7     | 4200   | 5      |
| sales     | 1     | 5000   | 1      |
| sales     | 4     | 4800   | 2      |
| personnel | 5     | 3500   | 2      |
| sales     | 3     | 4800   | 2      |
+-----------+-------+--------+--------+

与窗口函数相关的另一个重要概念是：对于每一行，在其分区中都有一组行，这组行称为该行的窗口帧（window frame）。有些窗口函数只作用于窗口帧内的行，而不是整个分区的行。下面是一个在查询中使用窗口帧的示例：

SELECT depname, empno, salary,
    avg(salary) OVER(ORDER BY salary ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg,
    min(salary) OVER(ORDER BY empno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_min
FROM empsalary
ORDER BY empno ASC;

+-----------+-------+--------+--------------------+---------+
| depname   | empno | salary | avg                | cum_min |
+-----------+-------+--------+--------------------+---------+
| sales     | 1     | 5000   | 5000.0             | 5000    |
| personnel | 2     | 3900   | 3866.6666666666665 | 3900    |
| sales     | 3     | 4800   | 4700.0             | 3900    |
| sales     | 4     | 4800   | 4866.666666666667  | 3900    |
| personnel | 5     | 3500   | 3700.0             | 3500    |
| develop   | 7     | 4200   | 4200.0             | 3500    |
| develop   | 8     | 6000   | 5600.0             | 3500    |
| develop   | 9     | 4500   | 4500.0             | 3500    |
| develop   | 10    | 5200   | 5133.333333333333  | 3500    |
| develop   | 11    | 5200   | 5466.666666666667  | 3500    |
+-----------+-------+--------+--------------------+---------+

当一个查询涉及多个窗口函数时，可以为每个函数分别写出各自的 OVER 子句，但如果多个函数需要相同的窗口行为，这样写不仅冗余，还容易出错。此时可以在 WINDOW 子句中为每种窗口行为命名，然后在 OVER 中引用它。例如：

SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

语法

OVER 子句的语法如下：

function([expr])
  OVER(
    [PARTITION BY expr[, …]]
    [ORDER BY expr [ ASC | DESC ][, …]]
    [ frame_clause ]
    )

其中 frame_clause 是以下之一：

  { RANGE | ROWS | GROUPS } frame_start
  { RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end

并且 frame_start 和 frame_end 可以是以下之一：

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

其中 offset 是一个非负整数。

RANGE 和 GROUPS 模式要求必须有 ORDER BY 子句（使用 RANGE 时，ORDER BY 必须且只能指定一个列）。

在 RANGE 模式下，offset 是以 ORDER BY 的值而非行数来计量的，因此边界是通过在当前行的 ORDER BY 值上加上或减去该值得出的。这就要求 offset PRECEDING 和 offset FOLLOWING 只能用于支持此类算术运算的 ORDER BY 类型，即数值型、日期型和时间戳型。其他可排序的类型（如字符串、二进制和时间类型）仍然可以与 UNBOUNDED PRECEDING、CURRENT ROW 和 UNBOUNDED FOLLOWING 搭配使用，这些边界是通过比较 ORDER BY 的值来定位的。

聚合窗口函数的 FILTER 子句

聚合窗口函数支持 SQL 的 FILTER (WHERE ...) 子句，用于在聚合中仅纳入窗口帧中满足该谓词的行。

sum(salary) FILTER (WHERE salary > 0)
  OVER (PARTITION BY depname ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

若对于某个输出行，框架内没有任何行满足该过滤条件，则 COUNT 返回 0，而 SUM/AVG/MIN/MAX 返回 NULL。

聚合函数

所有聚合函数都可以用作窗口函数。

排名函数

cume_dist

当前行的相对排名：（排在当前行之前或与当前行并列的行数）/（总行数）。

cume_dist()

示例

-- Example usage of the cume_dist window function:
SELECT salary,
    cume_dist() OVER (ORDER BY salary) AS cume_dist
FROM employees;

+--------+-----------+
| salary | cume_dist |
+--------+-----------+
| 30000  | 0.33      |
| 50000  | 0.67      |
| 70000  | 1.00      |
+--------+-----------+

dense_rank

返回当前行的排名，排名中不留空缺。该函数以密集的方式为行排名，即使值相同也会分配连续的排名。

dense_rank()

示例

-- Example usage of the dense_rank window function:
SELECT department,
    salary,
    dense_rank() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

+-------------+--------+------------+
| department  | salary | dense_rank |
+-------------+--------+------------+
| Sales       | 70000  | 1          |
| Sales       | 50000  | 2          |
| Sales       | 50000  | 2          |
| Sales       | 30000  | 3          |
| Engineering | 90000  | 1          |
| Engineering | 80000  | 2          |
+-------------+--------+------------+

ntile

返回 1 到参数值之间的整数，尽可能平均地划分分区。

ntile(expression)

参数

  • expression：一个整数，表示该分区应被划分成的分组数量

示例

-- Example usage of the ntile window function:
SELECT employee_id,
    salary,
    ntile(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

+-------------+--------+----------+
| employee_id | salary | quartile |
+-------------+--------+----------+
| 1           | 90000  | 1        |
| 2           | 85000  | 1        |
| 3           | 80000  | 2        |
| 4           | 70000  | 2        |
| 5           | 60000  | 3        |
| 6           | 50000  | 3        |
| 7           | 40000  | 4        |
| 8           | 30000  | 4        |
+-------------+--------+----------+

percent_rank

返回当前行在其分区内所处的百分位排名。取值范围为 0 到 1，计算公式为 (rank - 1) / (total_rows - 1)。

percent_rank()

示例

    -- Example usage of the percent_rank window function:
SELECT employee_id,
    salary,
    percent_rank() OVER (ORDER BY salary) AS percent_rank
FROM employees;

+-------------+--------+---------------+
| employee_id | salary | percent_rank  |
+-------------+--------+---------------+
| 1           | 30000  | 0.00          |
| 2           | 50000  | 0.50          |
| 3           | 70000  | 1.00          |
+-------------+--------+---------------+

rank

返回当前行在其分区内的排名，排名之间允许出现空缺。该函数提供的排名方式与 row_number 类似，但对于相同的值会跳过排名。

rank()

示例

-- Example usage of the rank window function:
SELECT department,
    salary,
    rank() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

+-------------+--------+------+
| department  | salary | rank |
+-------------+--------+------+
| Sales       | 70000  | 1    |
| Sales       | 50000  | 2    |
| Sales       | 50000  | 2    |
| Sales       | 30000  | 4    |
| Engineering | 90000  | 1    |
| Engineering | 80000  | 2    |
+-------------+--------+------+

row_number

当前行在其分区内从 1 开始计数的序号。

row_number()

示例

-- Example usage of the row_number window function:
SELECT department,
  salary,
  row_number() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

+-------------+--------+---------+
| department  | salary | row_num |
+-------------+--------+---------+
| Sales       | 70000  | 1       |
| Sales       | 50000  | 2       |
| Sales       | 50000  | 3       |
| Sales       | 30000  | 4       |
| Engineering | 90000  | 1       |
| Engineering | 80000  | 2       |
+-------------+--------+---------+

分析函数

first_value

返回在窗口帧第一行处求得的值。

first_value(expression)

参数

  • expression：要进行运算的表达式

示例

-- Example usage of the first_value window function:
SELECT department,
  employee_id,
  salary,
  first_value(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS top_salary
FROM employees;

+-------------+-------------+--------+------------+
| department  | employee_id | salary | top_salary |
+-------------+-------------+--------+------------+
| Sales       | 1           | 70000  | 70000      |
| Sales       | 2           | 50000  | 70000      |
| Sales       | 3           | 30000  | 70000      |
| Engineering | 4           | 90000  | 90000      |
| Engineering | 5           | 80000  | 90000      |
+-------------+-------------+--------+------------+

lag

返回分区内当前行之前偏移 offset 行处的行所计算出的值；如果不存在这样的行，则返回默认值（其类型必须与 value 的类型相同）。

lag(expression, offset, default)

参数

  • expression：要参与运算的表达式
  • offset：整数。指定回溯多少行来取 expression 的值。默认为 1。
  • default：当 offset 超出分区范围时使用的默认值，其类型必须与 expression 相同。

示例

-- Example usage of the lag window function:
SELECT employee_id,
    salary,
    lag(salary, 1, 0) OVER (ORDER BY employee_id) AS prev_salary
FROM employees;

+-------------+--------+-------------+
| employee_id | salary | prev_salary |
+-------------+--------+-------------+
| 1           | 30000  | 0           |
| 2           | 50000  | 30000       |
| 3           | 70000  | 50000       |
| 4           | 60000  | 70000       |
+-------------+--------+-------------+

last_value

返回在窗口帧最后一行的行上计算得到的值。

last_value(expression)

参数

  • expression：要操作的表达式

示例

-- SQL example of last_value:
SELECT department,
       employee_id,
       salary,
       last_value(salary) OVER (PARTITION BY department ORDER BY salary) AS running_last_salary
FROM employees;

+-------------+-------------+--------+---------------------+
| department  | employee_id | salary | running_last_salary |
+-------------+-------------+--------+---------------------+
| Sales       | 1           | 30000  | 30000               |
| Sales       | 2           | 50000  | 50000               |
| Sales       | 3           | 70000  | 70000               |
| Engineering | 4           | 40000  | 40000               |
| Engineering | 5           | 60000  | 60000               |
+-------------+-------------+--------+---------------------+

lead

返回在分区内当前行之后偏移 offset 行处求得的值；如果不存在这样的行，则返回默认值（其类型必须与 value 的类型相同）。

lead(expression, offset, default)

参数

  • expression：要进行运算的表达式
  • offset：整数。指定应向前取 expression 的值的行数。默认为 1。
  • default：当 offset 超出分区范围时使用的默认值。其类型必须与 expression 相同。

示例

-- Example usage of lead window function:
SELECT
    employee_id,
    department,
    salary,
    lead(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS next_salary
FROM employees;

+-------------+-------------+--------+--------------+
| employee_id | department  | salary | next_salary  |
+-------------+-------------+--------+--------------+
| 1           | Sales       | 30000  | 50000        |
| 2           | Sales       | 50000  | 70000        |
| 3           | Sales       | 70000  | 0            |
| 4           | Engineering | 40000  | 60000        |
| 5           | Engineering | 60000  | 0            |
+-------------+-------------+--------+--------------+

nth_value

返回在窗口框架第 n 行（从 1 开始计数）处计算得到的值。如果不存在这样的行，则返回 NULL。

nth_value(expression, n)

参数

  • expression：从中检索第 n 个值的列。
  • n：窗口帧中的整数位置。正值从第一行开始计数，起始为 1；负值从最后一行向前计数，其中 -1 表示最后一行。

示例

-- Sample employees table:
CREATE TABLE employees (id INT, salary INT);
INSERT INTO employees (id, salary) VALUES
(1, 30000),
(2, 40000),
(3, 50000),
(4, 60000),
(5, 70000);

-- Example usage of nth_value:
SELECT nth_value(salary, 2) OVER (
  ORDER BY salary
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS nth_value
FROM employees;

+-----------+
| nth_value |
+-----------+
| 40000     |
| 40000     |
| 40000     |
| 40000     |
| 40000     |
+-----------+

评论

登录后参与评论

正在加载评论…