DATE_BUCKET 函数 SQL Server 中的新 T-SQL 增强功能

一. 引言

随着SQL Server 2022的发布，微软引入了多个增强功能和新特性，以提高性能、可用性和功能性。其中，DATE_BUCKET函数是一个便捷的工具，特别适合开发者和数据专业人员处理时间数据。这一功能简化了日期分组和时间间隔管理，使得聚合和分析时间数据变得更加简单高效。

二. 理解DATE_BUCKET函数

什么是DATE_BUCKET？

DATE_BUCKET函数旨在帮助用户将日期分组并截断为固定的时间间隔（或“桶”）。这使得在日、周、月、季度或年等一致的时间段内进行数据分组和分析变得更加容易，尤其适用于生成时间基础的报告和数据可视化。

语法

DATE_BUCKET (datepart, number, date [, origin ] )

• datepart: 指定要分组的日期部分，如日、周、月等。
• number: 时间桶的大小，必须为整数。可以是年、月、周、日、小时、分钟、秒、毫秒等。
• date: 要根据指定的间隔和datepart进行截断和分组的日期。

返回类型

该函数返回一个日期/时间值，向下舍入到指定桶的起始位置。这使得时间数据能够被逻辑地分组到不同的时间间隔中。

DATE_BUCKET与其他T-SQL函数的区别

其他T-SQL函数，如DATEADD、DATEDIFF和DATEPART，通常用于操控日期、提取日期的部分或计算日期之间的差异。然而，这些函数并不支持固定时间间隔（桶）的概念。而DATE_BUCKET则允许将日期分组为常规间隔，对于生成基于时间的报告非常重要。

三. 示例

示例1：按月分组

以下示例将日期按两个月的间隔进行分组，从2024年1月1日开始。

DECLARE @DateOrigin date = '2024-01-01'  -- 设置日期起点为2024年1月1日
SELECT
    '1/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-01-01'), @DateOrigin),  -- 第一桶
    '1/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-02-01'), @DateOrigin),  -- 第二桶
    '2/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-03-01'), @DateOrigin),  -- 第三桶
    '2/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-04-01'), @DateOrigin),  -- 第四桶
    '1/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-05-01'), @DateOrigin),  -- 第五桶
    '1/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-06-01'), @DateOrigin),  -- 第六桶
    '2/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-07-01'), @DateOrigin),  -- 第七桶
    '2/2m' = DATE_BUCKET(MONTH, 2, CONVERT(date, '2024-08-01'), @DateOrigin)   -- 第八桶
GO

示例2：按周分组

以下示例将日期按两周的间隔进行分组，从2024年1月1日开始。

DECLARE @DateOrigin date = '2024-01-01'  -- 设置日期起点为2024年1月1日
SELECT
    '1/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-01-01'), @DateOrigin),  -- 第一桶
    '1/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-01-08'), @DateOrigin),  -- 第二桶
    '2/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-01-15'), @DateOrigin),  -- 第三桶
    '2/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-01-22'), @DateOrigin),  -- 第四桶
    '1/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-01-29'), @DateOrigin),  -- 第五桶
    '1/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-02-05'), @DateOrigin),  -- 第六桶
    '2/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-02-12'), @DateOrigin),  -- 第七桶
    '2/2w' = DATE_BUCKET(WEEK, 2, CONVERT(date, '2024-02-19'), @DateOrigin)   -- 第八桶
GO

示例3：按天分组

以下示例将日期按两天的间隔进行分组，从2024年1月1日开始。

DECLARE @DateOrigin date = '2024-01-01'  -- 设置日期起点为2024年1月1日
SELECT
    '1/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-01'), @DateOrigin),  -- 第一桶
    '2/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-02'), @DateOrigin),  -- 第二桶
    '1/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-03'), @DateOrigin),  -- 第三桶
    '2/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-04'), @DateOrigin),  -- 第四桶
    '1/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-05'), @DateOrigin),  -- 第五桶
    '2/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-06'), @DateOrigin),  -- 第六桶
    '1/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-07'), @DateOrigin),  -- 第七桶
    '2/2d' = DATE_BUCKET(DAY, 2, CONVERT(date, '2024-01-08'), @DateOrigin)   -- 第八桶
GO

四. 用例

1. 按周分组销售数据

假设我们想要分析AdventureWorks2022中的销售订单，并将订单按周间隔进行分组。使用DATE_BUCKET，我们可以轻松地根据Sales.SalesOrderHeader表中的OrderDate创建这些周桶。

以下查询示例：

USE AdventureWorks2022  -- 使用AdventureWorks2022数据库
GO

SELECT
    DATE_BUCKET(WEEK, 1, OrderDate) AS OrderWeek,  -- 按周分组
    COUNT(SalesOrderID) AS TotalOrders,  -- 统计订单总数
    SUM(TotalDue) AS TotalSales  -- 统计总销售额
FROM
    Sales.SalesOrderHeader
GROUP BY
    DATE_BUCKET(WEEK, 1, OrderDate)  -- 根据周进行分组
ORDER BY
    OrderWeek  -- 按订单周排序

2. 按月分析销售数据

对于长期趋势，我们可能希望按月聚合销售数据。DATE_BUCKET使得按月分组数据变得简单。

以下查询示例：

USE AdventureWorks2022  -- 使用AdventureWorks2022数据库
GO

SELECT
    DATE_BUCKET(MONTH, 1, OrderDate) AS OrderMonth,  -- 按月分组
    COUNT(SalesOrderID) AS TotalOrders,  -- 统计订单总数
    SUM(TotalDue) AS TotalSales  -- 统计总销售额
FROM
    Sales.SalesOrderHeader
GROUP BY
    DATE_BUCKET(MONTH, 1, OrderDate)  -- 根据月进行分组
ORDER BY
    OrderMonth  -- 按订单月排序

3. 自定义间隔分组（如10天桶）

虽然DATE_BUCKET允许使用标准间隔（如周或月），但你也可以将日期分组为自定义间隔。例如，如果你想创建基于10天的报告，DATE_BUCKET同样可以处理。

以下查询示例：

USE AdventureWorks2022  -- 使用AdventureWorks2022数据库
GO

SELECT
    DATE_BUCKET(DAY, 10, OrderDate) AS OrderPeriod,  -- 按10天间隔分组
    COUNT(SalesOrderID) AS TotalOrders

,  -- 统计订单总数
    SUM(TotalDue) AS TotalSales  -- 统计总销售额
FROM
    Sales.SalesOrderHeader
GROUP BY
    DATE_BUCKET(DAY, 10, OrderDate)  -- 根据10天进行分组
ORDER BY
    OrderPeriod  -- 按订单期间排序

五. 将DATE_BUCKET与其他函数进行比较

1. DATEADD和DATEDIFF

过去，SQL开发者通常会使用DATEADD和DATEDIFF的组合来按年分组日期。例如，使用这些函数按年分组销售数据：

USE AdventureWorks2022  -- 使用AdventureWorks2022数据库
GO

SELECT
    DATEADD(YEAR, DATEDIFF(YEAR, 0, OrderDate), 0) AS OrderYear,  -- 计算订单年份
    COUNT(SalesOrderID) AS TotalOrders,  -- 统计订单总数
    SUM(TotalDue) AS TotalSales  -- 统计总销售额
FROM
    Sales.SalesOrderHeader
GROUP BY
    DATEADD(YEAR, DATEDIFF(YEAR, 0, OrderDate), 0)  -- 根据年份进行分组

2. 在日期计算中使用FLOOR或CEILING

另一种将日期分组的方法是结合使用FLOOR或CEILING进行日期计算。虽然这种方法有效，但通常不够直观且难以维护。

以下示例使用FLOOR按周分组日期：

USE AdventureWorks2022  -- 使用AdventureWorks2022数据库
GO

SELECT
    FLOOR(DATEDIFF(DAY, '1900-01-01', OrderDate) / 7) AS WeekNumber,  -- 计算周数
    COUNT(SalesOrderID) AS TotalOrders  -- 统计订单总数
FROM
    Sales.SalesOrderHeader
GROUP BY
    FLOOR(DATEDIFF(DAY, '1900-01-01', OrderDate) / 7)  -- 根据周数进行分组

六. 使用DATE_BUCKET的好处

1. 简化代码

使用DATE_BUCKET的一个显著好处是与旧方法相比，代码更加简洁。现在，你可以用一个可读的函数实现同样的结果，而不必使用复杂的表达式。

2. 灵活性和强大功能

DATE_BUCKET提供了一个强大的工具，可以灵活地聚合时间数据。无论你需要按周、按月或自定义间隔（如十天或15分钟）分组，DATE_BUCKET都能轻松处理。

3. 提高性能

通过原生支持时间基础的间隔，DATE_BUCKET在性能上优于依赖复杂日期操作函数的替代方案。这些传统方法通常需要多次计算和转换，以达到类似的结果，从而增加了复杂性和计算开销。

七. 结论

DATE_BUCKET函数在SQL Server 2022中的引入，对于频繁处理时间数据的SQL开发者、数据工程师和数据库管理员来说，标志着一个重要的增强。通过简化将日期分组为一致间隔的过程，DATE_BUCKET不仅使查询更易读、更易维护，还通过减少对复杂手动日期操作函数的依赖，提高了性能。凭借其简化查询、改善代码可维护性和优化性能的能力，DATE_BUCKET成为SQL Server工具箱中一个宝贵的补充，帮助专业人员更好地管理和分析时间序列数据。