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: 先按照日期分组聚合求总：
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: 累加
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写到代码中，结果不变。
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;
至此，需求得到实现。
