|
原帖由 askgyliu 于 2007-12-14 22:49 发表
麻 烦 LZ 再 把 下 面 这 个 给 EXPLAIN 一 下 , 可 以 吗 ?
select coalesce(a1.group_id,a2.group_id,a3.group_id) as group_id,
coalesce(a1.pre_m_arpu_d,a2.pre_m_arpu_d,a3.pre_m_arpu_d),
EXIST_CUST_C,ADD_CUST_C,LOSS_CUST_C, a1.arpu,a1.m,a1.clv, a2.arpu,
a2.m,a2.clv, a3.arpu,a3.m,a3.clv, a1.EXIST_SUM_NOW_ARPU,
a2.ADD_SUM_NOW_ARPU,a3.LOSS_SUM_NOW_ARPU
from (
select b.cust_group_id as group_id, aa.pre_m_arpu_d,sum(aa.PRE_M_ARPU)
as arpu,sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as
EXIST_CUST_C, sum(aa.NOW_ARPU) as EXIST_SUM_NOW_ARPU
from session.V_PRE_EXP_GROUP_CUST1 a
inner join CUST_CCP_INFO_200710 cc on
cc.cust_id=a.cust_id
inner join CUST_CHURN_INFO_200710 aa on
aa.cust_id=cc.cust_id
inner join PAR_CUST_CUST_GRP_ASSOC_200710 b on
b.cust_id = aa.cust_id
group by b.cust_group_id,aa.pre_m_arpu_d ) a1 full outer join
(select b.cust_group_id as group_id,sum(cust.PRE_M_ARPU) as arpu,
sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as
ADD_CUST_C, cust.pre_m_arpu_d, sum(cust.NOW_ARPU) as
ADD_SUM_NOW_ARPU
from PAR_CUST_CUST_GRP_ASSOC_200710 b inner join
(select base.cust_id,base.pre_m_arpu_d,base.NOW_ARPU,base.PRE_M_ARPU
from CUST_CHURN_INFO_200710 base
where on_net_time=4) cust on cust.cust_id=b.cust_id inner join
CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id
group by b.cust_group_id,cust.pre_m_arpu_d ) a2 on
a1.group_id=a2.group_id and a1.pre_m_arpu_d= a2.pre_m_arpu_d full
outer join (
select a.cust_group_id as group_id,sum(churn.PRE_M_ARPU) as arpu,
sum(cc.m) as m,sum(cc.CLV) as clv, count(a.cust_id) as
LOSS_CUST_C, churn.pre_m_arpu_d, sum(churn.NOW_ARPU) as
LOSS_SUM_NOW_ARPU
from PAR_CUST_CUST_GRP_ASSOC_200710 a inner join
(select b.cust_id as cust_id,b.pre_m_arpu_d,b.PRE_M_ARPU,b.NOW_ARPU
from CUST_CHURN_INFO_200710 b
where b.LEAVE_DATE |
|