DATE_BUCKET (Transact-SQL)
重要
Azure SQL Edge は、2025 年 9 月 30 日に廃止されます。 詳細と移行オプションについては、 Retirement に関する通知を参照してください。
Note
Azure SQL Edge では、ARM64 プラットフォームがサポートされなくなりました。
この関数は、origin パラメーターによって定義されているタイムスタンプ、または origin パラメーターが指定されていない場合は 1900-01-01 00:00:00.000 の既定の基準値で定義されているタイムスタンプから、各 datetime バケットの開始に対応する datetime 値を返します。
Transact-SQL の日付と時刻のデータ型および関数の概要については、「日付と時刻のデータ型および関数 (Transact-SQL)」を参照してください。
構文
DATE_BUCKET (datePart , number , date , origin)
引数
datePart
次の表で示すように、"number" パラメーターで使われている "日時" の部分。 DATE_BUCKET は、datePart 引数に対し、ユーザー定義変数に相当するものは受け入れません。
| datePart | 省略形 |
|---|---|
| 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
datePart 引数と組み合わされてバケットの幅を決定する整数値。 これは、基準日時からの datePart バケットの幅を表します。 この引数には、"正" の整数値を指定する必要があります。
date
次のいずれかの値に解決できる式。
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
date に対し、DATE_BUCKET は、前に示したデータ型に解決される列式、式、またはユーザー定義変数を受け付けます。
配信元
次のいずれかの値に解決できるオプションの式:
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
- time
origin のデータ型は、date パラメーターのデータ型と一致する必要があります。
DATE_BUCKET は、origin 値が関数に指定されていない場合、既定の日付の基準値として 1900-01-01 00:00:00.000 (1900 年 1 月 1 日 (月) 午前 0:00) を使います。
の戻り値の型 :
このメソッドの戻り値のデータ型は動的です。 戻り値の型は、date に指定された引数によって異なります。 有効な入力データ型が date に指定された場合、DATE_BUCKET は同じデータ型を返します。 date パラメーターに文字列リテラルを指定した場合は、DATE_BUCKET でエラーが発生します。
戻り値
DATE_BUCKET からの出力を理解する
DATE_BUCKET からは、datePart と number パラメーターに対応する、最も新しい日付または時刻の値が返されます。 たとえば、次の式では、既定の基準日時 1900-01-01 00:00:00.000 からの 1 週間のバケットに基づいて出力が計算されるため、DATE_BUCKET から返される出力値は 2020-04-13 00:00:00.0000000 になります。 値 2020-04-13 00:00:00.0000000 は、基準値 1900-01-01 00:00:00.000 から 6276 週です。
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 1, @date);
以下のすべての式に対して、同じ出力値 2020-04-13 00:00:00.0000000 が返されます。 これは、2020-04-13 00:00:00.0000000 は基準の日付から 6276 週であり、6276 は 2、3、4、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);
下の式の出力は、既定の基準の日時 1900-01-01 00:00:00.000 から 6275 週である 2020-04-06 00:00:00.0000000 になります。
DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 5, @date);
下の式の出力は、指定された基準の日時 2019-01-01 00:00:00 から 75 週である 2020-06-09 00:00:00.0000000 になります。
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);
解説
次の句で DATE_BUCKET を使用します。
GROUP BYHAVINGORDER BYSELECT <list>WHERE
datePart 引数
dayofyear、day、および weekday は同じ値を返します。 各 datePart とその省略形は、同じ値を返します。
number 引数
number 引数は、正の int 値の範囲を超えることはできません。 次のステートメントでは、number の引数が int の範囲を 1 超えています。 以下のステートメントからは、次のエラー メッセージが返されます: 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);
DATE_BUCKET 関数に負の値の number を渡すと、次のエラーが返されます。
Msg 9834, Level 16, State 1, Line 1
Invalid bucket width value passed to DATE_BUCKET function. Only positive values are allowed.
date 引数
DATE_BUCKET からは、date 引数のデータ型に対応するベース値が返されます。 次の例では、datetime2 データ型の出力値が返されます。
SELECT DATE_BUCKET(DAY, 10, SYSUTCDATETIME());
origin 引数
origin と date 引数のデータ型は同じである必要があります。 異なるデータ型を使うと、エラーになります。
例
A. バケットの幅を 1 にして基準日時からの DATE_BUCKET を計算する
以下の各ステートメントでは、基準日時からのバケット幅 1 で DATE_BUCKET がインクリメントされます。
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);
結果セットは次のとおりです。
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. number パラメーターと date パラメーターの引数として式を使用する
次の例では、number パラメーターと date パラメーターの引数として、さまざまな種類の式を使用しています。 これらの例は、AdventureWorksDW2019 データベースを使用して構築されています。
number および date にユーザー定義変数を指定する
次の例では、number と 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);
結果セットは次のとおりです。
---------------------------
1999-12-08 00:00:00.0000000
(1 row affected)
列を date として指定する
次の例では、週単位の日付バケットでグループ化された OrderQuantity と UnitPrice の合計が計算されています。
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;
結果セットは次のとおりです。
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
スカラー システム関数を date として指定する
この例では、date の SYSDATETIME を指定しています。 返される厳密な値は、ステートメント実行の日時によって変わります。
SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());
結果セットは次のとおりです。
---------------------------
2020-03-02 00:00:00.0000000
(1 row affected)
number および date にスカラー サブクエリやスカラー関数を指定する
次の例では、number と date の引数として、スカラー サブクエリ (MAX(OrderDate)) を使用しています。 (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) は、値リストから number 引数を選択する方法を紹介するために用意した、数値パラメーターの架空の引数です。
SELECT DATE_BUCKET(WEEK,
(
SELECT TOP 1 CustomerKey
FROM dbo.DimCustomer
WHERE GeographyKey > 100
),
(
SELECT MAX(OrderDate)
FROM dbo.FactInternetSales
)
);
number および date として数値式やスカラー システム関数を指定する
この例では、数値式 ((10/2)) を使用し、number と date の引数としてスカラー システム関数 (SYSDATETIME) を使用しています。
SELECT DATE_BUCKET(WEEK, (10 / 2), SYSDATETIME());
number として集計関数を指定する
次の例では、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. 既定以外の基準値を使用する
この例では、既定以外の基準値を使用して、日付バケットを生成します。
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);