错误:频谱嵌套查询错误.引用嵌套表的子查询不能包含DISTINCT操作.Redshift

Question

当我运行这个查询时，我会收到上面的错误消息，这只发生在Redshift上。是否有其他方法来选择嵌套查询中的不同项？

select distinct(cast(A.id as varchar)) from db1.table1 A
join
(
select 
distinct(properties.event_id) from events.table2
where 
properties.event_screen in (' xxxxxxxxxxxx ',' yyyyyyyyyyyy ')                        
and properties.category_ID in (' zzzzzzzzz ',' aaaaaaaaaaaa ')
and date(dt) between date_add('day',-29,current_date) and date_add('day',0,current_date) 
)  as B
on A.key = B.event_id

Answer 1

我在这里看不到使用JOIN或DISTINCT的要求。

您可以按如下方式使用IN：

select distinct(cast(c.id as varchar)) from db1.table1 A
WHERE A.key IN -- used IN
(
select 
properties.event_id AS ID -- CONSIDERING THIS AS ID
from events.table2
where 
properties.event_screen in (' xxxxxxxxxxxx ',' yyyyyyyyyyyy ')                        
and properties.category_ID in (' zzzzzzzzz ',' aaaaaaaaaaaa ')
and date(dt) between date_add('day',-29,current_date) and date_add('day',0,current_date) 
) 

Answer 2

这行得通吗？

select A.id
from db1.table1 A join
(select  distinct properties.event_id
from events.table2 t2
where properties.event_screen in ('xxxxxxxxxxxx', 'yyyyyyyyyyyy') and                     
properties.category_ID in ('zzzzzzzzz', 'aaaaaaaaaaaa') and
dt >= date_add('day', -29, current_date) and 
dt < date_add('day', 1, current_date) 
) B
on A.key = B.event_id;

注：

• 这假设properties是table2中的嵌套结构
• 转换为字符串似乎是多余的
• 对于id列，我根本不明白为什么需要select distinct
• 我更改了日期算术，以避免对dt进行函数调用