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);更简单，更便宜的版本：
select min("date"), max("date"), sum(value)from ( select "date", value, "date" - (dense_rank() over(order by "date"))::int g from t) sgroup by s.gorder by 1我的第一次尝试更加复杂和昂贵：
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") qwhere g != 0group by gorder by 1;drop sequence s;输出：
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)
