DATE_BUCKET (Transact-SQL)

  • 项目

适用于： SQL Server 2022 (16.x) Azure SQL 数据库Azure SQL 托管实例 Azure SQL Edge

此函数从 origin 参数定义的时间戳或默认原始值 1900-01-01 00:00:00.000（如果未指定 origin 参数）返回与每个日期/时间存储桶的起始值相对应的日期/时间值。

有关所有 Transact-SQL 日期和时间数据类型及函数的概述，请参阅日期和时间数据类型及函数 (Transact-SQL)。

Transact-SQL 语法约定

语法

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

参数

datepart

与 number 参数一起使用的 date 部分，例如年、月、日、分钟、秒。

对于 datepart 参数，DATE_BUCKET 不接受用户定义的变量等效项。

datepart 缩写形式
day dd, d
week wk, ww
month mm, m
quarter qq, q
year yy, yyyy
hour hh
minute mi, n
second ss, s
millisecond ms

数字

一个整数，用于确定与 datePart 参数组合使用的存储桶的宽度。 这表示从原始时间开始的 datepart 存储桶的宽度。 此参数不能是负整数值。

date

可解析为下列值之一的表达式：

  • date
  • datetime
  • datetime2
  • datetimeoffset
  • smalldatetime
  • time

对于 date，DATE_BUCKET 将接受列表达式、表达式或用户定义的变量，前提是它们解析为上面提到的任何数据类型。

origin

可解析为下列值之一的可选表达式：

  • date
  • datetime
  • datetime2
  • datetimeoffset
  • smalldatetime
  • time

origin 的数据类型应与 date 参数的数据类型相匹配。

如果没有为该函数指定 origin 值，DATE_BUCKET 将使用默认原始日期值 1900-01-01 00:00:00.000，即 1900 年 1 月 1 日星期一中午 12:00。

返回类型

此方法的返回值数据类型是动态的。 返回类型取决于为 date 提供的参数。 如果为 date 提供了有效的输入数据类型，则 DATE_BUCKET 返回相同的数据类型。 如果为 date 参数指定了字符串文本，则 DATE_BUCKET 会引发错误。

返回值

了解来自 DATE_BUCKET 的输出

DATE_BUCKET 返回与 datepart 和 number 参数相对应的最晚日期或时间值。 例如，在下面的表达式中，DATE_BUCKET 将返回输出值 2020-04-13 00:00:00.0000000，因为输出是基于从默认原始时间 1900-01-01 00:00:00.000 开始的一周存储桶计算得出的。 2020-04-13 00:00:00.0000000 值是从原始值 1900-01-01 00:00:00.000 开始的 6,276 周。

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 1, @date);

对于下面的所有表达式，将返回相同的输出值 2020-04-13 00:00:00.0000000。 这是因为 2020-04-13 00:00:00.0000000 是从原始日期开始的 6,276 周，6,276 可被 2、3、4 和 6 整除。

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 2, @date);
SELECT DATE_BUCKET(WEEK, 3, @date);
SELECT DATE_BUCKET(WEEK, 4, @date);
SELECT DATE_BUCKET(WEEK, 6, @date);

以下表达式的输出为 2020-04-06 00:00:00.0000000，即，从默认原始时间 1900-01-01 00:00:00.000 开始的 6275 周。

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 5, @date);

以下表达式的输出为 2020-06-09 00:00:00.0000000，即，从指定原始时间 2019-01-01 00:00:00 开始的 75 周。

DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';
SELECT DATE_BUCKET(WEEK, 5, @date, @origin);

datepart 参数

dayofyear、day 和 weekday 返回相同的值 。 每个 datepart 及其缩写都返回相同的值。

number 参数

number 参数不能超出正 int 值的范围。 在以下语句中，number 的参数超出 int 的范围（超出 1）。 以下语句返回错误消息 Msg 8115, Level 16, State 2, Line 2. Arithmetic overflow error converting expression to data type int.

DECLARE @date DATETIME2 = '2020-04-30 00:00:00';
SELECT DATE_BUCKET(DAY, 2147483648, @date);

如果 number 的负值传递到 DATE_BUCKET 函数，将返回以下错误。

Msg 9834, Level 16, State 1, Line 1
Invalid bucket width value passed to DATE_BUCKET function. Only positive values are allowed.

date 参数

DATE_BUCKET 返回与 date 参数的数据类型相对应的基值。 在下面的示例中，将返回具有 datetime2 数据类型的输出值。

SELECT DATE_BUCKET(DAY, 10, SYSUTCDATETIME());

origin 参数

origin 和 date 参数的数据类型必须相同。 如果使用不同的数据类型，则会生成错误。

备注

在以下子句中使用 DATE_BUCKET：

  • GROUP BY
  • HAVING
  • ORDER BY
  • SELECT <list>
  • WHERE

示例

A. 从原始时间开始计算存储桶宽度为 1 的 DATE_BUCKET

其中每个语句都将从原始时间开始增加存储桶宽度为 1 的 DATE_BUCKET：

DECLARE @date DATETIME2 = '2020-04-30 21:21:21';
SELECT 'Week', DATE_BUCKET(WEEK, 1, @date)
UNION ALL
SELECT 'Day', DATE_BUCKET(DAY, 1, @date)
UNION ALL
SELECT 'Hour', DATE_BUCKET(HOUR, 1, @date)
UNION ALL
SELECT 'Minutes', DATE_BUCKET(MINUTE, 1, @date)
UNION ALL
SELECT 'Seconds', DATE_BUCKET(SECOND, 1, @date);

结果集如下。

Week    2020-04-27 00:00:00.0000000
Day     2020-04-30 00:00:00.0000000
Hour    2020-04-30 21:00:00.0000000
Minutes 2020-04-30 21:21:00.0000000
Seconds 2020-04-30 21:21:21.0000000

B. 使用表达式作为 number 和 date 形参的实参

以下示例使用不同类型的表达式作为 number 和 date 形参的实参 。

这些示例是使用 AdventureWorksDW2022 数据库生成的。

将用户定义的变量指定为 number 和 date

此示例将用户定义的变量指定为 number 和 date 的参数 ：

DECLARE @days INT = 365,
    @datetime DATETIME2 = '2000-01-01 01:01:01.1110000';
    /* 2000 was a leap year */;
SELECT DATE_BUCKET(DAY, @days, @datetime);

结果集如下。

1999-12-08 00:00:00.0000000

将一列指定为 date

在下面的示例中，我们将计算基于每周日期存储桶分组的 OrderQuantity 的总和及 UnitPrice 的总和。

SELECT DATE_BUCKET(WEEK, 1, CAST(ShipDate AS DATETIME2)) AS ShippedDateBucket
    , SUM(OrderQuantity) AS SumOrderQuantity
    , SUM(UnitPrice) AS SumUnitPrice
FROM dbo.FactInternetSales FIS
WHERE ShipDate BETWEEN '2011-01-03 00:00:00.000'
        AND '2011-02-28 00:00:00.000'
GROUP BY DATE_BUCKET(WEEK, 1, CAST(ShipDate AS DATETIME2))
ORDER BY ShippedDateBucket;

结果集如下。

ShippedDateBucket           SumOrderQuantity SumUnitPrice
--------------------------- ---------------- ---------------------
2011-01-03 00:00:00.0000000 21               65589.7546
2011-01-10 00:00:00.0000000 27               89938.5464
2011-01-17 00:00:00.0000000 31               104404.9064
2011-01-24 00:00:00.0000000 36               118525.6846
2011-01-31 00:00:00.0000000 39               123555.431
2011-02-07 00:00:00.0000000 35               109342.351
2011-02-14 00:00:00.0000000 32               107804.8964
2011-02-21 00:00:00.0000000 37               119456.3428
2011-02-28 00:00:00.0000000 9                28968.6982

将标量系统函数指定为 date

此示例指定 SYSDATETIME 为 date 。 返回的确切值取决于语句执行的日期和时间：

SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());

结果集如下。

2020-03-02 00:00:00.0000000

将标量子查询和标量函数指定为 number 和 date

此示例使用标量子查询 MAX(OrderDate) 作为 number 和 date 的参数 。 (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) 充当 number 形参的假实参，用来说明如何从值列表中选择 number 实参 。

SELECT DATE_BUCKET(WEEK, (
    SELECT TOP 1 CustomerKey
    FROM dbo.DimCustomer
    WHERE GeographyKey > 100
    ), (
    SELECT MAX(OrderDate)
    FROM dbo.FactInternetSales
));

将数值表达式和标量系统函数指定为 number 和 date

此示例使用数值表达式 ((10/2)) 和标量系统函数 (SYSDATETIME) 作为 number 和 date 的参数。

SELECT DATE_BUCKET(WEEK, (10/2), SYSDATETIME());

将聚合开窗函数指定为 number

此示例使用聚合开窗函数作为 number 的参数 。

SELECT DISTINCT DATE_BUCKET(DAY, 30, CAST([ShipDate] AS DATETIME2)) AS DateBucket
    , FIRST_VALUE([SalesOrderNumber]) OVER (
        ORDER BY DATE_BUCKET(DAY, 30, CAST([ShipDate] AS DATETIME2))
        ) AS First_Value_In_Bucket
    , LAST_VALUE([SalesOrderNumber]) OVER (
        ORDER BY DATE_BUCKET(DAY, 30, CAST([ShipDate] AS DATETIME2))
        ) AS Last_Value_In_Bucket
FROM [dbo].[FactInternetSales]
WHERE ShipDate BETWEEN '2011-01-03 00:00:00.000'
        AND '2011-02-28 00:00:00.000'
ORDER BY DateBucket;
GO

C. 使用非默认原始值

本示例使用非默认原始值来生成日期 Bucket。

DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';
SELECT DATE_BUCKET(HOUR, 2, @date, @origin);

另请参阅