new_user_cnt
from
DW_CHNL_USER_200708
group by
channel_id,
city_id,
county_id,
parent_channel_id,
selfsite_id,
brand_id,
plan_id,
region_type,
chnl_type1,
chnl_type2,
chnl_type3,
exclusive,
sale_type,
online_flag
) e,
(select
a.channel_id,
count(distinct a.user_id) as renew_user_cnt
from
DW_CHNL_USER_200708 a,
DMRN_USER_MS b
where
a.user_id = bigint(b.rn_user_id)
and b.rn_date = ‘2007-08-01’
and a.month_new_mark >0
group by
a.channel_id
) d
where
e.channel_id = d.channel_id
group by
e.channel_id,
e.city_id,
e.county_id,
e.parent_channel_id,
e.selfsite_id,
e.brand_id,
e.plan_id,
e.region_type,
e.chnl_type1,
e.chnl_type2,
e.chnl_type3,
e.exclusive,
e.sale_type,
e.online_flag
INSERT INTO tmp_res_imei (
user_id,
product_no,
imei,
city_id,
county_id,
first_use_date,
last_use_date,
imei_seg )
Select
a.user_id,
a.product_no,
a.imei,
a.city_id,
a.county_id,
a.first_use_date,
a.last_use_date,
max(b.imei) as imei_seg
from tmp_imei_cdr a
left join dim_res_imei b on substr(a.imei,1,6)=b.imei or substr(a.imei,1,8)=b.imei
group by
a.user_id,
a.product_no,
a.imei,
a.city_id,
a.county_id,
a.first_use_date,
a.last_use_date ;
原sql的db2expln结果：
Estimated Cost = 18038278.000000
Estimated Cardinality = 306464.000000
Optimizer Plan:
INSERT
( 2)
/ \
DTQ Table:
( 3) DW
| TMP_RES_IMEI
GRPBY
( 4)
|
LMTQ
( 5)
|
GRPBY
( 6)
|
NLJOIN
( 7)
/ \
TBSCAN TBSCAN
( 8) ( 11)
| |
SORT TEMP
( 9) ( 12)
| |
TBSCAN BTQ
( 10) ( 13)
| |
Table: LTQ
DW ( 14)
TMP_IMEI_CDR |
TBSCAN
( 15)
|
Table:
DW
DIM_RES_IMEI
优化后sql代码段:
INSERT INTO tmp_res_imei (
user_id,
product_no,
imei,
city_id,
county_id,
first_use_date,
last_use_date,
imei_seg )
select
w.user_id,
w.product_no,
w.imei,
w.city_id,
w.county_id,
w.first_use_date,
w.last_use_date,
max(w.imei_seg)
from (
(Select
a.user_id,
a.product_no,
a.imei,
a.city_id,
a.county_id,
a.first_use_date,
a.last_use_date,
b.imei as imei_seg
from
(select
user_id,
product_no,
imei,
city_id,
county_id,
first_use_date,
last_use_date,
substr(imei,1,6) as imei_6
from
tmp_imei_cdr) as a
left join dim_res_imei b on a.imei_6=b.imei
)
union all
( Select
c.user_id,
c.product_no,
c.imei,
c.city_id,
c.county_id,
c.first_use_date,
c.last_use_date,
d.imei as imei_seg
from
(
select
user_id,
product_no,
imei,
city_id,
county_id,
first_use_date,
last_use_date,
substr(imei,1,8) as imei_8
from
tmp_imei_cdr) as c
left join dim_res_imei d on c.imei_8=d.imei
)
) as w
group by
w.user_id,
w.product_no,
w.imei,
w.city_id,
w.county_id,
w.first_use_date,
w.last_use_date;
select
‘2008-06-10’,
a.city_id,
a.county_id,
a.brand_id,
count(distinct a.user_id ) ,
count(distinct case when b.cring_used_mark =1 then a.user_id end),
count(distinct case when a.day_new_mark = 1 then a.user_id end),
count(distinct case when a.day_canceling_mark =1 or a.day_canceled_mark = 1 then a.user_id end),
count(distinct case when a.day_canceled_mark = 1 then a.user_id end),
count(distinct case when a.day_canceling_mark = 1 then a.user_id end),
count(distinct case when b.cring_down_mark = 1 then a.user_id end),
count(distinct case when b.cring_12530_mark = 1 then a.user_id end),
sum(a.mo_counts)+sum(a.mt_counts),
sum(a.mt_counts),
sum(a.cring_down_counts),
以下文章点击率最高
Loading…