UNNESTを利用してパターン毎の処理を短く記述する

15062 ワード
SQL BigQuery SQL テキストリンク

これは何

UNNESTの複数の使い方を組み合わせることで、SQLを大幅に短く書く方法を紹介します。

課題設定

ユーザーu毎に2列のパターンt1,t2と値vを持つログがあるとします。

ユーザー×パターン毎に、そのパターンのログ数がユーザーのログ数全体に占める割合を計算したいです。
例えば、
- u=1, t1=a のログがu=1のログ全体に占める割合は1/2
- u=1, t1=a, t2=pのログがu=1のログ全体に占める割合は1/2
- u=1, t1=a, t2=qのログがu=1のログ全体に占める割合は0
- u=3, t2=qのログがu=3のログ全体に占める割合は1

といった具合です。

パターンの組み合わせは
{a/b/both} × {p/q/both} - 1 = 3×3-1 = 8
となります。

解決策1: 横持ちで集計してから縦持ちに持ち替える

愚直に8パターンを書き出します。
横持ちで集計を行ったあと、UNNESTによるクロス積を利用して縦持ちに持ち替えます。

WITH df AS (
SELECT 1 AS u, "a" AS t1, "p" AS t2, "x" AS v
UNION ALL SELECT 1 AS u, "b" AS t1, "p" AS t2, "y" AS v
UNION ALL SELECT 2 AS u, "a" AS t1, "p" AS t2, "y" AS v
UNION ALL SELECT 2 AS u, "a" AS t1, "p" AS t2, "z" AS v
UNION ALL SELECT 3 AS u, "b" AS t1, "q" AS t2, "z" AS v
)
,
df_extend AS (
  SELECT
    u
    , COUNTIF(t1="a") / COUNT(*) AS t1a
    , COUNTIF(t1="b") / COUNT(*) AS t1b
    , COUNTIF(t2="p") / COUNT(*) AS t2p
    , COUNTIF(t2="q") / COUNT(*) AS t2q
    , COUNTIF(t1="a" AND t2="p") / COUNT(*) AS t1at2p
    , COUNTIF(t1="a" AND t2="q") / COUNT(*) AS t1at2q
    , COUNTIF(t1="b" AND t2="p") / COUNT(*) AS t1bt2p
    , COUNTIF(t1="b" AND t2="q") / COUNT(*) AS t1bt2q
  FROM
    df
  GROUP BY
    u
)
SELECT
  u
  , type
  , CASE type
    WHEN "t1a" THEN t1a
    WHEN "t1b" THEN t1b
    WHEN "t2p" THEN t2p
    WHEN "t2q" THEN t2q
    WHEN "t1at2p" THEN t1at2p
    WHEN "t1at2q" THEN t1at2q
    WHEN "t1bt2p" THEN t1bt2p
    WHEN "t1bt2q" THEN t1bt2q
  END AS value
FROM
  df_extend, UNNEST(["t1at2p", "t1at2q", "t1a", "t1bt2p", "t1bt2q", "t1b", "t2p", "t2q"]) AS type

出力結果(抜粋)

ユーザー×パターン毎に、そのパターンのログがユーザー全体のログに占める割合が計算されました。

問題

今回のケースはパターン数が8パターンなので個々のパターンをSQLクエリの中に全て書き出しても許容出来ます。
しかし、例えばt1が9種類、t2が49種類あった場合、組み合わせは(9+1)*(49+1)-1=499種類となり、個々のパターンをSQLクエリの中に書き出すのは現実的ではありません。
苦行ですし、ミスを生みやすく、改修もし辛いです。

解決策2: UNNESTをフル活用して最初から縦持ちで集計する

UNNESTを利用します。

WITH df AS (
SELECT 1 AS u, "a" AS t1, "p" AS t2, "x" AS v
UNION ALL SELECT 1 AS u, "b" AS t1, "p" AS t2, "y" AS v
UNION ALL SELECT 2 AS u, "a" AS t1, "p" AS t2, "y" AS v
UNION ALL SELECT 2 AS u, "a" AS t1, "p" AS t2, "z" AS v
UNION ALL SELECT 3 AS u, "b" AS t1, "q" AS t2, "z" AS v
)
SELECT
  u
  , v1
  , v2
  , COUNTIF(t1=v1 AND t2=v2) / (COUNTIF(t1="all" AND t2="all")) AS rate
FROM
  df
  , UNNEST([t1, "all"]) AS t1
  , UNNEST([t2, "all"]) AS t2
  , UNNEST(["a", "b", "all"]) AS v1
  , UNNEST(["p", "q", "all"]) AS v2
GROUP BY
  u, v1, v2
HAVING
  NOT(v1 = "all" AND v2 = "all") -- 全てallのパターンは要らないので取り除く

出力結果(抜粋)

UNNEST([カラム名 + "all"])とする事で、カラム名だけを"all"に変えたデータを再度呼び出すことができます。

これを利用することで、集計クエリが大幅に短くなりました。

まとめ

UNNESTを使いこなすことで、パターン毎の処理を短く記述することが出来ました。