按日统计注册人数SQL语句详解（自动补齐空缺数据）

 

按日统计XX人数其实是一个很常用的功能。比如说按日统计注册人数、按日统计订单数等。原理其实很相似，一通百通。

话不多说，进入正题。

一、最终sql语句展示

我们先来看下最终的sql语句及得到的结果：

SELECT
	date,
	sum（total）
FROM
	(
	   SELECT
			DATE_FORMAT(create_time, '%Y-%m-%d') date,
			COUNT(id) total
		FROM
			app_user
		WHERE
			create_time >= '2018-10-15'
		AND create_time <= '2018-10-20'
		GROUP BY
			DATE_FORMAT(create_time, '%Y-%M-%D')
		UNION
			(SELECT '2018-10-15', 0)
		UNION
			(SELECT '2018-10-16', 0)
		UNION
			(SELECT '2018-10-17', 0)
		UNION
			(SELECT '2018-10-18', 0)
		UNION
			(SELECT '2018-10-19', 0)
		UNION
			(SELECT '2018-10-20', 0)
	) AS od
GROUP BY
	date
ORDER BY
	date

以下是执行结果：

这其实就是我们想要的结果了，上述sql其实略微复杂，接下来我们将对上述sql语句进行解析。

二、内层SQL语句解析

下面我们来解析一下上面的sql语句,这部分的作用是补全空缺记录。首先拿到内层sql，即FROM之后的语句：

        SELECT
			DATE_FORMAT(create_time, '%Y-%m-%d') date,
			COUNT(id) total
		FROM
			app_user
		WHERE
			create_time >= '2018-10-15'
		AND create_time <= '2018-10-20'
		GROUP BY
			DATE_FORMAT(create_time, '%Y-%M-%D')
		UNION
			(SELECT '2018-10-15', 0)
		UNION
			(SELECT '2018-10-16', 0)
		UNION
			(SELECT '2018-10-17', 0)
		UNION
			(SELECT '2018-10-18', 0)
		UNION
			(SELECT '2018-10-19', 0)
		UNION
			(SELECT '2018-10-20', 0)

 以上sql语句的执行结果为（已做排序）：

 上面这句sql语句，以union子句为分界线可以分为前后两个部分。前半部分的作用是按日期分组统计人数，后半部分的作用是根据查询日期补齐所有日期（注意这里是要进行遍历补齐的，具体的操作方式可以用java代码遍历拼接）。

 三、外层SQL语句解析

接下来是外层sql，这部分的操作是基于内层sql的结果的，内层sql中我们已经完成了空缺日期的补齐操作，但是又引入新的问题，那就是同个日期可能存在多条记录，我们在外层sql要执行的就是去重操作。

SELECT
	date,
	sum（total）
FROM
	(
		第一部分的代码
	) AS od
GROUP BY
	date
ORDER BY
	date

这其实是很基础的sql语句，就是按日期分组后统计总数。ok，到这里我们就大功告成了。

四、总结

总结一下，这种解决方式的点睛之处就是采用了union进行了虚拟数据的拼接，而拼接完成后再使用分组函数达到去重效果，其实用的也是很基础的sql知识，希望大家看了能有所收获！