1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

[SQL] Postgres: Session duration per event (row)

Discussão em 'Outras Linguagens' iniciado por Stack, Julho 21, 2021.

  1. Stack

    Stack Membro Participativo

    I'm trying to write a query that builds a session duration per each event. The database houses events from a webapp, each with a session-id and a timestamp. Each row represents one event. I thought I could solve this with a recursive query, but every attempt runs for minutes with no return. It's driving me crazy. This is what I have so far.

    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;

    Continue reading...

Compartilhe esta Página