BigQueryで〜年前の今日からいついつまでのデータをとるSQL

BigQueryのサンプルデータセットは、数年前からの日付固定で、リアルタイムに更新をかけるダッシュボードで扱うのは多少変なので、今日という日は動かさず、何年前かを、今年であるかのように仮定したかったので、クエリを書きました

〜年前の今日から〜日間先までの総PV

SELECT
  date,
  SUM(totals.pageviews) as TotalPageviews
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN
      FORMAT_DATE('%Y%m%d',
        DATE_SUB(
          CURRENT_DATE('Asia/Tokyo'),
          INTERVAL 4 YEAR
        )
      ) 
      AND
      FORMAT_DATE('%Y%m%d',
        DATE_ADD(
          DATE_SUB(
            CURRENT_DATE('Asia/Tokyo'),
            INTERVAL 4 YEAR
          ), 
         INTERVAL 1 MONTH)
      )  
GROUP BY date
ORDER BY date

〜年前の今日とかをDECRAREしてしまう

DECRARE文を使って、上記クエリを使うたびに書かずとも、変数として扱うことができます。

DECLARE startDate STRING;
DECLARE endDate STRING;

SET startDate = (
  FORMAT_DATE('%Y%m%d',
    DATE_SUB(
      CURRENT_DATE('Asia/Tokyo'),
        INTERVAL 4 YEAR
      )
    ) 
  );

SET endDate = (
  FORMAT_DATE('%Y%m%d',
    DATE_ADD(
      DATE_SUB(
        CURRENT_DATE('Asia/Tokyo'),
          INTERVAL 4 YEAR
        ), 
      INTERVAL 1 MONTH)
    ) 
  );

DECRAREした変数の使用例

上記２つを組み合わせて、(〜年前の)今日から何日か先までのトータルページビューを取得してみます。

DECLARE startDate STRING;
DECLARE endDate STRING;

SET startDate = (
  FORMAT_DATE('%Y%m%d',
    DATE_SUB(
      CURRENT_DATE('Asia/Tokyo'),
        INTERVAL 4 YEAR
      )
    ) 
  );

SET endDate = (
  FORMAT_DATE('%Y%m%d',
    DATE_ADD(
      DATE_SUB(
        CURRENT_DATE('Asia/Tokyo'),
          INTERVAL 4 YEAR
        ), 
      INTERVAL 1 MONTH)
    ) 
  );

SELECT
  date,
  SUM(totals.pageviews) as TotalPageviews
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN  startDate AND endDate
GROUP BY date
ORDER BY date