セッショナイズされたアクセスログから部分パスの出現回数を集計する

collect_partial_path.sql

create table sessionized_access_log
( session_id int
, session_seq int
, log_time timestamp
, pv_signature text   -- 1アクセスを表現する値。pathとか
);

-- 長さ3の部分パスの出現回数を集計（ウィンドウ関数使わない編）
-- partitionから任意長のウィンドウフレームを自力で切り出すためにも使えるぞ！
select
    partial_path
    , count(*)
from (
    select
        array_agg(a.pv_signature order by session_seq) as partial_path
    from
        sessionized_access_log as a
        cross join generate_series(1,3) as s (seq)
    group by
        a.session_id
        , a.session_seq + s.seq
    ) tmp
group by
    partial_path
;

-- 長さ3の部分パスの出現回数を集計（PostgreSQLのウィンドウ関数使う）
-- session_seqいらなかった。
select
    partial_path
    , count(*)
from (
    select
        array_agg(pv_signature) over (
            partition by session_id
            order by log_time
            rows between 2 preceding and current row
        ) as partial_path
    from
        sessionized_access_log 
    ) t
group by
    partial_path
;