これは何
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を使いこなすことで、パターン毎の処理を短く記述することが出来ました。