SQL开窗函数有哪些并详细介绍

在 SQL 查询中，开窗函数（Window Function）是一种强大的工具，它允许我们在不改变原始数据行数的前提下，对数据进行聚合、排序和计算。与传统的聚合函数不同，开窗函数可以在每一行上返回一个结果，而不是将多行合并为一行。这种特性使得开窗函数在数据分析、报表生成、排名统计等场景中具有广泛的应用价值。本文将详细介绍常见的 SQL 开窗函数，并结合实际例子说明其使用方法和应用场景。

一、什么是开窗函数

开窗函数是 SQL 中用于在查询结果集中对一组行执行计算的函数。它们通常与 OVER() 子句一起使用，用来定义“窗口”或“分区”，即哪些行被包含在当前计算中。开窗函数可以实现如排名、累计求和、移动平均等功能，而不会像传统聚合函数那样减少行数。

二、常见的 SQL 开窗函数类型

1. ROW_NUMBER()：行号函数

ROW_NUMBER() 用于为查询结果中的每一行分配一个唯一的序号。它可以根据指定的列进行排序，并按顺序编号。

语法示例：

SELECT 
    id, name, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

说明： 上述查询会根据学生的成绩从高到低排序，并为每行分配一个行号。适用于排行榜、分页等场景。

1. RANK()：排名函数

RANK() 用于对数据进行排名，如果有相同的值，它们会获得相同的排名，但下一个排名会跳过相应的数字。例如，如果两个学生分数相同且排名第一，下一位将是第三名。

语法示例：

SELECT 
    id, name, score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

说明： 这个函数常用于竞赛、考试排名等需要考虑并列情况的场景。

1. DENSE_RANK()：密集排名函数

DENSE_RANK() 与 RANK() 类似，但不会跳过排名。即使有多个相同值，排名也会连续递增。

语法示例：

SELECT 
    id, name, score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

说明： 在需要连续排名的情况下，DENSE_RANK() 更加适用，例如在体育比赛或评分系统中。

1. NTILE()：分组函数

NTILE(n) 将数据划分为 n 个组，每个组内的行数尽可能相等。适用于将数据分成若干个区间，如将用户分为前 20%、中间 60% 和后 20% 等。

语法示例：

SELECT 
    id, name, score,
    NTILE(5) OVER (ORDER BY score DESC) AS group_number
FROM students;

说明： 此函数常用于市场细分、用户分群等分析场景。

1. LEAD() 和 LAG()：前后行访问函数

LEAD() 和 LAG() 用于访问当前行的下一行或上一行的数据，常用于比较相邻行的数据差异。

语法示例：

SELECT 
    id, name, score,
    LAG(score, 1) OVER (ORDER BY id) AS previous_score,
    LEAD(score, 1) OVER (ORDER BY id) AS next_score
FROM students;

说明： 该函数可用于分析时间序列数据，如股票价格波动、销售趋势等。

1. SUM()、AVG()、MAX()、MIN() 等聚合函数作为开窗函数

除了上述专门的开窗函数外，传统的聚合函数也可以作为开窗函数使用，以实现对特定范围内的数据进行计算。

语法示例：

SELECT 
    id, name, score,
    SUM(score) OVER (PARTITION BY department ORDER BY date) AS cumulative_score
FROM employees;

说明： 通过 PARTITION BY 分组，ORDER BY 排序，可以实现累计求和、移动平均等功能。

三、开窗函数的常用子句

1. OVER() 子句

所有开窗函数都必须与 OVER() 子句一起使用，用于定义窗口的范围。OVER() 可以包含以下子句：

PARTITION BY：将数据按某一列进行分组，类似 GROUP BY。

ORDER BY：定义窗口内行的排序方式。

ROWS BETWEEN ... AND ...：定义窗口的起始和结束位置，用于限制计算范围。

1. PARTITION BY 的作用

PARTITION BY 用于将数据按某一列或表达式进行分组，开窗函数会在每个分组内独立计算。

示例：

SELECT 
    department, name, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

说明： 此查询会按部门对员工进行排名，每个部门内部独立计算。

四、开窗函数的实际应用案例

1. 排名统计

在电商平台上，常常需要对商品销量进行排名，以便找出畅销商品。使用 RANK() 或 DENSE_RANK() 可以轻松实现这一功能。

1. 累计求和

在财务报表中，可能需要计算某段时间内的累计收入或支出。通过 SUM() 函数配合 ORDER BY 和 ROWS BETWEEN 可以实现。

1. 趋势分析

在时间序列分析中，使用 LAG() 和 LEAD() 可以比较同一指标在不同时间段的变化，帮助发现趋势。

1. 数据分组与分类

利用 NTILE() 可以将客户、用户或产品按照某种标准分成不同的群体，便于后续分析。

SQL 开窗函数是处理复杂查询的强大工具，能够实现多种高级分析功能。通过掌握 ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()、LEAD()、LAG() 以及聚合函数作为开窗函数的使用方式，可以显著提升数据处理的效率和灵活性。

声明：所有来源为“聚合数据”的内容信息，未经本网许可，不得转载！如对内容有异议或投诉，请与我们联系。邮箱：marketing@think-land.com