単純に足し算をしても、金額の合計は出ない。
DBに入れた情報は、外国通貨が混ざっているから、日本円に変換する必要がある。
DBデータイメージ
purchase_amount_text | purchase_prifix | purchase_amount |
---|---|---|
$14.99 | $ | 14.99 |
¥500 | ¥ | 500 |
SGD 5.00 | SGD | 5 |
これをレート変換するためのテーブルを2つ作る。
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% |
データ量が多すぎてあってるかどうかが分からん。
短い動画でデータが正しいか検証して見る必要がありそう。