Condividi tramite


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

Vedi anche