DATE_BUCKET (Transact-SQL)

  • Artikel

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

Weitere Informationen