日毎の登録者数をカウントして歯抜けさせずに日付毎のデータを取得する

※過去ブログの転記

※転記してて思ったが日付の穴埋めくらいアプリケーション側でやれ

id	create_time
1	2018-09-05 08:00:00
2	2018-09-05 12:35:00
3	2018-08-21 20:10:22
4	2018-09-02 11:10:48
5	2018-09-03 19:13:14

日付毎にユーザーの登録数を取得したい

SELECT 
    COUNT(users.id) AS id, 
    DATE_FORMAT(create_time, "%Y-%m-%d") AS create_time
FROM `users`
GROUP BY DATE_FORMAT(create_time, "%Y-%m-%d") ASC

↓ 結果

user_count	create_time
1	2018-08-21
1	2018-09-02
1	2018-09-03
2	2018-09-05

これだとカウント数がない日のデータが取得できない

SELECT
    COALESCE(user_tbl.user_count, 0) AS user_count, /*user_countがNULLの場合は0に変換*/
    date_tbl.`date`
FROM ( 
    SELECT DATE_ADD('2018-08-21', INTERVAL (num.generate_series -1) DAY ) AS `date` /*DATE_ADD()は当日を含めないので-1するか、前月末を基準とする*/
    FROM (
        SELECT 0 AS generate_series
        FROM DUAL 
        WHERE @num := (1 - 1) * 0 
        UNION ALL
        SELECT @num := @num + 1 /*step*/
        FROM `information_schema`.COLUMNS 
        LIMIT 31 /*1ヵ月分を取得するので末日の分だけ日数を取得*/
    ) AS num /*+1していく連番のテーブル*/
) AS date_tbl
LEFT JOIN(
    SELECT COUNT(users.id) AS user_count, DATE_FORMAT(users.create_time, '%Y-%m-%d') AS create_time
    FROM users
    GROUP BY DATE_FORMAT(users.create_time, '%Y-%m-%d') ASC
) AS user_tbl
ON date_tbl.`date` = user_tbl.create_time /*カレンダーの日付と本来のデータの日付をJOIN*/

↓ 結果

user_count	create_time
1	2018-08-21
0	2018-08-22
0	2018-08-23
0	2018-08-24
0	2018-08-25
0	2018-08-26
0	2018-08-27
0	2018-08-28
0	2018-08-29
0	2018-08-30
0	2018-08-31
0	2018-09-01
1	2018-09-02
1	2018-09-03
0	2018-09-04
2	2018-09-05
0	2018-09-06

(略)

これで歯抜けが埋まる
というか初めて知ったけどdateは予約語だと思ってバッククォートで囲ったけど予約語じゃなかった
MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.3 予約語

参考にしたサイト

symfoware.blog.fc2.com

d.hatena.ne.jp