你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站,请访问 https://docs.azure.cn

DATE_BUCKET (Transact-SQL)

重要

Azure SQL Edge 将于 2025 年 9 月 30 日停用。 有关详细信息和迁移选项,请参阅 停用通知

注意

Azure SQL Edge 不再支持 ARM64 平台。

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

有关所有 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 参数组合使用的存储桶的宽度。 这表示从原始时间开始的 dataPart 存储桶的宽度。 此参数必须是整数值。

date

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

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

对于 dateDATE_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 返回与 datePartnumber 参数相对应的最晚日期或时间值。 例如,在以下表达式中,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 开始的 6276 周。

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 是从原始日期开始的 6276 周,6276 可被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);

备注

在以下子句中使用 DATE_BUCKET

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

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 参数

origindate 参数的数据类型必须相同。 如果使用不同的数据类型,则会生成错误。

示例

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 形参的实参 。 这些示例是使用 AdventureWorksDW2019 数据库生成的。

将用户定义的变量指定为 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

(1 row affected)

将一列指定为 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

(1 row affected)

将标量子查询和标量函数指定为 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);