sql - Postgres：每个事件的会话持续时间（行）

Question

我正在尝试编写一个查询，为每个事件建立一个会话持续时间。该数据库包含来自 webapp 的事件，每个事件都有一个 session-id 和一个时间戳。每行代表一个事件。我以为我可以通过递归查询来解决这个问题，但是每次尝试都会运行几分钟而没有返回。这让我疯狂。这是我到目前为止所拥有的。

with recursive session_time as (
    select
        f.data->'sessionId' as session_id,
        f.ts,
        null::timestamp with time zone as prev_timestamp,
        0 as session_duration
    from arbiter_events as f
    union
    select
        n.data->'sessionId' as session_id,
        n.ts,
        st.ts as prev_timestamp,
        (EXTRACT(epoch from (n.ts - (
            select
                st.ts
            from arbiter_events p
            where p.ts < n.ts
            order by p.ts desc
            limit 1
        ))) + st.session_duration)::integer as session_duration
    from arbiter_events as n
    inner join session_time st on st.session_id = n.data->'sessionId'
)
SELECT
    ae.customer,
    ae.username,
    ae.data->'category' as category,
    ae.data->'subCategory' as subcategory,
    st.session_id,
    st.session_duration
from arbiter_events ae
left join session_time st on ae.data->'sessionId' = st.session_id;

标签： sqlpostgresqlrecursive-query