Youtubeビッグデータ解析　その3 スーパーチャット金額の集計

単純に足し算をしても、金額の合計は出ない。
DBに入れた情報は、外国通貨が混ざっているから、日本円に変換する必要がある。

DBデータイメージ

purchase_amount_text	purchase_prifix	purchase_amount
$14.99	$	14.99
￥500	￥	500
SGD 5.00	SGD	5

これをレート変換するためのテーブルを2つ作る。

currency

通貨記号をISOコードに変換するためのテーブル

prifix	iso	memo
₩	KRW	韓国ウォン
MX$	MXN	メキシコ・ペソ
HK$	HKD	香港ドル

exchange_rate

日本円に換算するためのレートテーブル

time_code	iso	rate
202007	KRW	0.09
202007	MXN	4.78
202007	HKD	13.84

レートをどんなスパンで管理するかは、後から適当に変更できるように考慮してみた。
ISOコード持ってるから、自動化しようと思えば、自動化もできる。
今回は手動で組んだわけだが。

通貨レートを変換した実態ビューの作成

time_codeは動画の投稿時間から生成した。

 SELECT t_cp.id,
    t_cp.purchase_amount_text,
    t_cp.purchase_prifix,
    t_cp.purchase_amount,
    t_cp.header_background_color,
    t_cp.header_text_color,
    t_cp.body_background_color,
    t_cp.body_text_color,
    t_cp.author_name_text_color,
    t_cp.timestamp_color,
    t_exchange.time_code,
    t_exchange.iso,
    t_exchange.rate,
    COALESCE(t_cp.purchase_amount, 0::numeric) * COALESCE(t_exchange.rate, 0::numeric) AS rated_yen_value,
        CASE
            WHEN t_cp.purchase_prifix::text = '￥'::text THEN t_cp.purchase_amount
            ELSE 0::numeric
        END AS only_yen_value,
        CASE
            WHEN t_cp.purchase_prifix::text = '￥'::text THEN 1
            ELSE 0
        END AS is_yen_flg
   FROM channels t_ch
     JOIN videos t_v ON t_ch.id::text = t_v.channel_id::text
     JOIN chats t_c ON t_v.id::text = t_c.video_id::text
     JOIN chat_paids t_cp ON t_c.id::text = t_cp.id::text
     LEFT JOIN currency t_currency ON t_cp.purchase_prifix::text = t_currency.prifix::text
     LEFT JOIN exchange_rate t_exchange ON t_currency.iso::text = t_exchange.iso::text AND to_number(to_char(t_v.uploaded_date, 'YYYYMM'::text), '999999'::text) = t_exchange.time_code::numeric;

実態ビューを使って、動画情報の集計SQLを組む

SELECT
    t_base.チャンネル名
    ,t_base.動画id
    ,t_base.動画名
    ,t_base.サムネイル
    ,t_base.総チャット数
    ,t_base.総チャット数 - t_base.スパチャ数 - メンバーシップ増加数 - Yotubeメッセージ数 通常チャット
    ,t_base.スパチャ数
    ,t_base.日本円スパチャ数
    ,CEILING(CAST(t_base.日本円スパチャ数 AS decimal ) / CAST(t_base.スパチャ数 AS decimal ) * 100 * 100) / 100 スパチャ数日本円率
    ,t_base.メンバーシップ増加数
    ,t_base.Yotubeメッセージ数
    ,t_base.メンバーシップメッセージ数
    ,CEILING(CAST(t_base.メンバーシップメッセージ数 AS decimal) / CAST((t_base.総チャット数 - メンバーシップ増加数 - Yotubeメッセージ数) AS decimal) * 100 * 100) / 100 メンバーシップチャット率
    ,t_base.スパチャ金額合計
    ,t_base.日本円のみ合計金額
    ,CEILING(CAST(t_base.日本円のみ合計金額 AS decimal) / CAST(t_base.スパチャ金額合計 AS decimal) * 100 * 100) / 100 スパチャ金額日本円率
FROM
(
    SELECT
        t_ch.name チャンネル名
        ,t_v.id 動画id
        ,t_v.thumbnail_url サムネイル
        ,t_v.name 動画名
        ,COUNT(t_c.id) 総チャット数
        ,COUNT(t_cp.id) スパチャ数
        ,SUM(t_cp.is_yen_flg) 日本円スパチャ数
        ,COUNT(t_m_item.id) メンバーシップ増加数
        ,COUNT(t_ce.id) Yotubeメッセージ数
        ,COUNT(t_m_info.id) メンバーシップメッセージ数
        ,SUM(t_cp.rated_yen_value) スパチャ金額合計
        ,SUM(t_cp.only_yen_value) 日本円のみ合計金額
    FROM channels t_ch
    INNER JOIN videos t_v ON t_ch.id = t_v.channel_id
    INNER JOIN chats t_c ON t_v.id = t_c.video_id
    LEFT JOIN chat_comments t_cc ON t_c.id = t_cc.id
    LEFT JOIN mv_chat_paids t_cp ON t_c.id = t_cp.id
    LEFT JOIN chat_membership_items t_m_item ON t_c.id = t_m_item.id
    LEFT JOIN chat_membership_infos t_m_info ON t_c.id = t_m_info.id
    LEFT JOIN chat_engagements t_ce ON t_c.id = t_ce.id
    WHERE
        t_v.id = 'k28zK41-jJA'
    GROUP BY 
        t_ch.name
        ,t_v.id
        ,t_v.name
) t_base

で、出来上がったモノがこちらになります。

チャンネル名	Haato Channel 赤井はあと
動画id	k28zK41-jJA
動画名	ENGLISH
サムネイル
総チャット数	13447
通常チャット	13293
スパチャ数	139
日本円スパチャ数	73
スパチャ数日本円率	52.52%
メンバーシップ増加数	14
yotubeメッセージ数	1
メンバーシップチャット数	2600
メンバーシップチャット率	19.36%
スパチャ金額合計	97832.6265円
日本円のみ合計金額	59940円
スパチャ金額日本円率	61.27%