窗口增强功能SQL Server 中新增的 T-SQL 增强功能
SQL Server 2022引入了几项重要的T-SQL增强功能,这些功能简化了数据分析、窗口函数和滑动聚合的数据操作。SQL开发者、数据工程师和数据库管理员可以利用这些新特性来简化复杂的数据操作,提高查询的可读性,并优化查询性能。
一、使用OVER with ORDER BY进行聚合函数操作
在SQL Server 2012中,微软引入了使用OVER with ORDER BY进行聚合函数操作的功能,这使得运行和滑动聚合成为可能。这个特性允许你在有序数据集上计算累积总和、移动平均值和其他指标,而无需自连接或相关子查询。
示例:按账户计算交易的累计总额
以下查询按AccountID计算每笔交易的累计总额,并按TransactionDate排序。
SELECT
AccountID, -- 账户ID
TransactionDate, -- 交易日期
Amount, -- 交易金额
SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate) AS RunningTotal -- 计算累计总额
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate; -- 按账户ID和交易日期排序
输出结果
AccountID | TransactionDate | Amount | RunningTotal
---------|----------------|--------|--------------
1 | 2023-01-01 | 100.00 | 100.00
1 | 2023-01-02 | 150.00 | 250.00
1 | 2023-01-03 | 200.00 | 450.00
1 | 2023-01-04 | NULL | 450.00
1 | 2023-01-05 | 300.00 | 750.00
2 | 2023-01-01 | 500.00 | 500.00
2 | 2023-01-02 | 700.00 | 1200.00
2 | 2023-01-03 | NULL | 1200.00
2 | 2023-01-04 | 800.00 | 2000.00
2 | 2023-01-05 | 900.00 | 2900.00
应用场景
累计总额有助于计算每个账户的累积支出,这在财务报告或客户分析中很常见。
二、带窗口行限制的滑动聚合
滑动聚合是一种特定类型的窗口计算,其中只考虑固定数量的先前或后续行。在SQL Server 2022中,这对于在指定行窗口上计算滚动平均值或总和特别有用。
示例:三行滑动总和
在此示例中,我们计算每个账户最近三笔交易的滑动总和。
SELECT
AccountID, -- 账户ID
TransactionDate, -- 交易日期
Amount, -- 交易金额
SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate ROWS 2 PRECEDING) AS SlidingSum -- 计算三行滑动总和
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate; -- 按账户ID和交易日期排序
输出结果
AccountID | TransactionDate | Amount | SlidingSum
---------|----------------|--------|------------
1 | 2023-01-01 | 100.00 | 100.00
1 | 2023-01-02 | 150.00 | 250.00
1 | 2023-01-03 | 200.00 | 450.00
1 | 2023-01-04 | NULL | 450.00
1 | 2023-01-05 | 300.00 | 600.00
2 | 2023-01-01 | 500.00 | 500.00
2 | 2023-01-02 | 700.00 | 1200.00
2 | 2023-01-03 | NULL | 1200.00
2 | 2023-01-04 | 800.00 | 2000.00
2 | 2023-01-05 | 900.00 | 2700.00
应用场景
财务分析师可能会使用滑动聚合来计算移动平均值,这对于在定义期间内平滑趋势非常有用。
三、使用WINDOW子句消除代码重复
SQL Server 2022引入的WINDOW子句允许你一次定义一个窗口框架并多次引用它。这个特性有助于减少代码重复并提高可读性,特别是在包含多个窗口计算的查询中。
示例:定义和使用窗口
在此示例中,我们使用WINDOW子句一次定义一个窗口并引用它来计算累计总和和滚动平均值。
SELECT
AccountID, -- 账户ID
TransactionDate, -- 交易日期
Amount, -- 交易金额
SUM(Amount) OVER w AS RunningTotal, -- 计算累计总和
AVG(Amount) OVER w AS RollingAverage -- 计算滚动平均值
FROM dbo.AccountTransactions
WINDOW w AS (PARTITION BY AccountID ORDER BY TransactionDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) -- 定义窗口
ORDER BY AccountID, TransactionDate; -- 按账户ID和交易日期排序
输出结果
AccountID | TransactionDate | Amount | RunningTotal | RollingAverage
---------|----------------|--------|--------------|----------------
1 | 2023-01-01 | 100.00 | 100.00 | 100.00
1 | 2023-01-02 | 150.00 | 250.00 | 125.00
1 | 2023-01-03 | 200.00 | 450.00 | 150.00
1 | 2023-01-04 | NULL | 450.00 | 150.00
1 | 2023-01-05 | 300.00 | 750.00 | 187.50
2 | 2023-01-01 | 500.00 | 500.00 | 500.00
2 | 2023-01-02 | 700.00 | 1200.00 | 600.00
2 | 2023-01-03 | NULL | 1200.00 | 600.00
2 | 2023-01-04 | 800.00 | 2000.00 | 700.00
2 | 2023-01-05 | 900.00 | 2900.00 | 775.00
应用场景
WINDOW子句在分析查询中特别有价值,其中多个窗口函数需要使用相同的划分和排序标准。
四、在FIRST_VALUE和LAST_VALUE函数中使用IGNORE NULLS
SQL Server 2022引入的IGNORE NULLS选项允许窗口函数如FIRST_VALUE和LAST_VALUE跳过NULL值,使得在包含缺失数据的序列中检索非NULL值变得更容易。
示例:获取每个账户的最后一个非NULL值
在此示例中,我们使用LAST_VALUE与IGNORE NULLS来获取每个账户最近的非NULL交易金额。
SELECT
AccountID, -- 账户ID
TransactionDate, -- 交易日期
Amount, -- 交易金额
LAST_VALUE(Amount) IGNORE NULLS OVER (PARTITION BY AccountID ORDER BY TransactionDate) AS LastNonNullAmount -- 获取最后一个非NULL值
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate; -- 按账户ID和交易日期排序
输出结果
AccountID | TransactionDate | Amount | LastNonNullAmount
---------|----------------|--------|-------------------
1 | 2023-01-01 | 100.00 | 300.00
1 | 2023-01-02 | 150.00 | 300.00
1 | 2023-01-03 | 200.00 | 300.00
1 | 2023-01-04 | NULL | 300.00
1 | 2023-01-05 | 300.00 | 300.00
2 | 2023-01-01 | 500.00 | 900.00
2 | 2023-01-02 | 700.00 | 900.00
2 | 2023-01-03 | NULL | 900.00
2 | 2023-01-04 | 800.00 | 900.00
2 | 2023-01-05 | 900.00 | 900.00
应用场景
这在处理不完整或间歇性数据时特别有用,例如填充缺失的股票价格、温度读数或任何基于时间的连续指标。
五、使用IGNORE NULLS选项处理聚合中的NULL值
SQL Server 2022的IGNORE NULLS选项也可以应用于SUM和AVG等函数,使得在包含缺失值的数据集中处理数据更加容易,而不影响计算结果。
示例:计算忽略NULL值的平均交易金额
以下查询计算每个账户的平均交易金额,同时忽略NULL值。
SELECT
AccountID, -- 账户ID
TransactionDate, -- 交易日期
Amount, -- 交易金额
SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) /
NULLIF(COUNT(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0)
AS AverageAmount -- 计算平均金额
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate; -- 按账户ID和交易日期排序
输出结果
AccountID | TransactionDate | Amount | AverageAmount
---------|----------------|--------|--------------
1 | 2023-01-01 | 100.00 | 187.50
1 | 2023-01-02 | 150.00 | 187.50
1 | 2023-01-03 | 200.00 | 187.50
1 | 2023-01-04 | NULL | 187.50
1 | 2023-01-05 | 300.00 | 187.50
2 | 2023-01-01 | 500.00 | 687.50
2 | 2023-01-02 | 700.00 | 687.50
2 | 2023-01-03 | NULL | 687.50
2 | 2023-01-04 | 800.00 | 687.50
2 | 2023-01-05 | 900.00 | 687.50
应用场景
NULL值在数据集中很常见,能够在聚合函数中直接忽略它们简化了代码并提高了结果的准确性。
结论
SQL Server 2022为窗口函数带来了强大的增强功能,使其更加灵活,能够处理SQL开发中的实际场景。从运行总和、滑动窗口到使用IGNORE NULLS处理NULL值,这些功能提高了窗口计算的清晰度、效率和准确性。通过利用这些新工具,SQL开发者、数据工程师和DBAs可以构建更具表现力和高性能的SQL查询,使SQL Server 2022成为数据密集型应用的强大选择。