-- 公众号投流-稳定 素材缺失排查
-- 分析为什么只有 33% 的记录有文章标题

SELECT  CASE WHEN 文章标题 IS NOT NULL AND 文章标题 != '' THEN '有文章标题' ELSE '无文章标题' END AS 文章标题状态
        ,公众号名
        ,COUNT(*) AS 记录数
        ,SUM(CASE WHEN wx_sn IS NOT NULL AND wx_sn != '' THEN 1 ELSE 0 END) AS 有wx_sn
        ,SUM(CASE WHEN contenturl IS NOT NULL AND contenturl != '' THEN 1 ELSE 0 END) AS 有contenturl
        ,SUM(CASE WHEN rootsourceid IS NOT NULL AND rootsourceid != '' THEN 1 ELSE 0 END) AS 有rootsourceid
        ,SUM(CASE WHEN 分享标题 IS NOT NULL AND 分享标题 != '' THEN 1 ELSE 0 END) AS 有分享标题
FROM    loghubods.opengid_base_data
WHERE   dt >= ${start}
AND     dt <= ${end}
AND     channel = '公众号投流-稳定'
AND     usersharedepth = 0
AND     videoid IS NOT NULL
GROUP BY CASE WHEN 文章标题 IS NOT NULL AND 文章标题 != '' THEN '有文章标题' ELSE '无文章标题' END
         ,公众号名
ORDER BY 文章标题状态, 记录数 DESC
;
