SnowflakeのCONDITIONAL_TRUE_EVENT 関数でユーザーの利用ログをセッション化する方法

ホンディー | ライフイズテック

はじめに

こんにちは、ライフイズテックサービス開発部データ基盤グループのホンディーです。転職してきて4ヶ月ほどが経ち、導入しているSnowflakeの扱いにもだいぶ慣れてきました。より効果的に活用していくために、時々Snowflakeのドキュメントを眺めているのですが、その中で他のDWHでは見かけない CONDITIONAL_TRUE_EVENT という関数を見つけました。

ドキュメントの説明の最初の方に、以下の記述がある通り、この関数を活用すると一連のログデータを手軽にセッションに分けることができます。

この関数の用途の1つは、ウィンドウパーティションをセッション化することです。例えば、クリックストリームデータでは、最後のイベントがしきい値よりも前であったかどうかを確認することにより、ユーザーが新しいセッションを開始したかどうかを判断するために関数を使用できます。

https://docs.snowflake.com/ja/sql-reference/functions/conditional_true_event

ユーザーの行動ログを分析するに当たって、セッション化は頻繁に行う操作にも関わらず手順が多くバグも発生しやすいのでこれは期待が持てますね。

そもそもセッション化とは

はじめに、ログのセッション化とは何かについて軽く説明します。

ログには様々な種類のものがありますが、基本的にはいつ、誰が、何を行ったかが、1回の行動(ログイン画面へのアクセス、ログイン、学習メニューの閲覧、学習の開始と終了、練習問題の回答など)につき1行のデータとして記録されています。
この単発の一つ一つのデータを、ユーザーの一連の利用単位でまとめて扱うことがセッション化です。基本的には、一定の時間(30分など)で閾値を決め、その閾値以内の間隔で次のログが発生したらそれは同じセッション内の行動とみなし、逆に閾値以上に時間が離れていたらユーザーは一度利用を中断して、また改めて利用を開始した(新しいセッションが始まった)と判断します。

このセッション化を行うことによって、ユーザーが一度にどのくらいの時間サービスを使ってくれているのかといった集計を正確に行うことができます。

逆に、セッション化せずに例えば1日の最初のログと最後のログの間隔を利用時間とするといった簡易的な基準で集計してしまうと、午前中と夕方に分けて利用ログがあった場合などに利用時間を過剰に集計してしまいます。

CONDITIONAL_TRUE_EVENT の説明

セッション化の説明が終わったところで、そのセッション化に使えるというCONDITIONAL_TRUE_EVENT関数の仕様を見ていきましょう。

ドキュメントから引用すると、次の構文で利用します。

CONDITIONAL_TRUE_EVENT( <expr1> ) OVER ( [ PARTITION BY <expr2> ] ORDER BY <expr3> )

https://docs.snowflake.com/ja/sql-reference/functions/conditional_true_event

引数は次の3個です。

  • <expr1>:  trueと評価されたときにウィンドウイベント番号の値を変更するブール式

  • <expr2> : パーティションを分割するオプションの式

  • <expr3> : 各パーティション内で並び替える式

そして、この次が重要なポイントですが、条件式<expr1>には、OVER句が等しいLAGとLEAD 関数を含めることができると記載されています。

通常、SQLのWindow関数は他のWindow関数を含めることができないので、これは非常に珍しい仕様です。そしてこれによって、CONDITIONAL_TRUE_EVENT は非常に便利な関数になっています。

<expr1>の式に前のレコードを参照するLAGを含めることができるため、前のログの時刻とそのログの時刻を比較して同じセッションとするか別セッションとするかの判定ができるわけです。

セッション化を行うSQLの例

関数の仕様面の説明は以上になるので、具体的にこれを使ったセッション化のSQLを書いていきます。
テーブル名と列名は架空のものですが、テーブル名は log_table として列名は次の3列とします。

  • user_id : ユーザのid

  • log_time : ログの発生時刻(=ユーザーがその行動を行った時刻)

  • action : 行動内容

このデータに対してセッションごとに番号、session_numberを振ることにしましょう。また、セッションを分ける時間の閾値は30分とします。

select
    user_id,
    action,
    log_time,
    conditional_true_event(
        datediff(
            second,
            lag(log_time) over (partition by user_id order by log_time),
            log_time
        ) >= 1800
    ) over (partition by user_id order by log_time) as session_number
from
    log_table

たったこれだけでOKです。LAG関数で1つ前のログの時刻を取ってきて、DATEDIFF で時刻の差分が何秒なのかを計算し、それが1800秒(=30分)以上であったらconditional_true_event が1つ大きい値を振ってくれます。

この番号はuser_idごとに0番から採番される点には注意してください。そのため、session_idではなくsession_numberとしています。

参考: CONDITIONAL_TRUE_EVENT を使わない方法

「たったこれだけ」といってもどれだけ楽になったのかわかりにくいと思うので、CONDITIONAL_TRUE_EVENTを使わないやり方も紹介します。
LAG関数で1つ前のログの時刻を取り、差分を見てセッションが変わっているかどうかの判定を行うところまでは同じです。CONDITIONAL_TRUE_EVENTを使わない場合は、ここで、セッションが変わったら1、変わってなければ0というフラグを立てて、SUM関数でそのフラグの累積和を計算することでセッション番号を振ります。

これだけ書くと、ほとんど手間が変わらないのですが、CONDITIONAL_TRUE_EVENT 以外のWindow関数は他のWindow関数を含めることができない、という制約がここで影響してきます。LAG関数を使って算出されているセッションの変更フラグをそのままSUM関数に渡せないため、WITH句を使うなどして別のSELECT句に切り出す必要があるのです。
結果的に、クエリは次のようになります。

with
add_new_session_flg as (
    select
        user_id,
        action,
        log_time,
        lag(log_time) over (
            partition by user_id
            order by log_time
        ) as pre_log_time,
        case
            when datediff(second, pre_log_time, log_time) >= 1800 then 1
            else 0
        end as new_session_flg
    from
        log_table
)

select
    user_id,
    action,
    log_time,
    pre_log_time,
    sum(new_session_flg) over (
        partition by user_id
        order by log_time, pre_log_time
    ) as session_number
from
    add_new_session_flg

WITH句を分けることが必須になったのでSQLのボリュームが大きくなりましたね。(SnowflakeはSELECT句内で算出した値を同一のSELECT句の以降の列で参照できる仕様があるため、これでも他のDBに比べるとずっとシンプルな記述で済んでいます。)
この記事では最低限のサンプルとして紹介しているため参照している列が少なくこの程度の増加で済んでいますが、実際の分析ではもっと多くの列を参照することになります。それらの全ての列を2回ずつ書かないといけなくなるためCONDITIONAL_TRUE_EVENTを使うかどうかで一層差が大きくなります。

また、注意深い方は、最初のWITH句のLAG関数に付随しているOVER句では、order by log_time となっているのが、最後のSELECT句のSUM関数に付随しているOVER句では、order by log_time, pre_log_time となっていることに気づかれるでしょう。
これは、同一時刻のログが複数行出ていた場合、WITH句内のSELECTと最後のSELECTでソート順が変わらないようにするために記述を変えています。こうしないとセッション化の誤りを引き起こす可能性があります。このように、単に記述が多くて面倒なだけでなく、ちょっとした不注意でバグを起こすリスクも大きく上がってしまいます。

まとめ： そもそも重要なのはセッション化した後に何を分析するか

ここまで、ログのセッション化について書いてきましたが、そもそもの話としてログをセッション化してそれで分析完了、となることはありません。

ライフイズテックのサービスを使って、1回の学習でどのくらい勉強時間を確保していただけているのか、その分布はどうなっているのか、その1回の学習(1セッション)の間に、教材をどのくらいの量進められているのか、1つの章の学習を終えるのに延べ何時間くらいかかっているのか、時間をかけてじっくり1周取り組んだ生徒と短時間で繰り返した生徒のどちらが知識の定着が良いのか、といったことを分析するためにログのセッション化を行います。

そのため、その分析の前準備であるセッション化にあまり時間と手間をかけたくないというのが本音のところで、これはさっとやって本当に分析したい内容に意識を集中したいのが常です。

そのため、このセッション化を簡略化してくれるCONDITIONAL_TRUE_EVENTは大変便利な関数だと思いました。

おしらせ

ライフイズテック サービス開発部では、気軽にご参加いただけるカジュアルなイベントを実施しています。開催予定のイベントは、 connpass のグループからご確認ください。興味のあるイベントがあったらぜひ参加登録をお願いいたします。皆さんのご参加をお待ちしています！

この記事が気に入ったらサポートをしてみませんか？