-- 分析 headvideoid 为空的占比
WITH t_rec AS (
    SELECT  dt
            ,mid
            ,subsessionid
            ,headvideoid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS in_out
            ,GET_JSON_OBJECT(extend,"$.extParams.userShareDepth") AS layer
            ,page
            ,CASE   WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐"
                    WHEN page IN ("回流页","其他") THEN "非推荐"
                    ELSE "其他"
            END AS page_rec
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   dt BETWEEN "${start}" AND "${end}"
    AND     apptype IN ('4','0')
    AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页","回流页","其他")
)
,t_agg AS (
    SELECT  dt
            ,CASE   WHEN in_out = '内部' THEN '内部'
                    WHEN layer = '0' THEN '外部0层'
                    WHEN CAST(layer AS INT) > 0 THEN '外部裂变'
                    ELSE '其他'
            END AS crowd
            ,CASE   WHEN headvideoid IS NULL OR headvideoid = '' THEN 'headvideoid为空'
                    ELSE 'headvideoid有值'
            END AS headvid_status
            ,COUNT(1) AS cnt
    FROM    t_rec
    WHERE   page_rec = '推荐'
    GROUP BY dt
            ,CASE   WHEN in_out = '内部' THEN '内部'
                    WHEN layer = '0' THEN '外部0层'
                    WHEN CAST(layer AS INT) > 0 THEN '外部裂变'
                    ELSE '其他'
            END
            ,CASE   WHEN headvideoid IS NULL OR headvideoid = '' THEN 'headvideoid为空'
                    ELSE 'headvideoid有值'
            END
)
SELECT  dt
        ,crowd
        ,headvid_status
        ,cnt
        ,round(cnt / SUM(cnt) OVER (PARTITION BY dt, crowd), 4) AS pct
FROM    t_agg
WHERE   crowd <> '其他'
ORDER BY dt DESC, crowd, headvid_status
;
