DATE_BUCKET (Transact-SQL)
Aplica-se a:
SQL Server 2022 (16.x) Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure SQL do Azure no Edge
Essa função retornará o valor de datetime correspondente ao início de cada bucket de datetime, do carimbo de data/hora definido pelo parâmetro origin ou o valor de origem padrão de 1900-01-01 00:00:00.000 se o parâmetro de origem não for especificado.
Confira Funções e tipos de dados de data e hora (Transact-SQL) para ter uma visão geral de todas as funções e tipos de dados de data e hora do Transact-SQL.
Convenções de sintaxe de Transact-SQL
Sintaxe
DATE_BUCKET (datepart, number, date [, origin ] )
Argumentos
datepart
A parte da data usada com o parâmetro number. Por exemplo, ano, mês, dia, minuto, segundo.
DATE_BUCKET não aceita os equivalentes de variável definidos pelo usuário para os argumentos datepart.
| datepart | Abreviações |
|---|---|
| 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
O número inteiro que decide a largura do bucket combinado com o argumento datepart. Representa a largura dos buckets de datepart a partir da hora de origem. Esse argumento não pode ser um valor inteiro negativo.
date
Uma expressão que pode resolver um dos seguintes valores:
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
Para date, DATE_BUCKET aceitará uma expressão de coluna, expressão ou variável definida pelo usuário se elas resolverem para qualquer um dos tipos de dados mencionados acima.
origin
Uma expressão opcional que pode resolver um dos seguintes valores:
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
O tipo de dados para origin deve coincidir com o tipo de dados do parâmetro date.
DATE_BUCKET usará um valor de data de origem padrão igual a 1900-01-01 00:00:00.000, ou seja, 0h na segunda-feira, 1º de janeiro de 1900, se não for especificado nenhum valor de origin para a função.
Tipo de retorno
O tipo de dados do valor retornado para esse método é dinâmico. O tipo de retorno depende do argumento fornecido para date. Se um tipo de dados de entrada válido for fornecido para date, DATE_BUCKET retornará o mesmo tipo de dados. DATE_BUCKET gera um erro se um literal de cadeia de caracteres é especificado para o parâmetro date.
Valores retornados
Entendendo a saída de DATE_BUCKET
DATE_BUCKET retorna o valor de data ou hora mais recente, correspondente aos parâmetros datepart e number. Por exemplo, nas expressões abaixo, DATE_BUCKET retornará o valor de saída de 2020-04-13 00:00:00.0000000, pois a saída é calculada com base em buckets de uma semana a partir da hora de origem padrão de 1900-01-01 00:00:00.000. O valor de 2020-04-13 00:00:00.0000000 é 6.276 semanas a partir do valor de origem de 1900-01-01 00:00:00.000.
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 1, @date);
Para todas as expressões abaixo, o mesmo valor de saída de 2020-04-13 00:00:00.0000000 será retornado. Isso ocorre porque 2020-04-13 00:00:00.0000000 é 6.276 semanas a partir da data de origem e 6.276 é divisível por 2, 3, 4 e 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);
A saída da expressão abaixo é 2020-04-06 00:00:00.0000000, que é de 6275 semanas a partir da hora de origem padrão 1900-01-01 00:00:00.000.
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 5, @date);
A saída da expressão abaixo é 2020-06-09 00:00:00.0000000, que é 75 semanas a partir da hora de origem especificada 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);
Argumento datepart
dayofyear, day e weekday retornam o mesmo valor. Cada datepart retorna o mesmo valor das abreviações dela.
Argumento number
O argumento number não pode exceder o intervalo de valores int positivos. Nas instruções a seguir, o argumento number excede o intervalo de int em 1. A instrução a seguir retorna a mensagem de erro 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);
Se um valor negativo do número for passado para a função DATE_BUCKET, será retornado o erro a seguir.
Msg 9834, Level 16, State 1, Line 1
Invalid bucket width value passed to DATE_BUCKET function. Only positive values are allowed.
Argumento date
DATE_BUCKET retorna o valor base correspondente ao tipo de dados do argumento date. No exemplo a seguir, um valor de saída com datatype datetime2 é retornado.
SELECT DATE_BUCKET(DAY, 10, SYSUTCDATETIME());
Argumento origin
O tipo de dados dos argumentos origin e date deve ser o mesmo. Se forem usados tipos de dados diferentes, isso gerará um erro.
Comentários
Use DATE_BUCKET nas seguintes cláusulas:
- GROUP BY
- HAVING
- ORDER BY
- SELECT <list>
- WHERE
Exemplos
a. Calcular DATE_BUCKET com uma largura de bucket igual a 1 a partir da hora de origem
Cada uma dessas instruções incrementa DATE_BUCKET com uma largura de Bucket de 1 da hora de origem:
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);
Veja a seguir o conjunto de resultados.
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. Usar expressões como argumentos para os parâmetros number e date
Estes exemplos usam diferentes tipos de expressões como argumentos para os parâmetros number e date.
Esses exemplos foram criados usando o banco de dados AdventureWorksDW2022.
Especificar variáveis definidas pelo usuário como número e data
Este exemplo especifica variáveis definidas pelo usuário como argumentos para número e data:
DECLARE @days INT = 365,
@datetime DATETIME2 = '2000-01-01 01:01:01.1110000';
/* 2000 was a leap year */;
SELECT DATE_BUCKET(DAY, @days, @datetime);
Veja a seguir o conjunto de resultados.
1999-12-08 00:00:00.0000000
Especificar uma coluna como data
No exemplo abaixo, estamos calculando a soma de OrderQuantity e a soma de UnitPrice agrupadas em buckets de datas semanais.
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;
Veja a seguir o conjunto de resultados.
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
Especificar a função do sistema escalar como data
Este exemplo especifica SYSDATETIME para data. O valor exato retornado depende do dia e da hora da execução da instrução:
SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());
Veja a seguir o conjunto de resultados.
2020-03-02 00:00:00.0000000
Especificar subconsultas e funções escalares como número e data
Este exemplo usa subconsultas escalares, MAX(OrderDate), como argumentos para número e data. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) funciona como um argumento artificial do parâmetro de número que mostra como selecionar um argumento number em uma lista de valores.
SELECT DATE_BUCKET(WEEK, (
SELECT TOP 1 CustomerKey
FROM dbo.DimCustomer
WHERE GeographyKey > 100
), (
SELECT MAX(OrderDate)
FROM dbo.FactInternetSales
));
Especificar expressões numéricas e funções do sistema escalares como número e data
Este exemplo usa uma expressão numérica ((10/2)) e funções escalares do sistema (SYSDATETIME) como argumentos para número e data.
SELECT DATE_BUCKET(WEEK, (10/2), SYSDATETIME());
Especificar uma função de janela de agregação como número
Este exemplo usa uma função de janela de agregação como um argumento para 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. Usar um valor de origem não padrão
Este exemplo usa um valor de origem não padrão para gerar os buckets de data.
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);
Confira também
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de