PostgreSQL按间隔分组 [英] PostgreSQL group by with interval
问题描述
嗯,我有一组看似简单的数据,但这给我带来了很多麻烦。
Well, I have a seemingly simple set of data but it gives me a lot of trouble.
这是我的数据看起来像的一个例子:
This is an example of what my data look like:
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分组的数量列进行汇总,这很容易,但是我需要考虑 price1和 price2的时间变化。数据按日期排序。
I need to sum up "quantity" column grouped by "price1" and "price2" and it would be very easy but I need to take into account time changes of "price1" and "price2". Data is sorted by "date".
我需要的是最后一行不与前两个分组,尽管它的 price1和 price2具有相同的值。我还需要获取每个间隔的最小和最大日期。
What I need is the last row to be not grouped with the first two although it has the same values for "price1" and "price2". Also I need to get minimal and maximal date of each interval.
最终结果应如下所示:
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查询有任何建议吗?
Any suggestions for a SQL query?
推荐答案
这是一个空白和孤岛的问题。使用以下代码:
It is a gap and island problem. Use the following code:
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
该解决方案基于对 price1
和 price2
的连续序列的识别,这是通过创建 grp
列来完成的。一旦分离出连续的序列,就可以使用 grp
进行简单分组。
The solution is based on an identification of consecutive sequences of price1
and price2
, which is done by a creation of the grp
column. Once you isolate the consecutive sequences then you do a simple group by using grp
as well.
这篇关于PostgreSQL按间隔分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!