Поделиться через

DATE_BUCKET (Transact-SQL)

  • Статья

Область применения: SQL Server 2022 (16.x) База данных SQL Azure Управляемый экземпляр SQL Azure 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_BUCKET не принимает эквивалентные переменные, определяемые пользователем, для аргументов datepart.

datepart Abbreviations
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

number

Целое число, которое определяет ширину контейнера в сочетании с аргументом datepart. Представляет ширину контейнеров datepart от исходного времени. Этот аргумент не может быть отрицательным целым числом.

date

Выражение, которое может быть разрешено в одно из следующих значений.

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

Для date (дата), DATE_BUCKET примет выражение столбца, выражение или пользовательскую переменную, если они разрешаются для любого из вышеперечисленных типов данных.

origin

Необязательное выражение, которое может быть разрешено для одного из следующих значений.

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

Тип данных для origin должен соответствовать типу данных параметра date.

DATE_BUCKET использует значение даты аргумента origin по умолчанию 1900-01-01 00:00:00.000, т.е. 00:00 понедельника 1 января 1900 года. В противном случае значение origin указывается для функции.

Возвращаемый тип

Тип данных возвращаемого значения для этого метода является динамическим. Тип возвращаемого значения зависит от типа аргумента, переданного в date. Если для date предоставляется другой тип допустимых входных данных, DATE_BUCKET возвращает тот же тип данных. DATE_BUCKET вызывает ошибку, если для параметра date указан строковый литерал.

Возвращаемые значения

Основные сведения о выходных данных из 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 составляет 6,276 недель от значения аргумента origin 1900-01-01 00:00:00.000.

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, что составляет 6275 недель от стандартного времени начала 1900-01-01 00:00:00.000.

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

Выходные данные для приведенного ниже выражения — 2020-06-09 00:00:00.0000000, что составляет 75 недель от указанного времени аргумента origin 2019-01-01 00:00:00.

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);

Если в функцию DATE_BUCKET передается отрицательное значение для "number", будет возвращена следующая ошибка:

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

Примеры

А. Вычисление DATE_BUCKET с шириной контейнера, равной 1, от времени аргумента origin

Каждый из этих операторов увеличивается DATE_BUCKET с шириной контейнера 1 от времени источника:

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 скалярной системной функции

В этом примере для аргумента date указано значение SYSDATETIME. Точное возвращаемое значение зависит от дня и времени выполнения инструкции.

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

Вот результирующий набор.

2020-03-02 00:00:00.0000000

Указание в качестве аргументов number и date скалярных вложенных запросов и скалярных функций

В этом примере в качестве аргументов для number и date используются скалярные вложенные запросы MAX(OrderDate). (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) является искусственным аргументом для числового параметра, показывающим способ выбора аргумента number из списка значений.

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

Указание в качестве аргументов number и date числовых выражений и скалярных системных функций

В этом примере в качестве аргументов для number и date используется числовое выражение((10/2)) и скалярные системные функции (SYSDATETIME).

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. Использование значения аргумента origin не по умолчанию

В этом примере используется значение origin, отличное от значения по умолчанию, для создания контейнеров даты.

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);

См. также