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として実行されます。