Tips3. result_key_stats のヒストグラムカラムの展開方法 (100件の例)

以下の result_key_stats テーブルのレコードに対して、 histogram_${key_name} カラムの値を整形して取り出し、ヒストグラムを描いてみよう。

from_table	total_distinct	distinct_with_email	distinct_with_td_client_id	distinct_with_td_global_id	distinct_with_td_ssc_id	histogram_email	histogram_td_client_id	histogram_td_global_id	histogram_td_ssc_id	time
*	1909226	30579	1909096	1908998	680373	1:30247,2:311,3:14,4:4,7:1,25:1,31:1	1:1738227,2:115270,3:26894,4:10851,5:5474,…	1:1575567,2:166595,3:69711,4:27998,5:16761,…	1:563326,2:96817,3:12685,4:3563,5:1579,…	1689326177

WITH tbl_histogram AS
(
  SELECT
     CAST(SPLIT_PART(elm,':',1) AS BIGINT) AS x
    ,CAST(SPLIT_PART(elm,':',2) AS BIGINT) AS num
  FROM (
    SELECT
      SPLIT(histogram_${key_name},',') AS hist_ary
      ,ROW_NUMBER()OVER(ORDER BY time DESC) AS id
    FROM ${canonical_id_name}_result_key_stats
    WHERE from_table = '*'
  )
  CROSS JOIN UNNEST(hist_ary) AS t(elm)
  WHERE id = 1
)
, tbl_serial_numbers AS
(
  SELECT val
  FROM ( VALUES( SEQUENCE(1,100,1) ) ) AS t(seq_ary)
  CROSS JOIN UNNEST(seq_ary) AS t(val)
)

SELECT x, num, num * 1.0 / SUM(num)OVER(ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS percentage_of_total
FROM
(
  SELECT x, num
  FROM tbl_histogram
  UNION ALL
  -- Completion of missing values
  SELECT x, 0 AS num
  FROM ( SELECT val AS x FROM tbl_serial_numbers)
  WHERE x NOT IN (SELECT x FROM tbl_histogram)
)
ORDER BY x
LIMIT 100

SQL内の percentage_of_total は、全体の数に対する、その x の値での数の割合を求めている。 また、以下の2箇所でヒストグラムの x 軸を 0 から 100 までに制限している。

FROM ( VALUES( SEQUENCE(1,100,1) ) ) AS t(seq_ary)

LIMIT 100

distinct_with_email のヒストグラム

アウトプット

x	num	percentage_of_total
1	30337	0.99
2	313	0.01
3	14	0.00
4	4	0.00
5	0	0.00
…	…

ヒストグラム

※ 以下のグラフは棒グラフとして描画した者だが、今回の SQL で x の値が抜けなく 1 ずつ刻むようになっているので (x 軸を数直線とみなすことができ) ヒストグラムとみなして良いことになる。また、同時に その x の値が全体に占める割合を円グラフでも描画している。

Fig. 60 ヒストグラム

円グラフ

Fig. 61 円グラフ

email の値はほとんど変更されることはないため、1つの email に対して canonical_id が生成されるケースが最も多い。故にそのケース数が 30,337 と圧倒的に多い今回の例は違和感は少ない。

distinct_with_td_client_id のヒストグラム

アウトプット

x	num	percentage_of_total
1	1690899	0.90
2	121851	0.07
3	28552	0.02
4	11489	0.01
5	5783	0.00
…	…

td_client_id は cookie_id のため、時間の経過とともに値が変化していく。十分に長い期間のテーブルがソースとなっていれば、1人のユーザーは複数の td_client_id を持つことになるので、5〜10 の td_client_id に対して canonical_id が生成されるケースが多くなるはずである。ただし、今回の例では 1 つの td_client_id に対して canonical_id が生成されたケースが圧倒的に多くなってしまっている。考えられる原因は以下のようなものがある：

• (正しく縫い合わせはできているが) 多くのユーザーが数回のアクセスのみで去ってしまっている場合だと、彼らは td_client_id を1種類しか持たないことになる。そのためにこのような結果になった。

• (うまく縫い合わせできなかった) 本来は同一人物であるが、td_client_id が変更された時にそれらを紐づけることができず (例えば単一テーブルしかないと紐付けが難しい) 、td_client_id ごとに canonical_id が生成されてしまった。

ヒストグラム

Fig. 62 ヒストグラム

円グラフ

Fig. 63 円グラフ