编辑代码

--大盘
SELECT
  CONCAT(ROUND((COUNT(*) / 10000) * 100, 2), '%') AS percentage
FROM
  (
    SELECT
      track_id,
      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
WHERE
  rk <= 10000
  AND actual_payment_mode = '0110';

--hw、vv渠道
SELECT
  channel_code,
  CONCAT(ROUND((COUNT(*) / 10000) * 100, 2), '%') AS percentage
FROM
  (
    SELECT
      channel_code,
      track_id,
      actual_payment_mode,
      RANK() OVER         (
        PARTITION BY
          channel_code
        ORDER BY
          total_play_count DESC
      ) rk
    FROM
      (
        SELECT
          channel_code,
          track_id,
          MAX(actual_payment_mode) actual_payment_mode,
          SUM(total_play_count)    total_play_count
        FROM
          (
            SELECT
              channel_code,
              track_id,
              FIRST_VALUE(actual_payment_mode) OVER (
                PARTITION BY
                  channel_code,
                  track_id
                ORDER BY
                  imp_date DESC
              ) actual_payment_mode,
              total_play_count
            FROM
              dws_cdn_online_d_di
            WHERE
              imp_date BETWEEN '20250512' AND '20250518'
              AND channel_code IN ('HW', 'VV')
          ) t
        GROUP BY
          channel_code,
          track_id
      ) t
  ) t
WHERE
  rk <= 10000
  AND actual_payment_mode = 0110
GROUP BY
  channel_code;



--hw、vv渠道(4月之前的数据)
SELECT
  channel_code,
  CONCAT(ROUND((COUNT(*) / 10000) * 100, 2), '%') AS percentage
FROM
  (
    SELECT
      channel_code,
      track_id,
      actual_payment_mode,
      RANK() OVER         (
        PARTITION BY
          channel_code
        ORDER BY
          total_play_count DESC
      ) rk
    FROM
      (
        SELECT
          channel_code,
          track_id,
          MAX(actual_payment_mode) actual_payment_mode,
          SUM(total_play_count)    total_play_count
        FROM
          (
            SELECT
              channel_code,
              itemid                                AS track_id,
              FIRST_VALUE(actual_payment_mode) OVER (
                PARTITION BY
                  channel_code,
                  itemid
                ORDER BY
                  dl_date DESC
              ) actual_payment_mode,
              play_count AS total_play_count
            FROM
              dws_at_cdn_online_di
            WHERE
              dl_date BETWEEN '20250407' AND '20250413'
              AND channel_code IN ('HW', 'VV')
          ) t
        GROUP BY
          channel_code,
          track_id
      ) t
  ) t
WHERE
  rk <= 10000
  AND actual_payment_mode = 0110
GROUP BY
  channel_code;