postgresql LAG 函数和 GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17301816/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LAG function and GROUP BY
提问by jjames
I have a table like this,
我有一张这样的桌子
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
I'd like to apply the lag function but also a group by so I can find the time intervals between any particular event_id.
我想应用滞后函数，但也想应用一组，这样我就可以找到任何特定 event_id 之间的时间间隔。
I'd like something like this:
我想要这样的东西：
SELECT event_id, difference
FROM (
SELECT event_id, date - lag(date) over (order by date) as
difference FROM table GROUP BY event_id
) t;
I cannot however use GROUP BY with the LAG function. I'd like a result similar to the following:
但是，我不能将 GROUP BY 与 LAG 功能一起使用。我想要类似于以下的结果：
63116743, {120, 60}
60894420, {60}
...
...
So there was a 120s and 60s window between the events for the first id, and a 60s window for the second id.
因此，第一个 id 的事件之间存在 120 秒和 60 秒的窗口，第二个 id 的事件之间存在 60 秒的窗口。
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
有没有办法做到这一点？输出格式不是太重要，只要我最后能把它放到一个数组中即可。我正在使用 Postgres 9.1
回答by
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;
Should work.
应该管用。
