开窗函数的使用详解(窗口范围ROWS与RANGE详解)

1、函数写法

函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)

由三部分组成： 函数名：如sum、max、min、count、avg等聚合函数以及lead、lag行比较函数等； over：
关键字，表示前面的函数是分析函数，不是普通的集合函数； 分组子句：over关键字后面挂号内的内容；

分析子句又由下面三部分组成： PARTITION BY ：分组子句，表示分析函数的计算范围，不同的组互不相干； ORDER BY：
排序子句，表示分组后，组内的排序方式； ROWS/RANGE：窗口子句，是在分组(PARTITION
BY)后，组内的子分组（也称窗口），此时分析函数的计算范围窗口，而不是PARTITON。窗口有两种，ROWS和RANGE；

             

1. PARTITION BY 子句：

   • 这是可选的。
   • 它用于将数据集分成不同的分区，每个分区可以有自己的窗口函数计算。
   • 例如，如果你有一个按部门分组的员工列表，并想为每个部门的员工分配一个序号，你可以使用 PARTITION BY 来指定部门。

2. ORDER BY 子句：

   • 这是必需的。
   • 它定义了在窗口函数应用之前，数据应如何排序。
   • 你可以根据一个或多个列来排序。例如，你可以根据员工的入职日期来排序。

3. ROWS/RANGE 子句：

   • 这是定义窗口函数计算的行范围的参数。
   • ROWS：指定一个明确的行数范围。例如，你可以指定从当前行开始并包括之前的5行。
   • RANGE：基于列值来定义范围。例如，你可以指定从当前行开始并包括到相同列值的最后一行。

开窗函数中的ROWS和RANGE子句用于定义窗口的范围，即函数将在哪些行上执行计算。这两个子句有一些关键的区别：

1. ROWS子句：

   • ROWS子句定义了一个明确的行数范围，它基于当前行来指定范围的开始和结束。
   • 范围的大小是固定的，并且与当前行无关。
   • 当你使用ROWS子句时，通常会使用诸如“UNBOUNDED PRECEDING”和“CURRENT ROW”之类的值来定义范围的开始和结束。例如，如果你指定“ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”，则函数将在从第一行到当前行的所有行上执行计算。

2. RANGE子句：

   • RANGE子句基于列值来定义范围。
   • 范围的大小是动态的，并基于当前行的值来调整。
   • 当使用RANGE子句时，通常会使用诸如“CURRENT ROW”之类的值来定义范围的开始，而范围的结束是基于列值的。例如，如果你指定“RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING”，则函数将在从当前行到结果集末尾的所有行上执行计算，其中每行的范围基于列值来定义。

总结：

• ROWS子句定义了一个基于行数的固定范围，而RANGE子句则基于列值来定义动态范围。
• 在使用ROWS子句时，你需要指定范围的开始和结束行号。而在使用RANGE子句时，你需要指定范围的开始行号和结束列值。
• 根据你的数据和需求，你可以选择适当的子句来执行所需的窗口函数计算。

以下详细的描述并且准确地解释了SQL中开窗函数中的ROWS和RANGE子句及其用法。

1. current row：当前行。
2. unbounded：表示无限的范围，例如在排序中表示从最前面或最后面的记录开始。
3. unbounded preceding：表示区间的第一行，通常与聚合函数一起使用，如计算总和或平均值。
4. unbounded following：表示区间的最后一行，常用于分组的结尾部分。
5. unbounded preceding and unbounded following：针对当前所有记录的前一条、后一条记录，分组中的所有记录。
6. preceding：在之前的行，n preceding：当前行之前的n行。
7. following：在之后的行，n following：当前行之后的n行。
8. rows between：用于定义一个明确的行数范围，如“rows between unbounded preceding and current row”表示从第一行到当前行的数据。
9. range between：基于列值的范围，如“range between current row and 350 following”表示从当前行到当前行数据+350的范围内的数据。
10. range between preceding and following：定义一个基于列值的动态范围，如“range between 5 preceding and 5 following”表示当前行数据幅度减5加5后的范围内的数据。

以下是一些实际性的案例，展示了如何使用开窗函数的ROWS和RANGE子句：

1. 案例一：计算每个部门的员工工资总额

假设有一个名为"employees"的表，其中包含员工的信息和工资。现在要计算每个部门的员工工资总额。

	SELECT department,
	name,
	salary,
	SUM(salary) OVER (PARTITION BY department) as total_salary
	FROM employees;

在这个例子中，我们使用PARTITION BY子句将数据分成不同的部门，然后使用SUM函数计算每个部门的员工工资总额。通过将SUM函数与OVER子句结合使用，我们可以轻松地计算每个部门的工资总额。

2. 案例二：计算每个员工的累计销售额

假设有一个名为"sales"的表，其中包含销售数据和销售人员的名字。现在要计算每个销售人员的累计销售额。

	SELECT name,
	sale_date,
	sale_amount,
	SUM(sale_amount) OVER (PARTITION BY name ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sales
	FROM sales;

在这个例子中，我们使用PARTITION BY子句按销售人员名字分组数据，并使用ORDER BY子句按销售日期排序。然后，我们使用SUM函数计算每个销售人员的累计销售额，通过指定ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW来定义窗口范围，包括从第一行到当前行的所有行。

3. 案例三：计算每个员工的销售排名

假设有一个名为"sales"的表，其中包含销售数据和销售人员的名字。现在要计算每个销售人员的销售排名。

	SELECT name,
	sale_amount,
	RANK() OVER (ORDER BY sale_amount DESC) as sales_rank
	FROM sales;

在这个例子中，我们使用RANK函数计算每个销售人员的销售排名。通过指定ORDER BY子句按销售金额降序排序，RANK函数将为每个销售人员分配一个排名值。使用OVER子句定义窗口范围，以便RANK函数在整个结果集上执行计算。