我有一个查询,给我上面的错误。我的代码如下:
SELECT *,
dense_rank() OVER (PARTITION BY email
ORDER BY priority_score,
comp) AS r
FROM main_query
WHERE NOT EXISTS
(SELECT name,
event,
email,
report_date
FROM gm
WHERE gm.name= main_query.name
AND gm.event= main_query.event
AND gm.email = main_query.email
AND gm.report_date >= (CURRENT_DATE - 25)::date)
ORDER BY priority_score ASC
我看到克服这些类型错误的一个解决方案是能够在不相关的查询(sherlock)中转换相关子查询。因此,我正在寻找使用where not exists
语句的其他方法,但没有相关的子查询,即在子查询(…)中调用main_query
表。from gm left join main_query on
(…))。有人知道这是可能的吗?怎么做?
任何建议都是欢迎的,提前感谢很多!
LEFT JOIN
版本工作吗?
SELECT mq.*,
dense_rank() OVER (PARTITION BY mq.email
ORDER BY mq.priority_score, comp
) AS r
FROM main_query mq LEFT JOIN
gm
ON gm.name = mq.name AND
gm.event= mq.event AND
gm.email = mq.email AND
gm.report_date >= (CURRENT_DATE - 25)::date)
WHERE gm.name IS NULL
ORDER BY priority_score ASC;
如果这不起作用,它应该像这样工作:
SELECT mq.*,
dense_rank() OVER (PARTITION BY mq.email
ORDER BY mq.priority_score, comp
) AS r
FROM main_query mq LEFT JOIN
(SELECT gm.*
FROM gm
WHERE gm.report_date >= (CURRENT_DATE - 25)::date)
) gm
ON gm.name = mq.name AND
gm.event= mq.event AND
gm.email = mq.email
WHERE gm.name IS NULL
ORDER BY priority_score ASC
从另一个帖子和我自己的实验来看,问题似乎是红移不允许关联子查询,因为它可能返回不止一行,这触发了不是很有用的错误消息"由于内部错误",相关子查询模式不受支持。
关于Redshift不支持的相关子查询类型,有一些AWS文档。
我发现我不能让'不存在'的方法工作,但通过使用计数比较,我可以达到相同的结果,它的优点是需要更少的代码返工比切换到'左连接'的方法。
select *
from stl_ddltext a
where 1 > (
select count(*)
from logs.mytable_stl_ddltext b
where a.userid = b.userid
and a.xid = b.xid
and a.pid = b.pid
and a.starttime = b.starttime
and a.endtime = b.endtime
and a.text::varchar(200) = b.text::varchar(200)
);