ウィンドウ関数のWindowFrame句で日付項目でソートする場合について、BigQueryのマニュアルには以下のように記載されています。
ヒント: 日付の範囲を使用する場合は、
UNIX_DATE()関数でORDER BYを使用します。タイムスタンプ付きの範囲を使用する場合は、UNIX_SECONDS()、UNIX_MILLIS()、またはUNIX_MICROS()関数を使用します。
既に知っている人にとっては何と言うことはないのですが、初学者に上記のマニュアルのページ全体を読んでこれをわかってと言うのは少し酷な気もしたので、記事に起こしました。
日付で ORDER BY してみる
以下のように、 ORDER BY に日付型の項目を設定すると…
WITH values AS (
SELECT
date
FROM
UNNEST(GENERATE_DATE_ARRAY(DATE'2022-01-01', '2022-01-05')) AS date
)
SELECT
*,
MIN(date) OVER (w) AS min,
MAX(date) OVER (w) AS max
FROM
values
WINDOW w AS (ORDER BY date RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
こんなエラーが表示されます。
ORDER BY key must be numeric in a RANGE-based window with OFFSET PRECEDING or OFFSET FOLLOWING boundaries, but has type DATE
UNIX_DATE() を利用
上記SQLに UNIX_DATE() を追加します。
WITH dates AS (
SELECT
date
FROM
UNNEST(GENERATE_DATE_ARRAY(DATE'2022-01-01', '2022-01-05')) AS date
)
SELECT
*,
UNIX_DATE(date) AS UnixDate,
MIN(date) OVER (w) AS min,
MAX(date) OVER (w) AS max
FROM
dates
WINDOW w AS (ORDER BY UNIX_DATE(date) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
実行結果
エラーが出ることなく実行できました。
MIN() , MAX() でそれぞれ1行前、1行後の日付も取れています。
| date | UnixDate | min | max |
|---|---|---|---|
| 2022-01-01 | 18993 | 2022-01-01 | 2022-01-02 |
| 2022-01-02 | 18994 | 2022-01-01 | 2022-01-03 |
| 2022-01-03 | 18995 | 2022-01-02 | 2022-01-04 |
| 2022-01-04 | 18996 | 2022-01-03 | 2022-01-05 |
| 2022-01-05 | 18997 | 2022-01-04 | 2022-01-05 |