SQL窗口函数（Window Function）

窗口函数（Window Function）是SQL语句中一种强大的分析功能，它允许您在一组相关行上执行计算，而不仅仅是针对单个行。通过使用窗口子句（WINDOW子句），您可以定义一个或多个命名的窗口（window），然后在窗口函数中引用这些窗口，以避免重复编写相同的窗口规范。这使得在查询中处理复杂的数据集变得更加简单和高效。

语法


WINDOW <window_name> AS (<window_specification>, ...)

其中，<window_name> 是为窗口指定的名称，它必须是一个有效的标识符，不能与表名或列名冲突。<window_specification> 是定义窗口范围和顺序的子句，它可以包含以下几个部分：

PARTITION BY 子句：指定如何将数据分成不同的组，每个组是一个分区（partition）。
ORDER BY 子句：指定在每个分区中如何对数据进行排序，排序顺序会影响一些窗口函数的结果，例如 ROW_NUMBER()、RANK() 等。
frame_clause 子句：指定窗口的一个子集，即窗口框架（window frame），它可以用来限制窗口函数的计算范围，只对窗口框架中的数据进行计算。frame_clause 子句有两种形式：ROWS frame 和 RANGE frame，它们的语法和含义请参考 Window Frame 的文档。

Window Clause的使用示例

以下是一些使用window clause的SQL语句的示例，以及它们的输出结果。假设我们有一个名为sales的表，它包含了每个月的销售额和利润率的数据，如下所示：


create table sales(month int,sales int,profit double);
INSERT INTO sales (month, sales, profit) VALUES
(1, 100, 0.1),
(2, 120, 0.15),
(3, 80, 0.05),
(4, 150, 0.2),
(5, 90, 0.1),
(6, 110, 0.12);

查询：使用window clause定义一个名为w的window，它按照月份排序，并且包含当前行和前两行的数据，然后使用SUM()函数和AVG()函数计算每个月的销售额的总和和平均值。


SELECT month, sales, SUM(sales) OVER w AS sum_sales, AVG(sales) OVER w AS avg_sales
FROM sales
WINDOW w AS (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);



+-------+-------+-----------+--------------------+
| month | sales | sum_sales |     avg_sales      |
+-------+-------+-----------+--------------------+
| 1     | 100   | 100       | 100.0              |
| 2     | 120   | 220       | 110.0              |
| 3     | 80    | 300       | 100.0              |
| 4     | 150   | 350       | 116.66666666666667 |
| 5     | 90    | 320       | 106.66666666666667 |
| 6     | 110   | 350       | 116.66666666666667 |
+-------+-------+-----------+--------------------+

查询：使用window clause定义两个名为w1和w2的window，它们分别按照季度和月份进行分组和排序，然后使用RANK()函数和ROW_NUMBER()函数计算每个月的销售额在季度和全年的排名。


SELECT month, sales, RANK() OVER w1 AS rank_quarter, ROW_NUMBER() OVER w2 AS rank_year
FROM sales
WINDOW w1 AS (PARTITION BY CEIL(month / 3) ORDER BY sales DESC),
       w2 AS (ORDER BY sales DESC);
       
       
+-------+-------+--------------+-----------+
| month | sales | rank_quarter | rank_year |
+-------+-------+--------------+-----------+
| 4     | 150   | 1            | 1         |
| 2     | 120   | 1            | 2         |
| 6     | 110   | 2            | 3         |
| 1     | 100   | 2            | 4         |
| 5     | 90    | 3            | 5         |
| 3     | 80    | 3            | 6         |
+-------+-------+--------------+-----------+

联系我们