mysql直方图统计

需求：按天统计2019-06-01 到 2019-06-30 这段时间每天的求助数量
如果单独用下面的语句查询：

SELECT
        count( id ) AS total,
        date_format( gmt_create, "%Y-%m-%d" ) help_date 
    FROM
        v_help_record 
    WHERE
        gmt_create BETWEEN "2019-06-01 00:00:00" 
        AND "2019-06-30 23:59:59" 
    GROUP BY
        help_date 

得到的结果是这样的：

结果1

为0的天数没有返回，导致日期不连续

---

把上面的语句修改一下：

SELECT
    t1.help_date AS help_date,
    IFNULL( t2.total, 0 ) AS total 
FROM
    (
    SELECT
        @s := @s + 1 AS _index,
        DATE_FORMAT( DATE_SUB( "2019-06-30 23:59:59", INTERVAL @s DAY ), "%Y-%m-%d" ) AS help_date 
    FROM
        v_help_record,
        ( SELECT @s :=- 1 ) temp 
    WHERE
        DATE( DATE_SUB( "2019-06-30 23:59:59", INTERVAL @s DAY ) ) > "2019-06-01 00:00:00" 
    ) AS t1
    LEFT JOIN (
    SELECT
        count( id ) AS total,
        date_format( gmt_create, "%Y-%m-%d" ) help_date 
    FROM
        v_help_record 
    WHERE
        gmt_create BETWEEN "2019-06-01 00:00:00" 
        AND "2019-06-30 23:59:59" 
    GROUP BY
        help_date 
    ) AS t2 ON t1.help_date = t2.help_date 
ORDER BY
    t1.help_date;

查询结果：

image.png
tips：这样查询效率有点慢，实测几乎需要5秒才返回数据，有大神有优化建议的话欢迎留言指教

分析：
1.首先，生成一个临时日期表t1

(
    SELECT
        @s := @s + 1 AS _index,
        DATE_FORMAT( DATE_SUB( "2019-06-30 23:59:59", INTERVAL @s DAY ), "%Y-%m-%d" ) AS help_date 
    FROM
        v_help_record,
        ( SELECT @s :=- 1 ) temp 
    WHERE
        DATE( DATE_SUB( "2019-06-30 23:59:59", INTERVAL @s DAY ) ) > "2019-06-01 00:00:00" 
    ) AS t1

2. 数据来源表t2

(
    SELECT
        count( id ) AS total,
        date_format( gmt_create, "%Y-%m-%d" ) help_date 
    FROM
        v_help_record 
    WHERE
            gmt_create BETWEEN "2019-06-01 00:00:00" 
        AND "2019-06-30 23:59:59" 
    GROUP BY
        help_date 
    ) AS t2

3. 用t1 and t2联合查询得到结果。