代码:
create table dwb_news.rsu comment 'retention speed up table'
with (format = 'ORC')
as
with t1 as
(select
distinct_id as uid ,
logday,
date_diff('day', from_iso8601_timestamp('2020-08-01'), from_unixtime(ctime/1000))as gap
from ods_news.event
where logday >='20200801' and event = 'AppPageView'
),
t2 as (
select logday ,distinct_id as uid from ods_news.event where logday>='20200801' and
event = 'AppPageView'
),
t3 as (
select t2.logday,
t2.uid, (t1.gap - date_diff('day', from_iso8601_timestamp('2020-08-01'),date_parse(t2.logday,'%Y%m%d')))as gap
from t2
left join t1
on t1.uid=t2.uid
where t1.logday>=t2.logday group by t2.logday,t1.gap,t2.uid
),
t4 as (
select date_format(date_parse(logday,'%Y%m%d'),'%Y-%m-%d') as reg_date,
date_format(date_add('day', gap, date_parse(logday,'%Y%m%d')),'%Y-%m-%d') as biz_date,
gap,
count(1) as retention_num
from t3 group by gap,logday order by logday, gap
),
t5 as
(select reg_date, retention_num from t4 where t4.gap=0)
select t4.*,t5.retention_num as new_num from t4 left join t5 on
t5.reg_date=t4.reg_date;
create table dws_news.rr1
with(format='ORC')
as
select
gap ,
sum(retention_num)*1.000/sum(new_num) as rr
from dwb_news.rsu
where biz_date between '2020-08-01' and '2020-08-07' and gap between 1 and 7
group by gap
order by gap
错误为:
在t4表的分区出现问题:
查找分区,错误原因:分区中出现了default