hive COALESCE 的使用

COALESCE(expr1, expr2, expr3)

当expr1为空时使用expr2为结果值，若expr2也为空，使用expr3为结果值

SELECT COALESCE (u.u_date, o.o_date, r.r_date, 'no_time') AS result_date, 
 COALESCE (u.na, o.na, r.na, 'no_netflow') AS result_na, 
 COALESCE (o.order_number, 0) AS on_order_num, COALESCE (o.total_price, 0) AS on_order_price, 
 COALESCE (r.order_num, 0) AS re_order_num, COALESCE (r.order_amount, 0) AS re_order_amount, 
 COALESCE (u.order_number, 0) AS un_order_num, 
 COALESCE (u.total_price, 0) AS un_total_price 
 FROM 
 underLine u FULL OUTER JOIN onLinePay o ON u.un_index = o.on_index FULL 
 OUTER JOIN refund r ON u.un_index = r.re_index;