スパチャ色割合の計算 - たきこみの丸太

takikomiprogramming.hateblo.jp
↑この辺のタイミングで、既に色情報は持っているので、期間区切って色とチャンネルでグルーピングしたものをピポットしてやれば、集計表はすぐ作れる。

SELECT
    t.id
    ,t.author_name
    ,t.thumbnail0_url
    ,t.thumbnail1_url
    ,t.thumbnail2_url
    ,COALESCE(max(CASE WHEN t.color_name = '青' THEN t.count END), 0) AS 青
    ,COALESCE(max(CASE WHEN t.color_name = '水色' THEN t.count END), 0) AS 水色
    ,COALESCE(max(CASE WHEN t.color_name = '黄緑' THEN t.count END), 0) AS 黄緑
    ,COALESCE(max(CASE WHEN t.color_name = '黄' THEN t.count END), 0) AS 黄
    ,COALESCE(max(CASE WHEN t.color_name = 'オレンジ' THEN t.count END), 0) AS オレンジ
    ,COALESCE(max(CASE WHEN t.color_name = 'ピンク' THEN t.count END), 0) AS ピンク
    ,COALESCE(max(CASE WHEN t.color_name = '赤' THEN t.count END), 0) AS 赤
FROM
(
    SELECT
        ch.id
        ,ch.author_name
        ,ch.thumbnail0_url
        ,ch.thumbnail1_url
        ,ch.thumbnail2_url
        ,mv.color_name
        ,COUNT(mv.id) count
    FROM mv_chat_paids mv
    INNER JOIN channels ch ON mv.channel_id = ch.id
    WHERE
        mv.video_uploaded_date >= '2020-07-01 00:00:00'
        AND mv.video_uploaded_date <= '2020-07-31 23:59:59'
    GROUP BY
        ch.id
        ,ch.author_name
        ,ch.thumbnail0_url
        ,ch.thumbnail1_url
        ,ch.thumbnail2_url
        ,mv.color_name
) t
GROUP BY
    t.id
    ,t.author_name
    ,t.thumbnail0_url
    ,t.thumbnail1_url
    ,t.thumbnail2_url