実務でのテーブル結合時のケア（重複排除など）について

SQL

こんにちは、TVerでデータ分析をしている高橋です。
弊社の分析業務の多くは BigQuery に蓄積されているログを使った分析で、大量のログを扱うため前処理から集計まで全てSQLで行っています。
本記事では、SQLを書く上で特に気を付けているテーブル結合時のケアについて紹介します。

分析業務の一例

「ホーム画面を開いてから10分以内にコンテンツを再生した割合を知りたい」という依頼が来ました1。
この集計は訪問ログと視聴ログを使い、ホーム画面に訪問したログを10分以内に再生した or 再生してないの2種類に分ければできそうです。
ここで、集計に用いるテーブルを簡単に紹介します。

訪問ログ (view_logs)

ホーム、マイページ、番組ページ、エピソードページなどに訪問したタイミングで発報されるログです。
ユーザー毎に時系列順に並べることで、サービス内でのページ遷移が分かります。

-- view_logs サンプルデータ
SELECT TIMESTAMP("2024-03-01 19:30:00") AS viewed_at, "hoge" AS user_id, "/home" AS url, 
UNION ALL SELECT TIMESTAMP("2024-03-01 19:32:00"), "hoge", "/mypage/fav"
UNION ALL SELECT TIMESTAMP("2024-03-01 19:35:00"), "hoge", "/episodes"
UNION ALL SELECT TIMESTAMP("2024-03-01 21:45:00"), "fuga", "/home"
UNION ALL SELECT TIMESTAMP("2024-03-01 22:25:00"), "piyo", "/home"
UNION ALL SELECT TIMESTAMP("2024-03-01 22:30:00"), "hogera", "/home"
UNION ALL SELECT TIMESTAMP("2024-03-01 22:32:00"), "hogera", "/search"
UNION ALL SELECT TIMESTAMP("2024-03-01 22:34:00"), "hogera", "/home"
viewed_at user_id url
2024-03-01 19:30:00 hoge /home
2024-03-01 19:32:00 hoge /mypage/fav
2024-03-01 19:35:00 hoge /episodes
2024-03-01 21:45:00 fuga /home
2024-03-01 22:25:00 piyo /home
2024-03-01 22:30:00 hogera /home
2024-03-01 22:32:00 hogera /search
2024-03-01 22:34:00 hogera /home

視聴ログ (play_logs)

視聴中の行動が記録されているログです。これを見るとシークバー移動のタイミングなどが分かります。
今回は視聴開始した時刻の情報だけ使用します。

-- サンプルデータ
SELECT TIMESTAMP("2024-03-01 19:35:30") AS begin_at, "hoge" AS user_id, "begin" AS action,
UNION ALL SELECT TIMESTAMP("2024-03-01 22:26:00"), "piyo", "begin",
UNION ALL SELECT TIMESTAMP("2024-03-01 22:27:00"), "piyo", "begin",
UNION ALL SELECT TIMESTAMP("2024-03-01 22:35:00"), "hogera", "begin"
begin_at user_id action
2024-03-01 19:35:30 hoge begin
2024-03-01 22:26:00 piyo begin
2024-03-01 22:27:00 piyo begin
2024-03-01 22:35:00 hogera begin

集計用クエリを書いてみる

今回の集計を行うには、どのようなクエリを書けばよいでしょうか？
素朴にやるなら、

  1. 訪問ログからホーム画面（/home）のログを抽出する
  2. 1に視聴ログを LEFT JOIN する

でしょうか。書いてみましょう。

SELECT
    view_logs.*,
    play_logs.begin_at,
    play_logs.begin_at IS NOT NULL AS has_played,
FROM (
    SELECT
        *
    FROM
        view_logs
    WHERE
        url = "/home"
) AS view_logs
LEFT OUTER JOIN
    play_logs
ON
    view_logs.user_id = play_logs.user_id
    -- 訪問後 10 分以内に再生してるか
    AND play_logs.begin_at BETWEEN view_logs.viewed_at AND TIMESTAMP_ADD(view_logs.viewed_at, INTERVAL 10 MINUTE)
ORDER BY
    viewed_at
viewed_at user_id url begin_at has_played
2024-03-01 19:30:00 hoge /home 2024-03-01 19:35:30 true
2024-03-01 21:45:00 fuga /home false
2024-03-01 22:25:00 piyo /home 2024-03-01 22:26:00 true
2024-03-01 22:25:00 piyo /home 2024-03-01 22:27:00 true
2024-03-01 22:30:00 hogera /home 2024-03-01 22:35:00 true
2024-03-01 22:34:00 hogera /home 2024-03-01 22:35:00 true

一見良さそうに見えますが、以下の問題があります。

  • user_id=piyo の 1回の/home訪問に2回の再生が紐づいている（JOIN によってレコードが増えた、いわゆる重複）
    • 分母となるホーム画面の訪問数が増えてしまう
  • user_id=hogera の2回の/home訪問に1回の再生が紐づいている
    • 時系列で考えると再生に直接寄与したのは2回目の/home訪問だと考えられるが、1回目の/home訪問も再生に寄与したと見なされてしまう

このまま集計すると間違った示唆を与えてしまうおそれがあります。
順番に解決していきましょう。

1回の訪問に2回の再生が紐づいているケース

このケースは短時間で複数回再生した場合に発生します（ザッピング的な再生など）。
今回の集計で興味があるのは10分以内の再生有無だけなので、/home訪問後最初の視聴が紐づくログだけ残すようにしましょう。
この処理は以下のようなQUALIFY句によって実現することができます。

QUALIFY
  -- 最初の視聴だけ残す
    ROW_NUMBER() OVER(PARTITION BY user_id, viewed_at ORDER BY begin_at ASC) = 1

ちなみに viewed_at, user_id, url をキーとしてGROUP BYしても同様の処理が可能です。
個人的にはSELECT文を変更する必要がない QUALIFY句を使用することが多いです。

2回の訪問に1回の再生が紐づいているケース

短時間で回遊したのちに再生した場合などでしばしば発生します。
このケースは視聴ログの突合条件に次の/home訪問までに再生しているかという条件を追加することで排除できそうです。
はじめに、view_logsに次の/home訪問した時刻の列を追加します。

SELECT
    *,
    -- 次の /home 訪問時刻
    LEAD(viewed_at, 1) OVER(PARTITION BY user_id ORDER BY viewed_at) AS next_viewed_at,
FROM
    view_logs
WHERE
    url = "/home"

この列を使い、以下のような処理を突合部分に追加します。

-- 次の /home 訪問までに再生してるか (次の /home 訪問がなければ無視)
AND IF(view_logs.next_viewed_at IS NOT NULL, play_logs.begin_at < view_logs.next_viewed_at, TRUE)

改良版クエリ

最終的にこのようになりました。

SELECT
    view_logs.* EXCEPT(next_viewed_at),
    play_logs.begin_at,
    play_logs.begin_at IS NOT NULL AS has_played,
FROM (
    SELECT
        *,
        -- 次の /home 訪問時刻
        LEAD(viewed_at, 1) OVER(PARTITION BY user_id ORDER BY viewed_at) AS next_viewed_at,
    FROM
        view_logs
    WHERE
        url = "/home"
) AS view_logs
LEFT OUTER JOIN
    play_logs
ON
    view_logs.user_id = play_logs.user_id
    -- 訪問後 10 分以内に再生してるか
    AND play_logs.begin_at BETWEEN view_logs.viewed_at AND TIMESTAMP_ADD(view_logs.viewed_at, INTERVAL 10 MINUTE)
    -- 次の /home 訪問までに再生してるか (次の /home 訪問がなければ無視)
    AND IF(view_logs.next_viewed_at IS NOT NULL, play_logs.begin_at < view_logs.next_viewed_at, TRUE)
-- 最初の視聴だけ残す
QUALIFY
    ROW_NUMBER() OVER(PARTITION BY user_id, viewed_at ORDER BY begin_at ASC) = 1
ORDER BY
    viewed_at
viewed_at user_id url begin_at has_played
2024-03-01 19:30:00 hoge /home 2024-03-01 19:35:30 true
2024-03-01 21:45:00 fuga /home false
2024-03-01 22:25:00 piyo /home 2024-03-01 22:26:00 true
2024-03-01 22:30:00 hogera /home false
2024-03-01 22:34:00 hogera /home 2024-03-01 22:35:00 true

viewed_at, begin_at どちらも一意化することができました。
このCTE を GROUP BYすることで目的の集計をすることができます。

最後に

テーブル結合時のケアについて2つの事例を紹介しましたが、これらの事象の発生を集計値だけ見て気付くことは非常に難しいです。
そのためレビューの際にはクエリのロジックを確認することは勿論のこと、中間テーブルの出力を確認したり個票チェックをしたりすることで集計ロジックの確からしさを検証しています。
同時に「レビューしやすいクエリ」を書くために、ロジックを考えたり社内ルール整備をしたりなどを日々行っています。この取り組みは分析チームが一丸となり、相当な力を入れて取り組んでいます2。

採用のこと

TVerでは一緒に分析業務をしてくれる方を募集しています。
カジュアル面談も受け付けていますので、「こういう取り組みのことをもっと知りたい」「普段どんな分析してるか知りたい」と思った方は以下よりお気軽にご連絡ください。お待ちしております！

herp.careers

1: 一例なのでかなり大味な依頼になっており、この集計結果を受けて具体的なアクションを行うことが難しいと予想されます。実際にこのような依頼が来た場合は、ホーム画面から再生までのユーザーの振る舞いや遷移についての仮説出しをしてからクエリを作成することが望ましいです。

2: テックブログにておいおい紹介する予定です。