はじめに

BigQueryの日付を付与したワイルドカードテーブルから、日付を指定するクエリをリファレンスとして記載しています。
以前に「BigQueryの日付テーブルにおける、日付指定リファレンス」を投稿しています。日付算術演算子やLAST_DAYファンクションが使えるようになったので、更新しました。

クエリリファレンス

前日

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

前月

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

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", LAST_DAY(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH))

前年

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", LAST_DAY(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 YEAR), YEAR))

前週

日曜日〜土曜日の指定。

SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX BETWEEN   
  FORMAT_DATE("%Y%m%d", DATE_TRUNC(CURRENT_DATE('Asia/Tokyo') - 7, WEEK(SUNDAY)))
  AND FORMAT_DATE("%Y%m%d", LAST_DAY(CURRENT_DATE('Asia/Tokyo') - 7, WEEK(SUNDAY)))

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

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)

注意点

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

終わりに

前の投稿から３年以上が経っていました。最近はBigQueryと疎遠で、キャッチアップできてないです・・・

参考

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#supported-format-elements-for-datetime
日付算術演算子
https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#date_arithmetics_operators