対象者:
SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceAzure SQL Edge SQL database in Microsoft Fabric
この関数は、配信元パラメーターで定義されたタイムスタンプから、各日時バケットの開始に対応する日時値、または 配信元 パラメーターが指定されていない場合は 1900-01-01 00:00:00.000 の既定の配信元の値を返します。
Transact-SQL のすべての日付と時刻のデータ型と関数の概要については日付と時刻のデータ型と関数に関するページを参照してください。
構文
DATE_BUCKET (datepart , number , date [ , origin ] )
引数
datepart
数値パラメーターで使用される日付の部分 (例: 年、月、日、分、秒)。
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 では、関数の配信元の値が指定されていない場合は、1900 年 1 月 1 日月曜日の午前 12 時 00 分である 1900-01-01 00:00:00.000 の既定の 配信元 の日付値が使用されます。
戻り値の型
このメソッドの戻り値のデータ型は動的です。 戻り値の型は、date に指定された引数によって異なります。 有効な入力データ型が date に指定された場合、DATE_BUCKET は同じデータ型を返します。
DATE_BUCKET パラメーターに文字列リテラルを指定した場合は、 でエラーが発生します。
戻り値
DATE_BUCKETからの出力を理解する
DATE_BUCKET からは、datepart および number パラメーターに対応する、最も新しい日付または時刻の値が返されます。 たとえば、次の式では、既定の基準日時 DATE_BUCKET からの 1 週間のバケットに基づいて出力が計算されるため、2020-04-13 00:00:00.0000000 から返される出力値は 1900-01-01 00:00:00.000 になります。 値 2020-04-13 00:00:00.0000000 は、基準値 1900-01-01 00:00:00.000 から 6,276 週です。
DECLARE @date AS 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 は基準の日付から 6,276 週であり、6,276 は 2、3、4、6 で割り切れるためです。
DECLARE @date AS 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);
次の式の出力は 2020-04-06 00:00:00.0000000です。これは、既定の配信元時刻 1900-01-01 00:00:00.000から 6,275 週間です。
DECLARE @date AS DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 5, @date);
次の式の出力は 2020-06-09 00:00:00.0000000です。これは、指定した元の時刻 2019-01-01 00:00:00から 75 週間です。
DECLARE @date AS DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin AS DATETIME2 = '2019-01-01 00:00:00';
SELECT DATE_BUCKET(WEEK, 5, @date, @origin);
datepart 引数
dayofyear、day、および weekday は同じ値を返します。 -各日付構成要素とその省略形は、同じ値を返します。
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 AS 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 引数のデータ型は同じである必要があります。 異なるデータ型を使うと、エラーになります。
解説
次の句で DATE_BUCKET を使用します。
GROUP BYHAVINGORDER BYSELECT <list>WHERE
例
この記事のコード サンプルでは、AdventureWorks2025 または AdventureWorksDW2025 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクト ホーム ページからダウンロードできます。
A. バケットの幅を 1 にして基準日時からの DATE_BUCKET を計算する
以下の各ステートメントでは、基準日時からのバケット幅 1 で DATE_BUCKET がインクリメントされます。
DECLARE @date AS 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 パラメーターの引数として、さまざまな種類の式を使用しています。
これらの例は、AdventureWorksDW2025 データベースを使用して構築されています。
number および date にユーザー定義変数を指定する
次の例では、number と date の引数としてユーザー定義変数を指定しています。
DECLARE @days AS INT = 365,
@datetime AS DATETIME2 = '2000-01-01 01:01:01.1110000';
SELECT DATE_BUCKET(DAY, @days, @datetime);
結果セットは次のとおりです。
1999-12-08 00:00:00.0000000
列を 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 AS 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 として指定する
この例では、SYSDATETIME の を指定しています。 返される厳密な値は、ステートメント実行の日時によって変わります。
SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());
結果セットは次のとおりです。
2020-03-02 00:00:00.0000000
number および date にスカラー サブクエリやスカラー関数を指定する
次の例では、MAX(OrderDate) と date の引数として、スカラー サブクエリ () を使用しています。
(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)) とスカラー システム関数 (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 AS DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin AS DATETIME2 = '2019-01-01 00:00:00';
SELECT DATE_BUCKET(HOUR, 2, @date, @origin);