[bigquery]対象日付を基準にして、特定曜日の日付を抽出する方法

2021-08-28 2022-02-25

-- 事前にDECLAREで変数定義

DECLARE target_date DATE;

-- 変数に値を設定

SET target_date = DATE('2021-01-01');

SELECT

target_date

, DATE_TRUNC(target_date, WEEK(MONDAY)) AS target_monday

, DATE_TRUNC(target_date, WEEK(TUESDAY)) AS target_tuesday

, DATE_TRUNC(target_date, WEEK(WEDNESDAY)) AS target_wednesday

, DATE_TRUNC(target_date, WEEK(THURSDAY)) AS target_thursday

, DATE_TRUNC(target_date, WEEK(FRIDAY)) AS target_friday

, DATE_TRUNC(target_date, WEEK(SATURDAY)) AS target_saturday

, DATE_TRUNC(target_date, WEEK(SUNDAY)) AS target_sunday

-- 事前にDECLAREで変数定義 DECLARE target_date DATE; -- 変数に値を設定 SET target_date = DATE('2021-01-01'); SELECT target_date , DATE_TRUNC(target_date, WEEK(MONDAY)) AS target_monday , DATE_TRUNC(target_date, WEEK(TUESDAY)) AS target_tuesday , DATE_TRUNC(target_date, WEEK(WEDNESDAY)) AS target_wednesday , DATE_TRUNC(target_date, WEEK(THURSDAY)) AS target_thursday , DATE_TRUNC(target_date, WEEK(FRIDAY)) AS target_friday , DATE_TRUNC(target_date, WEEK(SATURDAY)) AS target_saturday , DATE_TRUNC(target_date, WEEK(SUNDAY)) AS target_sunday

-- 事前にDECLAREで変数定義
DECLARE target_date DATE;
-- 変数に値を設定
SET target_date = DATE('2021-01-01');

SELECT
  target_date
  , DATE_TRUNC(target_date, WEEK(MONDAY)) AS target_monday
  , DATE_TRUNC(target_date, WEEK(TUESDAY)) AS target_tuesday
  , DATE_TRUNC(target_date, WEEK(WEDNESDAY)) AS target_wednesday
  , DATE_TRUNC(target_date, WEEK(THURSDAY)) AS target_thursday
  , DATE_TRUNC(target_date, WEEK(FRIDAY)) AS target_friday
  , DATE_TRUNC(target_date, WEEK(SATURDAY)) AS target_saturday
  , DATE_TRUNC(target_date, WEEK(SUNDAY)) AS target_sunday

◆実行結果