MySQL数据统计存储过程小时天月分组无数据补0

-- 存储过程代码
CREATE PROCEDURE `proc_statistics`()
BEGIN
    -- 按小时分组
    CREATE TEMPORARY TABLE IF NOT EXISTS `tmp_statistics_hour` (
        `stat_time` DATETIME NOT NULL,
        `count` INT UNSIGNED NOT NULL DEFAULT 0,
        PRIMARY KEY (`stat_time`)
    );
    INSERT INTO `tmp_statistics_hour` (`stat_time`, `count`) 
        SELECT DATE_FORMAT(`created_at`, '%Y-%m-%d %H:00:00') AS `stat_time`, COUNT(*) AS `count` 
        FROM `table_name` 
        WHERE `created_at` BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW() 
        GROUP BY `stat_time`;

    -- 按天分组
    CREATE TEMPORARY TABLE IF NOT EXISTS `tmp_statistics_day` (
        `stat_time` DATE NOT NULL,
        `count` INT UNSIGNED NOT NULL DEFAULT 0,
        PRIMARY KEY (`stat_time`)
    );
    INSERT INTO `tmp_statistics_day` (`stat_time`, `count`) 
        SELECT DATE(`created_at`) AS `stat_time`, COUNT(*) AS `count` 
        FROM `table_name` 
        WHERE `created_at` BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() 
        GROUP BY `stat_time`;

    -- 按月分组
    CREATE TEMPORARY TABLE IF NOT EXISTS `tmp_statistics_month` (
        `stat_time` DATE NOT NULL,
        `count` INT UNSIGNED NOT NULL DEFAULT 0,
        PRIMARY KEY (`stat_time`)
    );
    INSERT INTO `tmp_statistics_month` (`stat_time`, `count`) 
        SELECT DATE_FORMAT(`created_at`, '%Y-%m-01') AS `stat_time`, COUNT(*) AS `count` 
        FROM `table_name` 
        WHERE `created_at` BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW() 
        GROUP BY `stat_time`;

    -- 补0
    SELECT `stat_time`, IFNULL(`count`, 0) AS `count` 
    FROM (
        SELECT `stat_time` FROM `tmp_statistics_hour` 
        UNION SELECT `stat_time` FROM `tmp_statistics_day` 
        UNION SELECT `stat_time` FROM `tmp_statistics_month`
    ) AS `tmp_stat_time` 
    LEFT JOIN `tmp_statistics_hour` ON `tmp_stat_time`.`stat_time` = `tmp_statistics_hour`.`stat_time`
    LEFT JOIN `tmp_statistics_day` ON `tmp_stat_time`.`stat_time` = `tmp_statistics_day`.`stat_time`
    LEFT JOIN `tmp_statistics_month` ON `tmp_stat_time`.`stat_time` = `tmp_statistics_month`.`stat_time`
    ORDER BY `stat_time` ASC;

    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS `tmp_statistics_hour`;
    DROP TEMPORARY TABLE IF EXISTS `tmp_statistics_day`;
    DROP TEMPORARY TABLE IF EXISTS `tmp_statistics_month`;
END;