DATE_BUCKET (Transact-SQL)

  • [アーティクル]

重要

Azure SQL Edge では、ARM64 プラットフォームがサポートされなくなりました。

この関数は、origin パラメーターによって定義されているタイムスタンプ、または origin パラメーターが指定されていない場合は 1900-01-01 00:00:00.000 の既定の基準値で定義されているタイムスタンプから、各 datetime バケットの開始に対応する datetime 値を返します。

Transact-SQL の日付と時刻のデータ型および関数の概要については、「日付と時刻のデータ型および関数 (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 BY
  • HAVING
  • ORDER BY
  • SELECT <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);

関連項目