版本：2.0.11
be配置：32c128g
描述：同样的sql 在1be执行和8be执行的时间没有明显差异。
查询sql
select
aa.µêÆÌ×¢²áÊ¡,
aa.µêÆÌID,
aa.µêÆÌÃû³Æ,
aa.sku_id,
aa.ÉÌÆ·Ô±àÂë,
aa.Ö÷±ê×¼¿âid,
aa.spu_id,
case
when ÅÅÃû <=50 then "A(1-50)"
when ÅÅÃû <=100 then "B(51-100)"
when ÅÅÃû <=200 then "C(101-200)"
when ÅÅÃû <=300 then "D(201-300)"
when ÅÅÃû <=400 then "E(301-400)"
when ÅÅÃû <=500 then "F(401-500)"
when ÅÅÃû <=600 then "G(501-600)"
when ÅÅÃû <=700 then "H(601-700)"
when ÅÅÃû <=800 then "I(701-800)"
when ÅÅÃû <=900 then "J(801-900)"
when ÅÅÃû <=1000 then "K(901-1000)"
when ÅÅÃû <=2000 then "L(1001-2000)"
when ÅÅÃû <=3000 then "M(2001-3000)"
when aa.Ò»¼¶·ÖÀà in ('ÖÐÎ÷³ÉÒ©','×¢ÉäÓÃÒ©') then 'N(3000+)'
ELSE "·ÇÖÐÎ÷³ÉÒ©ºÍ×¢ÉäÓÃÒ©" end ÉÏÔÂʵ¸¶·Ö²ã,
ÅÅÃû as ÉÏÔÂʵ¸¶ÅÅÃû,
aa.Ò»¼¶·ÖÀà,
aa.¶þ¼¶·ÖÀà,
aa.»î¶¯id,
aa.»î¶¯Ãû³Æ,
aa.ÉÌÆ·Ãû³Æ,
aa.ͨÓÃÃû,
aa.`¹æ¸ñ`,
aa.`Éú²ú³§¼Ò`,
aa.ÊÇ·ñ½üЧÆÚ,
aa.¿ÉÊÛÊ¡·ÝÊý,
aa.Æð¹ºÊýÁ¿,
aa.¸öÈËÏÞ¹ºÊýÁ¿,
aa.×ÜÏÞ¹ºÊýÁ¿,
aa. ¿â´æ½ð¶î,
aa.Åú¹º»î¶¯¼Û,
bb.Æ´ÍÅ×îµÍ¼Û as ƽ̨Ï¿ÉÊÛÊ¡·Ý´óÓÚ10µÄ×îµÍÆ´ÍżÛ,
ifnull(dd.ʵ¸¶GMV ,0) as »î¶¯Á´½Óʵ¸¶gmv,
ifnull(cc.`ʵ¸¶GMV`,0) ½ü30Ììƽ̨spu_idʵ¸¶,
aa.Åú¹º»î¶¯¼Û-bb.Æ´ÍÅ×îµÍ¼Û as ÓëÆ´Íż۲î,
case
when aa.Åú¹º»î¶¯¼Û in (8888,9999,99999,88888) then 'Åú¹º¼Û¸ñÒì³£'
when bb.Æ´ÍÅ×îµÍ¼Û is null then 'ÎÞÆ´ÍżÛ'
when (aa.Åú¹º»î¶¯¼Û-bb.Æ´ÍÅ×îµÍ¼Û)/aa.Åú¹º»î¶¯¼Û>=0.1 or (aa.Åú¹º»î¶¯¼Û-bb.Æ´ÍÅ×îµÍ¼Û)/aa.Åú¹º»î¶¯¼Û<=-0.1 then '¼Û¸ñ²îÒìÕý¸º10¸öµã'
when aa.Åú¹º»î¶¯¼Û<=bb.Æ´ÍÅ×îµÍ¼Û then 'ÓżÛ'
when (aa.Åú¹º»î¶¯¼Û-bb.Æ´ÍÅ×îµÍ¼Û)/bb.Æ´ÍÅ×îµÍ¼Û<=0.01 and (aa.Åú¹º»î¶¯¼Û-bb.Æ´ÍÅ×îµÍ¼Û)/bb.Æ´ÍÅ×îµÍ¼Û>0 then '´ÎÓżÛ'
when (aa.Åú¹º»î¶¯¼Û-bb.Æ´ÍÅ×îµÍ¼Û)/bb.Æ´ÍÅ×îµÍ¼Û>0.01 then '´ÎÁÓ¼Û'
else 'ÁÓ¼Û'
end as ÊÇ·ñÓżÛ,
case when ÅÅÃû <=300 then 'ÊÇ' else '·ñ' end as ÊÇ·ñTOP300,
¿ÉÊÛÊ¡·ÝÊý,
ÉÌȦ,
aa.µêÆÌÈÏ֤״̬,
aa.µêÆÌÔÚÏß״̬,
dense_rank() over(partition by aa.ͨÓÃÃû order by aa.Åú¹º»î¶¯¼Û asc) as ͬͨÓÃÃûÏÂÅú¹º»î¶¯¼ÛÅÅÃû,
dense_rank() over(partition by aa.ͨÓÃÃû order by ifnull(cc.`ʵ¸¶GMV`,0) desc) as ͬͨÓÃÃûÏÂspuʵ¸¶ÅÅÃû
from
(select
case when left(n.prov,2)="ºÚÁú" then "ºÚÁú½" when left(n.prov,2)="ÄÚÃÉ" then "ÄÚÃɹÅ" else left(n.prov,2) end as µêÆÌ×¢²áÊ¡,
e.org_id as µêÆÌID,
e.company_name as µêÆÌÃû³Æ,
e1.barcode ÉÌÆ·Ô±àÂë,
e.sku_id,
e.master_standard_product_id Ö÷±ê×¼¿âid,
e.spu_id spu_id,
Ò»¼¶·ÖÀà,
¶þ¼¶·ÖÀà,
tmas.marketing_id »î¶¯id,
tmas.name »î¶¯Ãû³Æ,
e.product_name ÉÌÆ·Ãû³Æ,
e.common_name ͨÓÃÃû,
e.spec as `¹æ¸ñ`,
e.manufacturer as `Éú²ú³§¼Ò`,
tmas.discount_price Åú¹º»î¶¯¼Û,
case when (DATEDIFF(if(length(e.near_effect)=7,concat(e.near_effect,"-01"),e.near_effect),curdate()) > 180
and (ifnull(replace(replace(replace(replace(replace(replace(replace(e.shelf_life,"-",""),"-1",""),"*",""),"Äê",""),"ÔÂ",""),"¸ö",""),"ÈÕ",""),0) <= 12
or DATEDIFF(if(length(e.near_effect)=7,concat(e.near_effect,"-01"),e.near_effect),curdate()) >= 270)) then '·ñ' else 'ÊÇ'
end as ÊÇ·ñ½üЧÆÚ,
array_size(split_by_string(sale_area_province,',')) ¿ÉÊÛÊ¡·ÝÊý,
tmas.sale_area_province_name ÉÌȦ,
tmas.start_qty Æð¹ºÊýÁ¿,
tmas.qty AS ¸öÈËÏÞ¹ºÊýÁ¿,
tmas.total_qty ×ÜÏÞ¹ºÊýÁ¿,
available_qty*tmas.discount_price ¿â´æ½ð¶î,
row_number() over(partition by e.spu_id order by tmas.discount_price asc) Åú¹º¼ÛÅÅÃû,
case n.state
when 1 then 'δÈÏÖ¤'
when 2 then '´ýÉóºË'
when 3 then '´ý¸¶¿î'
when 4 then '¸¶¿îÉóºËδͨ¹ý'
when 5 then 'ÒÑÈÏÖ¤'
when 6 then '²µ»Ø' end as µêÆÌÈÏ֤״̬,
case n.shop_status
when 1 then '´ýÉÏÏß'
when 2 then 'ÒÑÉÏÏß'
when 3 then 'ÒÑÏÂÏß'
when 4 then 'ÒѹرÕ'
else shop_status
end as µêÆÌÔÚÏß״̬
from b2b_dwd.dwd_realtime_ec_tb_marketing_activity_detail tmas
left join b2b_dim.dim_realtime_ec_tb_sku e on tmas.sku_id =e.sku_id
left join b2b_dim.dim_realtime_ec_tb_sku_origin e1 on e.sku_id =e1.sku_id
LEFT JOIN b2b_dim.dim_realtime_ec_tb_pop_corporation n ON e.org_id = n.org_id
left join (select spu_id as spu_id,
max(first_category_name) as Ò»¼¶·ÖÀà,max(second_category_name) as ¶þ¼¶·ÖÀà,
max(third_category_name) as Èý¼¶·ÖÀà, max(fourth_category_name) as Ëļ¶·ÖÀà
from xyy_b2b_load.ywfx_spu_position as a
group by spu_id
) fl on e.spu_id = fl.spu_id
where e.status=1
and tmas.activity_type = 30
and tmas.stime <= now()
and tmas.etime >= now()
and tmas.business_status=3 -- ½øÐÐÖÐ
and n.shop_status=2
and e.is_third_company=1
and array_size(split_by_string(sale_area_province,',')) > 10
and available_qty *tmas.discount_price>500
)aa
left join -- ƽ̨Ï¿ÉÊÛÊ¡·Ý´óÓÚ10µÄ×îµÍÆ´ÍżÛ
(select * from
(
select
tmas.sku_id as
sku_id ,
a.spu_id as spu_id ,
tmas.discount_price Æ´ÍÅ×îµÍ¼Û,
row_number() over(partition by a.spu_id order by tmas.discount_price asc ) Æ´ÍżÛÅÅÃû
from
b2b_dwd.dwd_realtime_ec_tb_marketing_activity_detail tmas
left join b2b_dim.dim_realtime_ec_tb_sku a on tmas.sku_id =a.sku_id
LEFT JOIN b2b_dim.dim_realtime_ec_tb_pop_corporation n ON a.org_id = n.org_id
where a.status=1
and tmas.stime <= now()
and tmas.etime >= now()
and tmas.activity_type =22
and n.shop_status=2
and array_size(split_by_string(sale_area_province,',')) >10
and tmas.discount_price*available_qty>500
and tmas.name not like '%¶¨Í¶%' and tmas.name not like '%пÍ%'
and a.org_id not in ('SH10018113','SH10018125','SH10019149','SH10019153','SH10019091','SH10046884')-- ÌÞ³ý¿ØÏú
) a
where Æ´ÍżÛÅÅÃû=1
) bb on aa.spu_id =bb.spu_id
left join -- ½üÈýÊ®Ììƽ̨spu_idʵ¸¶
(
select
e.spu_id,
sum((a.product_amount) *a.product_price-(a.use_balance_amount+a.discount_amount)) as `ʵ¸¶GMV`
from b2b_dwd.dwd_realtime_ec_tb_order_detail a
left join b2b_dim.dim_realtime_ec_tb_sku e on a.sku_id = e.sku_id
where
substr(if (a.pay_time is null and a.payment_time is null,a.create_time, if (a.pay_time is null,a.payment_time,a.pay_time)),1,10)
>= DATE_SUB(curdate(),INTERVAL 30 day)
and a.status in (1,2,3,7,20) -- ÓÐЧ¶©µ¥×´Ì¬£¬
group by e.spu_id
) cc on cc.spu_id =aa.spu_id
left join -- »î¶¯Á´½Óʵ¸¶
(
select
a.sku_id ,
sum((a.product_amount) *a.product_price-(a.use_balance_amount+a.discount_amount)) as `ʵ¸¶GMV`
from b2b_dwd.dwd_realtime_ec_tb_order_detail a
where
a.status in (1,2,3,7,20) -- ÓÐЧ¶©µ¥×´Ì¬£¬
group by a.sku_id
)dd on aa.sku_id =dd.sku_id
left join
(select -- 4¼¾¶ÈÅÅÃû,ȫƽ̨TOP
-- ifnull(e.master_standard_product_id,e1.barcode ) as Ö÷±ê×¼¿âid,
e.spu_id as spu_id,
max(a.product_name) as ÉÌÆ·Ãû³Æ,
max(a.spec) as ¹æ¸ñ,
sum( round((a.product_amount-a.refund_product_amount) *a.product_price-(a.use_balance_amount+a.discount_amount)/a.product_amount*(a.product_amount-a.refund_product_amount),2) ) ʵ¸¶GMV,
row_number() over(order by sum( round((a.product_amount-a.refund_product_amount) *a.product_price-(a.use_balance_amount+a.discount_amount)/a.product_amount*(a.product_amount-a.refund_product_amount),2)) desc) as ÅÅÃû
from b2b_dwd.dwd_realtime_ec_tb_order_detail a
left join b2b_dim.dim_realtime_ec_tb_merchant c on a.merchant_id = c.id
left join b2b_dim.dim_realtime_ec_tb_sku e on a.sku_id = e.sku_id
left join b2b_dim.dim_realtime_ec_tb_pop_corporation n on a.org_id = n.org_id
left join (select spu_id as spu_id,
max(first_category_name) as Ò»¼¶·ÖÀà,max(second_category_name) as ¶þ¼¶·ÖÀà,
max(third_category_name) as Èý¼¶·ÖÀà, max(fourth_category_name) as Ëļ¶·ÖÀà
from xyy_b2b_load.ywfx_spu_position as a
group by spu_id
) fl on e.spu_id = fl.spu_id
where
substr(if(a.pay_time is null and a.payment_time is null,a.create_time, if (a.pay_time is null,a.payment_time,a.pay_time)),1,7)
=substr(DATE_ADD(CURDATE(),INTERVAL -1 month) ,1,7)-- ÉÏÔÂÔ·Ý
and a.org_id not in ('SH10018113','SH10018125','SH10019149','SH10019153','SH10019091','SH10046884')
and a.status in (1,2,3,7,20) -- ÓÐЧ¶©µ¥×´Ì¬£¬
and c.customer_type not in (5,21,22,23)
and a.merchant_type = 1 -- ÌÞ³ý²âÊÔµ¥
and e.spu_id is not null
and Ò»¼¶·ÖÀà in ('ÖÐÎ÷³ÉÒ©','×¢ÉäÓÃÒ©')
group by 1
)top on aa.spu_id=top.spu_id
where aa.Åú¹º¼ÛÅÅÃû=1
and Ò»¼¶·ÖÀà ="ÖÐÎ÷³ÉÒ©"
and ÊÇ·ñ½üЧÆÚ ='·ñ'
