Mysql中查询连续一段时间内统计数据

Mysql中查询连续一段时间内统计数据

在一次生产环境中遇到统计当前日期的前10天每天的订单量，一开始想到的就是根据日期进行分组统计,但在查询的时候会存在一个问题:有可能某一天没有订单交易，日期分组中就不会出现，导致查询出来的数据日期不连续。
如果在代码中进行处理让日期变得连续，需要写一大堆代码，显然代码不够优雅。所以思来想去还是打数据库的主意，能不能通过数据库进行连续日期的构造，这样进行关联查询就可以避免上述的问题。

• 首先从用户自定变量构建一个序列开始讲起吧

-- 构建一个序列
select @i := @i+1 as 'NO' from mysql.help_topic,(select @i := 0) t where @i < 10;

下面是执行结果

通过构建这个简单的序列很快就能想到，通过同样的方法构建一个连续的日期序列自然不在话下。

• 接下来通过用户自定变量查询出当前日期前N天的连续日期列表

-- 构建一个日期序列
select @i := @i+1 as 'NO',DATE(DATE_SUB(CURRENT_DATE,INTERVAL @i DAY)) as 'date' from mysql.help_topic,(select @i := 0) t where @i < 10;

下面是执行结果

很显然通过同样的方式构建出了连续的日期序列，离想要的结果又进了一步，到了这里自然而然的就想到通过左关联查询业务分组后的数据就能得到想要的结果了。

• 通过关联查询获取想要的结果

-- 实际业务
SELECT t1.date,ifnull( t2.num, 0 ) AS num  FROM (SELECT @i := @i + 1 AS NO, DATE( DATE_SUB( CURRENT_DATE, INTERVAL @i DAY ) ) AS date FROM mysql.help_topic, (SELECT @i := 0) t WHERE @i < 10 ORDER BY date ) t1 
LEFT JOIN 
(SELECT DATE( create_time ) AS date, count( 1 ) AS num FROM `XXX_order` WHERE create_time > DATE( DATE_SUB( CURRENT_DATE, INTERVAL 10 DAY ) ) GROUP BY date) t2 ON t1.date = t2.date

最终结果出来了

写到这里大功告成，通过这种方法查询连续的一段时间内统计数据很方便，既满足业务需求又不用在程序中写多余的代码，在图表中可以直接使用。
举一反三查询连续的月份、年份统计数据同样一招搞定，再也不用在程序中去一大堆逻辑代码了。

参考MySql中用户定义的变量