SQL_跨年周留存计算

跨年周留存计算如何计算？

正常计算周留存，可以直接用 weekofyear(t1.dt) = weekofyear(t2.dt) -1
但是如果遇到跨年，按照一年52个周，新年又从1开始，导致跨年的周留存，计算不出来；

解决之道：

每一周的开始日期和结束日期是相对固定的，可以先把日期固定到周，然后取各个周的开始日期，本周与下周的开始日期，固定的日期之差正好是7天，就可以计算跨年周留存；

跨年的周留存代码

with tmp_da_user as (
SELECT  ds    --日期
        ,event_id    --访问、事件
        ,page    --页面
        ,user_Id
        ,dt
FROM    (select *
,to_date(ds,'yyyymmdd') as dt 
from ods_all_mobile_log)t
WHERE   ds BETWEEN '20210301'  AND     '20210317'
AND     page = 'PageId-DEGG34F9'    --页面
-- AND     elementid IN ('ElementId-97F76HA3')    --事件
AND     event_id = 2001    --2001访问、19999事件
GROUP BY ds
        ,dt 
         ,event_id
         ,page    --页面
         ,user_Id
         --,elementid    --事件
)

,tmp_da_user_weekly as (
    select 
    weekly
    ,to_date(start_weekly,'yyyymmdd') as start_weekly
    ,weekly_range
    ,event_id
         ,page    --页面
         ,user_Id
    from (select 
      *
      ,weekofyear(dt) as weekly
        ,to_char(DATEADD(dt,-WEEKDAY(dt),'dd'),'yyyymmdd') as start_weekly
        ,concat(to_char(DATEADD(dt,-WEEKDAY(dt),'dd'),'yyyymmdd')
            ,'~',to_char(DATEADD(dt,6-WEEKDAY(dt),'dd'),'yyyymmdd')) as weekly_range
    from tmp_da_user )
    group by 
    weekly
    ,start_weekly
    ,weekly_range
    ,event_id
    ,page    --页面
    ,user_Id
)

select 
    t1.weekly
    ,t1.start_weekly
    ,t1.weekly_range
    ,t1.event_id
    ,t1.page    --页面
    ,count(distinct t1.user_id) as uv 
    ,count(distinct t2.user_id) as 次周留存uv
from tmp_da_user_weekly t1 
left join tmp_da_user_weekly t2 
on (t1.page = t2.page
and t1.user_id = t2.user_id
and t1.event_id = t2.event_id 
and DATEDIFF(t2.start_weekly,t1.start_weekly) = 7
)
group by 
    t1.weekly
    ,t1.start_weekly
    ,t1.weekly_range
    ,t1.event_id
    ,t1.page    --页面

• 跨年的周留存代码-结果集
  

不跨年的周留存代码

with tmp_da_user_weekly as (
SELECT  weekofyear(dt) as weekly    --日期
 ,concat(to_char(DATEADD(dt,-WEEKDAY(dt),'dd'),'yyyymmdd')
            ,'~',to_char(DATEADD(dt,6-WEEKDAY(dt),'dd'),'yyyymmdd')) as weekly_range
        ,event_id    --访问、事件
        ,page    --页面
        ,user_Id
FROM    (select *
    ,to_date(ds,'yyyymmdd') as dt 
    from ods_all_mobile_log)t
WHERE   ds BETWEEN '20210301'  AND     '20210317'
AND     page = 'PageId-DEGG34F9'    --页面
-- AND     elementid IN ('ElementId-97F76HA3')    --事件
AND     event_id = 2001    --2001访问、19999事件
GROUP BY weekofyear(dt)
         ,concat(to_char(DATEADD(dt,-WEEKDAY(dt),'dd'),'yyyymmdd')
            ,'~',to_char(DATEADD(dt,6-WEEKDAY(dt),'dd'),'yyyymmdd'))
         ,event_id
         ,page    --页面
         ,user_Id
)

select 
    t1.weekly
    ,t1.weekly_range
    ,t1.event_id
    ,t1.page    --页面
    ,count(distinct t1.user_id) as uv 
    ,count(distinct t2.user_id) as 次周留存uv
from tmp_da_user_weekly t1 
left join tmp_da_user_weekly t2 
on (t1.page = t2.page
and t1.user_id = t2.user_id
and t1.weekly = t2.weekly - 1 
)
group by 
    t1.weekly
    ,t1.weekly_range
    ,t1.event_id
    ,t1.page    --页面