-- Step7: 头部视频（Top10曝光）的 vov 时间趋势
SELECT  dt
        ,COUNT(1) AS top10_cnt
        ,ROUND(AVG(vov0), 4) AS vov_mean
        ,ROUND(PERCENTILE(CAST(vov0*10000 AS BIGINT), 0.5)/10000, 4) AS vov_median
        ,ROUND(AVG(str_t0), 4) AS str_mean
        ,ROUND(AVG(ros_t0), 2) AS ros_mean
        ,ROUND(AVG(vor_t0), 2) AS vor_mean
        -- 低vov高曝光比例（vov < 0.35 且 rank <= 5）
        ,SUM(CASE WHEN vov0 < 0.35 AND 曝光rank <= 5 THEN 1 ELSE 0 END) AS problem_cnt
        ,ROUND(SUM(CASE WHEN vov0 < 0.35 AND 曝光rank <= 5 THEN 1 ELSE 0 END) / COUNT(1) * 100, 1) AS problem_pct
FROM    (
            SELECT  dt
                    ,视频id AS vid
                    ,曝光rank
                    ,ROUND(当日分发拉回曝光pv / NULLIF(当日分发曝光pv, 0), 4) AS vov0
                    ,ROUND(当日分发分享pv / NULLIF(当日分发曝光pv, 0), 4) AS str_t0
                    ,ROUND(当日分发回流uv / NULLIF(当日分发分享pv, 0), 4) AS ros_t0
                    ,ROUND(当日分发拉回曝光pv / NULLIF(当日分发回流uv, 0), 4) AS vor_t0
            FROM    loghubods.video_dimension_detail_add_column
            WHERE   dt = '${dt}'
            AND     曝光rank <= 10
        )
GROUP BY dt
;
