SQL 语句

圈子和圈子下帖子的UV和留存（分新老用户）
with circlepage as (
SELECT distinct
                        a1.date as date,
                        a1.deviceid
                    FROM
                        hive.log."tracking_log" as a1
                    where
                        date between date '2020-10-24'
                        and date '2020-11-29'
                        and actiontype = 'pageShow'
                        and pagename = 'circlepage'
),
postPage as (
SELECT distinct
                    a11.date
                    ,a11.deviceid
                FROM (select 
                        a1.date as date,
                        a1.deviceid,
                        cast(json_extract(a1.param, '$.invitationID') as varchar) as postid
                    from 
                        hive.log."tracking_log" as a1
                    where
                        date between date '2020-10-24'
                        and date '2020-11-29'
                        and actiontype = 'pageShow'
                        and pagename in (
                                            'postPage'
                                            ,'invitationLanding'
                                            )
                    ) as a11
                    inner join (select distinct id
                        from default.ods_forum_post
                        where circle_id is not null
                    ) as a12
                    on a11.postid = a12.id
),
device_daily_new as (
select
                            distinct dayString,
                            deviceid
                        from
                            hive.statistics."device_daily_new"
                        where
                            dayString between '2020-10-24'
                            and '2020-11-29'
),
liucun as (
        SELECT   
                    a1.date as date,
                    if(a2.deviceid is not null, '新激活设备', '非新激活设备') is_new,
                    a1.deviceid
                FROM (
                select date,deviceid
                from circlepage 
                union all
                select date,deviceid
                from postPage 
                ) as a1 
                left join device_daily_new as a2  on a1.deviceid = a2.deviceid and from_iso8601_date(a2.dayString) = a1.date
)
select date,is_new,max(if(datecha=0,device_cnt,null)) as t1,max(if(datecha=1,device_cnt,null)) as t2
from (
select a1.date,a1.is_new,date_diff('day',a1.date,a2.date) as datecha,count(distinct a1.deviceid) as device_cnt
from liucun as a1
left join liucun as a2 on a1.deviceid=a2.deviceid 
group by a1.date,a1.is_new,date_diff('day',a1.date,a2.date)
) t 
where datecha between 0 and 1
group by date,is_new
order by date



总圈子feed点击
SELECT date,count(distinct deviceid) as UV,count(deviceid) as pv
FROM hive.log."tracking_log"
where date between date'2020-11-01'
    and date '2020-11-18'
    and pagename = 'circlepage'
    and moditemname in ('allCardClick-alone','userName','postPicture','postVidio','tail','likeButton','collectButton','goodcomment','share','fastComment'
        ,'feedback','commentButton')
    and actiontype = 'click'
group by date
order by date


各圈子feed点击
SELECT date,json_extract(param,'$.pagecircleid') as id,count(distinct deviceid) as UV,count(deviceid) as pv
FROM hive.log."tracking_log"
where date between date'2020-11-01'
               and date '2020-11-29'
    and pagename = 'circlepage'
    and moditemname in ('allCardClick-alone','userName','postPicture','postVidio','tail','likeButton','collectButton','goodcomment','share','fastComment'
        ,'feedback','commentButton')
    and actiontype = 'click'
group by date,json_extract(param,'$.pagecircleid')
order by date


总圈子生产
select date,
          count(distinct post_id)as post_num
from hive.log.tracking_log
where pagename in( 'postPage','circlepage',invit
and actiontype = 'pageShow'
and date between date '2020-11-29' and date '2020-11-29'




圈子下帖子的消费（PV、UV）
select date,if(b.circle_id is not null,'圈子帖子','非圈子帖子'),count(a.deviceid) as pv,count(distinct a.deviceid) as uv
from (
    SELECT
        date
        ,deviceid
        ,cast(json_extract(param, '$.invitationID') as varchar) as id
    FROM hive.log."tracking_log"
    where date between date'2020-11-01'
        and date '2020-11-18'
        and pagename in ('postPage','invitationLanding')
        and actiontype = 'pageShow'
    ) as a
left join (
    select distinct id
    from default.ods_forum_post
    where circle_id is not null
    ) as b
on a.id = b.id
group by date
order by date




总圈子互动
SELECT 
    com.dt
    ,count(distinct com._id) as comment
    ,count(distinct l.id) as l
    ,count(distinct f.id) as favor
from (
    select distinct id,circle_id
    from default.ods_forum_post
    where circle_id is not null
    ) as post
left join (
    select distinct socialid,_id,dt
    from hive.allhistory."ods_user_comment_log"
    where level = 1
        and type = 'post'
        and dt >= '2020-11-01'
        and dt <= '2020-11-18'
    ) as com
on post.id = com.socialid
left join (
    select distinct id,social_id,dt
    from hive.default."ods_allhistory_user_social"
    where dt >= '2020-11-01'
        and dt <= '2020-11-18'
        and type = 'post'
        and social_type = 'like'
    ) as l
on post.id = l.social_id
and l.dt = com.dt
left join (
    select distinct id,social_id,dt
    from hive.default."ods_allhistory_user_social"
    where dt >= '2020-11-01'
        and dt <= '2020-11-18'
        and type = 'post'
        and social_type = 'favor'
    ) as F
on post.id = f.social_id
and f.dt = com.dt
group by com.dt,post.circle_id
order by com.dt






各个圈子UV、留存（分新老用户）
with circlepage as (
SELECT distinct
                        a1.date as date,
                        a1.deviceid,
                        cast(json_extract(a1.param, '$.circleid') as varchar) as cid
                    FROM
                        hive.log."tracking_log" as a1
                    where
                        date between date '2020-10-24'
                        and date '2020-11-29'
                        and actiontype = 'pageShow'
                        and pagename = 'circlepage'
),
postPage as (
SELECT distinct
                    a11.date
                    ,a11.deviceid
                    ,a12.cid
                FROM (select 
                        a1.date as date,
                        a1.deviceid,
                        cast(json_extract(a1.param, '$.invitationID') as varchar) as postid
                    from 
                        hive.log."tracking_log" as a1
                    where
                        date between date '2020-10-24'
                        and date '2020-11-29'
                        and actiontype = 'pageShow'
                        and pagename in (
                                            'postPage'
                                            ,'invitationLanding'
                                            )
                    ) as a11
                    inner join (select distinct 
                                        id
                                        ,circle_id as cid
                        from default.ods_forum_post
                        where circle_id is not null
                    ) as a12
                    on a11.postid = a12.id
),
device_daily_new as (
select
                            distinct dayString,
                            deviceid
                        from
                            hive.statistics."device_daily_new"
                        where
                            dayString between '2020-10-24'
                            and '2020-11-29'
),
liucun as (
        SELECT   
                    a1.date as date,
                    if(a2.deviceid is not null, '新激活设备', '非新激活设备') is_new,
                    a1.cid,
                    a1.deviceid
                FROM (
                select date,cast(cid as varchar) as cid,deviceid
                from circlepage 
                union all
                select date,cast(cid as varchar) as cid,deviceid
                from postPage 
                ) as a1 
                left join device_daily_new as a2  on a1.deviceid = a2.deviceid and from_iso8601_date(a2.dayString) = a1.date
)
select date,cid,is_new,max(if(datecha=0,device_cnt,null)) as t1,max(if(datecha=1,device_cnt,null)) as t2
from (
select a1.date,a1.is_new,a1.cid,date_diff('day',a1.date,a2.date) as datecha,count(distinct a1.deviceid) as device_cnt
from liucun as a1
left join liucun as a2 on a1.deviceid=a2.deviceid and a1.cid=a2.cid 
group by a1.date,a1.is_new,a1.cid,date_diff('day',a1.date,a2.date)
) t 
where datecha between 0 and 1
group by date,cid,is_new
order by date



各个圈子互动（评、赞、藏）
SELECT 
    com.dt
    ,post.circle_id
    ,count(distinct com._id) as comment
    ,count(distinct l.id) as l
    ,count(distinct f.id) as favor
from (
    select distinct id,circle_id
    from default.ods_forum_post
    where circle_id is not null
    ) as post
left join (
    select distinct socialid,_id,dt
    from hive.allhistory."ods_user_comment_log"
    where level = 1
        and type = 'post'
        and dt >= '2020-11-01'
        and dt <= '2020-11-18'
    ) as com
on post.id = com.socialid
left join (
    select distinct id,social_id,dt
    from hive.default."ods_allhistory_user_social"
    where dt >= '2020-11-01'
        and dt <= '2020-11-18'
        and type = 'post'
        and social_type = 'like'
    ) as l
on post.id = l.social_id
and l.dt = com.dt
left join (
    select distinct id,social_id,dt
    from hive.default."ods_allhistory_user_social"
    where dt >= '2020-11-01'
        and dt <= '2020-11-18'
        and type = 'post'
        and social_type = 'favor'
    ) as F
on post.id = f.social_id
and f.dt = com.dt
group by com.dt,post.circle_id
order by com.dt



圈子主页来源页面PV、UV
select
    a.date,
    a.prepagename,
    a.prepagename_pv,
    a.prepagename_uv,
    b.pagename_pv,
    b.pagename_uv
from
    (
        select
            date,
            prepagename,
            count(deviceid) as prepagename_pv,
            count(distinct deviceid) as prepagename_uv
        from
            hive.log."tracking_log"
        where
            pagename = 'circlepage'
            and actiontype = 'pageShow'
            and date between date '2020-11-01'
            and date '2020-11-18'
        group by
            date,
            prepagename
    ) a
    left join(
        select
            date,
            pagename,
            count(deviceid) as pagename_pv,
            count(distinct deviceid) as pagename_uv
        from
            hive.log."tracking_log"
        where
            date between date '2020-11-01'
            and date '2020-11-18'
        group by
            date,
            pagename
    ) b on a.prepagename = b.pagename
    and a.date = b.date
order by
    a.date desc 



圈子主页的下级页面PV 、 UV
select
    a.date,
    a.pagename,
    a.pagename_pv,
    a.pagename_uv,
    b.nextpagename_pv,
    b.nextpagename_uv
from
    (
        select
            date,
            pagename,
            count(deviceid) as pagename_pv,
            count(distinct deviceid) as pagename_uv
        from
            hive.log."tracking_log"
        where
            prepagename = 'circlepage'
            and actiontype = 'pageShow'
            and date between date '2020-11-01'
            and date '2020-11-18'
        group by
            date,
            pagename
    ) a
    left join(
        select
            date,
            pagename,
            count(deviceid) as nextpagename_pv,
            count(distinct deviceid) as nextpagename_uv
        from
            hive.log."tracking_log"
        where
            actiontype = 'pageShow'
            and date between date '2020-11-01'
            and date '2020-11-18'
        group by
            date,
            pagename
    ) b on a.pagename = b.pagename
    and a.date = b.date
order by
    a.date desc