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

_images/10-2-1.png

Fig. 60 ヒストグラム#

円グラフ#

_images/10-2-2.png

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 が生成されてしまった。

ヒストグラム#

_images/10-3-1.png

Fig. 62 ヒストグラム#

円グラフ#

_images/10-3-2.png

Fig. 63 円グラフ#