Hi，all
I create 3 tables at context with color, a_110,b_110,c_110, then the script
become
SELECT count(*)
FROM testtmp.a_110 a
LEFT OUTER JOIN testtmp.b_110 b ON a.cms_id = b.cms_id AND a.pltfm_id =
b.pltfm_id
LEFT OUTER JOIN testtmp.c_110 c ON b.cms_id = c.cms_id AND b.categ_lvl2_id =
c.categ_lvl2_id AND b.pltfm_id = c.pltfm_id
LEFT OUTER JOIN dw.dim_cms dim ON a.cms_id= dim.cms_id
and GetTimestampFmt(dim.CMS_START_TIME) <= GetTimestampFmt('2015-10-18')
AND GetTimestampFmt(dim.CMS_END_TIME) >= GetTimestampFmt('2015-10-18')
where GetTimestampFmt(dim.CMS_START_TIME) <= GetTimestampFmt('2015-10-18')
AND GetTimestampFmt(dim.CMS_END_TIME) >= GetTimestampFmt('2015-09-01') ;
--11524
But, problems arise,two scripts result is defferent, who can tell reason,
thanks
SELECT count(*)
FROM
(SELECT nav_page_value AS cms_id,
pltfm_id,
COUNT(DISTINCT(CASE WHEN t.nav_tracker_id > 0 THEN t.sessn_id ELSE
NULL END)) AS cms_vstrs,
COUNT(DISTINCT(CASE WHEN t.nav_tracker_id > 0
AND t.nav_next_tracker_id > 0 THEN t.sessn_id ELSE
NULL END)) AS cms_click_vstrs,
COUNT(DISTINCT(CASE WHEN t.nav_tracker_id > 0 THEN t.nav_tracker_id
ELSE NULL END)) AS cms_pv,
COUNT(DISTINCT(CASE WHEN t.nav_tracker_id > 0
AND t.sessn_pv > 1 THEN t.sessn_id ELSE NULL END)) AS
cms_sec_vstrs,
COUNT(DISTINCT(CASE WHEN (t.detl_tracker_id > 0
AND (length(t.detl_button_position) = 0
OR t.detl_button_position IS NULL
OR t.detl_button_position = 'null'))
OR (t.cart_tracker_id > 0
AND length(t.detl_tracker_id) = 0) THEN t.sessn_id
ELSE NULL END)) AS cms_detl_vstrs,
COUNT(DISTINCT(CASE WHEN t.nav_tracker_id > 0 THEN t.ordr_code ELSE
NULL END)) AS cms_ordr_num
FROM dw.fct_traffic_cms_detl t
WHERE ds= '2015-10-18'
AND t.nav_page_value IS NOT NULL
GROUP BY nav_page_value,
pltfm_id) a
LEFT OUTER JOIN
(SELECT nvl(t3.mg_brand_id,-999999) AS mg_brand_id,
nvl(t1.nav_page_value,-1) AS cms_id,
hc.categ_lvl2_id ,
t1.pltfm_id,
nvl(COUNT(DISTINCT(CASE WHEN t1.detl_pv > 0
OR dirct_cart_pv>0 THEN t1.sessn_id ELSE NULL
END)), 0) AS detl_vstrs,
COUNT(DISTINCT(CASE WHEN t1.ordr_tranx_activ_flg=1 THEN parnt_ordr_id
ELSE NULL END)) AS ordr_num,
COUNT(DISTINCT(CASE WHEN t1.ordr_tranx_activ_flg=1 THEN
t1.end_user_id ELSE NULL END)) AS cust_num
FROM dw.fct_traffic_prdt_cart_path t1 LEFT
OUTER JOIN dw.dim_prod t2 ON t1.prod_id = t2.prod_id
AND t2.cur_flag = 1 LEFT
OUTER JOIN dw.hier_categ hc ON t2.categ_lvl_id = hc.categ_lvl_id INNER
JOIN dw.dim_mrchnt b ON t1.mrchnt_id = b.mrchnt_id
AND b.cur_flag = 1 LEFT
OUTER JOIN dw.dim_brand t3 ON t2.brand_id = t3.brand_id
AND t3.cur_flag = 1
WHERE t1.ds = '2015-10-18'
AND b.biz_unit = 1
AND t1.nav_page_categ_id = 1
AND t1.nav_page_value>0
GROUP BY nvl(t3.mg_brand_id,-999999),
nvl(t1.nav_page_value,-1) ,
hc.categ_lvl2_id ,
t1.pltfm_id) b ON a.cms_id = b.cms_id
AND a.pltfm_id = b.pltfm_id
LEFT OUTER JOIN
(SELECT nvl(t1.nav_page_value,-1) AS cms_id,
hc.categ_lvl2_id ,
t1.pltfm_id,
nvl(COUNT(DISTINCT(CASE WHEN t1.detl_pv > 0
OR dirct_cart_pv>0 THEN t1.sessn_id ELSE NULL
END)), 0) AS categ_lvl2_cms_detl_vstrs,
COUNT(DISTINCT(CASE WHEN t1.ordr_tranx_activ_flg=1 THEN parnt_ordr_id
ELSE NULL END)) AS categ_lvl2_cms_ordr_num
FROM dw.fct_traffic_prdt_cart_path t1 LEFT
OUTER JOIN dw.dim_prod t2 ON t1.prod_id = t2.prod_id
AND t2.cur_flag = 1 LEFT
OUTER JOIN dw.hier_categ hc ON t2.categ_lvl_id = hc.categ_lvl_id INNER
JOIN dw.dim_mrchnt b ON t1.mrchnt_id = b.mrchnt_id
AND b.cur_flag = 1 LEFT
OUTER JOIN dw.dim_brand t3 ON t2.brand_id = t3.brand_id
AND t3.cur_flag = 1
WHERE t1.ds = '2015-10-18'
AND b.biz_unit = 1
AND t1.nav_page_categ_id = 1
AND t1.nav_page_value>0
GROUP BY nvl(t1.nav_page_value,-1) ,
hc.categ_lvl2_id ,
t1.pltfm_id) c ON b.cms_id = c.cms_id
AND b.categ_lvl2_id = c.categ_lvl2_id
AND b.pltfm_id = c.pltfm_id
LEFT OUTER JOIN dw.dim_cms dim ON a.cms_id= dim.cms_id
and GetTimestampFmt(dim.CMS_START_TIME) <= GetTimestampFmt('2015-10-18')
AND GetTimestampFmt(dim.CMS_END_TIME) >= GetTimestampFmt('2015-10-18')
where GetTimestampFmt(dim.CMS_START_TIME) <= GetTimestampFmt('2015-10-18')
AND GetTimestampFmt(dim.CMS_END_TIME) >= GetTimestampFmt('2015-09-01') ;
--10723
