SQLServer2022新特性Window子句

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