编辑代码

--hw
SELECT
  CONCAT(
    ROUND(
      SUM(IF(actual_payment_mode = '0110', play_cnt, 0)) / MAX(dp_play_cnt) * 100,
      2
    ),
    '%'
  ) AS percentage
FROM
  (
    SELECT
      itemid track_id,
      MAX(actual_payment_mode) actual_payment_mode,
      SUM(play_count)    play_cnt
    FROM
      tme_cd_aiting_core.dws_at_cdn_online_di
    WHERE
      dl_date BETWEEN '20250407' AND '20250413'
      AND channel_code = 'HW'
    GROUP BY
      1
    ORDER BY
      SUM(play_count) DESC
    LIMIT
      1000
  ) a
  LEFT JOIN (
    SELECT
      SUM(play_count) dp_play_cnt
    FROM
      tme_cd_aiting_core.dws_at_cdn_online_di
    WHERE
      dl_date BETWEEN '20250407' AND '20250413'
      AND channel_code = 'HW'
  ) b ON 1 = 1;


--vv
SELECT
  CONCAT(
    ROUND(
      SUM(IF(actual_payment_mode = 0110, play_cnt, 0)) / MAX(dp_play_cnt) * 100,
      2
    ),
    '%'
  ) AS percentage
FROM
  (
    SELECT
      itemid track_id,
      MAX(actual_payment_mode) actual_payment_mode,
      SUM(play_count)    play_cnt
    FROM
      tme_cd_aiting_core.dws_at_cdn_online_di
    WHERE
      dl_date BETWEEN '20250407' AND '20250413'
      AND channel_code = 'VV'
    GROUP BY
      1
    ORDER BY
      SUM(play_count) DESC
    LIMIT
      1000
  ) a
  LEFT JOIN (
    SELECT
      SUM(play_count) dp_play_cnt
    FROM
      tme_cd_aiting_core.dws_at_cdn_online_di
    WHERE
      dl_date BETWEEN '20250407' AND '20250413'
      AND channel_code = 'VV'
  ) b ON 1 = 1;