九、信贷异动拆解
--********************首次成功授信人数波动拆解*************--
--****day over day 乘法拆解****--
select
'天' as tag1,
'乘法拆解' as tag2,
dt,
'汇总' as third_level_nm,
first_succ_uv as third_first_succ_uv,
first_succ_uv0 as third_first_succ_uv0,
first_succ_uv_change as third_first_succ_uv_change,
first_succ_uv,
first_succ_uv0,
first_succ_uv_change,
null as third_first_succ_uv_attribution,
----
bg_uv,
bg_uv0,
(bg_uv / bg_uv0 -1) as bg_uv_change,
nvl(
(ln(bg_uv) - ln(bg_uv0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as bg_uv_attribution,
----
ck_rate,
ck_rate0,
(ck_rate / ck_rate0 -1) as ck_rate_change,
nvl(
(ln(ck_rate) - ln(ck_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as ck_rate_attribution,
----
ctr,
ctr0,
(ctr / ctr0 -1) as ctr_rate_change,
nvl(
(ln(ctr) - ln(ctr0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as ctr_attribution,
----
fenfa_rate,
fenfa_rate0,
(fenfa_rate / fenfa_rate0 -1) as fenfa_rate_change,
nvl(
(ln(fenfa_rate) - ln(fenfa_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as fenfa_rate_attribution,
----
succ_fenfa_rate,
succ_fenfa_rate0,
(succ_fenfa_rate / succ_fenfa_rate0 -1) as succ_fenfa_rate_change,
nvl(
(ln(succ_fenfa_rate) - ln(succ_fenfa_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as succ_fenfa_rate_attribution,
----
submit_rate,
submit_rate0,
(submit_rate / submit_rate0 -1) as submit_rate_change,
nvl(
(ln(submit_rate) - ln(submit_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as submit_rate_attribution,
----
succ_rate,
succ_rate0,
(succ_rate / succ_rate0 -1) as succ_rate_change,
nvl(
(ln(succ_rate) - ln(succ_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as succ_rate_attribution,
----
first_rate,
first_rate0,
(first_rate / first_rate0 -1) as first_rate_change,
nvl(
(ln(first_rate) - ln(first_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as first_rate_attribution
from(
select
*,
lag(bg_uv, 1) over (
order by
dt
) as bg_uv0,
lag(ck_rate, 1) over (
order by
dt
) as ck_rate0,
lag(ctr, 1) over (
order by
dt
) as ctr0,
lag(fenfa_rate, 1) over (
order by
dt
) as fenfa_rate0,
lag(succ_fenfa_rate, 1) over (
order by
dt
) as succ_fenfa_rate0,
lag(submit_rate, 1) over (
order by
dt
) as submit_rate0,
lag(succ_rate, 1) over (
order by
dt
) as succ_rate0,
lag(first_rate, 1) over (
order by
dt
) as first_rate0,
lag(first_succ_uv, 1) over (
order by
dt
) as first_succ_uv0,
nvl (
first_succ_uv / lag(first_succ_uv, 1) over (
order by
dt
) -1,
0
) as first_succ_uv_change
from(
select
dt,
bg_uv,
dj_uv / bg_uv as ck_rate,
dd_uv / dj_uv as ctr,
ff_uv / dd_uv as fenfa_rate,
succ_ff_uv / ff_uv as succ_fenfa_rate,
submit_uv / succ_ff_uv as submit_rate,
succ_uv / submit_uv as succ_rate,
first_succ_uv / succ_uv as first_rate,
first_succ_uv
from(
select
dt,
count(
distinct case
when is_baoguang = 1 then uid
end
) as bg_uv,
count(
distinct case
when is_dianji = 1 then uid
end
) as dj_uv,
count(
distinct case
when dau = 1 then uid
end
) as dd_uv,
count(
distinct case
when is_fenfa = 1 then uid
end
) as ff_uv,
count(
distinct case
when is_succ_fenfa = 1 then uid
end
) as succ_ff_uv,
count(
distinct case
when is_curr_day_credit_submit = 1 then uid
end
) as submit_uv,
count(
distinct case
when is_curr_day_credit_succ = 1 then uid
end
) as succ_uv,
count(
distinct case
when is_first_succ = 1 then uid
end
) as first_succ_uv
from
fin_ds.dwd_flow_pool_to_credit_di
where
dt between '2023-03-31'
and '$[YYYY-MM-DD - 1D]'
group by
dt --third_level_nm
) t0
) t1
) t2
union all
--****day over day 三级渠道拆解****--
select
'天' as tag1,
'三级渠道拆解' as tag2,
dt,
third_level_nm,
third_first_succ_uv,
third_first_succ_uv0,
third_first_succ_uv_change,
first_succ_uv,
first_succ_uv0,
first_succ_uv / first_succ_uv0 -1 as first_succ_uv_change,
nvl(
(third_first_succ_uv - third_first_succ_uv0) / first_succ_uv0,
0
) as third_first_succ_uv_attribution,
bg_uv,
bg_uv0,
(bg_uv / bg_uv0 -1) as bg_uv_change,
nvl(
(ln(bg_uv) - ln(bg_uv0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as bg_uv_attribution,
----
ck_rate,
ck_rate0,
(ck_rate / ck_rate0 -1) as ck_rate_change,
nvl(
(ln(ck_rate) - ln(ck_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as ck_rate_attribution,
----
ctr,
ctr0,
(ctr / ctr0 -1) as ctr_rate_change,
nvl(
(ln(ctr) - ln(ctr0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as ctr_attribution,
----
fenfa_rate,
fenfa_rate0,
(fenfa_rate / fenfa_rate0 -1) as fenfa_rate_change,
nvl(
(ln(fenfa_rate) - ln(fenfa_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as fenfa_rate_attribution,
----
succ_fenfa_rate,
succ_fenfa_rate0,
(succ_fenfa_rate / succ_fenfa_rate0 -1) as succ_fenfa_rate_change,
nvl(
(ln(succ_fenfa_rate) - ln(succ_fenfa_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as succ_fenfa_rate_attribution,
----
submit_rate,
submit_rate0,
(submit_rate / submit_rate0 -1) as submit_rate_change,
nvl(
(ln(submit_rate) - ln(submit_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as submit_rate_attribution,
----
succ_rate,
succ_rate0,
(succ_rate / succ_rate0 -1) as succ_rate_change,
nvl(
(ln(succ_rate) - ln(succ_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as succ_rate_attribution,
----
first_rate,
first_rate0,
(first_rate / first_rate0 -1) as first_rate_change,
nvl(
(ln(first_rate) - ln(first_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as first_rate_attribution
from(
select
a00.dt as dt,
third_level_nm,
third_first_succ_uv,
third_first_succ_uv0,
case
when third_first_succ_uv / third_first_succ_uv0 < 1000000 then third_first_succ_uv / third_first_succ_uv0 -1
else null
end as third_first_succ_uv_change,
first_succ_uv,
first_succ_uv0,
first_succ_uv / first_succ_uv0 -1 as first_succ_uv_change,
bg_uv,
dj_uv / bg_uv as ck_rate,
dd_uv / dj_uv as ctr,
ff_uv / dd_uv as fenfa_rate,
succ_ff_uv / ff_uv as succ_fenfa_rate,
submit_uv / succ_ff_uv as submit_rate,
succ_uv / submit_uv as succ_rate,
third_first_succ_uv / succ_uv as first_rate,
dj_uv0 / bg_uv0 as ck_rate0,
bg_uv0,
dd_uv0 / dj_uv0 as ctr0,
ff_uv0 / dd_uv0 as fenfa_rate0,
succ_ff_uv0 / ff_uv0 as succ_fenfa_rate0,
submit_uv0 / succ_ff_uv0 as submit_rate0,
succ_uv0 / submit_uv0 as succ_rate0,
third_first_succ_uv0 / succ_uv0 as first_rate0
from
(
select
nvl(a0.dt, a1.dt + 1) as dt,
nvl(a0.third_level_nm, a1.third_level_nm) as third_level_nm,
case
when third_first_succ_uv > 0 then third_first_succ_uv
else 0.0000000000000001
end as third_first_succ_uv,
case
when third_first_succ_uv0 > 0 then third_first_succ_uv0
else 0.0000000000000001
end as third_first_succ_uv0,
case
when bg_uv > 0 then bg_uv
else 0.0000000000000001
end as bg_uv,
case
when bg_uv0 > 0 then bg_uv0
else 0.0000000000000001
end as bg_uv0,
case
when dj_uv > 0 then dj_uv
else 0.0000000000000001
end as dj_uv,
case
when dj_uv0 > 0 then dj_uv0
else 0.0000000000000001
end as dj_uv0,
case
when dd_uv > 0 then dd_uv
else 0.0000000000000001
end as dd_uv,
case
when dd_uv0 > 0 then dd_uv0
else 0.0000000000000001
end as dd_uv0,
case
when ff_uv > 0 then ff_uv
else 0.0000000000000001
end as ff_uv,
case
when ff_uv0 > 0 then ff_uv0
else 0.0000000000000001
end as ff_uv0,
case
when succ_ff_uv > 0 then succ_ff_uv
else 0.0000000000000001
end as succ_ff_uv,
case
when succ_ff_uv0 > 0 then succ_ff_uv0
else 0.0000000000000001
end as succ_ff_uv0,
case
when submit_uv > 0 then submit_uv
else 0.0000000000000001
end as submit_uv,
case
when submit_uv0 > 0 then submit_uv0
else 0.0000000000000001
end as submit_uv0,
case
when succ_uv > 0 then succ_uv
else 0.0000000000000001
end as succ_uv,
case
when succ_uv0 > 0 then succ_uv0
else 0.0000000000000001
end as succ_uv0
from
(
select
dt,
nvl(third_level_nm, 'unknown') as third_level_nm,
count(
distinct case
when is_baoguang = 1 then uid
end
) as bg_uv,
count(
distinct case
when is_dianji = 1 then uid
end
) as dj_uv,
count(
distinct case
when dau = 1 then uid
end
) as dd_uv,
count(
distinct case
when is_fenfa = 1 then uid
end
) as ff_uv,
count(
distinct case
when is_succ_fenfa = 1 then uid
end
) as succ_ff_uv,
count(
distinct case
when is_curr_day_credit_submit = 1 then uid
end
) as submit_uv,
count(
distinct case
when is_curr_day_credit_succ = 1 then uid
end
) as succ_uv,
count(
distinct case
when is_first_succ = 1 then uid
end
) as third_first_succ_uv
from
fin_ds.dwd_flow_pool_to_credit_di
where
dt between '2023-03-31'
and '$[YYYY-MM-DD - 1D]'
group by
dt,
third_level_nm
) a0 full
join (
select
dt,
nvl(third_level_nm, 'unknown') as third_level_nm,
count(
distinct case
when is_baoguang = 1 then uid
end
) as bg_uv0,
count(
distinct case
when is_dianji = 1 then uid
end
) as dj_uv0,
count(
distinct case
when dau = 1 then uid
end
) as dd_uv0,
count(
distinct case
when is_fenfa = 1 then uid
end
) as ff_uv0,
count(
distinct case
when is_succ_fenfa = 1 then uid
end
) as succ_ff_uv0,
count(
distinct case
when is_curr_day_credit_submit = 1 then uid
end
) as submit_uv0,
count(
distinct case
when is_curr_day_credit_succ = 1 then uid
end
) as succ_uv0,
count(
distinct case
when is_first_succ = 1 then uid
end
) as third_first_succ_uv0
from
fin_ds.dwd_flow_pool_to_credit_di
where
dt between '2023-03-31'
and '$[YYYY-MM-DD - 1D]'
group by
dt,
third_level_nm
) a1 on a0.dt = date_add(a1.dt, 1)
and a0.third_level_nm = a1.third_level_nm
) a00
left join(
select
dt,
first_succ_uv,
lag(first_succ_uv, 1) over (
order by
dt
) as first_succ_uv0
from(
select
dt,
count(
distinct case
when is_first_succ = 1 then uid
end
) as first_succ_uv
from
fin_ds.dwd_flow_pool_to_credit_di
where
dt between '2023-03-31'
and '$[YYYY-MM-DD - 1D]'
group by
dt
) a000
) a2 on a00.dt = a2.dt
) a1 --**** week over week 乘法拆解****--
union all
select
'周' as tag1,
'乘法拆解' as tag2,
dt,
null as third_level_nm,
null as third_first_succ_uv,
null as third_first_succ_uv0,
null as third_first_succ_uv_change,
first_succ_uv,
first_succ_uv0,
first_succ_uv_change,
null as third_first_succ_uv_attribution,
----
bg_uv,
bg_uv0,
(bg_uv / bg_uv0 -1) as bg_uv_change,
nvl(
(ln(bg_uv) - ln(bg_uv0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as bg_uv_attribution,
----
ck_rate,
ck_rate0,
(ck_rate / ck_rate0 -1) as ck_rate_change,
nvl(
(ln(ck_rate) - ln(ck_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as ck_rate_attribution,
----
ctr,
ctr0,
(ctr / ctr0 -1) as ctr_rate_change,
nvl(
(ln(ctr) - ln(ctr0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as ctr_attribution,
----
fenfa_rate,
fenfa_rate0,
(fenfa_rate / fenfa_rate0 -1) as fenfa_rate_change,
nvl(
(ln(fenfa_rate) - ln(fenfa_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as fenfa_rate_attribution,
----
succ_fenfa_rate,
succ_fenfa_rate0,
(succ_fenfa_rate / succ_fenfa_rate0 -1) as succ_fenfa_rate_change,
nvl(
(ln(succ_fenfa_rate) - ln(succ_fenfa_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as succ_fenfa_rate_attribution,
----
submit_rate,
submit_rate0,
(submit_rate / submit_rate0 -1) as submit_rate_change,
nvl(
(ln(submit_rate) - ln(submit_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as submit_rate_attribution,
----
succ_rate,
succ_rate0,
(succ_rate / succ_rate0 -1) as succ_rate_change,
nvl(
(ln(succ_rate) - ln(succ_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as succ_rate_attribution,
----
first_rate,
first_rate0,
(first_rate / first_rate0 -1) as first_rate_change,
nvl(
(ln(first_rate) - ln(first_rate0)) /(ln(first_succ_uv) - ln(first_succ_uv0)) * first_succ_uv_change,
0
) as first_rate_attribution
from(
select
*,
lag(bg_uv, 7) over (
order by
dt
) as bg_uv0,
lag(ck_rate, 7) over (
order by
dt
) as ck_rate0,
lag(ctr, 7) over (
order by
dt
) as ctr0,
lag(fenfa_rate, 7) over (
order by
dt
) as fenfa_rate0,
lag(succ_fenfa_rate, 7) over (
order by
dt
) as succ_fenfa_rate0,
lag(submit_rate, 7) over (
order by
dt
) as submit_rate0,
lag(succ_rate, 7) over (
order by
dt
) as succ_rate0,
lag(first_rate, 7) over (
order by
dt
) as first_rate0,
lag(first_succ_uv, 7) over (
order by
dt
) as first_succ_uv0,
nvl (
first_succ_uv / lag(first_succ_uv, 7) over (
order by
dt
) -1,
0
) as first_succ_uv_change
from(
select
dt,
bg_uv,
dj_uv / bg_uv as ck_rate,
dd_uv / dj_uv as ctr,
ff_uv / dd_uv as fenfa_rate,
succ_ff_uv / ff_uv as succ_fenfa_rate,
submit_uv / succ_ff_uv as submit_rate,
succ_uv / submit_uv as succ_rate,
first_succ_uv / succ_uv as first_rate,
first_succ_uv
from(
select
t1.dt,
count(
distinct case
when is_baoguang = 1 then uid
end
) as bg_uv,
count(
distinct case
when is_dianji = 1 then uid
end
) as dj_uv,
count(
distinct case
when dau = 1 then uid
end
) as dd_uv,
count(
distinct case
when is_fenfa = 1 then uid
end
) as ff_uv,
count(
distinct case
when is_succ_fenfa = 1 then uid
end
) as succ_ff_uv,
count(
distinct case
when is_curr_day_credit_submit = 1 then uid
end
) as submit_uv,
count(
distinct case
when is_curr_day_credit_succ = 1 then uid
end
) as succ_uv,
count(
distinct case
when is_first_succ = 1 then uid
end
) as first_succ_uv
from
(
select
date_add('2023-04-13', pos) as dt
from
(
select
posexplode(
split(
space(datediff('$[YYYY-MM-DD - 1D]', '2023-04-13')),
' '
)
) as (pos, val)
) temptable
) t1
join(
select
dt1,
uid,
is_baoguang,
is_dianji,
dau,
is_fenfa,
is_succ_fenfa,
is_curr_day_credit_submit,
is_curr_day_credit_succ,
is_first_succ
from
fin_ds.dwd_flow_pool_to_credit_di
where
dt between '2023-03-31'
and '$[YYYY-MM-DD - 1D]'
) t2
where
t2.dt1 between DATE_ADD(t1.dt, - DATEDIFF(t1.dt, '2022-01-03') % 7)
and t1.dt
group by
t1.dt
) t0
) t1
) t2
union all
select
'周' as tag1,
'三级渠道拆解' as tag2,
dt,
third_level_nm,
third_first_succ_uv,
third_first_succ_uv0,
third_first_succ_uv_change,
first_succ_uv,
first_succ_uv0,
first_succ_uv / first_succ_uv0 -1 as first_succ_uv_change,
nvl(
(third_first_succ_uv - third_first_succ_uv0) / first_succ_uv0,
0
) as third_first_succ_uv_attribution,
bg_uv,
bg_uv0,
(bg_uv / bg_uv0 -1) as bg_uv_change,
nvl(
(ln(bg_uv) - ln(bg_uv0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as bg_uv_attribution,
----
ck_rate,
ck_rate0,
(ck_rate / ck_rate0 -1) as ck_rate_change,
nvl(
(ln(ck_rate) - ln(ck_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as ck_rate_attribution,
----
ctr,
ctr0,
(ctr / ctr0 -1) as ctr_rate_change,
nvl(
(ln(ctr) - ln(ctr0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as ctr_attribution,
----
fenfa_rate,
fenfa_rate0,
(fenfa_rate / fenfa_rate0 -1) as fenfa_rate_change,
nvl(
(ln(fenfa_rate) - ln(fenfa_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as fenfa_rate_attribution,
----
succ_fenfa_rate,
succ_fenfa_rate0,
(succ_fenfa_rate / succ_fenfa_rate0 -1) as succ_fenfa_rate_change,
nvl(
(ln(succ_fenfa_rate) - ln(succ_fenfa_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as succ_fenfa_rate_attribution,
----
submit_rate,
submit_rate0,
(submit_rate / submit_rate0 -1) as submit_rate_change,
nvl(
(ln(submit_rate) - ln(submit_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as submit_rate_attribution,
----
succ_rate,
succ_rate0,
(succ_rate / succ_rate0 -1) as succ_rate_change,
nvl(
(ln(succ_rate) - ln(succ_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as succ_rate_attribution,
----
first_rate,
first_rate0,
(first_rate / first_rate0 -1) as first_rate_change,
nvl(
(ln(first_rate) - ln(first_rate0)) /(
ln(third_first_succ_uv) - ln(third_first_succ_uv0)
) * third_first_succ_uv_change,
0
) as first_rate_attribution
from(
select
a00.dt as dt,
third_level_nm,
third_first_succ_uv,
third_first_succ_uv0,
case
when third_first_succ_uv / third_first_succ_uv0 < 1000000 then third_first_succ_uv / third_first_succ_uv0 -1
end as third_first_succ_uv_change,
first_succ_uv,
first_succ_uv0,
first_succ_uv / first_succ_uv0 -1 as first_succ_uv_change,
bg_uv,
dj_uv / bg_uv as ck_rate,
dd_uv / dj_uv as ctr,
ff_uv / dd_uv as fenfa_rate,
succ_ff_uv / ff_uv as succ_fenfa_rate,
submit_uv / succ_ff_uv as submit_rate,
succ_uv / submit_uv as succ_rate,
third_first_succ_uv / succ_uv as first_rate,
dj_uv0 / bg_uv0 as ck_rate0,
bg_uv0,
dd_uv0 / dj_uv0 as ctr0,
ff_uv0 / dd_uv0 as fenfa_rate0,
succ_ff_uv0 / ff_uv0 as succ_fenfa_rate0,
submit_uv0 / succ_ff_uv0 as submit_rate0,
succ_uv0 / submit_uv0 as succ_rate0,
third_first_succ_uv0 / succ_uv0 as first_rate0
from
(
select
nvl(a0.dt, a1.dt + 7) as dt,
nvl(a0.third_level_nm, a1.third_level_nm) as third_level_nm,
case
when third_first_succ_uv > 0 then third_first_succ_uv
else 0.0000000000000001
end as third_first_succ_uv,
case
when third_first_succ_uv0 > 0 then third_first_succ_uv0
else 0.0000000000000001
end as third_first_succ_uv0,
case
when bg_uv > 0 then bg_uv
else 0.0000000000000001
end as bg_uv,
case
when bg_uv0 > 0 then bg_uv0
else 0.0000000000000001
end as bg_uv0,
case
when dj_uv > 0 then dj_uv
else 0.0000000000000001
end as dj_uv,
case
when dj_uv0 > 0 then dj_uv0
else 0.0000000000000001
end as dj_uv0,
case
when dd_uv > 0 then dd_uv
else 0.0000000000000001
end as dd_uv,
case
when dd_uv0 > 0 then dd_uv0
else 0.0000000000000001
end as dd_uv0,
case
when ff_uv > 0 then ff_uv
else 0.0000000000000001
end as ff_uv,
case
when ff_uv0 > 0 then ff_uv0
else 0.0000000000000001
end as ff_uv0,
case
when succ_ff_uv > 0 then succ_ff_uv
else 0.0000000000000001
end as succ_ff_uv,
case
when succ_ff_uv0 > 0 then succ_ff_uv0
else 0.0000000000000001
end as succ_ff_uv0,
case
when submit_uv > 0 then submit_uv
else 0.0000000000000001
end as submit_uv,
case
when submit_uv0 > 0 then submit_uv0
else 0.0000000000000001
end as submit_uv0,
case
when succ_uv > 0 then succ_uv
else 0.0000000000000001
end as succ_uv,
case
when succ_uv0 > 0 then succ_uv0
else 0.0000000000000001
end as succ_uv0
from
(
select
t1.dt,
third_level_nm,
count(
distinct case
when is_baoguang = 1 then uid
end
) as bg_uv,
count(
distinct case
when is_dianji = 1 then uid
end
) as dj_uv,
count(
distinct case
when dau = 1 then uid
end
) as dd_uv,
count(
distinct case
when is_fenfa = 1 then uid
end
) as ff_uv,
count(
distinct case
when is_succ_fenfa = 1 then uid
end
) as succ_ff_uv,
count(
distinct case
when is_curr_day_credit_submit = 1 then uid
end
) as submit_uv,
count(
distinct case
when is_curr_day_credit_succ = 1 then uid
end
) as succ_uv,
count(
distinct case
when is_first_succ = 1 then uid
end
) as third_first_succ_uv
from
(
select
date_add('2023-04-13', pos) as dt
from
(
select
posexplode(
split(
space(datediff('$[YYYY-MM-DD - 1D]', '2023-04-13')),
' '
)
) as (pos, val)
) temptable
) t1
join(
select
dt1,
nvl(third_level_nm, 'unknown') as third_level_nm,
uid,
is_baoguang,
is_dianji,
dau,
is_fenfa,
is_succ_fenfa,
is_curr_day_credit_submit,
is_curr_day_credit_succ,
is_first_succ
from
fin_ds.dwd_flow_pool_to_credit_di
where
dt between '2023-03-31'
and '$[YYYY-MM-DD - 1D]'
) t2
where
t2.dt1 between DATE_ADD(t1.dt, - DATEDIFF(t1.dt, '2022-01-03') % 7)
and t1.dt
group by
dt,
third_level_nm
) a0 full
join (
select
t1.dt,
third_level_nm,
count(
distinct case
when is_baoguang = 1 then uid
end
) as bg_uv0,
count(
distinct case
when is_dianji = 1 then uid
end
) as dj_uv0,
count(
distinct case
when dau = 1 then uid
end
) as dd_uv0,
count(
distinct case
when is_fenfa = 1 then uid
end
) as ff_uv0,
count(
distinct case
when is_succ_fenfa = 1 then uid
end
) as succ_ff_uv0,
count(
distinct case
when is_curr_day_credit_submit = 1 then uid
end
) as submit_uv0,
count(
distinct case
when is_curr_day_credit_succ = 1 then uid
end
) as succ_uv0,
count(
distinct case
when is_first_succ = 1 then uid
end
) as third_first_succ_uv0
from
(
select
date_add('2023-04-13', pos) as dt
from
(
select
posexplode(
split(
space(datediff('$[YYYY-MM-DD - 1D]', '2023-04-13')),
' '
)
) as (pos, val)
) temptable
) t1
join(
select
dt1,
nvl(third_level_nm, 'unknown') as third_level_nm,
uid,
is_baoguang,
is_dianji,
dau,
is_fenfa,
is_succ_fenfa,
is_curr_day_credit_submit,
is_curr_day_credit_succ,
is_first_succ
from
fin_ds.dwd_flow_pool_to_credit_di
where
dt between '2023-03-31'
and '$[YYYY-MM-DD - 1D]'
) t2
where
t2.dt1 between DATE_ADD(t1.dt, - DATEDIFF(t1.dt, '2022-01-03') % 7)
and t1.dt
group by
dt,
third_level_nm
) a1 on a0.dt = date_add(a1.dt, 7)
and a0.third_level_nm = a1.third_level_nm
) a00
left join (
select
dt,
first_succ_uv,
lag(first_succ_uv, 7) over (
order by
dt
) as first_succ_uv0
from(
select
t1.dt,
count(
distinct case
when is_first_succ = 1 then uid
end
) as first_succ_uv
from
(
select
date_add('2023-04-13', pos) as dt
from
(
select
posexplode(
split(
space(datediff('$[YYYY-MM-DD - 1D]', '2023-04-13')),
' '
)
) as (pos, val)
) temptable
) t1
join(
select
dt1,
uid,
is_first_succ
from
fin_ds.dwd_flow_pool_to_credit_di
where
dt between '2023-03-31'
and '$[YYYY-MM-DD - 1D]'
) t2
where
t2.dt1 between DATE_ADD(t1.dt, - DATEDIFF(t1.dt, '2022-01-03') % 7)
and t1.dt
group by
t1.dt
) temp
) a000 on a00.dt = a000.dt
) a;
十、信用卡周度异动拆解
--********************************************************************--
--查询hive分区表时，为避免全表扫描，需要在where条件中设置分区字段
----------------------------------
--原数易数据源日期变量改为 系统预置变量-详情请查看SQL规则。例如 concat(year,month,day)='$[YYYYMMDD - 1D]'
--示例：select * from database.table where pt='$[YYYY-MM-DD - 1D - 1D]'数据集在每次刷新时将执行 当前系统天-1天的时间
----------------------------------
--提取工具模版变量用法：如果需要在数据集中添加模版变量请设置参数‘${data}’注：设置自定义参数后数据集不可加速
--示例：select order_id,city_name from database.table where city_name='${city_name}'数据集查询时可指定城市查询
----------------------------------
--全量数据加速，每次加速提取最新数据覆盖历史数据，SQL中取需要的全部数据，例：select * from database.table where pt >='$[YYYYMMDD−15D]' and pt<='$[YYYYMMDD - 1D]' 每次加速将会取最近15天的数据，仅保留最近15天的数据
--增量数据加速，每次加速提取最新数据追加历史数据，SQL中取最新的数据，例：select * from database.table where pt ='$[YYYYMMDD - 1D]'每次加速将会取最近1天的数据追加到历史的数据中，第一次加速至今的历史数据均会保留
--********************************************************************--
-- select
-- '周' as tag,
-- case when datediff(dt, '1990-12-31') % 7 + 1=7 then 'Y' else 'N' end as sunday_tag,
-- *,
-- flow_show_uv_attribution0+ flow_show_uv_attribution1+flow_show_uv_attribution2 as flow_show_uv_attribution,
-- ctr_attribution0+ctr_attribution1+ctr_attribution2 as ctr_attribution,
-- ck_conversion_attribution0+ck_conversion_attribution1+ck_conversion_attribution2 as ck_conversion_attribution,
-- total_apply_uv_rate_attribution0+total_apply_uv_rate_attribution1+total_apply_uv_rate_attribution2 as total_apply_uv_rate_attribution,
-- first_card_pass_rate_attribution0+first_card_pass_rate_attribution1+first_card_pass_rate_attribution2 as first_card_pass_rate_attribution,
-- first_card_active_rate_attribution0+first_card_active_rate_attribution1 as first_card_active_rate_attribution
-- from
-- (select
-- *,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(flow_show_uv)-ln(flow_show_uv0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as flow_show_uv_attribution0,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(ctr)-ln(ctr0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as ctr_attribution0,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(ck_conversion)-ln(ck_conversion0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as ck_conversion_attribution0,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(total_apply_uv_rate)-ln(total_apply_uv_rate0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as total_apply_uv_rate_attribution0,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(first_card_pass_rate)-ln(first_card_pass_rate0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as first_card_pass_rate_attribution0,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(pass_income_avg)-ln(pass_income_avg0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as pass_income_attribution,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(flow_show_uv)-ln(flow_show_uv0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as flow_show_uv_attribution1,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(ctr)-ln(ctr0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as ctr_attribution1,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(ck_conversion)-ln(ck_conversion0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as ck_conversion_attribution1,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(total_apply_uv_rate)-ln(total_apply_uv_rate0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as total_apply_uv_rate_attribution1,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(first_card_pass_rate)-ln(first_card_pass_rate0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as first_card_pass_rate_attribution1,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(first_card_active_rate)-ln(first_card_active_rate0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as first_card_active_rate_attribution0,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(active_income_avg)-ln(active_income_avg0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as active_income_attribution,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(flow_show_uv)-ln(flow_show_uv0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as flow_show_uv_attribution2,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(ctr)-ln(ctr0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as ctr_attribution2,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(ck_conversion)-ln(ck_conversion0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as ck_conversion_attribution2,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(total_apply_uv_rate)-ln(total_apply_uv_rate0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as total_apply_uv_rate_attribution2,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(first_card_pass_rate)-ln(first_card_pass_rate0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as first_card_pass_rate_attribution2,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(first_card_active_rate)-ln(first_card_active_rate0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as first_card_active_rate_attribution1,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(first_card_swipe_rate)-ln(first_card_swipe_rate0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as first_card_swipe_rate_attribution,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(swipe_income_avg)-ln(swipe_income_avg0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as swipe_income_attribution
-- from
-- (select
-- a.last_dt as dt,
-- a.role_nm,
-- a.wk_stat,
-- a.flow_show_uv,
-- a.flow_click_uv,
-- a.visit_show_uv,
-- a.visit_apply_ck_uv,
-- a.total_apply_uv,
-- a.first_card_pass,
-- a.first_card_active,
-- a.first_card_swipe,
-- a.fin_income,
-- a.pass_income,
-- a.activate_income,
-- a.swipe_income,
-- a.ctr,
-- a.ck_conversion,
-- a.total_apply_uv_rate,
-- a.first_card_pass_rate,
-- a.first_card_swipe_rate,
-- a.pass_income_avg,
-- a.first_card_active_rate,
-- a.active_income_avg,
-- a.swipe_income_avg,
-- b.flow_show_uv as flow_show_uv0,
-- b.flow_click_uv as flow_click_uv0,
-- b.visit_show_uv as visit_show_uv0,
-- b.visit_apply_ck_uv as visit_apply_ck_uv0,
-- b.total_apply_uv as total_apply_uv0,
-- b.first_card_pass as first_card_pass0,
-- b.first_card_active as first_card_active0,
-- b.first_card_swipe as first_card_swipe0,
-- b.fin_income as fin_income0,
-- b.pass_income as pass_income0,
-- b.activate_income as activate_income0,
-- b.swipe_income as swipe_income0,
-- b.ctr as ctr0,
-- b.ck_conversion as ck_conversion0,
-- b.total_apply_uv_rate as total_apply_uv_rate0,
-- b.first_card_pass_rate as first_card_pass_rate0,
-- b.first_card_swipe_rate as first_card_swipe_rate0,
-- b.pass_income_avg as pass_income_avg0,
-- b.first_card_active_rate as first_card_active_rate0,
-- b.active_income_avg as active_income_avg0,
-- b.swipe_income_avg as swipe_income_avg0,
-- (a.fin_income-b.fin_income)/b.fin_income as fin_income_variation,
-- (a.overall_income-b.overall_income)/b.overall_income as overall_income_variation,
-- (a.pass_income-b.pass_income)/b.pass_income as pass_income_variation,
-- (a.activate_income-b.activate_income)/b.activate_income as activate_income_variation,
-- (a.swipe_income-b.swipe_income)/b.swipe_income as swipe_income_variation,
-- b.pass_income/b.fin_income as pass_income_contribution,
-- b.activate_income/b.fin_income as activate_income_contribution,
-- b.swipe_income/b.fin_income as swipe_income_contribution,
-- (a.overall_income-b.overall_income)/b.overall_income*(a.fin_income-b.fin_income)/(a.overall_income-b.overall_income) as driver_passenger_attribution
-- from
-- (
-- select
-- *, max(fin_income) over (partition by wk_stat) as overall_income,
-- flow_click_uv/flow_show_uv as ctr,
-- visit_apply_ck_uv/flow_click_uv as ck_conversion,
-- total_apply_uv/visit_apply_ck_uv as total_apply_uv_rate,
-- first_card_pass/total_apply_uv as first_card_pass_rate,
-- first_card_active/first_card_pass as first_card_active_rate,
-- first_card_swipe/first_card_active as first_card_swipe_rate,
-- pass_income/first_card_pass as pass_income_avg,
-- activate_income/first_card_active as active_income_avg,
-- swipe_income/first_card_swipe as swipe_income_avg
-- from
-- (select
-- role_nm,
-- DATE_ADD('2020-01-05',INT((DATEDIFF(stat_date,'2020-01-05')-1)/7)*7+1) as wk_stat,
-- sum(flow_show_uv) as flow_show_uv,
-- sum(flow_click_uv) as flow_click_uv,
-- sum(visit_show_uv) as visit_show_uv,
-- sum(visit_apply_ck_uv) as visit_apply_ck_uv,
-- sum(total_apply_uv) as total_apply_uv,
-- sum(first_card_pass) as first_card_pass,
-- sum(first_card_active) as first_card_active,
-- sum(first_card_swipe) as first_card_swipe,
-- sum(case when fin_income>0 then fin_income else 0.0000000000000001 end) as fin_income,
-- sum(case when pass_income>0 then pass_income else 0.0000000000000001 end) as pass_income,
-- sum(case when activate_income>0 then activate_income else 0.0000000000000001 end) as activate_income,
-- sum(case when swipe_income>0 then swipe_income else 0.0000000000000001 end) as swipe_income,
-- min(stat_date) as first_stat,
-- max(stat_date) as last_dt
-- from
-- fin_ds.app_credit_card_report_df
-- where
-- dt='$[YYYY-MM-DD - 1D]'
-- --and stat_date between DATE_ADD('2020-01-05',INT((DATEDIFF('$[YYYY-MM-DD - 1D]','2020-01-05')-1)/7)*7+1)
-- --and '$[YYYY-MM-DD - 1D]'
-- group by
-- role_nm,DATE_ADD('2020-01-05',INT((DATEDIFF(stat_date,'2020-01-05')-1)/7)*7+1) )
-- f1
-- )a
-- left join
-- (
-- select
-- *, max(fin_income) over (partition by wk_stat) as overall_income,
-- flow_click_uv/flow_show_uv as ctr,
-- visit_apply_ck_uv/flow_click_uv as ck_conversion,
-- total_apply_uv/visit_apply_ck_uv as total_apply_uv_rate,
-- first_card_pass/total_apply_uv as first_card_pass_rate,
-- first_card_active/first_card_pass as first_card_active_rate,
-- first_card_swipe/first_card_active as first_card_swipe_rate,
-- pass_income/first_card_pass as pass_income_avg,
-- activate_income/first_card_active as active_income_avg,
-- swipe_income/first_card_swipe as swipe_income_avg
-- from
-- (
-- select
-- role_nm, DATE_ADD('2020-01-05',INT((DATEDIFF(stat_date,'2020-01-05')-1)/7)*7+1) as wk_stat,
-- sum(flow_show_uv) as flow_show_uv,
-- sum(flow_click_uv) as flow_click_uv,
-- sum(visit_show_uv) as visit_show_uv,
-- sum(visit_apply_ck_uv) as visit_apply_ck_uv,
-- sum(total_apply_uv) as total_apply_uv,
-- sum(first_card_pass) as first_card_pass,
-- sum(first_card_active) as first_card_active,
-- sum(first_card_swipe) as first_card_swipe,
-- sum(case when fin_income>0 then fin_income else 0.0000000000000001 end) as fin_income,
-- sum(case when pass_income>0 then pass_income else 0.0000000000000001 end) as pass_income,
-- sum(case when activate_income>0 then activate_income else 0.0000000000000001 end) as activate_income,
-- sum(case when swipe_income>0 then swipe_income else 0.0000000000000001 end) as swipe_income
-- from
-- fin_ds.app_credit_card_report_df
-- where
-- dt='$[YYYY-MM-DD - 1D]'
-- and stat_date<=date_sub('$[YYYY-MM-DD - 1D]',7)
-- group by
-- role_nm,DATE_ADD('2020-01-05',INT((DATEDIFF(stat_date,'2020-01-05')-1)/7)*7+1)
-- )f2
-- )b
-- on a.role_nm=b.role_nm and a.wk_stat=date_add(b.wk_stat,7)
-- )f3
-- )f4
-- union all
-- select
-- '月' as tag,
-- case when last_day(dt)=dt then 'Y' else 'N' end as sunday_tag,
-- *,
-- flow_show_uv_attribution0+ flow_show_uv_attribution1+flow_show_uv_attribution2 as flow_show_uv_attribution,
-- ctr_attribution0+ctr_attribution1+ctr_attribution2 as ctr_attribution,
-- ck_conversion_attribution0+ck_conversion_attribution1+ck_conversion_attribution2 as ck_conversion_attribution,
-- total_apply_uv_rate_attribution0+total_apply_uv_rate_attribution1+total_apply_uv_rate_attribution2 as total_apply_uv_rate_attribution,
-- first_card_pass_rate_attribution0+first_card_pass_rate_attribution1+first_card_pass_rate_attribution2 as first_card_pass_rate_attribution,
-- first_card_active_rate_attribution0+first_card_active_rate_attribution1 as first_card_active_rate_attribution
-- from
-- (select
-- *,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(flow_show_uv)-ln(flow_show_uv0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as flow_show_uv_attribution0,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(ctr)-ln(ctr0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as ctr_attribution0,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(ck_conversion)-ln(ck_conversion0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as ck_conversion_attribution0,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(total_apply_uv_rate)-ln(total_apply_uv_rate0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as total_apply_uv_rate_attribution0,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(first_card_pass_rate)-ln(first_card_pass_rate0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as first_card_pass_rate_attribution0,
-- case when pass_income_variation=0 then 0 else
-- pass_income_variation*((ln(pass_income_avg)-ln(pass_income_avg0))/(ln(pass_income)-ln(pass_income0)))*pass_income_contribution
-- end as pass_income_attribution,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(flow_show_uv)-ln(flow_show_uv0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as flow_show_uv_attribution1,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(ctr)-ln(ctr0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as ctr_attribution1,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(ck_conversion)-ln(ck_conversion0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as ck_conversion_attribution1,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(total_apply_uv_rate)-ln(total_apply_uv_rate0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as total_apply_uv_rate_attribution1,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(first_card_pass_rate)-ln(first_card_pass_rate0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as first_card_pass_rate_attribution1,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(first_card_active_rate)-ln(first_card_active_rate0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as first_card_active_rate_attribution0,
-- case when activate_income_variation=0 then 0 else
-- activate_income_variation*((ln(active_income_avg)-ln(active_income_avg0))/(ln(activate_income)-ln(activate_income0)))*activate_income_contribution
-- end as active_income_attribution,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(flow_show_uv)-ln(flow_show_uv0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as flow_show_uv_attribution2,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(ctr)-ln(ctr0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as ctr_attribution2,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(ck_conversion)-ln(ck_conversion0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as ck_conversion_attribution2,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(total_apply_uv_rate)-ln(total_apply_uv_rate0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as total_apply_uv_rate_attribution2,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(first_card_pass_rate)-ln(first_card_pass_rate0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as first_card_pass_rate_attribution2,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(first_card_active_rate)-ln(first_card_active_rate0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as first_card_active_rate_attribution1,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(first_card_swipe_rate)-ln(first_card_swipe_rate0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as first_card_swipe_rate_attribution,
-- case when swipe_income_variation=0 then 0 else
-- swipe_income_variation*((ln(swipe_income_avg)-ln(swipe_income_avg0))/(ln(swipe_income)-ln(swipe_income0)))*swipe_income_contribution
-- end as swipe_income_attribution
-- from
-- (select
-- last_dt as dt,
-- a.role_nm,
-- a.wk_stat,
-- a.flow_show_uv,
-- a.flow_click_uv,
-- a.visit_show_uv,
-- a.visit_apply_ck_uv,
-- a.total_apply_uv,
-- a.first_card_pass,
-- a.first_card_active,
-- a.first_card_swipe,
-- a.fin_income,
-- a.pass_income,
-- a.activate_income,
-- a.swipe_income,
-- a.ctr,
-- a.ck_conversion,
-- a.total_apply_uv_rate,
-- a.first_card_pass_rate,
-- a.first_card_swipe_rate,
-- a.pass_income_avg,
-- a.first_card_active_rate,
-- a.active_income_avg,
-- a.swipe_income_avg,
-- b.flow_show_uv as flow_show_uv0,
-- b.flow_click_uv as flow_click_uv0,
-- b.visit_show_uv as visit_show_uv0,
-- b.visit_apply_ck_uv as visit_apply_ck_uv0,
-- b.total_apply_uv as total_apply_uv0,
-- b.first_card_pass as first_card_pass0,
-- b.first_card_active as first_card_active0,
-- b.first_card_swipe as first_card_swipe0,
-- b.fin_income as fin_income0,
-- b.pass_income as pass_income0,
-- b.activate_income as activate_income0,
-- b.swipe_income as swipe_income0,
-- b.ctr as ctr0,
-- b.ck_conversion as ck_conversion0,
-- b.total_apply_uv_rate as total_apply_uv_rate0,
-- b.first_card_pass_rate as first_card_pass_rate0,
-- b.first_card_swipe_rate as first_card_swipe_rate0,
-- b.pass_income_avg as pass_income_avg0,
-- b.first_card_active_rate as first_card_active_rate0,
-- b.active_income_avg as active_income_avg0,
-- b.swipe_income_avg as swipe_income_avg0,
-- (a.fin_income-b.fin_income)/b.fin_income as fin_income_variation,
-- (a.overall_income-b.overall_income)/b.overall_income as overall_income_variation,
-- (a.pass_income-b.pass_income)/b.pass_income as pass_income_variation,
-- (a.activate_income-b.activate_income)/b.activate_income as activate_income_variation,
-- (a.swipe_income-b.swipe_income)/b.swipe_income as swipe_income_variation,
-- b.pass_income/b.fin_income as pass_income_contribution,
-- b.activate_income/b.fin_income as activate_income_contribution,
-- b.swipe_income/b.fin_income as swipe_income_contribution,
-- (a.overall_income-b.overall_income)/b.overall_income*(a.fin_income-b.fin_income)/(a.overall_income-b.overall_income) as driver_passenger_attribution
-- from
-- (
-- select
-- *, max(fin_income) over (partition by wk_stat) as overall_income,
-- flow_click_uv/flow_show_uv as ctr,
-- visit_apply_ck_uv/flow_click_uv as ck_conversion,
-- total_apply_uv/visit_apply_ck_uv as total_apply_uv_rate,
-- first_card_pass/total_apply_uv as first_card_pass_rate,
-- first_card_active/first_card_pass as first_card_active_rate,
-- first_card_swipe/first_card_active as first_card_swipe_rate,
-- pass_income/first_card_pass as pass_income_avg,
-- activate_income/first_card_active as active_income_avg,
-- swipe_income/first_card_swipe as swipe_income_avg
-- from
-- (select
-- role_nm,
-- --DATE_ADD('2020-01-05',INT((DATEDIFF(stat_date,'2020-01-05')-1)/7)*7+1) as
-- concat_ws('-', substr(stat_date,1,7), '01') as wk_stat,
-- sum(flow_show_uv) as flow_show_uv,
-- sum(flow_click_uv) as flow_click_uv,
-- sum(visit_show_uv) as visit_show_uv,
-- sum(visit_apply_ck_uv) as visit_apply_ck_uv,
-- sum(total_apply_uv) as total_apply_uv,
-- sum(first_card_pass) as first_card_pass,
-- sum(first_card_active) as first_card_active,
-- sum(first_card_swipe) as first_card_swipe,
-- sum(case when fin_income>0 then fin_income else 0.0000000000000001 end) as fin_income,
-- sum(case when pass_income>0 then pass_income else 0.0000000000000001 end) as pass_income,
-- sum(case when activate_income>0 then activate_income else 0.0000000000000001 end) as activate_income,
-- sum(case when swipe_income>0 then swipe_income else 0.0000000000000001 end) as swipe_income,
-- min(stat_date) as first_stat,
-- max(stat_date) as last_dt
-- from
-- fin_ds.app_credit_card_report_df
-- where
-- dt='$[YYYY-MM-DD - 1D]'
-- --and stat_date between DATE_ADD('2020-01-05',INT((DATEDIFF('$[YYYY-MM-DD - 1D]','2020-01-05')-1)/7)*7+1)
-- --and '$[YYYY-MM-DD - 1D]'
-- group by
-- role_nm,concat_ws('-', substr(stat_date,1,7), '01'))
-- --,DATE_ADD('2020-01-05',INT((DATEDIFF(stat_date,'2020-01-05')-1)/7)*7+1) )
-- f1
-- )a
-- left join
-- (
-- select
-- *, max(fin_income) over (partition by wk_stat) as overall_income,
-- flow_click_uv/flow_show_uv as ctr,
-- visit_apply_ck_uv/flow_click_uv as ck_conversion,
-- total_apply_uv/visit_apply_ck_uv as total_apply_uv_rate,
-- first_card_pass/total_apply_uv as first_card_pass_rate,
-- first_card_active/first_card_pass as first_card_active_rate,
-- first_card_swipe/first_card_active as first_card_swipe_rate,
-- pass_income/first_card_pass as pass_income_avg,
-- activate_income/first_card_active as active_income_avg,
-- swipe_income/first_card_swipe as swipe_income_avg
-- from
-- (
-- select
-- role_nm,
-- concat_ws('-',substr(date_sub(concat_ws('-', substr(stat_date,1,7),'01'),1),1,7),'01') as wk_stat,
-- sum(flow_show_uv) as flow_show_uv,
-- sum(flow_click_uv) as flow_click_uv,
-- sum(visit_show_uv) as visit_show_uv,
-- sum(visit_apply_ck_uv) as visit_apply_ck_uv,
-- sum(total_apply_uv) as total_apply_uv,
-- sum(first_card_pass) as first_card_pass,
-- sum(first_card_active) as first_card_active,
-- sum(first_card_swipe) as first_card_swipe,
-- sum(case when fin_income>0 then fin_income else 0.0000000000000001 end) as fin_income,
-- sum(case when pass_income>0 then pass_income else 0.0000000000000001 end) as pass_income,
-- sum(case when activate_income>0 then activate_income else 0.0000000000000001 end) as activate_income,
-- sum(case when swipe_income>0 then swipe_income else 0.0000000000000001 end) as swipe_income
-- from
-- fin_ds.app_credit_card_report_df
-- where
-- dt='$[YYYY-MM-DD - 1D]'
-- and stat_date<=concat_ws('-',substr(date_sub(concat_ws('-', substr('$[YYYY-MM-DD - 1D]',1,7),'01'),1),1,7),substr('$[YYYY-MM-DD - 1D]', 9,10))
-- --date_sub('$[YYYY-MM-DD - 1D]',7)
-- group by
-- role_nm,concat_ws('-',substr(date_sub(concat_ws('-', substr(stat_date,1,7),'01'),1),1,7),'01')
-- )f2
-- )b
-- on a.role_nm=b.role_nm and substr(date_sub(a.wk_stat,1),1,7)=substr(b.wk_stat,1,7)
-- )f3
-- )f4;
select
'周' as tag,
case
when datediff(dt, '1990-12-31') % 7 + 1 = 7 then 'Y'
else 'N'
end as sunday_tag,
*,
flow_show_uv_attribution0 + flow_show_uv_attribution1 + flow_show_uv_attribution2 + flow_show_uv_attribution3 as flow_show_uv_attribution,
ctr_attribution0 + ctr_attribution1 + ctr_attribution2 + ctr_attribution3 as ctr_attribution,
ck_conversion_attribution0 + ck_conversion_attribution1 + ck_conversion_attribution2 + ck_conversion_attribution3 as ck_conversion_attribution,
total_apply_uv_rate_attribution0 + total_apply_uv_rate_attribution1 + total_apply_uv_rate_attribution2 + total_apply_uv_rate_attribution3 as total_apply_uv_rate_attribution,
first_card_pass_rate_attribution0 + first_card_pass_rate_attribution1 + first_card_pass_rate_attribution2 as first_card_pass_rate_attribution,
first_card_active_rate_attribution0 + first_card_active_rate_attribution1 as first_card_active_rate_attribution
from
(
select
*,
--------0、前端曝光uv*到达率*申请点击率*完件率*一卡审核通过率*avg批核（包含面签）单卡收入 对于 批核收入 的贡献度拆解
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(ln(flow_show_uv) - ln(flow_show_uv0)) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as flow_show_uv_attribution0,
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(ln(ctr) - ln(ctr0)) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as ctr_attribution0,
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(ln(ck_conversion) - ln(ck_conversion0)) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as ck_conversion_attribution0,
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(
ln(total_apply_uv_rate) - ln(total_apply_uv_rate0)
) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as total_apply_uv_rate_attribution0,
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(
ln(first_card_pass_rate) - ln(first_card_pass_rate0)
) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as first_card_pass_rate_attribution0,
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(ln(pass_income_avg) - ln(pass_income_avg0)) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as pass_income_attribution,
--------1、前端曝光uv*到达率*申请点击率*完件率*一卡审核通过率*一卡激活率*avg激活单卡收入 对于 激活收入 的贡献度拆解
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(ln(flow_show_uv) - ln(flow_show_uv0)) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as flow_show_uv_attribution1,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(ln(ctr) - ln(ctr0)) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as ctr_attribution1,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(ln(ck_conversion) - ln(ck_conversion0)) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as ck_conversion_attribution1,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(
ln(total_apply_uv_rate) - ln(total_apply_uv_rate0)
) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as total_apply_uv_rate_attribution1,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(
ln(first_card_pass_rate) - ln(first_card_pass_rate0)
) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as first_card_pass_rate_attribution1,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(
ln(first_card_active_rate) - ln(first_card_active_rate0)
) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as first_card_active_rate_attribution0,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(ln(active_income_avg) - ln(active_income_avg0)) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as active_income_attribution,
--------2、前端曝光uv*到达率*申请点击率*完件率*一卡审核通过率*一卡激活率*一卡首刷率*avg首刷单卡收入 对于 首刷收入 的贡献度拆解
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(ln(flow_show_uv) - ln(flow_show_uv0)) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as flow_show_uv_attribution2,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(ln(ctr) - ln(ctr0)) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as ctr_attribution2,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(ln(ck_conversion) - ln(ck_conversion0)) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as ck_conversion_attribution2,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(
ln(total_apply_uv_rate) - ln(total_apply_uv_rate0)
) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as total_apply_uv_rate_attribution2,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(
ln(first_card_pass_rate) - ln(first_card_pass_rate0)
) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as first_card_pass_rate_attribution2,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(
ln(first_card_active_rate) - ln(first_card_active_rate0)
) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as first_card_active_rate_attribution1,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(
ln(first_card_swipe_rate) - ln(first_card_swipe_rate0)
) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as first_card_swipe_rate_attribution,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(ln(swipe_income_avg) - ln(swipe_income_avg0)) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as swipe_income_attribution,
--------3、前端曝光uv*到达率*申请点击率*完件率*初审通过率*avg初审单卡收入 对于 首刷收入 的贡献度拆解
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(ln(flow_show_uv) - ln(flow_show_uv0)) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as flow_show_uv_attribution3,
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(ln(ctr) - ln(ctr0)) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as ctr_attribution3,
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(ln(ck_conversion) - ln(ck_conversion0)) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as ck_conversion_attribution3,
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(
ln(total_apply_uv_rate) - ln(total_apply_uv_rate0)
) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as total_apply_uv_rate_attribution3,
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(ln(pre_pass_rate) - ln(pre_pass_rate0)) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as pre_pass_rate_attribution,
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(
ln(pre_pass_income_avg) - ln(pre_pass_income_avg0)
) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as pre_pass_income_attribution
from
(
select
a.last_dt as dt,
a.role_nm,
a.wk_stat,
a.flow_show_uv,
a.flow_click_uv,
a.visit_show_uv,
a.visit_apply_ck_uv,
a.total_apply_uv,
a.pre_pass_uv,
a.first_card_pass,
a.first_card_active,
a.first_card_swipe,
a.fin_income,
a.pre_pass_income,
a.pass_income,
a.activate_income,
a.swipe_income,
a.ctr,
a.ck_conversion,
a.total_apply_uv_rate,
a.pre_pass_rate,
a.first_card_pass_rate,
a.first_card_active_rate,
a.first_card_swipe_rate,
a.pre_pass_income_avg,
a.pass_income_avg,
a.active_income_avg,
a.swipe_income_avg,
b.flow_show_uv as flow_show_uv0,
b.flow_click_uv as flow_click_uv0,
b.visit_show_uv as visit_show_uv0,
b.visit_apply_ck_uv as visit_apply_ck_uv0,
b.total_apply_uv as total_apply_uv0,
b.pre_pass_uv as pre_pass_uv0,
b.first_card_pass as first_card_pass0,
b.first_card_active as first_card_active0,
b.first_card_swipe as first_card_swipe0,
b.fin_income as fin_income0,
b.pre_pass_income as pre_pass_income0,
b.pass_income as pass_income0,
b.activate_income as activate_income0,
b.swipe_income as swipe_income0,
b.ctr as ctr0,
b.ck_conversion as ck_conversion0,
b.total_apply_uv_rate as total_apply_uv_rate0,
b.pre_pass_rate as pre_pass_rate0,
b.first_card_pass_rate as first_card_pass_rate0,
b.first_card_swipe_rate as first_card_swipe_rate0,
b.first_card_active_rate as first_card_active_rate0,
b.pre_pass_income_avg as pre_pass_income_avg0,
b.pass_income_avg as pass_income_avg0,
b.active_income_avg as active_income_avg0,
b.swipe_income_avg as swipe_income_avg0,
(a.fin_income - b.fin_income) / b.fin_income as fin_income_variation,
(a.overall_income - b.overall_income) / b.overall_income as overall_income_variation,
(a.pre_pass_income - b.pre_pass_income) / b.pre_pass_income as pre_pass_income_variation,
(a.pass_income - b.pass_income) / b.pass_income as pass_income_variation,
(a.activate_income - b.activate_income) / b.activate_income as activate_income_variation,
(a.swipe_income - b.swipe_income) / b.swipe_income as swipe_income_variation,
b.pre_pass_income / b.fin_income as pre_pass_income_contribution,
b.pass_income / b.fin_income as pass_income_contribution,
b.activate_income / b.fin_income as activate_income_contribution,
b.swipe_income / b.fin_income as swipe_income_contribution,
(a.overall_income - b.overall_income) / b.overall_income *(a.fin_income - b.fin_income) /(a.overall_income - b.overall_income) as driver_passenger_attribution
from
(
select
*,
max(fin_income) over (partition by wk_stat) as overall_income,
flow_click_uv / flow_show_uv as ctr,
visit_apply_ck_uv / flow_click_uv as ck_conversion,
total_apply_uv / visit_apply_ck_uv as total_apply_uv_rate,
pre_pass_uv / total_apply_uv as pre_pass_rate,
first_card_pass / total_apply_uv as first_card_pass_rate,
first_card_active / first_card_pass as first_card_active_rate,
first_card_swipe / first_card_active as first_card_swipe_rate,
pre_pass_income / pre_pass_uv as pre_pass_income_avg,
pass_income / first_card_pass as pass_income_avg,
activate_income / first_card_active as active_income_avg,
swipe_income / first_card_swipe as swipe_income_avg
from
(
select
role_nm,
min(stat_date) as first_stat,
max(stat_date) as last_dt,
DATE_ADD(
'2020-01-05',
INT((DATEDIFF(stat_date, '2020-01-05') -1) / 7) * 7 + 1
) as wk_stat,
sum(flow_show_uv) as flow_show_uv,
sum(flow_click_uv) as flow_click_uv,
sum(visit_show_uv) as visit_show_uv,
sum(visit_apply_ck_uv) as visit_apply_ck_uv,
sum(total_apply_uv) as total_apply_uv,
sum(first_card_pass) as first_card_pass,
sum(first_card_active) as first_card_active,
sum(first_card_swipe) as first_card_swipe,
sum(
case
when fin_income > 0 then fin_income
else 0.0000000000000001
end
) as fin_income,
sum(
case
when pass_income > 0 then pass_income
else 0.0000000000000001
end
) as pass_income,
sum(
case
when activate_income > 0 then activate_income
else 0.0000000000000001
end
) as activate_income,
sum(
case
when swipe_income > 0 then swipe_income
else 0.0000000000000001
end
) as swipe_income
from
fin_ds.app_credit_card_report_df
where
dt = '$[YYYY-MM-DD - 1D]'
group by
role_nm,
DATE_ADD(
'2020-01-05',
INT((DATEDIFF(stat_date, '2020-01-05') -1) / 7) * 7 + 1
)
) temp1
left join (
select
role_id,
DATE_ADD(
'2020-01-05',
INT((DATEDIFF(stat_date, '2020-01-05') -1) / 7) * 7 + 1
) as wk_stat2,
sum(pre_pass_uv) as pre_pass_uv,
sum(
case
when pre_pass_income > 0 then pre_pass_income
else 0.0000000000000001
end
) as pre_pass_income
from
fin_ds.app_cd_income_dfv2
where
dt = '$[YYYY-MM-DD - 1D]'
and bank_code = '汇总'
and bank_name = '汇总'
and long_short_type = '汇总'
and flow_type_name = '汇总'
and third_level_name = '汇总'
group by
role_id,
DATE_ADD(
'2020-01-05',
INT((DATEDIFF(stat_date, '2020-01-05') -1) / 7) * 7 + 1
)
) temp2 on temp1.role_nm = temp2.role_id
and temp1.wk_stat = temp2.wk_stat2
) a
left join (
select
*,
max(fin_income) over (partition by wk_stat) as overall_income,
flow_click_uv / flow_show_uv as ctr,
visit_apply_ck_uv / flow_click_uv as ck_conversion,
total_apply_uv / visit_apply_ck_uv as total_apply_uv_rate,
pre_pass_uv / total_apply_uv as pre_pass_rate,
first_card_pass / total_apply_uv as first_card_pass_rate,
first_card_active / first_card_pass as first_card_active_rate,
first_card_swipe / first_card_active as first_card_swipe_rate,
pre_pass_income / pre_pass_uv as pre_pass_income_avg,
pass_income / first_card_pass as pass_income_avg,
activate_income / first_card_active as active_income_avg,
swipe_income / first_card_swipe as swipe_income_avg
from
(
select
role_nm,
DATE_ADD(
'2020-01-05',
INT((DATEDIFF(stat_date, '2020-01-05') -1) / 7) * 7 + 1
) as wk_stat,
sum(flow_show_uv) as flow_show_uv,
sum(flow_click_uv) as flow_click_uv,
sum(visit_show_uv) as visit_show_uv,
sum(visit_apply_ck_uv) as visit_apply_ck_uv,
sum(total_apply_uv) as total_apply_uv,
sum(first_card_pass) as first_card_pass,
sum(first_card_active) as first_card_active,
sum(first_card_swipe) as first_card_swipe,
sum(
case
when fin_income > 0 then fin_income
else 0.0000000000000001
end
) as fin_income,
sum(
case
when pass_income > 0 then pass_income
else 0.0000000000000001
end
) as pass_income,
sum(
case
when activate_income > 0 then activate_income
else 0.0000000000000001
end
) as activate_income,
sum(
case
when swipe_income > 0 then swipe_income
else 0.0000000000000001
end
) as swipe_income,
min(stat_date) as first_stat,
max(stat_date) as last_dt
from
fin_ds.app_credit_card_report_df
where
dt = '$[YYYY-MM-DD - 1D]'
group by
role_nm,
DATE_ADD(
'2020-01-05',
INT((DATEDIFF(stat_date, '2020-01-05') -1) / 7) * 7 + 1
)
) temp3
left join (
select
role_id,
DATE_ADD(
'2020-01-05',
INT((DATEDIFF(stat_date, '2020-01-05') -1) / 7) * 7 + 1
) as wk_stat2,
sum(pre_pass_uv) as pre_pass_uv,
sum(
case
when pre_pass_income > 0 then pre_pass_income
else 0.0000000000000001
end
) as pre_pass_income
from
fin_ds.app_cd_income_dfv2
where
dt = '$[YYYY-MM-DD - 1D]'
and bank_code = '汇总'
and bank_name = '汇总'
and long_short_type = '汇总'
and flow_type_name = '汇总'
and third_level_name = '汇总'
group by
role_id,
DATE_ADD(
'2020-01-05',
INT((DATEDIFF(stat_date, '2020-01-05') -1) / 7) * 7 + 1
)
) temp4 on temp3.role_nm = temp4.role_id
and temp3.wk_stat = temp4.wk_stat2
) b on a.role_nm = b.role_nm
and a.wk_stat = date_add(b.wk_stat, 7)
) f3
) f4
union all
select
'月' as tag,
case
when last_day(dt) = dt then 'Y'
else 'N'
end as sunday_tag,
*,
flow_show_uv_attribution0 + flow_show_uv_attribution1 + flow_show_uv_attribution2 + flow_show_uv_attribution3 as flow_show_uv_attribution,
ctr_attribution0 + ctr_attribution1 + ctr_attribution2 + ctr_attribution3 as ctr_attribution,
ck_conversion_attribution0 + ck_conversion_attribution1 + ck_conversion_attribution2 + ck_conversion_attribution3 as ck_conversion_attribution,
total_apply_uv_rate_attribution0 + total_apply_uv_rate_attribution1 + total_apply_uv_rate_attribution2 + total_apply_uv_rate_attribution3 as total_apply_uv_rate_attribution,
first_card_pass_rate_attribution0 + first_card_pass_rate_attribution1 + first_card_pass_rate_attribution2 as first_card_pass_rate_attribution,
first_card_active_rate_attribution0 + first_card_active_rate_attribution1 as first_card_active_rate_attribution
from
(
select
*,
--------0、前端曝光uv*到达率*申请点击率*完件率*一卡审核通过率*avg批核（包含面签）单卡收入 对于 批核收入 的贡献度拆解
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(ln(flow_show_uv) - ln(flow_show_uv0)) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as flow_show_uv_attribution0,
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(ln(ctr) - ln(ctr0)) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as ctr_attribution0,
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(ln(ck_conversion) - ln(ck_conversion0)) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as ck_conversion_attribution0,
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(
ln(total_apply_uv_rate) - ln(total_apply_uv_rate0)
) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as total_apply_uv_rate_attribution0,
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(
ln(first_card_pass_rate) - ln(first_card_pass_rate0)
) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as first_card_pass_rate_attribution0,
case
when pass_income_variation = 0 then 0
else pass_income_variation *(
(ln(pass_income_avg) - ln(pass_income_avg0)) /(ln(pass_income) - ln(pass_income0))
) * pass_income_contribution
end as pass_income_attribution,
--------1、前端曝光uv*到达率*申请点击率*完件率*一卡审核通过率*一卡激活率*avg激活单卡收入 对于 激活收入 的贡献度拆解
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(ln(flow_show_uv) - ln(flow_show_uv0)) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as flow_show_uv_attribution1,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(ln(ctr) - ln(ctr0)) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as ctr_attribution1,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(ln(ck_conversion) - ln(ck_conversion0)) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as ck_conversion_attribution1,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(
ln(total_apply_uv_rate) - ln(total_apply_uv_rate0)
) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as total_apply_uv_rate_attribution1,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(
ln(first_card_pass_rate) - ln(first_card_pass_rate0)
) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as first_card_pass_rate_attribution1,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(
ln(first_card_active_rate) - ln(first_card_active_rate0)
) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as first_card_active_rate_attribution0,
case
when activate_income_variation = 0 then 0
else activate_income_variation *(
(ln(active_income_avg) - ln(active_income_avg0)) /(ln(activate_income) - ln(activate_income0))
) * activate_income_contribution
end as active_income_attribution,
--------2、前端曝光uv*到达率*申请点击率*完件率*一卡审核通过率*一卡激活率*一卡首刷率*avg首刷单卡收入 对于 首刷收入 的贡献度拆解
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(ln(flow_show_uv) - ln(flow_show_uv0)) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as flow_show_uv_attribution2,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(ln(ctr) - ln(ctr0)) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as ctr_attribution2,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(ln(ck_conversion) - ln(ck_conversion0)) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as ck_conversion_attribution2,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(
ln(total_apply_uv_rate) - ln(total_apply_uv_rate0)
) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as total_apply_uv_rate_attribution2,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(
ln(first_card_pass_rate) - ln(first_card_pass_rate0)
) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as first_card_pass_rate_attribution2,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(
ln(first_card_active_rate) - ln(first_card_active_rate0)
) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as first_card_active_rate_attribution1,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(
ln(first_card_swipe_rate) - ln(first_card_swipe_rate0)
) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as first_card_swipe_rate_attribution,
case
when swipe_income_variation = 0 then 0
else swipe_income_variation *(
(ln(swipe_income_avg) - ln(swipe_income_avg0)) /(ln(swipe_income) - ln(swipe_income0))
) * swipe_income_contribution
end as swipe_income_attribution,
--------3、前端曝光uv*到达率*申请点击率*完件率*初审通过率*avg初审单卡收入 对于 首刷收入 的贡献度拆解
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(ln(flow_show_uv) - ln(flow_show_uv0)) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as flow_show_uv_attribution3,
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(ln(ctr) - ln(ctr0)) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as ctr_attribution3,
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(ln(ck_conversion) - ln(ck_conversion0)) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as ck_conversion_attribution3,
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(
ln(total_apply_uv_rate) - ln(total_apply_uv_rate0)
) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as total_apply_uv_rate_attribution3,
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(ln(pre_pass_rate) - ln(pre_pass_rate0)) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as pre_pass_rate_attribution,
case
when pre_pass_income_variation = 0 then 0
else pre_pass_income_variation *(
(
ln(pre_pass_income_avg) - ln(pre_pass_income_avg0)
) /(ln(pre_pass_income) - ln(pre_pass_income0))
) * pre_pass_income_contribution
end as pre_pass_income_attribution
from
(
select
a.last_dt as dt,
a.role_nm,
a.wk_stat,
a.flow_show_uv,
a.flow_click_uv,
a.visit_show_uv,
a.visit_apply_ck_uv,
a.total_apply_uv,
a.pre_pass_uv,
a.first_card_pass,
a.first_card_active,
a.first_card_swipe,
a.fin_income,
a.pre_pass_income,
a.pass_income,
a.activate_income,
a.swipe_income,
a.ctr,
a.ck_conversion,
a.total_apply_uv_rate,
a.pre_pass_rate,
a.first_card_pass_rate,
a.first_card_active_rate,
a.first_card_swipe_rate,
a.pre_pass_income_avg,
a.pass_income_avg,
a.active_income_avg,
a.swipe_income_avg,
b.flow_show_uv as flow_show_uv0,
b.flow_click_uv as flow_click_uv0,
b.visit_show_uv as visit_show_uv0,
b.visit_apply_ck_uv as visit_apply_ck_uv0,
b.total_apply_uv as total_apply_uv0,
b.pre_pass_uv as pre_pass_uv0,
b.first_card_pass as first_card_pass0,
b.first_card_active as first_card_active0,
b.first_card_swipe as first_card_swipe0,
b.fin_income as fin_income0,
b.pre_pass_income as pre_pass_income0,
b.pass_income as pass_income0,
b.activate_income as activate_income0,
b.swipe_income as swipe_income0,
b.ctr as ctr0,
b.ck_conversion as ck_conversion0,
b.total_apply_uv_rate as total_apply_uv_rate0,
b.pre_pass_rate as pre_pass_rate0,
b.first_card_pass_rate as first_card_pass_rate0,
b.first_card_swipe_rate as first_card_swipe_rate0,
b.first_card_active_rate as first_card_active_rate0,
b.pre_pass_income_avg as pre_pass_income_avg0,
b.pass_income_avg as pass_income_avg0,
b.active_income_avg as active_income_avg0,
b.swipe_income_avg as swipe_income_avg0,
(a.fin_income - b.fin_income) / b.fin_income as fin_income_variation,
(a.overall_income - b.overall_income) / b.overall_income as overall_income_variation,
(a.pre_pass_income - b.pre_pass_income) / b.pre_pass_income as pre_pass_income_variation,
(a.pass_income - b.pass_income) / b.pass_income as pass_income_variation,
(a.activate_income - b.activate_income) / b.activate_income as activate_income_variation,
(a.swipe_income - b.swipe_income) / b.swipe_income as swipe_income_variation,
b.pre_pass_income / b.fin_income as pre_pass_income_contribution,
b.pass_income / b.fin_income as pass_income_contribution,
b.activate_income / b.fin_income as activate_income_contribution,
b.swipe_income / b.fin_income as swipe_income_contribution,
(a.overall_income - b.overall_income) / b.overall_income *(a.fin_income - b.fin_income) /(a.overall_income - b.overall_income) as driver_passenger_attribution
from
(
select
*,
max(fin_income) over (partition by wk_stat) as overall_income,
flow_click_uv / flow_show_uv as ctr,
visit_apply_ck_uv / flow_click_uv as ck_conversion,
total_apply_uv / visit_apply_ck_uv as total_apply_uv_rate,
pre_pass_uv / total_apply_uv as pre_pass_rate,
first_card_pass / total_apply_uv as first_card_pass_rate,
first_card_active / first_card_pass as first_card_active_rate,
first_card_swipe / first_card_active as first_card_swipe_rate,
pre_pass_income / pre_pass_uv as pre_pass_income_avg,
pass_income / first_card_pass as pass_income_avg,
activate_income / first_card_active as active_income_avg,
swipe_income / first_card_swipe as swipe_income_avg
from
(
select
role_nm,
min(stat_date) as first_stat,
max(stat_date) as last_dt,
concat_ws('-', substr(stat_date, 1, 7), '01') as wk_stat,
sum(flow_show_uv) as flow_show_uv,
sum(flow_click_uv) as flow_click_uv,
sum(visit_show_uv) as visit_show_uv,
sum(visit_apply_ck_uv) as visit_apply_ck_uv,
sum(total_apply_uv) as total_apply_uv,
sum(first_card_pass) as first_card_pass,
sum(first_card_active) as first_card_active,
sum(first_card_swipe) as first_card_swipe,
sum(
case
when fin_income > 0 then fin_income
else 0.0000000000000001
end
) as fin_income,
sum(
case
when pass_income > 0 then pass_income
else 0.0000000000000001
end
) as pass_income,
sum(
case
when activate_income > 0 then activate_income
else 0.0000000000000001
end
) as activate_income,
sum(
case
when swipe_income > 0 then swipe_income
else 0.0000000000000001
end
) as swipe_income
from
fin_ds.app_credit_card_report_df
where
dt = '$[YYYY-MM-DD - 1D]'
group by
role_nm,
concat_ws('-', substr(stat_date, 1, 7), '01')
) temp1
left join (
select
role_id,
concat_ws('-', substr(stat_date, 1, 7), '01') as wk_stat2,
sum(pre_pass_uv) as pre_pass_uv,
sum(
case
when pre_pass_income > 0 then pre_pass_income
else 0.0000000000000001
end
) as pre_pass_income
from
fin_ds.app_cd_income_dfv2
where
dt = '$[YYYY-MM-DD - 1D]'
and bank_code = '汇总'
and bank_name = '汇总'
and long_short_type = '汇总'
and flow_type_name = '汇总'
and third_level_name = '汇总'
group by
role_id,
concat_ws('-', substr(stat_date, 1, 7), '01')
) temp2 on temp1.role_nm = temp2.role_id
and temp1.wk_stat = temp2.wk_stat2
) a
left join (
select
*,
max(fin_income) over (partition by wk_stat) as overall_income,
flow_click_uv / flow_show_uv as ctr,
visit_apply_ck_uv / flow_click_uv as ck_conversion,
total_apply_uv / visit_apply_ck_uv as total_apply_uv_rate,
pre_pass_uv / total_apply_uv as pre_pass_rate,
first_card_pass / total_apply_uv as first_card_pass_rate,
first_card_active / first_card_pass as first_card_active_rate,
first_card_swipe / first_card_active as first_card_swipe_rate,
pre_pass_income / pre_pass_uv as pre_pass_income_avg,
pass_income / first_card_pass as pass_income_avg,
activate_income / first_card_active as active_income_avg,
swipe_income / first_card_swipe as swipe_income_avg
from
(
select
role_nm,
concat_ws(
'-',
substr(
date_sub(concat_ws('-', substr(stat_date, 1, 7), '01'), 1),
1,
7
),
'01'
) as wk_stat,
sum(flow_show_uv) as flow_show_uv,
sum(flow_click_uv) as flow_click_uv,
sum(visit_show_uv) as visit_show_uv,
sum(visit_apply_ck_uv) as visit_apply_ck_uv,
sum(total_apply_uv) as total_apply_uv,
sum(first_card_pass) as first_card_pass,
sum(first_card_active) as first_card_active,
sum(first_card_swipe) as first_card_swipe,
sum(
case
when fin_income > 0 then fin_income
else 0.0000000000000001
end
) as fin_income,
sum(
case
when pass_income > 0 then pass_income
else 0.0000000000000001
end
) as pass_income,
sum(
case
when activate_income > 0 then activate_income
else 0.0000000000000001
end
) as activate_income,
sum(
case
when swipe_income > 0 then swipe_income
else 0.0000000000000001
end
) as swipe_income,
min(stat_date) as first_stat,
max(stat_date) as last_dt
from
fin_ds.app_credit_card_report_df
where
dt = '$[YYYY-MM-DD - 1D]'
and stat_date <= concat_ws(
'-',
substr(
date_sub(
concat_ws('-', substr('$[YYYY-MM-DD - 1D]', 1, 7), '01'),
1
),
1,
7
),
substr('$[YYYY-MM-DD - 1D]', 9, 10)
) --date_sub('$[YYYY-MM-DD - 1D]',7)
group by
role_nm,
concat_ws(
'-',
substr(
date_sub(concat_ws('-', substr(stat_date, 1, 7), '01'), 1),
1,
7
),
'01'
)
) temp3
left join (
select
role_id,
concat_ws(
'-',
substr(
date_sub(concat_ws('-', substr(stat_date, 1, 7), '01'), 1),
1,
7
),
'01'
) as wk_stat2,
sum(pre_pass_uv) as pre_pass_uv,
sum(
case
when pre_pass_income > 0 then pre_pass_income
else 0.0000000000000001
end
) as pre_pass_income
from
fin_ds.app_cd_income_dfv2
where
dt = '$[YYYY-MM-DD - 1D]'
and stat_date <= concat_ws(
'-',
substr(
date_sub(
concat_ws('-', substr('$[YYYY-MM-DD - 1D]', 1, 7), '01'),
1
),
1,
7
),
substr('$[YYYY-MM-DD - 1D]', 9, 10)
)
and bank_code = '汇总'
and bank_name = '汇总'
and long_short_type = '汇总'
and flow_type_name = '汇总'
and third_level_name = '汇总'
group by
role_id,
concat_ws(
'-',
substr(
date_sub(concat_ws('-', substr(stat_date, 1, 7), '01'), 1),
1,
7
),
'01'
)
) temp4 on temp3.role_nm = temp4.role_id
and temp3.wk_stat = temp4.wk_stat2
) b on a.role_nm = b.role_nm
and substr(date_sub(a.wk_stat,1),1,7)=substr(b.wk_stat,1,7)
) f3
) f4;
十一、落地页uid粒度
select
a.stat_date as dt,
a.uid as uid,
nvl(a.bank_name0, 'unknown') as bank_name0,
nvl(channel_name, 'unknown') as channel_name,
nvl(a.channel_id, 'unknown') as channel_id,
nvl(a.role_id, 'unknown') as role_id,
nvl(city_name, 'unknown') as city_name,
nvl(pro_name, 'unknown') as pro_name,
nvl(city_level, 'unknown') as city_level,
nvl(page_name, 'unknown') as page_name,
nvl(first_level_nm, 'unknown') as first_level_nm,
nvl(second_level_nm, 'unknown') as second_level_nm,
nvl(third_level_nm, 'unknown') as third_level_nm,
nvl(instance_id, 'unkown') as instance_id,
nvl(apply_page, 'unkown') as apply_page,
nvl(referrer_uid, 'unkown') as referrer_uid,
case
when instance_id is null then 'N'
else 'Y'
end as driver_referral,
case
when first_visit_date = a.stat_date then '首次访问'
else '非首次'
end as tag,
first_apply,
first_visit_date,
c.order_no,
--,total_apply_ck_times first_apply_ck_date,
case
when is_sw = 1 then a.uid
end as is_sw,
case
when is_ck = 1 then a.uid
end as is_ck,
case
when is_apply_ck = 1 then a.uid
end as is_apply_ck,
case
when is_apply = 1
and (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then c.uid0
end as is_apply,
case
when is_apply = 1
and (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
)
and order_status = '审核更新' then c.uid0
end as order_update,
case
when is_first_card = 1
and is_apply = 1
and (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then c.uid0
end as is_apply_first,
case
when is_pre_pass = 1
and (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then c.uid0
end as is_pre_pass,
case
when is_short_pre_exam = 1
and (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then c.uid0
end as is_short_pre_exam,
case
when is_pass = 1
and (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then c.uid0
end as is_pass,
case
when is_first_card = 1
and is_pass = 1
and (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then c.uid0
end as is_pass_first,
case
when is_first_card = 1
and is_activate = 1
and is_pass = 1
and (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then c.uid0
end as is_activate_first,
case
when is_first_card = 1
and is_swipe = 1
and is_pass = 1
and (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then c.uid0
end as is_swipe_first,
case
when is_apply = 1
and is_pass = 0
and order_status not in (33, 99)
and (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then c.uid0
end as is_on_the_way,
case
when (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then prepass_income
end as prepass_income,
case
when (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then fin_income
end as fin_income,
case
when (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then income
end as income,
case
when (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then pass_income
end as pass_income,
case
when (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then activate_income
end as activate_income,
case
when (
datediff(c.dt, a.stat_date) between 0
and 3
or c.dt is null
) then swipe_income
end as swipe_income
from
(
select
a0.*,
referrer_uid
from
(
select
stat_date,
uid,
bank_name0,
channel_name,
channel_id,
role_id,
city_name,
pro_name,
city_level,
page_name,
first_level_nm,
second_level_nm,
third_level_nm,
instance_id,
case
when bank_name0 = 'GFYH' and stat_date>='2023-06-22'
AND (
apply_page like '%long%'
or apply_page like '%长表%'
) then '广发长表'
when bank_name0 = 'GFYH' and stat_date>='2023-06-22'
AND (
apply_page like '%short%'
or apply_page like '%短表%'
) then '广发短表'
when bank_name0 = 'GFYH'
then '广发长表'
else bank_name0
end as connect_bank,
apply_page,
is_sw,
is_ck,
is_apply_ck
from
fin_ds.dwd_middle_flow_user_cd_dfv2
where
dt = '$[YYYY-MM-DD - 1D]'
and stat_date >= '2023-04-26'
) a0
left join (
select
instance_id,
referrer_uid
from
bizdata.dwd_trip_mkt_virus_referrer_di
where
dt >= '2023-01-01'
group by
instance_id,
referrer_uid
) a1 on a0.instance_id = a1.instance_id
) a
left join(
select
dt,
uid,
total_visit_day,
first_visit_date --,total_apply_ck_times, first_apply_ck_date
from
fin_ds.dim_cd_user_flow_df
where
dt = '$[YYYY-MM-DD - 1D]'
) b on a.uid = b.uid --and a.stat_date = b.dt
left join (
----信用卡信息
select
*,case
when is_apply = 1
and dt = first_apply_time then '首次提交进件'
else '非首次'
end as first_apply
from
(
select
to_date(apply_time) as dt,
min(to_date(apply_time)) over(partition by uid) as first_apply_time,
uid as uid0,
case
when order_status in (12, 13, 30, 33, 35, 40, 50, 98, 99) then '审核更新'
else '未更新'
end as order_status,
case
when delivery_channel_id is null
or delivery_channel_id = '' then 'unknown'
else delivery_channel_id
end as delivery_channel_id,
bank_name,
is_apply,
is_pre_pass,
is_short_pre_exam,
is_pass,
is_activate,
bank_code,
is_swipe,
is_first_card,
order_no,
long_short_type,
case
when bank_code = 'GFYH'
AND long_short_type = 2 then '广发长表'
when bank_code = 'GFYH'
and long_short_type = 1 then '广发短表'
when bank_code = 'GFYH' then '广发长表'
else bank_code
end as connect_bank
from
manhattan_dw.dwd_cd_trd_credit_card_order_df
where
dt = '$[YYYY-MM-DD - 1D]'
and is_apply = 1
and uid > 0
) temp
) c on a.channel_id = c.delivery_channel_id
and a.uid = c.uid0
and a.connect_bank = c.connect_bank --and a.apply_page = c.long_short_type
left join (
select
uid,
order_no,
delivery_channel_id,
sum(charge_node_income) as income,
sum(
case
when charge_node_type = '预审' then charge_node_income
else 0
end
) as prepass_income,
sum(
case
when charge_node_type = '面签' then charge_node_income
else 0
end
) as fin_income,
sum(
case
when charge_node_type = '批核' then charge_node_income
else 0
end
) as pass_income,
sum(
case
when charge_node_type = '激活' then charge_node_income
else 0
end
) as activate_income,
sum(
case
when charge_node_type = '首刷' then charge_node_income
else 0
end
) as swipe_income
from
ddpay.dwd_cd_fin_order_node_income_df
where
dt = '$[YYYY-MM-DD - 1D]'
and to_date(charge_node_time) >= '2023-01-01'
group by
uid,
order_no,
delivery_channel_id
) c0 on a.uid = c0.uid
and c.order_no = c0.order_no
and a.channel_id = c0.delivery_channel_id;
十二、pyspark从数据库取数
import os
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import prestodb
from prestodb import dbapi
import os
## import pyspark.sql.functions as f
import pandas as pd
import numpy as np
import time
from datetime import date,timedelta
import datetime
from pyhive import hive
import seaborn as sns
import matplotlib.pyplot as plt
#创建连接 Create connection by your hadoop user
#默认使用了你的账号，不用修改。
conn=prestodb.dbapi.connect(
host=os.environ['PRESTO_HOST'],
port=os.environ['PRESTO_PORT'],
user=os.environ['JUPYTER_HADOOP_USER'],
password=os.environ['HADOOP_USER_PASSWORD'],
catalog='hive'
)
sql='''
select
passenger_id,
discount_amt/total_fee as dis_amt_percent,
discount_ord/ord_cnt as ord_percent,
discount_amt
from fin_ds_dev.user_dim_price_table1
where order_area='汇总'
and product_line_name='汇总'
'''
cur = conn.cursor()
cur.execute(sql)
query_result = cur.fetchall()
#Convert to Pandas
colnames = [part[0] for part in cur.description]
df= pd.DataFrame(query_result, columns=colnames)
df.head()
sqltest = '''
select
passenger_id,
cast(discount_amt/total_fee as double) as dis_amt_percent,
cast(discount_ord/ord_cnt as double) as ord_percent,
cast(discount_amt as double) discount_amt
from fin_ds_dev.user_dim_price_table1
where order_area='汇总'
and product_line_name='汇总'
'''
dftest = spark.sql(sqltest)
dftest.show()
# dftest.saveastable('schema.table')
spark
spark.sparkContext.addPyFile("hdfs://DClusterNmg2/user/prod_fin_ds/fin_ds_dev/hdp/notebook_joeydujian/jieba.tgz")
import jieba
import pyspark.sql.functions as f
import pandas as pd
import numpy as np
import time
from datetime import date,timedelta
print("finish-"+time.strftime("%Y/%m/%d,%H:%M:%S",time.localtime()))
sqlinput = '''
select product_name,sub_product_name,path_name
from ese_dw.dwd_worksheet_detail_wild_d -- 千帆工单宽表
where concat_ws('-',year,month,day)>='2021-06-01'
and product_name not like '%金融%'
group by 1,2,3
'''
dfinput = spark.sql(sqlinput)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
