impala实现oracle Minus函数

去整理别人的存储过程实现sql的时候，发现用到了oracle的Minus函数，然后就根据业务逻辑实现。sql代码如下：

– 找出在Ａ表中的数据在Ｂ表中不存在的数据
SELECT
s.pro_no,
nvl ( s.lsg_month, ‘209912’ ) lsg_month,
s.brd_season_no
FROM
dws_hive.tb1 s
LEFT OUTER JOIN
(
SELECT
s1.pro_no,
s1.lsg_month,
s1.brd_season_no
FROM
( SELECT pro_no, lsg_month, brd_season_no, rank ( ) over ( PARTITION BY pro_no ORDER BY update_sdate DESC ) order_flag FROM dws_hive.tb2 ) s1
WHERE
s1.order_flag = 1
) ss1
ON s.pro_no = ss1.pro_no
WHERE
ss1.pro_no IS NULL

UNION -- 拿出并集

---- 找出在A表和B表都存在但内容不同的数据

SELECT
t.pro_no,
t.lsg_month,
t.brd_season_no
FROM
(
SELECT
IF
(
regexp_replace ( concat( a.lsg_month, a.brd_season_no ), " ", “” ) = regexp_replace ( concat( b.lsg_month, b.brd_season_no ), " ", “” ),
‘’,
a.pro_no
) AS v,
a.pro_no,
a.lsg_month,
a.brd_season_no
FROM
( SELECT s.pro_no, nvl ( s.lsg_month, ‘209912’ ) AS lsg_month, s.brd_season_no FROM dws_hive.tb1 s ) a
JOIN
(
SELECT
pro_no,
lsg_month,
brd_season_no
FROM
( SELECT pro_no, lsg_month, brd_season_no, rank ( ) over ( PARTITION BY pro_no ORDER BY update_sdate DESC ) order_flag FROM dws_hive.tb2 ) s
WHERE
s.order_flag = 1
) b
ON ( a.pro_no = b.pro_no )
) t
WHERE
t.v != ‘’