hive not in效率优化

hive not in 效率忍不了

select
  shop_id
from
  whitelist
where
  date = '${date-1}'
  and shop_id not in (
    select
      shop_id
    from
      whitelist
    where
      date =  '${date-2}'
  )
  limit 20

愣是跑了十几分钟没完
修改后

select
  a.shop_id
from
  (
    select
      shop_id
    from
      whitelist
    where
      date = '${date-1}'
  ) a
  left join (
    select
      shop_id
    from
      whitelist
    where
      date = '${date-2}'
  ) b
on a.shop_id = b.shop_id
where
  b.shop_id is null

4s跑完