BigQueryの日付テーブルにおける、日付指定リファレンス

はじめに

BigQueryの日付を付与したワイルドカードテーブルから、日付を指定するクエリをリファレンスとして記載しています。
Standard SQLを使用します。

→ 日付算術演算子やLAST_DAYファンクションが使えるようになったので、新しい投稿を作成しました。こちら

クエリリファレンス

前日

#standardSQL
SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY))

前月

前月の1日から最終日。
前月１日、前月末日を指定する場合には、このBETWEENのFROMかTOが使えます。

#standardSQL
SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX BETWEEN 
  FORMAT_DATE("%Y%m%d", DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH))
  AND FORMAT_DATE("%Y%m%d", DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 DAY))

少し短い記述。文字列比較なので、31日が無い月の指定も31の記述で大丈夫です。気持ち悪く感じなければ。

#standardSQL
SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX BETWEEN 
  FORMAT_DATE("%Y%m01", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH))
  AND FORMAT_DATE("%Y%m31", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH))

前年

#standardSQL
SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX BETWEEN 
  FORMAT_DATE("%Y%m%d", DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 YEAR), YEAR))
  AND FORMAT_DATE("%Y%m%d", DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), YEAR), INTERVAL 1 DAY))

前週

日曜日〜土曜日の指定。
DATE_TRUNCは、DAYOFWEEKが指定出来ません。

#standardSQL
SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX BETWEEN 
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE('Asia/Tokyo'))+6 DAY))
  AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE('Asia/Tokyo')) DAY))

注意点

• CURRENT_TIMESTAMP()を使うと、タイムゾーン指定が出来ないので、DATE型やDATETIME型を使用しています。
• テーブルのプレフィックスには注意が必要です。公式ドキュメントに記載があります。 https://cloud.google.com/bigquery/docs/wildcard-tables#best_practices 例えば、table_20170727とtable_tmp_20170727というテーブルがあった場合、table_*を指定すると両方のテーブルが一致してしまうためです。そのため、パーティションを示す"_P"を付けることにしました。

参考

日付文字列変換など覚え書き

#standardSQL
SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP()), --UTC
  CURRENT_DATETIME('Asia/Tokyo'), --JST
  STRING(CURRENT_TIMESTAMP(), 'Asia/Tokyo'), --日本(+9H)
  STRING(CURRENT_TIMESTAMP(), '+8') --中国など(+8H)

• 接頭辞「#standardSQL」を指定すると、WEB UIでLegacySQLのチェックを外さなくてもStandard SQLとして実行されます。