BigQuery: 操作履歴テーブルからいい感じにデータを抽出する

やりたいこと

操作履歴を保存しているテーブルから特定のケースを抽出する際に、悩んだケースに当たったので記事にして供養します。

状況

このような何かしらの操作履歴を保存しているテーブル

テーブル名: hoge.fuga.history

カラム名	型	説明
id	int	主キー
status	int	操作成功:1, 失敗: 2
before_value	int	変更前の値
after_value	int	変更後の値
reflection_type	int	自動操作:1, 手動操作:2
created_at	datetime	作成日時
completed_at	datetime	完了日時

があるとします。このテーブルには、操作の成否、変更前後の値、操作の種類（自動または手動）、作成日時、完了日時などが記録されています。

同一IDで同一日に、「最初に自動操作が成功したが、後にそれが間違いな事を検知し、手動でその操作を戻そうとしたけどなぜか失敗した...」というケースを抽出したいと思います。

lagを使ったクエリ

• id, date(completed_at)毎にpartitionを切る
• partition毎に、必要な要素のlagをとる
• 手動操作で失敗した行について、lagが自動操作で成功でかつbefore_value, after_valueの値が逆になっているケースを抽出

with raw_history as (
    select
        *,
        row_number() over (
            partition by id, date(completed_at, 'Asia/Tokyo')
            order by completed_at asc
        ) as row_number
    from `hoge.fuga.history`
),

history as (
    select
        *,
        lag(status, 1) over (
            partition by id, date(completed_at, 'Asia/Tokyo')
            order by row_number asc
        ) as prev_status,
        lag(before_value, 1) over (
            partition by id, date(completed_at, 'Asia/Tokyo')
            order by row_number asc
        ) as prev_before_value,
        lag(after_value, 1) over (
            partition by id, date(completed_at, 'Asia/Tokyo')
            order by row_number asc
        ) as prev_after_value,
        lag(reflection_type, 1) over (
            partition by id, date(completed_at, 'Asia/Tokyo')
            order by row_number asc
        ) as prev_reflection_type
    from raw_history
)

select *
from history
where
    status = 2 /* 失敗 */
    and reflection_type = 2 /* 手動 */
    and prev_status = 1 /* 成功 */
    and prev_reflection_type = 1 /* 自動 */
    and before_value = prev_after_value
    and after_value = prev_before_value

これで目的のケースを抽出できました。
またlagを使わないやり方でも書けます。

操作のペアを作って絞り込むクエリ

with auto_success as (
    select
        *,
        date(completed_at, 'Asia/Tokyo') as completed_date
    from `hoge.fuga.history`
    where
        status = 1 /* 成功 */
        and reflection_type = 1 /* 自動 */
),

manual_failure as (
    select
        *,
        date(completed_at, 'Asia/Tokyo') as completed_date
    from `hoge.fuga.history`
    where
        status = 2 /* 失敗 */
        and reflection_type = 2 /* 自動 */
),

paired_operations as (
    select
        auto_success.id as auto_success_id,
        auto_success.completed_date as auto_success_completed_date,
        auto_success.completed_at as auto_success_completed_at,
        auto_success.before_value as auto_success_before_value,
        auto_success.after_value as auto_success_after_value,
        auto_success.reflection_type as auto_success_reflection_type,
        manual_failure.id as manual_failure_id
    from auto_success
    inner join manual_failure
        on
            auto_success.id = manual_failure.id
            and auto_success.completed_date = manual_failure.completed_date
)

select * from paired_operations
where
    auto_success_before_value = manual_failure_after_value
    and auto_success_after_value = manual_failure_before_value
    and auto_success_completed_at < manual_failure_completed_at

timestamp -> dateにして、dateをキーにした操作のペアを作ることでも目的のケースを抽出できました。

抜け落ちていたケース

どちらのクエリでも(自動,成功)->(手動、失敗)の抽出はできますが、このままでは、
(自動,成功)-> (自動,失敗) -> (手動、失敗)
のような間に操作が挟まっているケースの抽出ができません。
今回のケースではこれも抽出する必要があったので、以下のように操作のペアを作って絞り込むクエリをベースに手動成功のレコードを作ってから絞り込むことで対応しました。

対応したクエリ

with manual_success as (
    select
        *,
        date(completed_at, 'Asia/Tokyo') as completed_date
    from `hoge.fuga.history`
    where
        status = 1 /* 成功 */
        and reflection_type = 2 /* 手動 */
),

auto_success as (
    select
        *,
        date(completed_at, 'Asia/Tokyo') as completed_date
    from `hoge.fuga.history`
    where
        status = 1 /* 成功 */
        and reflection_type = 1 /* 自動 */
),

manual_failure as (
    select
        *,
        date(completed_at, 'Asia/Tokyo') as completed_date
    from `hoge.fuga.history`
    where
        status = 2 /* 失敗 */
        and reflection_type = 2 /* 自動 */
),

paired_operations as (
    select
        manual_success.id as manual_success_id,
        manual_success.completed_date as manual_success_completed_date,
        manual_success.completed_at as manual_success_completed_at,
        manual_success.before_value as manual_success_before_value,
        manual_success.after_value as manual_success_after_value,
        manual_success.reflection_type as manual_success_reflection_type,
        auto_success.id as auto_success_id,
        auto_success.completed_date as auto_success_completed_date,
        auto_success.completed_at as auto_success_completed_at,
        auto_success.before_value as auto_success_before_value,
        auto_success.after_value as auto_success_after_value,
        auto_success.reflection_type as auto_success_reflection_type,
        manual_failure.id as manual_failure_id
    from auto_success
    inner join manual_failure
        on
            auto_success.id = manual_failure.id
            and auto_success.completed_date = manual_failure.completed_date
    left join manual_success
        on
            auto_success.id = manual_success.id
            and auto_success.completed_date = manual_success.completed_date
)

select * from paired_operations
where
    auto_success_before_value = manual_failure_after_value
    and auto_success_after_value = manual_failure_before_value
    and auto_success_completed_at < manual_failure_completed_at
    /* ここで自動成功->手動成功->手動失敗の組み合わせを除外 */
    and manual_success_id is null

結論

• 今回のケースでは、完了日の粒度で操作のペアを作って条件で絞るのが拡張性が高い形で書きやすかったです
• prev_before_valueの命名はどうなんだろうと思いながら執筆しました