-- 素材字段填充率分析
-- 分析各渠道的素材字段填充情况（文章标题、卡片标题、卡片封面、长文封面）
-- 使用: python fetch_daily.py "tasks/00_表的洞察/loghubods.opengid_base_data/01_素材字段填充率.sql"

SELECT  dt
        ,channel
        ,COUNT(*) AS 记录数
        -- 覆盖率
        ,ROUND(SUM(CASE WHEN 文章标题 IS NOT NULL AND 文章标题 != '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS 文章标题覆盖率
        ,ROUND(SUM(CASE WHEN 分享标题 IS NOT NULL AND 分享标题 != '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS 卡片标题覆盖率
        ,ROUND(SUM(CASE WHEN 分享封面 IS NOT NULL AND 分享封面 != '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS 卡片封面覆盖率
        ,ROUND(SUM(CASE WHEN 长文封面 IS NOT NULL AND 长文封面 != '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS 长文封面覆盖率
        -- 数量
        ,SUM(CASE WHEN 文章标题 IS NOT NULL AND 文章标题 != '' THEN 1 ELSE 0 END) AS 有文章标题
        ,SUM(CASE WHEN 分享标题 IS NOT NULL AND 分享标题 != '' THEN 1 ELSE 0 END) AS 有卡片标题
        ,SUM(CASE WHEN 分享封面 IS NOT NULL AND 分享封面 != '' THEN 1 ELSE 0 END) AS 有卡片封面
        ,SUM(CASE WHEN 长文封面 IS NOT NULL AND 长文封面 != '' THEN 1 ELSE 0 END) AS 有长文封面
FROM    loghubods.opengid_base_data
WHERE   dt = '${dt}'
AND     usersharedepth = 0
AND     videoid IS NOT NULL
GROUP BY dt, channel
ORDER BY 记录数 DESC
;
