Mysql累计查询

最新推荐文章于 2024-10-27 19:49:02 发布

原创最新推荐文章于 2024-10-27 19:49:02 发布 · 2.3k 阅读

5 ·

CC 4.0 BY-SA版权

Mysql查询优化专栏收录该内容

2 篇文章

订阅专栏

DROP TABLE IF EXISTS `w_test`;

CREATE TABLE `w_test` (
  `id` int(11) DEFAULT NULL,
  `w_time` datetime DEFAULT NULL,
  `w_money` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- ----------------------------
-- Records of w_test
-- ----------------------------
INSERT INTO `w_test` VALUES ('1', '2017-01-01 09:40:10', '1');
INSERT INTO `w_test` VALUES ('2', '2017-02-07 11:40:31', '2');
INSERT INTO `w_test` VALUES ('3', '2017-02-14 09:40:54', '3');
INSERT INTO `w_test` VALUES ('4', '2017-03-07 11:40:31', '4');
INSERT INTO `w_test` VALUES ('5', '2017-07-17 11:40:31', '5');
INSERT INTO `w_test` VALUES ('6', '2016-04-14 11:02:47', '1');
INSERT INTO `w_test` VALUES ('7', '2017-04-14 11:02:49', '1');

尽量不要用标量子查询参考mysql中使用分析函数(开窗函数)

及https://stackoverflow.com/questions/3333665/rank-function-in-mysql

1.每个时间点累计之前的值

select id,w_time,
	(select sum(b.w_money) from w_test b where b.w_time<=a.w_time) w_money
from w_test a ORDER BY w_time
改写如下：
select  
	a.w_time,a.w_money,
	@curVal := @curVal + a.w_money as sum_money
from w_test a,(select @curVal:=0) r  
order by a.w_time

查询结果

20W记录测试 w_test02表

ALTER TABLE w_test02 ADD INDEX w_test02_nk (w_time);
select 
	max(DATE_FORMAT(w_time,'%Y-%m')),max(sum_money)
FROM(
		select    
				a.w_time,a.w_money,  
				@curVal := @curVal + a.w_money as sum_money  
		from w_test02 a,(select @curVal:=0) r    
		order by a.w_time 
		) aa
GROUP BY DATE_FORMAT(w_time,'%Y-%m')

0.5秒左右

2.按月累计

select id,DATE_FORMAT(w_time, '%Y-%m') w_time,
	(select sum(b.w_money) from w_test b where DATE_FORMAT(b.w_time, '%Y-%m')<=DATE_FORMAT(a.w_time, '%Y-%m')) w_money
from w_test a
GROUP BY DATE_FORMAT(w_time, '%Y-%m')

3.按月累计，缺失月份补全

select DATE_FORMAT(c.cal_date, '%Y-%m') cal_date,
	(select sum(b.w_money) from w_test b where DATE_FORMAT(b.w_time, '%Y-%m')<=DATE_FORMAT(a.w_time, '%Y-%m')) w_money
from w_test a
RIGHT JOIN sys_month c on(DATE_FORMAT(a.w_time, '%Y-%m')=DATE_FORMAT(c.cal_date, '%Y-%m'))
where c.cal_date>=STR_TO_DATE('2017-01','%Y-%m')
		and c.cal_date<STR_TO_DATE('2018-01','%Y-%m')
GROUP BY DATE_FORMAT(c.cal_date, '%Y-%m')

4.按月累计，缺失月份补全,值缺失则等于上月值

select DATE_FORMAT(c.cal_date, '%Y-%m') cal_date,	
  (select sum(b.w_money) from w_test b where DATE_FORMAT(b.w_time, '%Y-%m')<=DATE_FORMAT(c.cal_date, '%Y-%m')) w_money
from sys_month c 
where c.cal_date>=STR_TO_DATE('2017-01','%Y-%m')
	and c.cal_date<STR_TO_DATE('2018-01','%Y-%m')
GROUP BY DATE_FORMAT(c.cal_date, '%Y-%m')

日期表