GROUP BY连续日期，以空格分隔

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)