LAG函数和GROUP BY [英] LAG function and GROUP BY
本文介绍了LAG函数和GROUP BY的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个这样的表
event_id | date
----------+------------------------
1703702 | 2013-06-25 07:50:57-04
3197588 | 2013-06-25 07:51:57-04
60894420 | 2013-06-25 07:52:57-04
60894420 | 2013-06-25 07:53:57-04
183503 | 2013-06-25 07:54:57-04
63116743 | 2013-06-25 07:55:57-04
63110451 | 2013-06-25 07:56:57-04
63116743 | 2013-06-25 07:57:57-04
63116743 | 2013-06-25 07:58:57-04
我想应用滞后功能
我想要这样的东西:
SELECT event_id, difference
FROM (
SELECT event_id, date - lag(date) over (order by date) as
difference FROM table GROUP BY event_id
) t;
但是我不能将GROUP BY与LAG功能一起使用。我想要类似以下结果:
I cannot however use GROUP BY with the LAG function. I'd like a result similar to the following:
63116743, {120, 60}
60894420, {60}
...
...
所以有120第一个ID的事件之间有60s的窗口,第二个id的事件之间有60s的窗口。
So there was a 120s and 60s window between the events for the first id, and a 60s window for the second id.
有没有办法做到这一点?只要我可以将其最终放入数组,输出格式就不太重要。我正在使用Postgres 9.1
Is there a way to do this? The output format is not too important as long as I can get it into an array in the end. I'm using Postgres 9.1
推荐答案
WITH diffs as (
SELECT
event_id,
date - lag(date) over (partition BY event_id ORDER BY date) as difference
FROM
TABLE
)
SELECT
event_id,
array_agg( difference ) as all_diffs
FROM
diffs
GROUP BY event_id;
应该工作。
这篇关于LAG函数和GROUP BY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文