# 分组计算时要包含前一组的最后一条记录，怎么实现？

t= table(concatDT(today(), 09:30:00.001 09:30:59.999 09:31:00.000 09:31:25.812 09:32:01.001 09:32:40.021) as timestamp, 1..6 as vol)


timestamp                 vol
2020.07.20T09:30:00.001    1
2020.07.20T09:30:59.999    2
2020.07.20T09:31:00.000    3
2020.07.20T09:31:25.812    4
2020.07.20T09:32:01.001    5
2020.07.20T09:32:40.021    6

windowStart                sum_vol
2020.07.20T09:30:00.000     3
2020.07.20T09:31:00.000     9
2020.07.20T09:32:00.000     15

0

select date_format(time,'%Y-%m-%d')as time, sum(vol) from
(
-- 数据拼接：拼接分组后每组中最小时间和上一组的最大时间的vol值
select i.time,  j.vol  from
(
select time, max(time1)as time1 from
(
select e.time as time, f.time as time1 from test e,
(
-- 获取分组后的除最后一组之外的每组中最后一条数据
select c.time, d.vol from
(
select a.time from
(select max(time) as time  from test group by date_format(time,'%Y-%m-%d') order by time) a ,
(select max(time) as time  from test group by date_format(time,'%Y-%m-%d') order by time desc  limit 1) b
where a.time != b.time
)c, test d
where c.time = d.time
) f
where e.time > f.time  group by date_format(e.time,'%Y-%m-%d') ,date_format(f.time,'%Y-%m-%d')

) h group by time
)i, test j where i.time1 = j.time

union all

-- 查询分组后每组的最大时间和每组求和后的vol值
select max(time) as time, sum(vol) as vol  from test group by date_format(time,'%Y-%m-%d') order by time
) g group by date_format(g.time,'%Y-%m-%d')