DATE_BUCKET (Transact-SQL)
Gilt für:
SQL Server 2022 (16.x) Azure SQL-Datenbank Azure SQL verwaltete Instanz Azure SQL Edge
Diese Funktion gibt den datetime-Wert zurück, der dem Anfang jedes datetime-Buckets entspricht, beginnend beim Zeitstempel, der vom origin-Parameter definiert wird, oder den origin-Wert von 1900-01-01 00:00:00.000, wenn der origin-Parameter nicht angegeben ist.
Eine Übersicht über alle Datums- und Uhrzeitdatentypen und zugehörigen Funktionen für Transact-SQL finden Sie unter Datums- und Uhrzeitdatentypen und zugehörige Funktionen (Transact-SQL).
Transact-SQL-Syntaxkonventionen
Syntax
DATE_BUCKET (datepart, number, date [, origin ] )
Argumente
datepart
Der Teil von date, der mit dem number-Parameter verwendet wird, z. B. Jahr, Monat, Tag, Minute, Sekunde.
DATE_BUCKET akzeptiert für die datepart-Argumente keine benutzerdefinierten Variablenentsprechungen.
| datepart | Abkürzungen |
|---|---|
| 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 |
Zahl
Der Integerwert, der die Breite des Buckets in Kombination mit dem datepart-Argument bestimmt. Dies stellt die Breite des datepart-Buckets ab der Ursprungszeit dar. Dieses Argument kann kein negativer ganzzahliger Wert sein.
date
Ein Ausdruck, der in einen der folgenden Werte aufgelöst werden kann:
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
Für date akzeptiert DATE_BUCKET einen Spaltenausdruck, einen Ausdruck oder eine benutzerdefinierte Variable, wenn diese in einen der oben genannten Datentypen aufgelöst werden.
origin
Ein optionaler Ausdruck, der in einen der folgenden Werte aufgelöst werden kann:
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
Der Datentyp für origin sollte mit dem Datentyp des date-Parameters übereinstimmen.
DATE_BUCKET verwendet einen origin-Standardwert für „date“ von 1900-01-01 00:00:00.000, d. h. 12:00 Uhr am Montag, 1. Januar 1900, wenn für die Funktion kein origin-Wert angegeben ist.
Rückgabetyp
Der Datentyp des Rückgabewerts für diese Methode ist dynamisch. Der Rückgabetyp hängt von dem Argument ab, das für date angegeben wird. Wenn für date ein gültiger Eingabedatentyp angegeben wird, gibt DATE_BUCKET denselben Datentyp zurück. DATE_BUCKET löst einen Fehler aus, wenn für den date-Parameter ein Zeichenfolgenliteral angegeben wird.
Rückgabewerte
Grundlegendes zur Ausgabe von DATE_BUCKET
DATE_BUCKET gibt den aktuellsten Datums- oder Uhrzeitwert zurück, der dem datepart- und dem number-Parameter entspricht. In den folgenden Ausdrücken gibt DATE_BUCKET z. B. den Ausgabewert 2020-04-13 00:00:00.0000000 zurück, da die Ausgabe basierend auf einwöchigen Buckets aus der Standardursprungszeit von 1900-01-01 00:00:00.000 berechnet wird. Der Wert 2020-04-13 00:00:00.0000000 ist 6.276 Wochen vom Ursprungswert 1900-01-01 00:00:00.000 entfernt.
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 1, @date);
Für alle folgenden Ausdrücke wird der gleiche Ausgabewert von 2020-04-13 00:00:00.0000000 zurückgegeben. Dies liegt daran, dass 2020-04-13 00:00:00.0000000 6.276 Wochen vom origin-Wert entfernt und 6.276 durch 2, 3, 4 und 6 teilbar ist.
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);
Die Ausgabe für den unten angegebenen Ausdruck ist 2020-04-06 00:00:00.0000000, der 6275 Wochen von der origin-Standardzeit 1900-01-01 00:00:00.000 entfernt ist.
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 5, @date);
Die Ausgabe für den unten angegebenen Ausdruck ist 2020-06-09 00:00:00.0000000, der 75 Wochen von der angegebenen origin-Zeit 2019-01-01 00:00:00 entfernt ist.
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-Argument
dayofyear, day und weekday geben den gleichen Wert zurück. Jedes datepart-Argument und die jeweils zugehörigen Abkürzungen geben den gleichen Wert zurück.
number-Argument
Das number-Argument kann den Bereich von positiven int-Werten nicht überschreiten. In den folgenden Anweisungen überschreitet das Argument für number den Bereich von int um 1. Die folgende Anweisung gibt folgende Fehlermeldung zurück: 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);
Wenn ein negativer Wert für „number“ an die DATE_BUCKET-Funktion übergeben wird, wird der folgende Fehler zurückgegeben.
Msg 9834, Level 16, State 1, Line 1
Invalid bucket width value passed to DATE_BUCKET function. Only positive values are allowed.
date-Argument
DATE_BUCKET gibt den Basiswert zurück, der dem Datentyp des date-Arguments entspricht. Im folgenden Beispiel wird ein Ausgabewert mit dem datetime2-Datentyp zurückgegeben.
SELECT DATE_BUCKET(DAY, 10, SYSUTCDATETIME());
origin-Argument
Die Datentypen der Argumente origin und date müssen identisch sein. Wenn unterschiedliche Datentypen verwendet werden, wird ein Fehler ausgegeben.
Bemerkungen
Verwenden Sie DATE_BUCKET in den folgenden Klauseln:
- GROUP BY
- HAVING
- ORDER BY
- SELECT <list>
- WHERE
Beispiele
A. Berechnen von DATE_BUCKET mit einer Bucketbreite von 1 ab der Ursprungszeit
Jede dieser Anweisungen erhöht DATE_BUCKET mit einer Bucketbreite von 1 ab der Ursprungszeit:
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);
Hier sehen Sie das Ergebnis.
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. Verwenden von Ausdrücken als Argumente für den number-Parameter und den date-Parameter
In diesen Beispielen werden verschiedene Typen von Ausdrücken als Argumente für die Parameter number und date verwendet.
Diese Beispiele werden mithilfe der AdventureWorksDW2022-Datenbank erstellt.
Angeben von benutzerdefinierten Variablen als Argumente für number und date
In diesem Beispiel werden benutzerdefinierte Variablen als Argumente für number und date angegeben:
DECLARE @days INT = 365,
@datetime DATETIME2 = '2000-01-01 01:01:01.1110000';
/* 2000 was a leap year */;
SELECT DATE_BUCKET(DAY, @days, @datetime);
Hier sehen Sie das Ergebnis.
1999-12-08 00:00:00.0000000
Angeben einer Spalte als date-Parameter
Im folgenden Beispiel berechnen wir die Summe von OrderQuantity und die Summe von UnitPrice, gruppiert über wöchentliche date-Buckets.
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;
Hier sehen Sie das Ergebnis.
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
Angeben einer skalaren Systemfunktion als Argument für date
In diesem Beispiel wird SYSDATETIME für date angegeben. Welcher Wert genau zurückgegeben wird, hängt davon ab, an welchem Tag und zu welcher Uhrzeit die Anweisung ausgeführt wird:
SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());
Hier sehen Sie das Ergebnis.
2020-03-02 00:00:00.0000000
Angeben von skalaren Unterabfragen und skalaren Funktionen als Argumente für number und date
In diesem Beispiel werden skalare Unterabfragen (MAX(OrderDate)) als Argumente für number und date verwendet. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) dient als Beispielargument für den Parameter „number“, das veranschaulicht, wie ein number-Argument aus einer Werteliste ausgewählt wird.
SELECT DATE_BUCKET(WEEK, (
SELECT TOP 1 CustomerKey
FROM dbo.DimCustomer
WHERE GeographyKey > 100
), (
SELECT MAX(OrderDate)
FROM dbo.FactInternetSales
));
Angeben von numerischen Ausdrücken und skalaren Systemfunktionen als Argumente für number und date
In diesem Beispiel werden ein numerischer Ausdruck ((10/2)) und skalare Systemfunktionen (SYSDATETIME) als Argumente für number und date verwendet.
SELECT DATE_BUCKET(WEEK, (10/2), SYSDATETIME());
Angeben einer Aggregatfensterfunktion als Argument für number
In diesem Beispiel wird eine Aggregatfensterfunktion als Argument für number verwendet.
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. Verwenden eines nicht standardmäßigen Ursprungswerts
In diesem Beispiel wird ein nicht standardmäßiger Ursprungswert für date-Buckets verwendet.
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);