MySQL:实现累加求和的功能
MySQL:实现累加求和的功能
1、表结构和表数据
id | company_id | start_time | finish_time | watch_time |
---|---|---|---|---|
1 | 2703 | 2020-08-12 21:51:27 | 2020-08-12 21:51:32 | 185 |
2 | 2703 | 2020-08-12 21:55:21 | 2020-08-12 21:55:26 | 640 |
3 | 2703 | 2020-08-13 13:59:18 | 2020-08-13 13:59:22 | 410 |
4 | 2703 | 2020-08-13 14:07:34 | 2020-08-13 14:08:34 | 890 |
5 | 2703 | 2020-08-13 14:24:59 | 2020-08-13 14:25:55 | 205 |
6 | 2703 | 2020-08-13 14:38:32 | 2020-08-13 14:39:28 | 115 |
7 | 2703 | 2020-08-15 14:48:01 | 2020-08-15 14:49:00 | 120 |
8 | 2703 | 2020-08-16 14:50:16 | 2020-08-16 14:51:16 | 300 |
9 | 2703 | 2020-08-17 15:00:16 | 2020-08-17 15:01:16 | 160 |
10 | 2703 | 2020-08-17 15:08:14 | 2020-08-17 15:09:13 | 75 |
2、需求说明
现在希望得到每天累计的观看时长,即8月12日到8月13日的累计,8月12日到8月15日的累计,以此类推。
3、解决方法
step1: 先按照日期分组聚合求总:
sql
select finish_time, SUM(watch_time) as watch_time from live_results where company_id = 2703 GROUP BY DATE_FORMAT(finish_time,'%Y-%m-%d');
分组聚合
step2: 累加
sql
SET @sum := 0;SELECT DATE_FORMAT(t.finish_time, '%Y-%m-%d') as date, (@sum := @sum + t.watch_time) as day_total from ( select finish_time, SUM(watch_time) as watch_time from live_results where company_id = 2703 GROUP BY DATE_FORMAT(finish_time,'%Y-%m-%d') ) as t;
累加
其中:
SET @sum := 0:@表示自定义一个变量; :=是赋值;即设置了一个变量sum并赋值0。
@sum := @sum + t.watch_time 类似a+=1,即a=a+1的思想,求变量的累加值。
step3: 合并
用 select @sum :=0 查做一个表来初始化变量sum = 0, 然后进行联合查询,这样就并为一个sql写到代码中,结果不变。
sql
SELECT DATE_FORMAT(t.finish_time, '%Y-%m-%d') as date, (@sum := @sum + t.watch_time) as day_total from ( select finish_time, SUM(watch_time) as watch_time from live_results a, (select @sum :=0) b where company_id = 2703 GROUP BY DATE_FORMAT(finish_time,'%Y-%m-%d') ) as t;
至此,需求合到实现。
来源https://www.cnblogs.com/luckyliulin/p/14682206.html