1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

【BigQuery】ウィンドウ関数にて日付でソートする方法

Last updated at Posted at 2022-10-06

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?