timestamp_bucket関数

timestamp_bucket関数は、指定されたタイムスタンプ値を、指定された間隔(バケット)の先頭に丸めます。間隔は、タイムラインの指定された起点から始まります。

この関数を使用すると、周期性と呼ばれる目的の時間間隔に時系列データを集計できます。場合によっては、すべての時系列データを指定された周期の等価バケットに配置し、各バケットが同じ時間量を表すようにすることが望ましい場合があります。

構文:

TIMESTAMP timestamp_bucket(<timestamp>[, interval [,origin ])

セマンティクス:

timestamp: timestamp引数は、TIMESTAMP値またはTIMESTAMP型にキャストできる値を取ります。
interval: interval引数はオプションであり、STRINGデータ型です。intervalは、<n> unitとして指定されます。
説明

nは、間隔の値を指定します。nは0より大きくする必要があります

unitは、時間隔コンポーネントを定義します。この関数は、単数形式または複数形式のWEEK、DAY、HOUR、MINUTEおよびSECONDをサポートします。

たとえば、"5 MINUTE"または"5 MINUTES"です。

ノート:
単位の大/小文字は区別されません。
origin: origin引数は、タイムライン上のバケットの開始点を表します。この引数はオプションで、TIMESTAMP値を取ります。originには、TIMESTAMP型にキャストできる任意のデータ型を指定できます。指定しない場合、Unixエポック1970-01-01がデフォルト値です。

ノート:
また、この関数は、originより小さい入力タイムスタンプを指定された間隔の先頭に丸めます。つまり、タイムラインの入力タイムスタンプ値と比較して、将来のタイムスタンプ値を持つoriginを指定できます。
戻り値: TIMESTAMP(9)
この関数は、次の場合にNULLを返します。
- いずれかの引数がNULLに設定されている場合。
- 入力timestampがTIMESTAMP型にキャスト可能でない場合。

例12-16 2019年1月1日から始まるバケットを使用した、12時間ごとにIST空港から出発する乗客数の統計のフェッチ。2019年2月のデータのみを考慮します

SELECT $t AS DATE,
count($t) AS FLIGHTCOUNT 
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[] $f, 
timestamp_bucket($f.flightDate, '12 HOURS', '2019-01-01T00') $t
WHERE $f.fltRouteSrc =any "IST" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-02-01T00:00:00Z'
GROUP BY $t
ORDER BY $t

説明: 2019年2月に旅行する乗客を考慮するには、timestamp_floor関数を使用して、flightDateを月の初めまで切り下げます。結果を文字列"2019-02-01T00:00:00Z"と比較します。この例では、ISO-8601形式の文字列で日付を指定します。この文字列は、暗黙的にCASTをTIMESTAMP値に取得します。

IST空港からの乗継便を含めるには、配列コンストラクタ[ ]を使用して、flightLegsが配列であることを示し、検索で各fltRouteSrc配列要素を考慮します。

intervalが12時間でoriginが2019年1月1日であるflightDateフィールドで、timsestamp_bucket関数を使用します。

出力:

{"DATE":"2019-02-02T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-04T00:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-04T12:00:00.000000000Z","FLIGHTCOUNT":2}
{"DATE":"2019-02-07T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-11T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-12T00:00:00.000000000Z","FLIGHTCOUNT":2}
{"DATE":"2019-02-12T12:00:00.000000000Z","FLIGHTCOUNT":1}

例12-17 ストリーミング・メディア・サービス・データから、2023年1月1日の正午から始まるバケットを使用した、毎週期限切れになるアカウント数の統計のフェッチ。2023年12月1日からのデータを考慮します

SELECT array_collect(s.account_expiry) as ACCOUNT_EXPIRY,
$t AS WEEK,
count($t) AS ACCOUNTS
FROM stream_acct s,
timestamp_bucket(s.account_expiry, '1 week', '2023-01-01T12') $t
WHERE s.account_expiry >= '2023-12-01'
GROUP BY $t
ORDER BY $t;

説明: intervalが1週間、originが1月12日の午後1時であるaccount_expiryフィールドでtimsestamp_bucket関数を使用します。この関数は、2023年1月1日午後12時から1週間の周期性を計算し、account_expiry値を対応する週バケットの先頭に丸めます。

array_collect関数を使用して、account_expiryフィールドを表示することに注意してください。SELECT式にグループ化関数が含まれている場合、グループ化式、集計関数または外部変数のみを参照できます。

出力:

{"ACCOUNT_EXPIRY":["2023-12-18T00:00:00.000000000Z"],"WEEK":"2023-12-17T12:00:00.000000000Z","ACCOUNTS":1}
{"ACCOUNT_EXPIRY":["2023-12-31T00:00:00.000000000Z"],"WEEK":"2023-12-24T12:00:00.000000000Z","ACCOUNTS":1}
{"ACCOUNT_EXPIRY":["2024-03-18T00:00:00.000000000Z"],"WEEK":"2024-03-17T12:00:00.000000000Z","ACCOUNTS":1}