Hi Lingang,
(1) Could you first specify what is the difference between the results
in details? If you can provide the schema of the table, and also some data
of the rows of the table that can help reproduce the problem, that is very
helpful.
(2) And, what Hive version are you using and settings, configurations?
(3) If possible, could you also post the result when you run explain?
That would help us better help you. Thanks!
Best
Pengcheng Xiong
On Wed, Oct 21, 2015 at 4:13 AM, Deng Lingang(上海_技术部_架构部_基础架构_邓林钢) <
dengling...@yhd.com> wrote:
> 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
>
