db2与oracle的全面对比21 | 温室小花.技术.博客

sum(a.bill_down_counts),

sum(a.cring_12530_duration_m),

sum(a.call_fee),

sum(a.info_fee)

from

dw_depth_busi_cring_20080609 a

inner join

temp_mark_day b

a.user_id = b.user_id

group by

a.city_id,

a.county_id,

a.brand_id

**********************************************************

优化前执行计划(产生过大的内部排序)

Estimated Cost = 1262707.500000

Estimated Cardinality = 122.375000

Optimizer Plan:

INSERT

( 2)

/–/ \

GRPBY Table:

( 3) DW

| TEMP_CRING_KPI_DAY

TBSCAN

( 4)

SORT

( 5)

UNION

( 6)

+——-+——-+——-+—+—+——-+——-+——-+

GRPBY GRPBY GRPBY GRPBY GRPBY GRPBY GRPBY GRPBY

( 7) ( 17) ( 23) ( 29) ( 35) ( 41) ( 47) ( 53)

| | | | | | | |

MDTQ MDTQ MDTQ MDTQ MDTQ MDTQ MDTQ MDTQ

( 8) ( 18) ( 24) ( 30) ( 36) ( 42) ( 48) ( 54)

| | | | | | | |

GRPBY GRPBY GRPBY GRPBY GRPBY GRPBY GRPBY GRPBY

( 9) ( 19) ( 25) ( 31) ( 37) ( 43) ( 49) ( 55)

| | | | | | | |

TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN

( 10) ( 20) ( 26) ( 32) ( 38) ( 44) ( 50) ( 56)

| | | | | | | |

SORT SORT SORT SORT SORT SORT SORT SORT

( 11) ( 21) ( 27) ( 33) ( 39) ( 45) ( 51) ( 57)

| | | | | | | |

TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN

( 12) ( 22) ( 28) ( 34) ( 40) ( 46) ( 52) ( 58)

| | | | | | | |

TEMP Temp: Temp: Temp: Temp: Temp: Temp: Temp:

( 13) ID=t2 ID=t2 ID=t2 ID=t2 ID=t2 ID=t2 ID=t2

| Src=13 Src=13 Src=13 Src=13 Src=13 Src=13 Src=13

HSJOIN

( 14)

/—-/ \—\

TBSCAN TBSCAN

( 15) ( 16)

| |

Table: Table:

DW DW

DW_DEPTH_BUSI_CRING_20080609 TEMP_MARK_DAY

13.7 语法讲解

Select

 VALUE(A.comp_product_no,B.comp_product_no) AS comp_product_no,

 VALUE(A.comp_city_id,B.comp_city_id) AS comp_city_id,

 WHEN (YEAR(‘${ARG_OPTIME_ISO}’)-YEAR(VALUE(A.comp_last_date,B.comp_last_date)))*12+MONTH(‘${ARG_OPTIME_ISO}’)-MONTH(VAL

 UE(A.comp_last_date,B.comp_last_date))<=3 THEN 1

 ELSE 4 END AS comp_userstatus_id,

 VALUE(B.comp_open_date,A.comp_open_date) AS comp_open_date,

 CASE WHEN (VALUE(B.comp_day_new_mark,9) =9 OR (VALUE(A.mo_sms_counts,0)+VALUE(A.mt_sms_counts,0)+VALUE(A.in_call_cou

 nts,0) +VALUE(A.out_call_counts,0)>0 AND VALUE(B.comp_userstatus_id,9)=4 ))

 THEN 1 ELSE 0 end AS comp_day_new_mark,

 CASE WHEN (VALUE(B.mtd_in_call_duration_m,0) + VALUE(A.in_call_duration_m,0)+

 VALUE(B.mtd_out_call_duration_m,0) + VALUE(A.out_call_duration_m,0))/DAY(date(‘${ARG_OPTIME_ISO}’)) >=

 30 THEN 1

 ELSE 0 END AS comp_vip_mark,

 CASE WHEN ((VALUE(B.mtd_in_call_duration_m,0) + VALUE(A.in_call_duration_m,0) +

 VALUE(B.mtd_out_call_duration,0) + VALUE(A.out_call_duration_m,0))/DAY(date(‘${ARG_OPTIME_ISO}’)) >=

 30 and VALUE(B.comp_vip_mark,0) <> 1 ) THEN 1

 ELSE 0 END AS comp_new_vip_mark,

 CASE WHEN (VALUE(A.in_call_counts,0)+VALUE(A.out_call_counts,0)+VALUE(A.mo_sms_counts,0)+

 VALUE(A.mt_sms_counts,0) + VALUE(B.mtd_in_call_counts,0)+VALUE(B.mtd_out_call_counts,0)

 +VALUE(B.mtd_mo_sms_counts,0)+VALUE(B.mtd_mt_sms_counts,0)>0) THEN 1

 ELSE 0 END AS comp_active_mark,

 VALUE(A.out_call_duration_m,0) AS out_call_duration_m,

 VALUE(A.sms_fee,0) AS sms_fee,

 VALUE(A.mo_sms_counts,0)+ VALUE(B.mtd_mo_sms_counts,0) AS mtd_mo_sms_counts,

 VALUE(A.out_call_duration,0) + VALUE(B.mtd_out_call_duration,0) AS mtd_out_call_duration,

 FROM

 xxxxxxxxxxx A FULL OUTER JOIN yyyyyyyyyyy B

 ON A.comp_product_no=B.comp_product_no AND A.comp_city_id=B.comp_city_id