MySQL按日统计数据，时间范围内无数据补0

转载

MySQL按日统计数据，时间范围内无数据补0

统计用户在指定日期范围内的 日访问量 ，根据此数据画出走势图。

如果直接按照日期字段group by 查询，若某天没有访问，则该日期数据是不出现的，将导致走势图不能很好地表明当前状况，因此无数据情况下补0，来达成需求。

解决思路：

1. 先用一个查询把指定日期范围的日期列表搞出来

   SELECT
   	@cdate := date_add(@cdate, INTERVAL - 1 DAY) AS date_str,
   	0 AS date_count
   FROM
   	(
   		SELECT
   			@cdate := date_add(CURDATE(), INTERVAL + 1 DAY)
   		FROM
   			数据库随便某个有数据的表名
   	) t1
   WHERE
   	@cdate > '2020-07-01'
   AND @cdate < '2020-07-31'
   
      
      
   
      
      
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13

2. 业务统计查询也按上述日期查询给统计日期和数量设置别名

   SELECT
   	FROM_UNIXTIME(m.时间戳字段, "%Y-%m-%d") AS date_str,
   	count(*) AS date_count
   FROM
   	业务数据表名 AS m
   GROUP BY
   	FROM_UNIXTIME(m.时间戳字段, "%Y-%m-%d")
   
      
      
   
      
      
   1
   2
   3
   4
   5
   6
   7

3. 把两个查询用左连接合起，没数量的日期填0

   SELECT
   	t1.date_str,COALESCE (t2.date_total_count, 0) AS date_total_count
   FROM
   	(
   		SELECT
   			@cdate := date_add(@cdate, INTERVAL - 1 DAY) AS date_str
   		FROM
   			(
   				SELECT
   					@cdate := date_add(CURDATE(), INTERVAL + 1 DAY)
   				FROM
   					业务数据表名
   			) tmp1
   		WHERE
   			@cdate > '2020-07-01'
   	) t1
   LEFT JOIN (
   	SELECT
   		FROM_UNIXTIME(m.时间戳字段, "%Y-%m-%d") AS date_str,
   		count(*) AS date_total_count
   	FROM
   		业务数据表名 AS m
   	WHERE
   		m.时间戳字段 > 'XXXX-XX-XX'
   	GROUP BY
   		FROM_UNIXTIME(m.时间戳字段, "%Y-%m-%d")
   ) t2 ON t1.date_str = t2.date_str
   
      
      
   
      
      
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27

实际示例：

SELECT
	t1.logintime, COALESCE (t2.count, 0) AS count
FROM
	(
		SELECT@cdate := date_add(@cdate, INTERVAL - 1 DAY) AS logintime
		FROM
			(
				SELECT
					@cdate := date_add(CURDATE(), INTERVAL + 1 DAY)
				FROM
					tb_userloginhistory
			) tmp1
		WHERE
			@cdate > '2020-07-01'
	) t1
LEFT JOIN (
	SELECT
		FROM_UNIXTIME(logindatetime, "%Y-%m-%d") AS logintime, COUNT(1) AS count
	FROM
		tb_userloginhistory
	WHERE
		logindatetime > '2020-07-01'
	GROUP BY
		FROM_UNIXTIME(logindatetime, "%Y-%m-%d")
) t2 
ON t1.logintime = t2.logintime

 
 

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

运行结果：

                                </div><div><div></div></div>
            <link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-60ecaf1f42.css" rel="stylesheet">
                            </div>
        </article>