歯抜けになっているデータを埋めるのに便利な関数LAST_VALUE

これを

こうするやり方の紹介です。

 

アクセスログで会員IDを取ってる時に、以下の状況によく遭います。

そういう時にLAST_VALUEを使ってNULLをうまく埋めてやる例の紹介です。

BigQueryを使います。

埋める前に確認

NULLを埋める前に、どうやって会員IDを見るか決めておきましょう。

アクセスログにはセッションIDがあるので、基本的にはセッションID単位で会員ID列のNULLを埋めてやればいいです。

一番簡単なのはMAXとかを取る。

しかし、１つのセッションの中に存在する会員IDが1つじゃないときがあります。

こういう時にMAXで埋めてしまうと、実際のログイン状況と乖離してしまうことがあるので良くない。

赤線を引いた会員ID=vt333はログ上に存在するのに常に無視されてしまう。

会員IDの文字の並びによっては記録されやすいID、されにくいIDが生まれることになります。かわいそうじゃない？

今回は同一セッション中に2つ以上会員IDが記録されていた時は、「ユーザーが会員IDを切り替えている」と定義し、2つ目のID以前と以降でNULLを埋めるIDを変える判断をしました。

 

※状況によって適切な埋め方は違います。

ID2つ以上あってもMAXorMINでいい／最後に出現したIDで埋めたい／2IDとも考慮したいということもあるので、NULLの埋め方はあらかじめクリアにしておきましょう。

 

歯抜けのデータを埋める

1.LAST_VALUEを使う

LAST_VALUEを使って埋めていきましょう。

LAST_VALUEは表計算の関数です。指定した列の最後の行の値を取ってきます。

LAST_VALUE(`user_id` ignore nulls) OVER(partition by `session_id` order by `time`)

とすると、セッションの中で最後に入っていた会員IDの値を取ってくることができます。

order byを指定して時間順に並べているので、会員IDが最後に記録された時の値を取ってきています。

会員IDが途中で変わるとそれが最後の値になるので、以降の行のIDが新しいIDに変わる。

これで会員IDが2つ以上あってもいい感じに切り替わったタイミングで埋めることができました。

 

2.会員IDが記録される前のNULLを埋める

LAST_VALUEを使うと会員IDが入ってきて以降の行を埋めることができますが、会員IDが入る前のNULLを埋めることができません。

MAXなどを使って埋めましょう。

こんな感じで中間列を作り、lastvalueの列と会員ID入る前を埋める用の列を統合して作ります。

SQLはこんな感じでどうでしょうか……。

SELECT 
  `session_id`,`time`,`domain`,`page_title`,`user_id`,COALESCE(`lastvalue`,tmp2) as processed_user_id
FROM(
  SELECT 
    *,MAX(`tmp1`) OVER(partition by `session_id`) as tmp2
  FROM(
    SELECT 
      `session_id`,`time`,`domain`,`page_title`,`user_id`,
      LAST_VALUE(`user_id` ignore nulls) OVER(partition by `session_id` order by `time`) as lastvalue,
      IF(MIN(IF(`user_id` is not NULL,`time`,NULL))OVER(partition by`session_id`)=`time`,`user_id`,NULL) as tmp1
    FROM dataset_name.table_name
  )query01
)query02

 

終わり

参考↓

 

LAST_VALUEはデフォルトでは最初の行から現在の行までを範囲とするよ。という注意を見て思いついた方法でした。

LAG関数でも同じようなことができます。どっちが一般的なんでしょうか。

 

GRIはデータ利活用のプロフェッショナル集団です。

分析の受注やデータ分析基盤の作成など、お客様の課題に合わせたデータ活用のサポートを承っております。

 

お問い合わせはこちらから。

 

• データサイエンス
• Tips
• 技術情報