PostgreSQL group by with interval

Question

嗯，我有一组看似简单的数据，但它给我带来了很多麻烦。

这是我的数据的一个示例：

quantity  price1  price2 date
100       1       0      2018-01-01 10:00:00
200       1       0      2018-01-02 10:00:00
50        5       0      2018-01-02 11:00:00
100       1       1      2018-01-03 10:00:00
100       1       1      2018-01-03 11:00:00
300       1       0      2018-01-03 12:00:00

我需要总结"price1“和"price2”分组的"quantity“列，这将非常容易，但我需要考虑"price1”和"price2“的时间变化。数据按“日期”排序。

我需要的是最后一行不与前两行分组，尽管它具有相同的"price1“和"price2”值。另外，我需要得到每个间隔的最小和最大日期。

最终结果应该如下所示：

quantity price1 price2 dateStart            dateEnd
300      1      0      2018-01-01 10:00:00  2018-01-02 10:00:00 
50       5      0      2018-01-02 11:00:00  2018-01-02 11:00:00
200      1      1      2018-01-03 10:00:00  2018-01-03 11:00:00
300      1      0      2018-01-03 12:00:00  2018-01-03 12:00:00

对SQL查询有什么建议吗？

Answer 1

这是一个鸿沟和岛屿问题。使用以下代码：

select sum(quantity), price1, price2, min(date) dateStart, max(date) dateend 
from
(
    select *,
           row_number() over (order by date) -
           row_number() over (partition by price1, price2 order by date) grp
    from data
) t
group by price1, price2, grp
order by dateStart

dbfiddle demo

该解决方案基于识别price1和price2的连续序列，这是通过创建grp列来完成的。一旦分离出连续的序列，就可以使用grp进行简单的分组。

Answer 2

我稍微更改了一下公认的答案，以捕捉相邻两行的"date“列完全相同的情况。我添加了第二个参数，这样它们就可以按正确的顺序排序(我的表有"oid“列)

select sum(quantity), price1, price2, min(date) dateStart, max(date) dateend 
from
(
    select *,
           row_number() over (order by date, oid) -
           row_number() over (partition by price1, price2 order by date, oid) grp
    from data
) t
group by price1, price2, grp
order by dateStart