1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| // 将原始数据表中的增量数据插入拉链表 // 利用源数据和目标表的order_id进行匹配,若匹配则更新现有订单信息,若不匹配则插入新订单 merge into hive_zipper_pc_order as O using ( select order_id, user_id, order_create_dt, order_modify_dt, order_money, current_status, year(order_create_dt) as year, month(order_create_dt) as month, day(order_create_dt) as day from ( select order_id,user_id,order_money, min(order_modify_dt) as order_create_dt, max(order_modify_dt) as order_modify_dt, max(current_status) as current_status from hive_zipper_order group by order_id,user_id,order_money )T ) as H on O.order_id=H.order_id when matched then update set order_modify_dt=H.order_modify_dt,current_status=H.current_status when not matched then insert values(H.order_id,H.user_id,H.order_create_dt,H.order_modify_dt,H.order_money,H.current_status,H.year,H.month,H.day);
|