FFFT

主にwebプロダクト開発に関連する話をつらつらと

MySQL/SQLiteで日別・週別・月別で集計するクエリまとめ

Re:dashでメトリクスを作成する際に日別、週別、月別にデータの推移を見たく、いろいろ触ってみたので残しておく。

データ分析対象のアプリケーションのDBはMySQLを使用している。
また、adwordsで使われている集客コストをspread sheetに吐き出して、Re:dashでそれをインポートして利用しているのでQueryResultを操作するクエリも実行する必要があったので、今回はMySQLSQLiteの日別・週別・月別のデータを集計するクエリをまとめる。(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

■日別

MySQL

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;

■週別

MySQL

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で日曜スタートで集計したい場合は下記のようにおこなう。

MySQL

select count(id) as new_user_count, date_format(created_at ,'%Y-%U weeks') as week from users group by week;

■月別

MySQL

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;