"NOT IN" / "NOT EXISTS" 用于成对列比较在 Redshift 中不起作用

Question

我想用红移重写下面的语句。对于多列比较，Redshift不支持IN/not-IN。当我使用NOT EXISTS尝试相同的查询时，我也会得到以下错误：

无效操作：由于内部错误，不支持这种类型的相关子查询模式；

select count(distinct item) from tbl1  
where (item,store) not in (
select distint item, store form tbl1 
where *store changed* -- some filters
)

Answer 1

Tim的想法看起来很酷，但您也可以使用以下代码：

select     count(distinct t1.item)
from       tbl1 t1
left join
(
select distinct item, store
form   tbl1 
where  *store changed* -- some filters
) t2
on t2.item = t1.item
and t2.store = t1.store
where t2.item is null
and   t2.store is null

Answer 2

您的Redshift版本不支持此元组语法。你可以改写如下：

SELECT COUNT(DISTINCT item) AS cnt
FROM tbl1 t1
WHERE NOT EXISTS (
SELECT 1
FROM tbl1 t2
WHERE t2.item = t1.item AND
t2.store = t1.store AND
(other filters here...)
);