编辑代码

--大盘
SELECT
  CONCAT(
    ROUND(
      SUM(
        IF(actual_payment_mode = '0110', total_play_count, 0)
      ) / MAX(dp_play_cnt) * 100,
      2
    ),
    '%'
  ) AS percentage
FROM
  (
    SELECT
      track_id,
      total_play_count,
      RANK() OVER      (
        ORDER BY
          total_play_count DESC
      ) rk
    FROM
      (
        SELECT
          track_id,
          SUM(total_play_count) total_play_count
        FROM
          tme_cd_aiting_core.dws_cdn_online_d_di
        WHERE
          imp_date BETWEEN '20250512' AND '20250518'
        GROUP BY
          track_id
      ) t
  ) a
  LEFT JOIN (
    SELECT
      a.itemid,
      IF(b.exclusivity = '0110', '0110', a.exclusivity) actual_payment_mode
    FROM
      tme_cd_aiting_core.dim_at_iteminfo_di PARTITION (p_20250518) a
      LEFT JOIN (
        SELECT
          itemid,
          MAX(IF(exclusivity = '0110', '0110', '0000')) exclusivity
        FROM
          (
            SELECT
              itemid,
              exclusivity
            FROM
              tme_cd_aiting_core.dim_at_item_channel_vv_di PARTITION (p_20250518) p
            WHERE
              exclusivity IS NOT NULL
            UNION ALL
            SELECT
              itemid,
              exclusivity
            FROM
              tme_cd_aiting_core.dim_at_item_channel_hw_di PARTITION (p_20250518) p
            WHERE
              exclusivity IS NOT NULL
            UNION ALL
            SELECT
              itemid,
              exclusivity
            FROM
              tme_cd_aiting_core.dim_at_item_channel_w1_di PARTITION (p_20250518) p
            WHERE
              exclusivity IS NOT NULL
          ) t
        GROUP BY
          1
      ) b ON a.itemid = b.itemid
  ) b ON a.track_id = b.itemid
  LEFT JOIN (
    SELECT
      SUM(total_play_count) dp_play_cnt
    FROM
      tme_cd_aiting_core.dws_cdn_online_d_di
    WHERE
      imp_date BETWEEN '20250512' AND '20250518'
  ) c ON 1 = 1
WHERE
  rk <= 10000;

--华为
SELECT
  CONCAT(
    ROUND(
      SUM(IF(actual_payment_mode = 0110, play_cnt, 0)) / MAX(dp_play_cnt) * 100,
      2
    ),
    '%'
  ) AS percentage
FROM
  (
    SELECT
      track_id,
      MAX(actual_payment_mode) actual_payment_mode,
      SUM(total_play_count)    play_cnt
    FROM
      tme_cd_aiting_core.dws_cdn_online_d_di
    WHERE
      imp_date BETWEEN '20250512' AND '20250518'
      AND channel_code = 'HW'
    GROUP BY
      1
    ORDER BY
      SUM(total_play_count) DESC
    LIMIT
      10000
  ) a
  LEFT JOIN (
    SELECT
      SUM(total_play_count) dp_play_cnt
    FROM
      tme_cd_aiting_core.dws_cdn_online_d_di
    WHERE
      imp_date BETWEEN '20250512' AND '20250518'
      AND channel_code = 'HW'
  ) b ON 1 = 1;

--vivo
SELECT
  CONCAT(
    ROUND(
      SUM(IF(actual_payment_mode = 0110, play_cnt, 0)) / MAX(dp_play_cnt) * 100,
      2
    ),
    '%'
  ) AS percentage
FROM
  (
    SELECT
      track_id,
      MAX(actual_payment_mode) actual_payment_mode,
      SUM(total_play_count)    play_cnt
    FROM
      tme_cd_aiting_core.dws_cdn_online_d_di
    WHERE
      imp_date BETWEEN '20250512' AND '20250518'
      AND channel_code = 'VV'
    GROUP BY
      1
    ORDER BY
      SUM(total_play_count) DESC
    LIMIT
      10000
  ) a
  LEFT JOIN (
    SELECT
      SUM(total_play_count) dp_play_cnt
    FROM
      tme_cd_aiting_core.dws_cdn_online_d_di
    WHERE
      imp_date BETWEEN '20250512' AND '20250518'
      AND channel_code = 'VV'
  ) b ON 1 = 1;