【データ分析】知っててよかったSQL3選

夏ももうすぐ終わりです。KIYONOエンジニアです。

今回はデータ分析を行う上で知っていて損がない便利なSQLを紹介します。ちょっと応用的な話で難しいかもしれませんが、これを知っていれば周りのエンジニアより１ランク上の集計ができること間違いなしです。

本記事ではWINDOW関数をメインに取り扱っておりますが、WINDOW関数の基礎についてはこちらの記事（window関数とは？構文、使い方をわかりやすく解説）をご参考ください。

※本記事のSQLはBigQuery上で動かすことを前提として記載しておりますので、他のクエリエンジンを使用されている場合は適宜ドキュメントを参照していただきますようご注意願います。

それでは事例に沿って紹介します。

重複排除

１つ目は重複排除のSQLです。

重複排除と言っても重複のパターンは２つあります。

1. 同一な値を持つレコードが１つのテーブルに２つ存在する状態
2. １つのIDが２つの異なる状態を持って存在する場合

同一な値を持つレコードが１つのテーブルに２つ存在する状態

これは以下のような状態です。

この場合はシンプルに

SELECT DISTINCT 
 ID,
 部署,
 名前
FROM 
 `テーブル`

で良いでしょう。

DISTINCTは対象のテーブルに同一な値を持つレコードがある場合に、１つのみ選択してくれる関数となっております。

１つのIDが２つの異なる状態を持って存在する場合

業務で出てくる重複排除はこのパターンが多いかと思います。

システム的な重複ではなくビシネスロジック的な重複排除ですね。それでは例を見てみましょう。

以下のテーブルは外部システムから顧客マスターのデータを日次で更新分のみ抽出して、データ分析基盤内のテーブルにappendして作成しているものです。データ分析では顧客登録情報の更新状態を後追いする場合もありますので、外部システムのマスターデータをそのままコピーするのではなく、履歴を追える状態で保存することがしばしばあります。

これをデータ分析基盤内で顧客マスターとして使用すると集計値が誤ったものとなったり、ポイントを使った施策を実施した際に誤ったユーザーに対して施策を行なってしまう場合があります。そこでデータ分析基盤側でも最新の顧客一覧を再現する必要があります。

履歴データから最新の状態を取得する場合は ROW_NUMBER()を使い処理を行います。

ROW_NUMBER関数は行番号を付与するWINDOW関数で、今回はユーザーIDごとに更新日降順で番号を付与しました。これにより各ユーザーIDごとに最新の状態を抽出したテーブルが作成できます。

SQL文と出力されるテーブルは以下のようになります。

WITH history AS (
  SELECT
    *,
    ROW_NUMBER()OVER(PARTITION BY ユーザーID ORDER BY 更新日 DESC) AS row_num
  FROM
    `顧客履歴テーブル`
)
SELECT 
  * EXCEPT(row_num) -- 行番号のカラムは不要なので出力に含めない
FROM 
  history
WHERE 
  row_num = 1

マスターの最新状態保持以外にも、売上データの修正などの特定レコードへの更新が後から入る場合に、取引IDに対してデータ取り込み日最新の状態を保持するロジックを作成することで、常に正しいデータを保持し続けることができます。

連続回数の集計

訪問回数、連勝記録などの集計時に連続回数の集計が必要となります。高頻度で依頼される一方で意外と計算が難しく、初めて集計する方はかなり苦戦するかと思います。

今回は例として毎月の訪問回数をユーザーIDごとに集計したテーブルを使い、連続訪問回数を集計してみましょう。

まず、各レコードに

• ユーザーIDごとに年月昇順での行番号　　　　　　　　　　　　 …①
• ユーザーIDごと、当月訪問ありフラグごとの年月昇順での行番号 …②
• ①−②

を作成します。SQL文と作成されるテーブルは次のようになります。

SELECT
  *,
  ROW_NUMBER()OVER(PARTITION BY ユーザーID ORDER BY 年月) AS ①,
  ROW_NUMBER()OVER(PARTITION BY ユーザーID, 当月訪問ありフラグ ORDER BY 年月) AS ②,
  ROW_NUMBER()OVER(PARTITION BY ユーザーID ORDER BY 年月)-ROW_NUMBER()OVER(PARTITION BY ユーザーID, 当月訪問ありフラグ ORDER BY 年月) AS ①-②FROM `テーブル`

ここで注目したいのが①-②で算出したカラムです。

実はこのカラムが連続で訪問した/してないグループを表すIDとなってます。

よって、各ユーザーの連続購買を求めたい場合は以下のようなSQL文を発行します。

-- 上のSQLを転記
WITH base AS (
  SELECT
    *,
    ROW_NUMBER()OVER(PARTITION BY ユーザーID ORDER BY 年月) AS ①,
    ROW_NUMBER()OVER(PARTITION BY ユーザーID, 当月訪問ありフラグ ORDER BY 年月) AS ②,
    ROW_NUMBER()OVER(PARTITION BY ユーザーID ORDER BY 年月)-ROW_NUMBER()OVER(PARTITION BY ユーザーID, 当月訪問ありフラグ ORDER BY 年月) AS ①-②
  FROM `テーブル`
)
SELECT
  ユーザーID,
  ①-②     AS 連続ID,
  当月訪問ありフラグ,
  COUNT(1) AS 連続数,
  MIN(年月) AS 連続開始年月,
  MAX(年月) AS 連続終了年月
FROM 
  base
GROUP BY 
  1, 2, 3

あとはここから最大連続数を持つレコードを抽出したり、連続が終了した年月が直近のものを抽出したりすることができます。

セッションIDを作成

最後はセッションIDを作成する方法を紹介します。Google Analytisを使ってWebアクセス解析を行なっている方には馴染みのあるセッションですが、定義としてはあるイベントとイベントの間隔が特定時間以内であった場合を同一のイベントの集団とし、特定の時間以上の間隔が空いた場合を別のイベントの集団として取り扱うというものです。このイベントの集団のことをセッションと呼びます。

webサイト上での動きとコールセンターへの問い合わせ・アプリ上のコンテンツ閲覧と購買など、プラットフォームを跨いだ一連の動きを把握する際に自前でセッションIDを作成できるとかなり集計が楽になります。

手順としては以下の通りです。

1. 前回イベントからの時間を算出
2. 新規セッションとなるかどうかのフラグを作成
3. フラグの集計
4. セッションIDの生成

今回はテストデータとしてGA4からBigQueryに出力できるログデータよりevent_timestamp, user_pseudo_id, event_nameを使いましょう。セッションは5分以上時間が開いた場合に切り替わるものとします。

１. 前回イベントからの時間を算出

特定条件における１つ前のレコードの値を取得するにはLAG関数を使います。また前処理としてunixtimeをDATETIMEに変換しましょう。

WITH base AS (
  SELECT
    user_pseudo_id,
    CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S',TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS DATETIME) event_at,
    event_name
  FROM
    `GA4ログテーブル`
)
SELECT
  *,
  COALESCE(TIMESTAMP_DIFF(event_at,LAG(event_at)OVER(PARTITION BY user_pseudo_id ORDER BY event_at, event_name), MINUTE),0) AS diff 
FROM 
  base

2. 新規セッションとなるかどうかのフラグを作成

前回イベントからの時間が5分以内であれば新規セッションとする条件で新規セッションとなるかどうかのフラグを付与します。

SELECT
  * except(diff),
  cast((diff>5) as INT64) AS is_new_session
FROM 
 `前のテーブル`

3.フラグの集計

上で作成したフラグはセッションが切り替わるタイミングで1が付与され、それ以外は0となっています。フラグの数値の累積和を求めることで同一セッション内では同じ数値を持つカラムを作成することができます。

SELECT
  * except(is_new_session),
  SUM(is_new_session)OVER(PARTITION BY user_pseudo_id ORDER BY event_at, event_name) AS int_session_id
FROM
 `前のテーブル`

4.セッションIDの生成

最後に数値型のセッションIDを一意な文字列にしましょう。

user_idとint_session_idを”-“で結合しbase64でデコードしIDを生成します。

SELECT
  * except(int_session_id),
  to_base64(MD5(FORMAT('%s-%d', user_pseudo_id, int_session_id))) AS session_id
FROM 
 `前のテーブル`

まとめると以下のようになります。

WITH base AS (
  SELECT
    user_pseudo_id,
    CAST(format_timestamp('%Y-%m-%d %H:%M:%S',timestamp_micros(event_timestamp), 'Asia/Tokyo') as DATETIME) AS event_at,
    event_name
FROM
  `GA4のログデータ`
)
,a as (
  SELECT
    *,
    COALESCE(timestamp_diff(event_at,LAG(event_at)OVER(PARTITION BY user_pseudo_id ORDER BY event_at, event_name), MINUTE),0) AS diff 
  FROM 
    base
)
,b as (
  SELECT
    * except(diff)
    ,cast((diff>5) as INT64) AS is_new_session
  FROM 
    a
)
,c as (
  SELECT
    * except(is_new_session),
    SUM(is_new_session)OVER(PARTITION BY user_pseudo_id ORDER BY event_at, event_name) AS int_session_id
  FROM
    b
)
SELECT
  * except(int_session_id),
  to_base64(MD5(FORMAT('%s-%d', user_pseudo_id, int_session_id))) AS session_id
FROM 
  c

今回は練習としてセッションが切れる時間を5分としましたが、業態やプラットフォームによってセッションの定義を自由に変更できます。

例えば、本記事で２つ目に紹介した連続回数の集計においても今回の集計は利用可能です。セッションの定義を「先月と今月の当月訪問フラグの値が等しい」とすることで連続したグループに対してIDを付与できます（記事を書いているときに気づきました）。

最後に

いかがだったでしょうか。SQLでの集計は覚えてしまえば楽にこなすことができますが初めて遭遇するものは思ったより処理が複雑で時間がかかります。今回共有した内容が皆様のお役に立てればと思います。

KIYONOではGCPのデータ分析基盤に関する資格（Professional Data Engineer）保有者が多数在籍しております。GA4のデータを加工したいけれどBigQueryの触り方に困っている方や自社でデータ基盤構築を検討されている方はぜひ下のフォームよりお問い合わせください。