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

小编典典

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

sql

假设（在Postgres 9.1中）有一个像这样的表：

date | value

其中有一些差距（我的意思是：并非min（date）和max（date）之间的每个可能的日期都有它的行）。

我的问题是如何汇总此数据，以便每个一致的组（不存在任何差距）被分别对待，如下所示：

min_date | max_date | [some aggregate of "value" column]

有什么想法怎么做？我相信使用窗口功能是可能的，但是尝试了一段时间后lag()，lead()我有点卡住了。

例如，如果数据是这样的：

 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

输出（sum作为汇总）将是：

   min     |    max     |  sum  
-----------+------------+-------  
2011-10-31 | 2011-11-02 |  20  
2012-09-13 | 2012-09-16 |  30  
2012-10-30 | 2012-10-30 |  10

阅读 312

2021-03-23

共1个答案

小编典典

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
) s
group by s.g
order 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"
) q
where g != 0
group by g
order 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)

2021-03-23