时间戳

首页 > 解决方案 > SQL Presto：不支持相关子查询

sql - SQL Presto：不支持相关子查询

问题描述

考虑表x

id,val
1,100
3,300

和表y

id
1
2
3

对于每一行，y我想要valfrom y 相等或最接近from的x地方，如下所示：ididx

id,val
1,100
2,100
3,300

我试图用相关的子查询找到最接近的 id：

WITH 
x AS (SELECT * FROM (VALUES (1, 100),(3, 300)) AS t(id, val)),
y AS (SELECT * FROM (VALUES 1,2,3) AS t(id))
SELECT *, (
    SELECT x.id
    FROM x
    WHERE x.id <= y.id
    ORDER BY x.id DESC
    LIMIT 1
) as closest_id
FROM y

但我明白了

SYNTAX_ERROR: line 5:5: Given correlated subquery is not supported

我还尝试了左连接：

SELECT *
FROM y
LEFT JOIN x ON x.id <= (
    SELECT MAX(xbis.id) FROM x AS xbis WHERE xbis.id <= y.id
)

但我得到了错误

SYNTAX_ERROR: line 7:5: Correlated subquery in given context is not supported

标签： sqlamazon-web-servicesjoinamazon-athenapresto

解决方案

您可以尝试根据小于条件加入，然后对结果进行分组并从分组中找到所需的数据：

WITH 
x AS (SELECT * FROM (VALUES (1, 100),(3, 300),(4, 400)) AS t(id, val)),
y AS (SELECT * FROM (VALUES 1,2,3,4) AS t(id))

SELECT y.id as yId,
    max(x.id) as xId,
    max_by(x.val, x.id) as val
FROM y
JOIN x on x.id <= y.id
GROUP BY y.id
ORDER BY y.id

输出：

身份证 xId 值
1 1 100
2 1 100
3 3 300
4 4 400

推荐阅读