Presto中多次使用NOT IN的SQL查询报错修正求助

嘿，我看了你的查询，报错的原因是两处语法问题，咱们一步步修正：

问题分析 #

• 第一个NOT IN后面的子查询返回了两列（user_id和转换后的日期），但NOT IN只能接收单列结果——毕竟你是用user_id做匹配，所以这里只需要保留user_id列就够了。
• 内层的AND user_id NOT IN select ...缺少括号，Presto要求NOT IN后面的子查询必须用()包裹，不然会被识别成无效语法。

修正后的查询语句 #

select user_id 
from gold.order_master_bi 
where portfolio = 'Women Sarees' 
  and order_date >= current_date - interval '30' day 
  and user_id NOT IN (
      select user_id 
      from silver.mixpanel_android__add_to_cart_clicked 
      where cast(year||'-'||month||'-'||day as date) = DATE('2021-07-09') 
        AND user_id NOT IN (
            select user_id 
            FROM gold.order_master_bi 
            where order_date = DATE('2021-07-09')
        )
  )

额外小建议 #

如果数据量比较大，NOT IN有时候可能会有性能瓶颈，你也可以试试用NOT EXISTS来改写，逻辑完全一致，但在某些场景下性能表现更好：

select omb.user_id 
from gold.order_master_bi omb
where omb.portfolio = 'Women Sarees' 
  and omb.order_date >= current_date - interval '30' day 
  and NOT EXISTS (
      select 1
      from silver.mixpanel_android__add_to_cart_clicked acc
      where cast(acc.year||'-'||acc.month||'-'||acc.day as date) = DATE('2021-07-09') 
        and acc.user_id = omb.user_id
        and NOT EXISTS (
            select 1
            FROM gold.order_master_bi omb_inner
            where omb_inner.order_date = DATE('2021-07-09')
              and omb_inner.user_id = acc.user_id
        )
  )

内容的提问来源于stack exchange，提问作者Sarvesh Joshi