MySQL
分析
GoogleBigQuery
Query

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

目的

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
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