【BigQuery】よく使うクエリ構文

よく使う構文

• DATE()：タイムスタンプの時間を日次（例：2020/1/1）に変換
• TIMESTAMP()：文字列の日付をタイムスタンプに変換
• EXTRACT(DAYOFWEEK FROM ***)：タイムスタンプから曜日を抽出（日曜:１～土曜7:）
• CAST(FORMAT_TIMESTAMP("%H", ***) AS int64) ：タイムスタンプ（***）から時間を抽出
• _TABLE_SUFFIX BETWEEN '***' AND '***'：複数のテーブルを指定の範囲から参照する
• ST_DWITHIN(ST_GeogPoint(139.***,  35.***), ST_GeogPoint(longitude,latitude),500)：経度（139.***, longitude）、緯度（35.***, latitude）が指定した範囲内（500m）であれば抽出

◇クエリ例

SELECT
--文字列の日付をタイムスタンプに変換して日次のみ抽出
  DATE(TIMESTAMP(time),'Asia/Tokyo') AS date,
--曜日を抽出：日曜 1 ~ 7 土曜
  EXTRACT(DAYOFWEEK
    FROM
      jpn_day ) AS youbi,
--時間を抽出
  CAST(FORMAT_TIMESTAMP("%H", TIMESTAMP(time)) AS int64) AS hour
FROM
  `data*`
WHERE
  --data[*]部分を指定したテーブルを参照
  _TABLE_SUFFIX BETWEEN '20200201'
  AND '2020203'
  --標準時間を日本時間に変換して期間を指定
  AND TIMESTAMP(DATETIME(TIMESTAMP(time),
      'Asia/Tokyo')) >= TIMESTAMP('2020-02-01 00:00:00')
  AND TIMESTAMP(DATETIME(TIMESTAMP(time),
      'Asia/Tokyo')) < TIMESTAMP('2020-02-03 00:00:00')
  --指定の緯度経度より500m以内,緯度: 35.*** 経度: 139.***
  AND ST_DWITHIN(ST_GeogPoint(139.***,
      35.***),
    ST_GeogPoint(longitude,
      latitude),
    500)