DATE_BUCKET (Transact-SQL)
Importante
SQL Edge di Azure verrà ritirato il 30 settembre 2025. Per altre informazioni e per le opzioni di migrazione, vedere l'avviso di ritiro.
Nota
SQL Edge di Azure non supporta più la piattaforma ARM64.
Questa funzione restituisce il valore datetime corrispondente all'inizio di ogni bucket datetime, dal timestamp definito dal origin
parametro o dal valore di origine predefinito di se il parametro di 1900-01-01 00:00:00.000
origine non è specificato.
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 utilizzata con il parametro 'number', come illustrato nella tabella seguente. DATE_BUCKET
non accetta equivalenti di variabili definite dall'utente per gli argomenti 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 deve essere un valore intero positivo .
date
Espressione che può risolversi in uno dei valori seguenti:
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
Per data, DATE_BUCKET
accetta 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 un valore di data di origine predefinito di 1900-01-01 00:00:00.000
, ovvero 12:00 il lunedì 1 gennaio 1900, se non viene specificato alcun valore di origine 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 per date viene fornito un tipo di dati di input valido, 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 di DATE_BUCKET
DATE_BUCKET
restituisce il valore di data o ora più recente, corrispondente ai parametri datePart e number . Nelle espressioni seguenti, ad esempio, DATE_BUCKET
restituisce il valore di output di , perché l'output viene calcolato in base a bucket di una settimana dall'ora di 2020-04-13 00:00:00.0000000
origine predefinita di 1900-01-01 00:00:00.000
. Il valore 2020-04-13 00:00:00.0000000
è successivo di 6276 settimane dal valore di origine 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 seguenti, viene restituito lo stesso valore di output di 2020-04-13 00:00:00.0000000
. Questo perché il valore 2020-04-13 00:00:00.0000000
è successivo di 6276 settimane dalla data di origine e 6276 è 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
, ovvero 75 settimane dall'ora 2019-01-01 00:00:00
di origine specificata.
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);
Osservazioni:
Usare DATE_BUCKET
nelle clausole seguenti:
GROUP BY
HAVING
ORDER BY
SELECT <list>
WHERE
datePart
argomento
dayofyear, day, e weekday restituiscono lo stesso valore. Ogni 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 seguente: 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 viene passato DATE_BUCKET
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 di origine e data in deve essere lo stesso. Se vengono usati tipi di dati diversi, viene generato un errore.
Esempi
R. Calcolare DATE_BUCKET con una larghezza di bucket di 1 dall'ora di origine
Ognuna di queste istruzioni incrementa 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 compilati usando il AdventureWorksDW2019
database.
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
(1 row affected)
Specificare una colonna per il parametro date
Nell'esempio seguente viene calcolata la somma di OrderQuantity e la somma di UnitPrice con raggruppamento 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
(1 row affected)
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
Questo esempio usa un'espressione numerica, ovvero (10/2), e funzioni di sistema scalari (SYSDATETIME) come argomenti per numero e data.
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);