GoogleBigQueryで日次、週次、月次の集計をする #MySQL

目的

GoogleBigQueryでDAU、WAU、MAUの集計をする。非エンジニア向けです。
特にWAUがやっかいだったので自分用にもまとめておきます。
MySQLとの違いで型まわりでひっかかることが多かったです。StandardSQLです。

データソース

daily_active_user

login_date	user_id
2018-01-01	100
2018-01-02	100
2018-01-03	100
2018-01-04	100
2018-01-05	100
2018-01-06	100
2018-01-07	100
2018-01-08	100
2018-01-09	100
2018-01-10	100
2018-01-11	100
2018-01-12	100
2018-01-13	100
2018-01-14	100
2018-01-14	200

期待する結果

DAU

date	dau
2018-01-01	1
2018-01-02	1
2018-01-03	1
2018-01-04	1
2018-01-05	1
2018-01-06	1
2018-01-07	1
2018-01-08	1
2018-01-09	1
2018-01-10	1
2018-01-11	1
2018-01-12	1
2018-01-13	1
2018-01-14	2

WAU

date	wau
2018-01-01	1
2018-01-08	2

MAU

date	mau
2018-01-01	2

Query

DAU

SELECT
  login_date,
  COUNT(DISTINCT user_id) AS dau
FROM daily_active_user
GROUP BY 
    login_date

WAU

SELECT
  DATE_ADD(login_date, INTERVAL - EXTRACT(DAYOFWEEK FROM DATE_ADD(login_date, INTERVAL -1 day)) +1 day) AS w_week,
  COUNT(DISTINCT user_id) AS wau
FROM daily_active_user
GROUP BY 
    w_week

DAYOFWEEK、EXTRACTなどは公式リファレンスを読んで下さい
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja

MAU

SELECT
  concat(substr(cast(login_date as string),1,7),"-01") as month
  COUNT(DISTINCT user_id) AS dau
FROM daily_active_user
GROUP BY 
    month

その他

mysqlだとこんな感じ

SELECT DATE(login_date) - INTERVAL WEEKDAY(login_date) DAY - INTERVAL 1 DAY AS w_week,
       COUNT(distinct user_id) AS wau
FROM daily_active_user
GROUP BY wday