case when案例

drop table if exists crm_dm.dm_order_detail;
create table if not exists crm_dm.dm_order_detail(
order_no string 
,plu string    
,item_name string   
,orig_price string   
,discount_price string 
,gdept_name string  
,promotion string   
)
partitioned by (order_date string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;



create temporary table tmp as
select order_no
,a.plu
,a.item_name
,a.orig_price
,a.discount_price
,b.gdept['NAME'] gdept_name
,a.promotion
,a.order_date
from 
(
select a.pos['ORDER_NO'] order_no
,b.plu
,b.name item_name
,b.unit_retail orig_price
,b.price discount_price
,to_date(b.BUSINESS_DATE) order_date

,case 
when unit_retail <= 40 and price <= unit_retail * 0.9 then 'true'
when unit_retail = 49 and price = 39 then 'true'
when unit_retail = 59 and price = 49 then 'true'
when unit_retail = 79 and price = 59 then 'true'
when unit_retail = 99 and price = 79 then 'true'
when unit_retail = 129 and price = 99 then 'true'
when unit_retail = 149 and price = 129 then 'true'
when unit_retail = 199 and price = 149 then 'true'
when unit_retail = 249 and price = 199 then 'true'
when unit_retail = 299 and price = 249 then 'true'
when unit_retail = 399 and price = 299 then 'true'
when unit_retail = 499 and price = 399 then 'true'
when unit_retail = 599 and price = 499 then 'true'
when unit_retail = 699 and price = 599 then 'true'
when unit_retail = 799 and price = 699 then 'true'
when unit_retail = 899 and price = 799 then 'true'
when unit_retail = 999 and price = 899 then 'true'
when unit_retail = 1299 and price = 999 then 'true'
when unit_retail = 1399 and price = 1299 then 'true'
when unit_retail = 1499 and price = 1399 then 'true'
when unit_retail = 2499 and price >= 1999 then 'true'

else 'false'

end as promotion 

from crm_dw.dw_15_pos_detail b left join crm_dw.dw_12_epay_payment a 
on to_date(b.business_date)=to_date(a.payment_date)
and b.store['GLOBAL_ID']=a.store['GLOBAL_ID']
and b.tran_no=a.pos['TRAN_NO']
and b.pos_machine_no=a.pos['MACHINE_NO']

union all
select order_no
,plu
,orig_item_name item_name
,orig_price
,discount_price 
,to_date(DELIVER_DATE) order_date
,case 
when orig_price <= 40 and tb_price <= orig_price * 0.9 then 'true'
when orig_price = 49 and tb_price >= 39 then 'true'
when orig_price = 59 and tb_price >= 49 then 'true'
when orig_price = 79 and tb_price >= 59 then 'true'
when orig_price = 99 and tb_price >= 79 then 'true'
when orig_price = 129 and tb_price >= 99 then 'true'
when orig_price = 149 and tb_price >= 129 then 'true'
when orig_price = 199 and tb_price >= 149 then 'true'
when orig_price = 249 and tb_price >= 199 then 'true'
when orig_price = 299 and tb_price >= 249 then 'true'
when orig_price = 399 and tb_price >= 299 then 'true'
when orig_price = 499 and tb_price >= 399 then 'true'
when orig_price = 599 and tb_price >= 499 then 'true'
when orig_price = 699 and tb_price >= 599 then 'true'
when orig_price = 799 and tb_price >= 699 then 'true'
when orig_price = 899 and tb_price >= 799 then 'true'
when orig_price = 999 and tb_price >= 899 then 'true'
when orig_price = 1299 and tb_price >= 999 then 'true'
when orig_price = 1399 and tb_price >= 1299 then 'true'
when orig_price = 1499 and tb_price >= 1399 then 'true'
when orig_price = 2499 and tb_price >= 1999 then 'true'
else 'false'
end as promotion 
from crm_dw.dw_32_tmall_detail

union all

select order_no
,plu
,orig_item_name item_name
,orig_price
,discount_price
,to_date(DELIVER_DATE) order_date
,case 
when orig_price <= 40 and tb_price <= orig_price * 0.9 then 'true'
when orig_price = 49 and tb_price >= 39 then 'true'
when orig_price = 59 and tb_price >= 49 then 'true'
when orig_price = 79 and tb_price >= 59 then 'true'
when orig_price = 99 and tb_price >= 79 then 'true'
when orig_price = 129 and tb_price >= 99 then 'true'
when orig_price = 149 and tb_price >= 129 then 'true'
when orig_price = 199 and tb_price >= 149 then 'true'
when orig_price = 249 and tb_price >= 199 then 'true'
when orig_price = 299 and tb_price >= 249 then 'true'
when orig_price = 399 and tb_price >= 299 then 'true'
when orig_price = 499 and tb_price >= 399 then 'true'
when orig_price = 599 and tb_price >= 499 then 'true'
when orig_price = 699 and tb_price >= 599 then 'true'
when orig_price = 799 and tb_price >= 699 then 'true'
when orig_price = 899 and tb_price >= 799 then 'true'
when orig_price = 999 and tb_price >= 899 then 'true'
when orig_price = 1299 and tb_price >= 999 then 'true'
when orig_price = 1399 and tb_price >= 1299 then 'true'
when orig_price = 1499 and tb_price >= 1399 then 'true'
when orig_price = 2499 and tb_price >= 1999 then 'true'
else 'false'
end as promotion 
from crm_dw.dw_34_tmall_o2o_detail) a
left join mdm_dw_des.dw_02_product b on a.plu=b.plu distribute by order_date;