SQLServer2022新特性Window子句
参考官方文档
https://learn.microsoft.com/zh-cn/sql/t-sql/queries/select-window-transact-sql?view=sql-server-ver16
1、本文内容
- 语法
- 参数
- 一般备注
- 示例
1.1、新特性适用于：
- SQL Server 2022 (16.x)
- Azure SQL 数据库
- Azure SQL 托管实例
在应用 OVER 子句中使用窗口的窗口函数之前，WINDOW 子句中的命名窗口定义确定行集的分区和排序。
1.2、备注
WINDOW 子句要求数据库兼容性级别为 160 或更高。 如果数据库兼容性级别低于 160，则 SQL Server 无法使用 WINDOW 子句执行查询。
可在 sys.databases 视图或数据库属性中查看兼容性级别。 可以使用以下命令更改数据库的兼容级别：
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 160
2、语法
WINDOW window_name AS ([ reference_window_name ] [ <PARTITION BY clause> ] [ <ORDER BY clause> ] [ <ROW or RANGE clause> ] ) <PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ] <ORDER BY clause> ::=
ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ] <ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>
3、参数
window_name
定义的窗口规范的名称。 OVER 子句中的窗口函数将使用此名称来引用窗口规范。 窗口名称必须符合标识符的规则。
reference_window_name
当前窗口引用的窗口的名称。 引用的窗口必须位于 WINDOW 子句中定义的窗口之间。
其他参数包括：
-
PARTITION BY：将查询结果集分为多个分区。
-
ORDER BY：定义结果集的每个分区中行的逻辑顺序。
-
ROWS/RANGE：通过指定分区中的起点和终点来限制分区中的行数。
有关参数的更多具体详细信息，请参阅 OVER 子句
https://learn.microsoft.com/zh-cn/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16
4、一般备注
可在 WINDOW 子句中定义多个命名窗口。
通过使用后跟附加规范的 window_name，可将附加组件添加到 OVER 子句中的命名窗口。 但是，不能在 OVER 子句中重新定义 WINDOW 子句中指定的属性。
当查询使用多个窗口时，一个命名窗口可以使用 window_name 引用另一个命名窗口。 在这种情况下，必须在引用窗口的窗口定义中指定引用 window_name。 一个窗口中定义的窗口组件不能由另一个引用它的窗口重新定义。
根据在 window 子句中定义窗口的顺序，允许向前和向后的窗口引用。 换句话说，一个窗口可使用在它所属的 <window_expression> 中定义的任何其他窗口作为 reference_window_name，而不管它们的定义顺序如何。 不允许循环引用和在单个窗口中使用多个窗口引用。
<window_expression> 中包含的已定义窗口的新 window_name 的范围由属于 <window_expression> 的任何窗口定义，以及包含该 window 子句的 <query_specification> 或 的 SELECT 子句组成。 如果 <window_expression> 包含在属于简单表查询 <query_expression> 的 <query_specification> 中，则新 window_name 的范围还包括该 <query_expression> 的 <order_by_expression>（如果有）。
OVER 子句中窗口规范与基于语义的聚合函数和分析函数的使用限制适用于 WINDOW 子句。
5、示例
5.1、 指定在 window 子句中定义的窗口
测试表及数据准备
create table t_order_sales(
order_id varchar(4) primary key,
user_no varchar(8),
amount int,
create_date datetime
);
insert into t_order_sales values ('1001','C0001',1920,'2020-01-01');
insert into t_order_sales values ('1002','C0002',3510,'2019-03-02');
insert into t_order_sales values ('1003','C0003',3500,'2022-04-12');
insert into t_order_sales values ('1004','C0004',8030,'2021-05-10');
insert into t_order_sales values ('1005','C0011',9600,'2023-03-12');
insert into t_order_sales values ('1006','C0002',5021,'2022-01-05');
insert into t_order_sales values ('1007','C1002',6160,'2018-01-06');
insert into t_order_sales values ('1008','C2007',3201,'2024-01-10');
insert into t_order_sales values ('1009','C3008',8760,'2023-01-16');
insert into t_order_sales values ('1010','V0002',8870,'2021-01-22');SELECT * FROM dbo.t_order_sales;order_id user_no amount create_date
-------- -------- ----------- -----------------------
1001 C0001 1920 2020-01-01 00:00:00.000
1002 C0002 3510 2019-03-02 00:00:00.000
1003 C0003 3500 2022-04-12 00:00:00.000
1004 C0004 8030 2021-05-10 00:00:00.000
1005 C0011 9600 2023-03-12 00:00:00.000
1006 C0002 5021 2022-01-05 00:00:00.000
1007 C1002 6160 2018-01-06 00:00:00.000
1008 C2007 3201 2024-01-10 00:00:00.000
1009 C3008 8760 2023-01-16 00:00:00.000
1010 V0002 8870 2021-01-22 00:00:00.000指定在 window 子句中定义的窗口
SELECT ROW_NUMBER() OVER win AS "Row Number",order_id,user_no,amount,create_date
FROM t_order_sales
WINDOW win AS (ORDER BY amount DESC)
GORow Number order_id user_no amount create_date
-------------------- -------- -------- ----------- -----------------------
1 1005 C0011 9600 2023-03-12 00:00:00.000
2 1010 V0002 8870 2021-01-22 00:00:00.000
3 1009 C3008 8760 2023-01-16 00:00:00.000
4 1004 C0004 8030 2021-05-10 00:00:00.000
5 1007 C1002 6160 2018-01-06 00:00:00.000
6 1006 C0002 5021 2022-01-05 00:00:00.000
7 1002 C0002 3510 2019-03-02 00:00:00.000
8 1003 C0003 3500 2022-04-12 00:00:00.000
9 1008 C2007 3201 2024-01-10 00:00:00.000
10 1001 C0001 1920 2020-01-01 00:00:00.000(10 行受影响)以下查询与上述查询等效，但不使用 WINDOW 子句。
Row Number order_id user_no amount create_date
-------------------- -------- -------- ----------- -----------------------
1 1005 C0011 9600 2023-03-12 00:00:00.000
2 1010 V0002 8870 2021-01-22 00:00:00.000
3 1009 C3008 8760 2023-01-16 00:00:00.000
4 1004 C0004 8030 2021-05-10 00:00:00.000
5 1007 C1002 6160 2018-01-06 00:00:00.000
6 1006 C0002 5021 2022-01-05 00:00:00.000
7 1002 C0002 3510 2019-03-02 00:00:00.000
8 1003 C0003 3500 2022-04-12 00:00:00.000
9 1008 C2007 3201 2024-01-10 00:00:00.000
10 1001 C0001 1920 2020-01-01 00:00:00.000(10 行受影响)
5.2、在多个 over 子句中指定单个窗口
SELECT order_id,user_no,amount,create_date,SUM(amount) OVER win AS Total,AVG(amount) OVER win AS "Avg",COUNT(amount) OVER win AS "Count",MIN(amount) OVER win AS "Min",MAX(amount) OVER win AS "Max"
FROM t_order_sales
WHERE user_no IN ('C0002','V0002')
WINDOW win AS (PARTITION BY user_no);
GOorder_id user_no amount Total Avg Count Min Max
-------- -------- ----------- ----------- ----------- ----------- ----------- -----------
1002 C0002 3510 8531 4265 2 3510 5021
1006 C0002 5021 8531 4265 2 3510 5021
1010 V0002 8870 8870 8870 1 8870 8870(3 行受影响)以下查询与上述查询等效，但不使用 WINDOW 子句。
SELECT order_id,user_no,amount,SUM(amount) OVER (PARTITION BY user_no) AS Total,AVG(amount) OVER (PARTITION BY user_no) AS "Avg",COUNT(amount) OVER (PARTITION BY user_no) AS "Count",MIN(amount) OVER (PARTITION BY user_no) AS "Min",MAX(amount) OVER (PARTITION BY user_no) AS "Max"
FROM t_order_sales
WHERE user_no IN ('C0002','V0002');
GOorder_id user_no amount Total Avg Count Min Max
-------- -------- ----------- ----------- ----------- ----------- ----------- -----------
1002 C0002 3510 8531 4265 2 3510 5021
1006 C0002 5021 8531 4265 2 3510 5021
1010 V0002 8870 8870 8870 1 8870 8870(3 行受影响)
5.3、在 window 子句中定义通用规范
SELECT order_id,user_no,amount,SUM(amount) OVER win AS Total,AVG(amount) OVER(win PARTITION BY user_no) AS Avg,COUNT(amount) OVER(win ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS Count
FROM t_order_sales
WHERE user_no IN ('C0002','V0002')
WINDOW win AS (ORDER BY user_no, create_date);
GOorder_id user_no amount Total Avg Count
-------- -------- ----------- ----------- ----------- -----------
1002 C0002 3510 3510 3510 2
1006 C0002 5021 8531 4265 3
1010 V0002 8870 17401 8870 3(3 行受影响)
以下查询与上述查询等效，但不使用 WINDOW 子句。
SELECT order_id,user_no,amount,SUM(amount) OVER (ORDER BY user_no, create_date) AS Total,AVG(amount) OVER (PARTITION BY user_no ORDER BY user_no, create_date) AS Avg,COUNT(amount) OVER(ORDER BY user_no, create_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS Count
FROM t_order_sales
WHERE user_no IN ('C0002','V0002');
GO
5.4、 向前和向后的窗口引用
此示例显示在 WINDOW 子句中定义新窗口时，将命名窗口用作前向引用和向后引用。
SELECT order_id,user_no,amount,SUM(amount) OVER win2 AS Total,AVG(amount) OVER win1 AS Avg
FROM t_order_sales
WHERE user_no IN ('C0002','V0002')
WINDOW win1 AS (win3),win2 AS (ORDER BY user_no),win3 AS (win2 PARTITION BY user_no);
GOorder_id user_no amount Total Avg
-------- -------- ----------- ----------- -----------
1002 C0002 3510 8531 4265
1006 C0002 5021 8531 4265
1010 V0002 8870 17401 8870(3 行受影响)
6、总结
Window 子句通常与 OVER() 子句相关，它经常与窗口函数（如 ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG(), MIN(), MAX() 等函数的 OVER() 版本）一起使用。这些窗口函数允许你在结果集的每个行上执行计算，这些计算会考虑与当前行相关的其他行（例如，前面的几个示例）
SQL Server 中与窗口函数相关的一般知识或最佳实践，那么以下是一些要点：
- 使用 OVER() 子句：通过为窗口函数指定 OVER() 子句，你可以定义窗口的边界和排序顺序。
- PARTITION BY：在 OVER() 子句中使用 PARTITION BY 可以将数据分成多个分区，并为每个分区独立地计算窗口函数。
- ORDER BY：在 OVER() 子句中使用 ORDER BY 可以定义窗口中行的顺序。这对于需要基于行顺序（如累积总和或运行平均值）的计算特别有用。
- 常见的窗口函数：包括 ROW_NUMBER(), RANK(), DENSE_RANK(), SUM() OVER(), AVG() OVER(), MIN() OVER(), MAX() OVER() 等。
- 性能考虑：窗口函数可能会对性能产生影响，特别是在处理大量数据时。确保你的查询已针对性能进行了优化，并考虑使用索引、分区等策略来加速查询。
- 兼容性：不同的数据库系统对窗口函数的支持可能有所不同。在将使用窗口函数的查询从一个系统迁移到另一个系统时，请务必检查目标系统的兼容性。
