mysql 日期区间创建_按日期范围统计数据

第一步：mysql使用存储过程，创建日历表

创建日历表：

CREATE TABLE `capacity_common_date` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`DAY_ID` varchar(10) DEFAULT NULL,

`DAY_SHORT_DESC` varchar(10) DEFAULT NULL,

`DAY_LONG_DESC` varchar(50) DEFAULT NULL,

`WEEK_DESC` varchar(20) DEFAULT NULL,

`WEEK_ID` varchar(20) DEFAULT NULL,

`WEEK_LONG_DESC` varchar(50) DEFAULT NULL,

`MONTH_ID` varchar(20) DEFAULT NULL,

`MONTH_LONG_DESC` varchar(50) DEFAULT NULL,

`QUARTER_ID` varchar(20) DEFAULT NULL,

`QUARTER_LONG_DESC` varchar(20) DEFAULT NULL,

`YEAR_ID` varchar(20) DEFAULT NULL,

`YEAR_LONG_DESC` varchar(50) DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

构建Mysql存储过程：单次创建一年数据，入参为年份

CREATE DEFINER=`root`@`localhost` PROCEDURE `f_m_dim_day`(in yr VARCHAR(20))

begin

declare i int;

declare start_date varchar(20);

declare end_date varchar(20);

declare date_count int;

set i=0;

set start_date= concat(yr, '-01-01');

set end_date = concat(yr+1,'-01-01');

DELETE from capacity_common_date where year_id = yr;

set date_count = datediff(end_date, start_date);

while i < date_count DO

INSERT into capacity_common_date (DAY_ID,DAY_SHORT_DESC,DAY_LONG_DESC,WEEK_DESC,WEEK_ID,WEEK_LONG_DESC,MONTH_ID,MONTH_LONG_DESC,QUARTER_ID,QUARTER_LONG_DESC,YEAR_ID,YEAR_LONG_DESC)

SELECT

DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m%d') DAY_ID,

DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') DAY_SHORT_DESC,

DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年%m月%d日') DAY_LONG_DESC,

case DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')) when 1 then '星期日' when 2 then '星期一' when 3 then '星期二' when 4 then '星期三' when 5 then '星期四' when 6 then '星期五' when 7 then '星期六' end WEEK_DESC,

DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%u') WEEK_ID,

DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%u周') WEEK_LONG_DESC,

DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m') MONTH_ID,

DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%m月') MONTH_LONG_DESC,

CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),quarter(STR_TO_DATE( start_date,'%Y-%m-%d %H:%i:%s'))) QUARTER_ID,

CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),'年第',quarter(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')),'季度') QUARTER_LONG_DESC,

DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y') YEAR_ID,

DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年') YEAR_LONG_DESC

from dual;

set i=i+1;

set start_date = DATE_FORMAT(date_add(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),interval 1 day),'%Y-%m-%d');

end while;

end

创建日期：按条件创建日历,入参：年份，次数：从yr年起到yr+num年

CREATE DEFINER=`root`@`localhost` PROCEDURE `count1`(in yr VARCHAR(20),in num int)

begin

declare i int;

set i=0;

while i <= num DO

CALL f_m_dim_day(yr+i);

set i=i+1;

end WHILE;

end

执行语句：从2019年开始创建100年的时间数据

call count1('2019',100);

执行完成之后千万别忘记删除自己创建的存储过程哦~~！😆😆😆

DROP PROCEDURE if EXISTS f_m_dim_day;

DROP PROCEDURE if EXISTS count1;

删除之后记得查询下存储过程或函数：

SHOW PROCEDURE STATUS;

到此就完成了全部创建过程，接下来，我们来试试查询统计吧~！

第二步：连表查询

使用用户数据表，其中一列是创建时间，

统计一段时间内每天创建的用户总数：

SELECT

DATE_FORMAT( a.DAY_SHORT_DESC, '%Y-%m-%d' ) t,

COUNT( s.id ) count

FROM

capacity_common_date a

LEFT JOIN capacity_pub_customer s ON DATE_FORMAT( a.DAY_SHORT_DESC, '%Y-%m-%d' ) = DATE_FORMAT( s.create_date, '%Y-%m-%d' )

WHERE

a.DAY_SHORT_DESC BETWEEN '2019-01-01'

AND '2020-01-01'

GROUP BY

t;