【BigQuery】日ごとのログインユーザー（DAU）をGROUP BYで集計する

 

GROUP BYで日ごとのユーザーを集計する

 

テーブル名：login_table

user_id	timestamp
101	2019-01-01 00:10:00
101	2019-01-02 12:00:00
102	2019-01-01 13:00:00
102	2019-01-02 10:00:00
102	2019-01-02 18:00:00
103	2019-01-03 10:00:00
104	2019-01-04 10:00:00
105	2019-01-05 10:00:00
106	2019-01-01 14:00:00

 

 

-- 日ごとのユーザー数を集計する
SELECT
  DATE(timestamp) AS login_date,
  COUNT(DISTINCT user_id)
FROM
  login_table
GROUP BY
  login_date

 

注意点はログインデータがタイムスタンプになっている場合、日付に直すためにDATE()を使っている点、これによって内部上は2019-01-02 12:00:00→2019-01-02 という形に変換されている。そしてGROUP BYのキーはメイン句で整形した列データを用いることが可能なところもポイント。

 

最後に日付ごとに集計して各日のログインユーザー数をCOUNT()で集計する。この際同じ日に複数回ログインしているユーザーが重複して集計されてしまうので、DISTINCTで重複排除している

 

 

終わり

 

ちなみに分析用SQLの話だと↓の参考書が非常に役立っているので、UDFとか分析関数とか使うような業務をされている人には非常にオススメです。