Mysql统计近30天的数据，无数据的填充0

r囧r小猫 2022-11-06 15:56 206阅读 0赞

Mysql统计近30天的数据，无数据的填充0。 这个应该是我们在做统计分析的时候，经常遇到的一个需求。

先说一般的实现方式，就是按照日期进行分组，但是这样会有一个问题，如果数据库表中有一天没有数据，那么是统计不出来结果的。类似下图

2020-01-01  10
    2020-01-03  20
    2020-01-04  4

这个时候，我们发现，2020-01-02 这一天是没有数据的，我们希望没有数据的这一天得到也能返回日期，而对应的数据是0，期望如下：

2020-01-01  10
    2020-01-02  0
    2020-01-03  20
    2020-01-04  4

这时候，单单group by就没办法实现了。

那应该怎么办呢？

一般情况就是我们应该先获取一个日期的虚拟表，把这30天的时间都列出来，然后用这个日期虚拟表再去关联我们的业务表，关联没数据的值设置为空即可，那么怎么得到近30天的日期的，给出sql实现方式

SELECT
    		@s := @s + 1 AS indexs,
    		DATE_FORMAT( DATE( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ), '%Y-%m-%d' ) AS dates 
    	FROM
    		mysql.help_topic,
    		( SELECT @s := -1 ) temp  #不想包含当天，@s:=0
    	WHERE
    		@s < 30 
    	ORDER BY
    		dates

运行得到的结果如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzcWluZ2Zlbmc_size_16_color_FFFFFF_t_70][]

当然可根据自己的需要进行排序，时间有了，接下来直接关联自己的业务表就可以了，给出demo：

SELECT
    	date_table.dates AS dateValue,
    	IFNULL( temp.count, 0 ) AS count 
    FROM
    	(
    	SELECT
    		@s := @s + 1 AS indexs,
    		DATE_FORMAT( DATE( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ), '%Y-%m-%d' ) AS dates 
    	FROM
    		mysql.help_topic,
    		( SELECT @s := 0 ) temp 
    	WHERE
    		@s < 30 
    	ORDER BY
    		dates 
    	) date_table
    	LEFT JOIN (
    	SELECT LEFT
    		( create_time, 10 ) AS dateValue,
    		count( * ) AS count 
    	FROM
    		monitor_log_record t1 
    	WHERE
    		t1.log_type = 1 
    		AND t1.error_type = 1 
    		AND t1.project_id = 1 
    	GROUP BY
    		LEFT ( create_time, 10 ) 
    	) temp ON date_table.dates = temp.dateValue 
    ORDER BY
    	date_table.dates DESC

好了，记录一下，希望可以帮助到你。有帮助的话记得点赞收藏加关注，感谢！！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzcWluZ2Zlbmc_size_16_color_FFFFFF_t_70]: /images/20221023/834f3dc592f84778972a967586a79b94.png