17,086
社区成员
发帖
与我相关
我的任务
分享
select to_date(stat_date,'yyyy-mm-dd') as "时间",cust_num,new_cust_num,'口袋APP下载' as "指标类型",cn "指标项",cv "指标值"
from (select stat_date,
sum(cust_num) as cust_num,
sum(cust_num)- nvl(lag(sum(cust_num))over(order by stat_date),0) new_cust_num,
round(sum(app_num)*100/sum(cust_num),2) as app_rate,
sum(app_num) as app_num,
sum(app_num) - nvl(lag(sum(app_num))over(order by stat_date),0) new_cust_num2
from tmp
group by stat_date
)
where stat_date is not null
model
return updated rows
partition by (stat_date)
dimension by (0 as n)
measures(cast('x' as varchar2(40)) as cn, cast(1 as number(30)) as cv, app_rate as c1,app_num as c2,new_cust_num2 as c3)
rules upsert all
(
cn[1] = 'App下载率(%)',
cn[2] = '累计下载客户数',
cn[3] = '当周新增客户数',
cv[1] = c1[0],
cv[2] = c2[0],
cv[3] = c3[0]
)