重学SQL窗口函数

窗口函数语法

SQL窗口函数是SQL中的一种高级函数，它允许用户在不显式分组查询的情况下对结果集进行分组和聚合计算。
窗口函数的特别之处在于，它们将结果集中的每一行看作一个单独的计算对象，而不是将结果集划分为分组并计算每个分组的聚合值。这就使得窗口函数能够为结果集中的每一行计算类似排名、行号、百分比和移动聚合函数等值。

SQL窗口函数的语法如下：

<窗口函数> OVER ([PARTITION BY <分组列> [, <分组列>...]]
                     [ORDER BY <排序列> [ASC | DESC] [, <排序列> [ASC | DESC]]...]
                     [<rows or range clause>])

其中:

<窗口函数> : 定义要在窗口中计算的聚合函数或其它分析函数，如COUNT、RANK、SUM等。
OVER : 窗口函数的核心关键字。
PARTITION BY : 定义要用来分组的一组列名。
ORDER BY : 定义用来排序的一组列名。
<rows or range clause> : 定义窗口的行集合。默认为 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ，表示窗口包括从窗口开始到当前行的所有行。

下面的例子说明了如何使用窗口函数计算每个部门的平均工资，并根据平均工资进行排名。

SELECT department, employee_name, salary, 
  AVG(salary) OVER (PARTITION BY department) average_salary, 
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) salary_rank
FROM employee;

在这个例子中，PARTITION BY定义了用于分组计算平均工资的列名，OVER后面依次定义了需要计算的列名和相应的窗口函数。 AVG(salary) OVER (PARTITION BY department) 的意思是对于每个部门，计算 salary 列的平均值，而 RANK() OVER (PARTITION BY department ORDER BY salary DESC) 的意思是计算每个部门中 salary 列的排名。
SQL窗口函数的语法和用法比较复杂，但它极大地拓展了SQL查询的能力，使得更多复杂的查询可以得以实现。

sale_date	sale_volume	sales_rank
2021-01-01	12500	1
2021-01-02	13800	1
2021-01-03	13000	1
2021-01-04	11800	2
2021-01-05	12500	1
2021-01-06	14000	1
2021-01-07	11500	1
2021-01-08	12800	1
2021-01-09	13100	1
2021-01-10	12900	1

SQL的窗口函数中常见的排序方法

SQL的窗口函数中常见的排序方法包括以下几种：

ROW_NUMBER()函数：按照指定的列或表达式对结果集进行排序，并为排序后的每一行分配一个唯一的行号。
RANK()函数：对结果集进行排序，并为排序后的相同值的行分配相同的排名，排名之间存在空缺。
DENSE_RANK()函数：对结果集进行排序，并为排序后的相同值的行分配相同的排名，排名之间不存在空缺。
CUME_DIST()函数：计算结果集中某个值在排序后出现的位置占总行数的比例。
NTILE(n)函数：将结果集划分为n份，并为每个分组分配一个编号。

注意，以上窗口函数中的排序方法都是基于ORDER BY子句中指定的列或表达式的排序。

以下是五种窗口函数的排序方法及其例子：

ROW_NUMBER()：按照指定的排序方法为每行分配一个唯一的整数行号，行号从1开始递增。示例如下：

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, employee_name, salary
FROM employee;

该语句会对employee表按照salary字段降序排序，并为每行分配一个不重复的行号存储在row_num字段中。

RANK()：按照指定的排序方法为每行分配一个排名，相等值将被分配相同的排名并跳过下一个排名。示例如下：

SELECT RANK() OVER (ORDER BY total_sales DESC) AS ranking, region_name, total_sales
FROM sales_summary
WHERE year = '2021';

该语句会对2021年销售总额按照降序排序，并为每行分配一个排名存储在ranking字段中。

DENSE_RANK()：按照指定的排序方法为每行分配一个密集排名，相等值将被分配相同的排名但不会跳过下一个排名。示例如下：

SELECT DENSE_RANK() OVER (ORDER BY exam_score DESC) AS rank, student_name, exam_score
FROM exam_results
WHERE exam_type = 'final';

该语句会对final考试的成绩按照降序排序，并为每行分配一个密集排名存储在rank字段中。

NTILE(n)：将行分成n个近似相等的桶，并为每行分配一个桶号（从1到n）。示例如下：

SELECT NTILE(4) OVER (ORDER BY product_sales DESC) AS quartile, product_name, product_sales
FROM product_summary
WHERE year = '2021';

该语句会将2021年产品销售额按照降序排序，并将其分成四个桶，为每行分配一个桶号存储在quartile字段中。

LAG()/LEAD()：LAG函数用于获取当前行之前的行数据，LEAD函数用于获取当前行之后的行数据。示例如下：

SELECT employee_name, salary,
       LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
       LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employee;

该语句会对employee表按照salary字段排序，并为每行获取前一行和后一行的salary值存储在prev_salary和next_salary字段中。

窗口函数的常见场景

窗口函数是在SELECT语句中用来执行聚合、排序、排名等操作的一种特殊函数。它们操作一个窗口（Window）内的数据集，并返回一个与当前行相关的聚合结果。

以下是一些使用窗口函数的常见场景和示例：

排序：使用窗口函数的排名功能可以将结果按照指定的列进行排序，而不影响结果集中其他列的排序。
例如，以下SQL语句用排名窗口函数将销售额进行降序排列，并在结果集中显示销售额排名的值：

    SELECT order_no, order_date, customer_name, order_amount,
           RANK() OVER (ORDER BY order_amount DESC) as rank
    FROM orders;

聚合：窗口函数可以将数据集划分为多个分区，并在每个分区内执行聚合操作。
例如，以下SQL语句使用SUM函数计算每个客户的累计销售额，并使用窗口函数将结果分区：

    SELECT customer_name, order_date, order_amount,
           SUM(order_amount) OVER (PARTITION BY customer_name ORDER BY order_date) as cumulative_sales
    FROM orders;

分析：使用窗口函数的分析功能可以计算每行与其他行之间的差异。例如，以下SQL语句计算每个销售额相对于上一个销售额的增量，并使用窗口函数进行分析：

    SELECT order_no, order_date, order_amount,
           order_amount - LAG(order_amount, 1, 0) OVER (ORDER BY order_date) as sales_increase
    FROM orders;

假设有一张包含学生成绩信息的表scores，包括学生ID，课程ID和成绩。现在需要查询每个学生最高成绩和次高成绩，并计算两者的差值（用作成绩提升分析）。

以下是使用窗口函数实现该查询的SQL语句：

SELECT student_id, MAX(score) as max_score, 
       LAG(MAX(score), 1, 0) OVER (PARTITION BY student_id ORDER BY score DESC) as second_max_score, 
       MAX(score) - LAG(MAX(score), 1, 0) OVER (PARTITION BY student_id ORDER BY score DESC) as score_gap
FROM scores
GROUP BY student_id

该查询将scores表按照student_id分组，计算每个学生的最高成绩和次高成绩，并计算两者的差值。以下是该查询的查询结果：

student_id	max_score	second_max_score	score_gap
1	95	89	6
2	88	82	6
3	86	75	11
4	92	84	8
5	94	91	3

以上是用markdown展示的查询结果，包括查询到的学生ID，最高成绩，次高成绩和两者的差值。

移动平均数：使用窗口函数的移动平均功能可以计算指定数量的前后数据行的平均值。
例如，以下SQL语句计算每个销售额的一个2周滑动平均值，并使用窗口函数进行计算：

    SELECT order_date, order_amount,
           AVG(order_amount) OVER (ORDER BY order_date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) as moving_average
    FROM orders;

这些只是窗口函数的一些常见用途和示例。实际上，窗口函数非常灵活，可以用于各种不同的数据分析和处理场景。

posted @ 2023-05-18 10:58 捷后愚生阅读(4633) 评论(0) 编辑收藏举报

刷新页面返回顶部

捷后愚生

重学SQL窗口函数

窗口函数语法

更多说明

SQL的窗口函数中常见的排序方法

窗口函数的常见场景

公告