Assume you have (in Postgres 9.1 ) a table like this:
date | value
which have some gaps in it (I mean: not every possible date between min(date) and max(date) has it's row).
My problem is how to aggregate this data so that each consistent group (without gaps) is treated separately, like this:
min_date | max_date | [some aggregate of "value" column]
Any ideas how to do it? I believe it is possible with window functions but after a while trying with lag()
and lead()
I'm a little stuck.
For instance if the data are like this:
date | value
---------------+-------
2011-10-31 | 2
2011-11-01 | 8
2011-11-02 | 10
2012-09-13 | 1
2012-09-14 | 4
2012-09-15 | 5
2012-09-16 | 20
2012-10-30 | 10
the output (for sum
as the aggregate) would be:
min | max | sum
-----------+------------+-------
2011-10-31 | 2011-11-02 | 20
2012-09-13 | 2012-09-16 | 30
2012-10-30 | 2012-10-30 | 10
create table t ("date" date, "value" int);
insert into t ("date", "value") values
('2011-10-31', 2),
('2011-11-01', 8),
('2011-11-02', 10),
('2012-09-13', 1),
('2012-09-14', 4),
('2012-09-15', 5),
('2012-09-16', 20),
('2012-10-30', 10);
Simpler and cheaper version:
select min("date"), max("date"), sum(value)
from (
select
"date", value,
"date" - (dense_rank() over(order by "date"))::int g
from t
) s
group by s.g
order by 1
My first try was more complex and expensive:
create temporary sequence s;
select min("date"), max("date"), sum(value)
from (
select
"date", value, d,
case
when lag("date", 1, null) over(order by s.d) is null and "date" is not null
then nextval('s')
when lag("date", 1, null) over(order by s.d) is not null and "date" is not null
then lastval()
else 0
end g
from
t
right join
generate_series(
(select min("date") from t)::date,
(select max("date") from t)::date + 1,
'1 day'
) s(d) on s.d::date = t."date"
) q
where g != 0
group by g
order by 1
;
drop sequence s;
The output:
min | max | sum
------------+------------+-----
2011-10-31 | 2011-11-02 | 20
2012-09-13 | 2012-09-16 | 30
2012-10-30 | 2012-10-30 | 10
(3 rows)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With