ウィンドウ関数の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 |