DATE_BUCKET (Transact-SQL)
Область применения:
SQL Server 2022 (16.x) Azure DatabaseAzure SQL Managed Instance 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 | Аббревиатуры |
|---|---|
| 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);
См. также
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по