Google BigQuery中WHERE NOT IN多列查询报错求助

报错原因 #

BigQuery的IN/NOT IN仅支持单列匹配，你用(id_loan,aud_insert_ts)两列和子查询的多列结果对比，触发了"Subquery of type IN must have only one output column"错误。BigQuery是支持NOT IN的，但仅限单列场景。

替代解决方案 #

方案1：使用NOT EXISTS

通过关联子查询实现多列匹配逻辑，代码如下：

SELECT * 
FROM `your-project.your-dataset.table` t1
WHERE NOT EXISTS (
    SELECT 1
    FROM `your-project.your-dataset.table` t2
    GROUP BY t2.id_loan
    HAVING t2.id_loan = t1.id_loan AND MAX(t2.aud_insert_ts) = t1.aud_insert_ts
)

方案2：使用窗口函数（更高效）

利用ROW_NUMBER()窗口函数标记每个id_loan分组内的最新记录，再筛选非最新的行，适合大数据量场景：

WITH ranked_data AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id_loan ORDER BY aud_insert_ts DESC) AS rn
    FROM `your-project.your-dataset.table`
)
SELECT * EXCEPT(rn)
FROM ranked_data
WHERE rn > 1

这个逻辑里，每个id_loan分组中最新的aud_insert_ts对应的行rn=1，筛选rn>1就得到了原查询想要的"不在最新记录集合里"的数据。

内容的提问来源于stack exchange，提问作者Manuel Ledezma