SQLServer2022新特性Window子句

2024-05-28 16:12
文章标签 特性 window 子句 sqlserver2022

本文主要是介绍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 ('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() 等。
  • 性能考虑：窗口函数可能会对性能产生影响，特别是在处理大量数据时。确保你的查询已针对性能进行了优化，并考虑使用索引、分区等策略来加速查询。
  • 兼容性：不同的数据库系统对窗口函数的支持可能有所不同。在将使用窗口函数的查询从一个系统迁移到另一个系统时，请务必检查目标系统的兼容性。

这篇关于SQLServer2022新特性Window子句的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1011049。

相关文章

新特性抢先看! Ubuntu 25.04 Beta 发布:Linux 6.14 内核

新特性抢先看! Ubuntu 25.04 Beta 发布:Linux 6.14 内核

《新特性抢先看!Ubuntu25.04Beta发布:Linux6.14内核》Canonical公司近日发布了Ubuntu25.04Beta版，这一版本被赋予了一个活泼的代号——“Plu... Canonical 昨日（3 月 27 日）放出了 Beta 版 Ubuntu 25.04 系统镜像，代号“Pluc
阅读更多...
如何使用Python实现一个简单的window任务管理器

如何使用Python实现一个简单的window任务管理器

《如何使用Python实现一个简单的window任务管理器》这篇文章主要为大家详细介绍了如何使用Python实现一个简单的window任务管理器,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起... 任务管理器效果图完整代码import tkinter as tkfrom tkinter i
阅读更多...
Rust中的Drop特性之解读自动化资源清理的魔法

Rust中的Drop特性之解读自动化资源清理的魔法

《Rust中的Drop特性之解读自动化资源清理的魔法》Rust通过Drop特性实现了自动清理机制,确保资源在对象超出作用域时自动释放,避免了手动管理资源时可能出现的内存泄漏或双重释放问题,智能指针如B... 目录自动清理机制：Rust 的析构函数提前释放资源：std::mem::drop android的妙
阅读更多...
Window Server创建2台服务器的故障转移群集的图文教程

Window Server创建2台服务器的故障转移群集的图文教程

《WindowServer创建2台服务器的故障转移群集的图文教程》本文主要介绍了在WindowsServer系统上创建一个包含两台成员服务器的故障转移群集,文中通过图文示例介绍的非常详细,对大家的... 目录一、 准备条件二、在ServerB安装故障转移群集三、在ServerC安装故障转移群集，操作与Ser
阅读更多...
Window Server2016加入AD域的方法步骤

Window Server2016加入AD域的方法步骤

《WindowServer2016加入AD域的方法步骤》：本文主要介绍WindowServer2016加入AD域的方法步骤,包括配置DNS、检测ping通、更改计算机域、输入账号密码、重启服务... 目录一、 准备条件二、配置ServerB加入ServerA的AD域（test.ly）三、查看加入AD域后的变
阅读更多...
Window Server2016 AD域的创建的方法步骤

Window Server2016 AD域的创建的方法步骤

《WindowServer2016AD域的创建的方法步骤》本文主要介绍了WindowServer2016AD域的创建的方法步骤,文中通过图文介绍的非常详细,对大家的学习或者工作具有一定的参考学习价... 目录一、准备条件二、在ServerA服务器中常见AD域管理器：三、创建AD域，域地址为“test.ly”
阅读更多...
五大特性引领创新! 深度操作系统 deepin 25 Preview预览版发布

五大特性引领创新! 深度操作系统 deepin 25 Preview预览版发布

《五大特性引领创新!深度操作系统deepin25Preview预览版发布》今日，深度操作系统正式推出deepin25Preview版本，该版本集成了五大核心特性：磐石系统、全新DDE、Tr... 深度操作系统今日发布了 deepin 25 Preview，新版本囊括五大特性：磐石系统、全新 DDE、Tree
阅读更多...
ActiveMQ—消息特性（延迟和定时消息投递）

ActiveMQ—消息特性（延迟和定时消息投递）

ActiveMQ消息特性：延迟和定时消息投递（Delay and Schedule Message Delivery） 转自：http://blog.csdn.net/kimmking/article/details/8443872 有时候我们不希望消息马上被broker投递出去，而是想要消息60秒以后发给消费者，或者我们想让消息没隔一定时间投递一次，一共投递指定的次数。。。 类似
阅读更多...
PostgreSQL核心功能特性与使用领域及场景分析

PostgreSQL核心功能特性与使用领域及场景分析

PostgreSQL有什么优点？ 开源和免费 PostgreSQL是一个开源的数据库管理系统，可以免费使用和修改。这降低了企业的成本，并为开发者提供了一个活跃的社区和丰富的资源。 高度兼容 PostgreSQL支持多种操作系统（如Linux、Windows、macOS等）和编程语言（如C、C++、Java、Python、Ruby等），并提供了多种接口（如JDBC、ODBC、ADO.NET等
阅读更多...
详解Tomcat 7的七大新特性和新增功能(1)

详解Tomcat 7的七大新特性和新增功能(1)

http://developer.51cto.com/art/201009/228537.htm http://tomcat.apache.org/tomcat-7.0-doc/index.html  Apache发布首个Tomcat 7版本已经发布了有一段时间了，Tomcat 7引入了许多新功能，并对现有功能进行了增强。很多文章列出了Tomcat 7的新功能，但大多数并没有详细解释它们
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2