往with as中写入数据的方法

方法1：直接写入，使用union all,简单直观，但程序运行效率低，几百条就很慢了

with dw_wms_outbound_info_v100 as(
  select '10700001' as order_no, '0011' as shop_no, '5501' as product_code, '2020-07-31' as order_date, 10 as payment_amount union all
  select '10700001' as order_no, '0011' as shop_no, '5502' as product_code, '2020-08-01' as order_date, 90 as payment_amount union all
  select '10700002' as order_no, '0011' as shop_no, '5501' as product_code, '2020-08-02' as order_date, 20 as payment_amount union all
  select '10700002' as order_no, '0011' as shop_no, '5503' as product_code, '2020-08-02' as order_date, 30 as payment_amount union all
  select '10700003' as order_no, '0011' as shop_no, '5501' as product_code, '2020-08-02' as order_date, 20 as payment_amount union all
  select '10700004' as order_no, '0012' as shop_no, '6601' as product_code, '2020-07-31' as order_date, 40 as payment_amount union all
  select '10700004' as order_no, '0012' as shop_no, '6601' as product_code, '2020-08-01' as order_date, 50 as payment_amount
)
  
, dim_store_info_v100 as (
  select '0011' as shop_no, '北京一店' as shop_name  union all
  select '0012' as shop_no, '北京二店' as shop_name 
)
  
, dim_sku_info_v100 as (
  select '5501' as product_code, '可乐350ml' as product_name  union all
  select '5502' as product_code, '雪碧500ml' as product_name  union all
  select '5504' as product_code, '芬达243ml' as product_name  union all
  select '6601' as product_code, '巧克力'    as product_name 
)
 
---------------------上面的SQL不要动-----在下面写自己的SQL--------------------
 
select
  t1.dt,
  t1.order_no,
  t1.shop_no,
  t2.shop_name,
  t1.product_code,
  t3.product_name,
  t1.order_date,
  t1.payment_amount
from dw_wms_outbound_info_v100 t1
left join dim_store_info_v100 t2 on t2.dt = '20200901' and t1.shop_no = t2.shop_no
left join dim_sku_info_v100 t3 on t3.dt = '20200901' and t1.product_code = t3.product_code

 

方法2：使用struct写入，然后列转行。效率比方法1高

with t_temp01 as (SELECT lv.* 
FROM   (SELECT 0) t 
lateral VIEW inline(array(
struct('A',10,'AAA'),
struct('B',20, 'BBB'),
struct('B',300, 'CCC') 
)) lv AS col1, col2, col3)
select col1 from t_temp01 where col1 = 'B'