トランザクションデータのまとまりにIDを割り当てる

概要

SQLでトランザクションデータを扱うとき、特定のルールに沿ったまとまりごとに処理を行いたい時がある。
自分で１から加工するのが結構大変だったので、備忘のために記事として残す。

やり方

1.扱いたいデータのまとまりの切り替わりのタイミングを定義し、UUIDを生成する
2.生成したUUIDをまとまりの中で割り当てる

例として、PVのトランザクションデータの中で、あるユーザが連続して同じ階層(page_category)をPVしたまとまりを取得したいときの例を以下に示す。
※BigQueryでの例
UUIDの生成やIDの振り分けは関数を用いており標準機能の範疇でないので、DBによって変える必要がある

連続するページカテゴリの回遊のまとまりを分類する

select
    *,
    case
        when unique_id is null then last_value(unique_id ignore nulls) over(
            partition by user_id
            order by pv_time rows unbounded preceding
        )
    end pv_chunk_id,
from (
    select
        *,
        case
            when page_category != lag(page_category, 1) over (
                partition by user_id
                order by pv_time
            ) then null
            else generate_uuid()
        end unique_id,
    from
        sample_pv_transaction
)

ここでは、ページ階層(page_category)が切り替わったタイミングでUUIDを生成しているが、この定義を変えることで色々なトランザクションのまとまりへの活用が効く。