MySQL/SQLiteで日別・週別・月別で集計するクエリまとめ
Re:dashでメトリクスを作成する際に日別、週別、月別にデータの推移を見たく、いろいろ触ってみたので残しておく。
データ分析対象のアプリケーションのDBはMySQLを使用している。
また、adwordsで使われている集客コストをspread sheetに吐き出して、Re:dashでそれをインポートして利用しているのでQueryResultを操作するクエリも実行する必要があったので、今回はMySQLとSQLiteの日別・週別・月別のデータを集計するクエリをまとめる。(Re:dashのQueryResultはSQLiteで実行される)
なお、Re:dashに取り込める形式でadwordsのレポートデータを定期的にspread sheetに吐き出す方法は下記の記事にまとめています。
keyama.hatenablog.com
新規ユーザーの推移を見ることを仮定して下記のusersテーブルを集計するクエリをまとめる。
id | created_at |
---|---|
00001 | 2018-01-20 00:00:00 |
00002 | 2018-01-20 13:30:00 |
00003 | 2018-01-20 14:00:00 |
00004 | 2018-01-21 00:00:00 |
00005 | 2018-01-22 00:00:00 |
00006 | 2018-01-23 00:00:00 |
00007 | 2018-01-24 00:00:00 |
00008 | 2018-01-25 00:00:00 |
00009 | 2018-01-26 00:00:00 |
00010 | 2018-01-27 00:00:00 |
00011 | 2018-01-27 10:00:00 |
00012 | 2018-01-28 00:00:00 |
00013 | 2018-01-29 00:00:00 |
00014 | 2018-01-30 00:00:00 |
00015 | 2018-01-31 00:00:00 |
00016 | 2018-02-01 00:00:00 |
00017 | 2018-02-02 00:00:00 |
00018 | 2018-02-03 00:00:00 |
00019 | 2018-02-04 00:00:00 |
■日別
select count(id) as new_user_count, date_format(created_at ,'%Y-%m-%d') as day from users group by day;
SQLite(QueryResult)
select count(id) as new_user_count, strftime('%Y-%m-%d', created_at) as day from users group by day;
■週別
select count(id) as new_user_count, date_format(created_at ,'%Y-%u weeks') as week from users group by week;
SQLite(QueryResult)
select count(id) as new_user_count, strftime('%Y-%W weeks', created_at) as week from users group by week;
対象の日付の年の何番目の週か、で集計する。
なお、最初の月曜日を1週目とするように集計している。
本来であれば日曜スタートで集計したいところだがSQLiteの週単位のフォーマットは月曜スタートしかサポートしておらず、MySQLもそれに合わせるカタチとしている。
なお、MySQLで日曜スタートで集計したい場合は下記のようにおこなう。
select count(id) as new_user_count, date_format(created_at ,'%Y-%U weeks') as week from users group by week;
■月別
select count(id) as new_user_count, date_format(created_at ,'%Y-%m') as month from users group by month;
SQLite(QueryResult)
select count(id) as new_user_count, strftime('%Y-%m', created_at) as month from users group by month;