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')