Question 90153 in PostgreSQL Question posted in
The official documentation can be found here.

Aggregate every 3 days – Postgresql

I have a table

datetime value
2022-10-21 11:23:00 1
2022-10-22 12:12:00 2
2022-10-23 13:43:00 0
2022-10-24 14:01:00 5
2022-10-25 10:23:00 2

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

datetime value
2022-10-21 – 2022-10-23 3
2022-10-24 – 2022-10-25 7

How can I do that?

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0 
    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

    Login or Signup to reply.
  3. 0

    You could select row number for each row, then apply aggregate SUM function on group of each 3 rows.

    SELECT 
  MIN(date_time) AS start_date, 
  MAX(date_time) AS end_date, 
  SUM(value) AS value 
FROM 
  (
    SELECT 
      *, 
      ROW_NUMBER () OVER (ORDER BY date_time) AS row_index 
    FROM 
      your_table
  ) AS data 
GROUP BY 
  (row_index - 1)/ 3;

    You could try it here: https://onecompiler.com/postgresql/3yv74qc4p

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search