DATE_BUCKET (Transact-SQL)
Si applica a:
SQL Server 2022 (16.x) database SQL di Azure Istanza gestita di SQL di Azure SQL Edge di Azure
Questa funzione restituisce il valore di data e ora corrispondente all'inizio di ogni bucket di data e ora del timestamp definito dal parametro origin. Se quest'ultimo non è specificato, la funzione restituisce il valore di origine predefinito, ovvero 1900-01-01 00:00:00.000.
Per una panoramica di tutti i tipi di dati e funzioni di data e ora Transact-SQL, vedere Funzioni e tipi di dati di data e ora (Transact-SQL).
Convenzioni relative alla sintassi Transact-SQL
Sintassi
DATE_BUCKET (datepart, number, date [, origin ] )
Argomenti
datepart
Parte della data usata con il parametro number, ad esempio anno, mese, giorno, minuto, secondo.
DATE_BUCKET non accetta equivalenti di variabili definite dall'utente come argomenti di 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
Numero intero che determina la larghezza del bucket combinato con l'argomento datepart. Rappresenta la larghezza dei bucket datepart dall'ora di origine. Questo argomento non può essere un valore intero negativo.
date
Espressione che può risolversi in uno dei valori seguenti:
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
Per date, DATE_BUCKET accetterà un'espressione di colonna, un'espressione o una variabile definita dall'utente se si risolve in uno dei tipi di dati indicati in precedenza.
origin
Espressione facoltativa che può risolversi in uno dei valori seguenti:
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
Il tipo di dati per origin deve corrispondere al tipo di dati del parametro date.
DATE_BUCKET usa il valore di data di origine predefinito 1900-01-01 00:00:00.000, ovvero le 12:00 di lunedì 1 gennaio 1900, se non viene specificato alcun valore origin per la funzione.
Tipo restituito
Il tipo di dati del valore restituito per questo metodo è dinamico. Il tipo restituito dipende dall'argomento specificato per date. Se viene fornito un tipo di dati di input valido per date, DATE_BUCKET restituisce lo stesso tipo di dati. DATE_BUCKET genera un errore se viene specificato un valore letterale stringa per il parametro date.
Valori restituiti
Informazioni sull'output da DATE_BUCKET
DATE_BUCKET restituisce il valore di data e ora più recente, corrispondente ai parametri datepart e number. Nelle espressioni riportate di seguito, ad esempio, DATE_BUCKET restituirà il valore di output di 2020-04-13 00:00:00.0000000, poiché l'output viene calcolato in base a bucket di una settimana dalla data/ora di origine predefinita 1900-01-01 00:00:00.000. Il valore 2020-04-13 00:00:00.0000000 è posteriore di 6.276 settimane rispetto al valore 1900-01-01 00:00:00.000.
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 1, @date);
Per tutte le espressioni riportate di seguito, verrà restituito lo stesso valore di output 2020-04-13 00:00:00.0000000. Ciò si verifica perché il valore 2020-04-13 00:00:00.0000000 è posteriore di 6.276 settimane rispetto alla data di origine e 6.276 è divisibile per 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);
L'output per l'espressione seguente è 2020-04-06 00:00:00.0000000, che è posteriore di 6.275 settimane rispetto alla data di origine predefinita 1900-01-01 00:00:00.000.
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 5, @date);
L'output per l'espressione seguente è 2020-06-09 00:00:00.0000000, che è posteriore di 75 settimane rispetto alla data di origine predefinita 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);
Argomento datepart
dayofyear, day, e weekday restituiscono lo stesso valore. Ogni elemento datepart e le relative abbreviazioni restituiscono lo stesso valore.
Argomento number
L'argomento number non può superare l'intervallo di valori int positivi. Nelle istruzioni seguenti l'argomento per il parametro number supera l'intervallo di int di una unità. L'istruzione seguente restituisce il messaggio di errore 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 alla funzione DATE_BUCKET viene passato un valore negativo per number, viene restituito l'errore seguente.
Msg 9834, Level 16, State 1, Line 1
Invalid bucket width value passed to DATE_BUCKET function. Only positive values are allowed.
Argomento date
DATE_BUCKET restituisce il valore di base corrispondente al tipo di dati dell'argomento date. Nell'esempio seguente viene restituito un valore di output con tipo di dati datetime2.
SELECT DATE_BUCKET(DAY, 10, SYSUTCDATETIME());
Argomento origin
Il tipo di dati degli argomenti origin e date deve essere lo stesso. Se vengono usati tipi di dati diversi, verrà generato un errore.
Osservazioni:
Usare DATE_BUCKET nelle clausole seguenti:
- GROUP BY
- HAVING
- ORDER BY
- SELECT <list>
- WHERE
Esempi
R. Calcolare DATE_BUCKET con una larghezza di bucket di 1 dall'ora di origine
Ognuna di queste istruzioni viene incrementata DATE_BUCKET con una larghezza del bucket pari a 1 dall'ora di origine:
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);
Il set di risultati è il seguente.
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. Utilizzare espressioni come argomenti per i parametri number e date
In questi esempi vengono usati tipi diversi di espressioni come argomenti per i parametri number e date.
Questi esempi vengono creati usando il database AdventureWorksDW2022.
Specificare variabili definite dall'utente per i parametri number e date
In questo esempio vengono specificate variabili definite dall'utente come argomenti per i parametri number e 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);
Il set di risultati è il seguente.
1999-12-08 00:00:00.0000000
Specificare una colonna per il parametro date
Nell'esempio seguente vengono calcolate la somma di OrderQuantity e la somma di UnitPrice raggruppate in base a bucket di data settimanali.
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;
Il set di risultati è il seguente.
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
Specificare una funzione di sistema scalare per il parametro date
Questo esempio specifica SYSDATETIME per date. Il valore esatto restituito dipende dal giorno e dall'ora di esecuzione dell'istruzione:
SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());
Il set di risultati è il seguente.
2020-03-02 00:00:00.0000000
Specificare sottoquery scalari e funzioni scalari per i parametri number e date
In questo esempio vengono usate sottoquery scalari, MAX(OrderDate), come argomenti per number e date. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) funge da argomento fittizio perché il parametro number illustri come selezionare un argomento number da un elenco di valori.
SELECT DATE_BUCKET(WEEK, (
SELECT TOP 1 CustomerKey
FROM dbo.DimCustomer
WHERE GeographyKey > 100
), (
SELECT MAX(OrderDate)
FROM dbo.FactInternetSales
));
Specificare espressioni numeriche e funzioni di sistema scalari per i parametri number e date
In questo esempio vengono usate un'espressione numerica, ((10/2)), e funzioni di sistema scalari (SYSDATETIME) come argomenti per number e date.
SELECT DATE_BUCKET(WEEK, (10/2), SYSDATETIME());
Specificare una funzione finestra di aggregazione per il parametro number
Questo esempio usa una funzione finestra di aggregazione come argomento per un parametro 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. Usare un valore di origine non predefinito
In questo esempio viene usato un valore di origine non predefinito per generare i bucket di 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);