SQL个人笔记——社区内容发帖数据统计逻辑总结

--内容社区发帖数据统计逻辑

--1-10月发帖量和发帖人数统计
select
     trunc(a.day,'MM') as month
    ,count(distinct a.tid) as tid_num  --可作为发帖数和发帖人数pv，这两个值等价
    ,count(distinct a.openid) as uv 
    ,count(a.openid) as pv   --改值有误，并不是实际的发帖人数pv，存在并集过多的情况
from
    (
    --发帖新口径
    select
         from_unixtime(publish, 'yyyy-MM-dd') as day
        ,open_id as openid   --作者id
        ,tid --帖子ID
    from
         eden.ods_vivocommunity_t_thread_info_df  --新的发帖表
    where 
         from_unixtime(publish, 'yyyy-MM-dd')  between '2020-01-01' and '2020-10-31'  
         and 
         tid is not null  --tid不为null
         --and tid <> ''  --tid不为空
    group by
         from_unixtime(publish, 'yyyy-MM-dd')
        ,open_id
        ,tid
    union all
    select
         s1.day as day 
        ,s2.openid as openid
        ,s1.tid as tid 
    from    
        (
        --发帖老口径
        select
             f