I have a table

and I would like to get a result like this (aggregate each 3 days), gaps are possible

How can I do that?

CodePudding user response：

lag can be used to find all datetimes that constitute the start of a day range and then in a recursive cte, the subsquent datetimes in its range can be joined onto it. Additionally, the cte tracks the count of the day interval, producing an id which can be used to separate day ranges into groups of three:

with recursive cte(id, dt, val, cnt, r_num) as (
   select t1.datetime, t1.datetime, t1.value, 0, 0 from (
       select t.*, date(lag(t.datetime, 1) over (order by t.datetime)) != date(t.datetime) - interval '1 day' s 
       from tbl t) t1 
   where t1.s is null or t1.s
   union all
   select c.id, t.datetime, t.value, floor((r_num   1)/3)::int, r_num   1 
   from cte c join tbl t on date(t.datetime) = date(c.dt)   interval '1 day'
)
select date(t1.min_dt)|| ' - ' || date(t1.max_dt), t1.sum_val from (
   select c.id, c.cnt, min(c.dt) min_dt, max(c.dt) max_dt, sum(c.val) sum_val from cte c
group by c.id, c.cnt) t1
order by t1.id, t1.cnt

See fiddle for results on both the sample and a larger set of test rows.

CodePudding user response：

with cte as
(select min(dt)::date min, max(dt)::date max from table1)

,ranges as(
select (min   (i) *3* interval '1 day')::date inidate
,case when (min::date   ((2 i*3) * interval '1 day' )) <= max::date  then 
  (min::date   ((2 i*3) * interval '1 day' ))::date
 else
   max::date
 end as endDate
FROM  cte cross join (SELECT generate_series(0,1000,1) AS i) numbers
where 
(min::date   ((2 (i-1)*3) * interval '1 day' )) <= max)

select inidate::text||' - '||enddate::text period, sum(t.value) valued 

from ranges r
inner join table1 t on t.dt::date between r.iniDate and r.enddate
group by inidate::text||' - '||enddate::text
order by inidate::text||' - '||enddate::text

Test

http://sqlfiddle.com/#!17/1cee2/58