select weeks
,concat(min(dt),'_',max(dt)) as wk
,CEILING(round(sum(counts) /7)) as avg_active_counts
from (
SELECT t2.dt,t2.weeks,t2.counts
from
(
select from_unixtime(unix_timestamp(cast (dt as string),'yyyyMMdd'),'yyyy-MM-dd') as dt
, concat(substr(from_unixtime(unix_timestamp(cast (dt as string),'yyyyMMdd'),'yyyy-MM-dd'),0,4)
,date_format(date_sub(from_unixtime(unix_timestamp(cast (dt as string),'yyyyMMdd'),'yyyy-MM-dd'), 5), '%w')) as weeks
,counts
from
(select dt
,count(1) counts
from *
where dt >= '20220101'
and is_active = '1' group by dt
) t1
) t2
) t3
group by
weeks
取上周五到本周四
最新推荐文章于 2024-04-12 08:44:03 发布