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 の値が全体に占める割合を円グラフでも描画している。
円グラフ#
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 が生成されてしまった。