MySQL分组查询中对数据补0的实现方式

-- 存储过程
DROP PROCEDURE IF EXISTS `fill_zero_data`;
DELIMITER $$
CREATE PROCEDURE `fill_zero_data`()
BEGIN
    DECLARE cur_date DATE;
    DECLARE cur_hour INT;
    DECLARE max_date DATE;
    DECLARE max_hour INT;
    SET cur_date = '2022-01-01';
    SET cur_hour = 0;
    SELECT MAX(date) INTO max_date FROM your_table;
    SELECT MAX(hour) INTO max_hour FROM your_table WHERE date = max_date;
    WHILE cur_date <= max_date DO
        WHILE cur_hour < 24 DO
            INSERT INTO your_table (date, hour, count) 
            SELECT cur_date, cur_hour, 0 
            FROM your_table 
            WHERE date = cur_date AND hour = cur_hour 
            HAVING COUNT(*) = 0;
            SET cur_hour = cur_hour + 1;
        END WHILE;
        SET cur_hour = 0;
        SET cur_date = DATE_ADD(cur_date, INTERVAL 1 DAY);
    END WHILE;
END$$
DELIMITER ;

-- 按小时分组查询并补充0
SELECT 
    DATE_FORMAT(`date`, '%Y-%m-%d %H') AS `hour`,
    COALESCE(SUM(`count`), 0) AS `count`
FROM 
    `your_table`
WHERE 
    `date` BETWEEN '2022-01-01 00:00:00' AND '2022-01-31 23:59:59'
GROUP BY 
    `date_format`(`date`, '%Y-%m-%d %H')
ORDER BY 
    `date_format`(`date`, '%Y-%m-%d %H');

-- 按天分组查询并补充0
SELECT 
    DATE_FORMAT(`date`, '%Y-%m-%d') AS `day`,
    COALESCE(SUM(`count`), 0) AS `count`
FROM 
    `your_table`
WHERE 
    `date` BETWEEN '2022-01-01 00:00:00' AND '2022-01-31 23:59:59'
GROUP BY 
    `date_format`(`date`, '%Y-%m-%d')
ORDER BY 
    `date_format`(`date`, '%Y-%m-%d');

-- 按月分组查询并补充0
SELECT 
    DATE_FORMAT(`date`, '%Y-%m') AS `month`,
    COALESCE(SUM(`count`), 0) AS `count`
FROM 
    `your_table`
WHERE 
    `date` BETWEEN '2022-01-01 00:00:00' AND '2022-01-31 23:59:59'
GROUP BY 
    `date_format`(`date`, '%Y-%m')
ORDER BY 
    `date_format`(`date`, '%Y-%m');