Presto报错不支持相关子查询，如何查询匹配最近前驱id对应的字段值

Presto 环境下可以通过非等值左连接 + 窗口排名的方案避开相关子查询限制，实现需求：

方案1：通用实现（适用于所有场景） #

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 id, val
FROM (
    SELECT 
        y.id,
        x.val,
        ROW_NUMBER() OVER (PARTITION BY y.id ORDER BY x.id DESC) AS rn
    FROM y
    LEFT JOIN x ON x.id <= y.id
) t
WHERE rn = 1

逻辑说明 #

• 第一步先对y和x做非等值左连接，关联条件为x.id <= y.id，会把每个y.id对应的所有符合条件的x行全部关联出来
• 第二步用窗口函数ROW_NUMBER()，按y.id分组，组内按x.id降序排序，每个y.id分组下x.id最大的行排名为1
• 最后过滤仅保留排名为1的行，即可得到预期结果

执行后输出和要求一致：

id | val
1  | 100
2  | 100
3  | 300

方案2：优化实现（适用于x.id离散有序的场景，性能更高） #

如果x的id是离散有序的，可以先给每个x.id生成有效匹配区间，再和y做区间匹配，避免非等值连接产生大量中间行，性能提升明显：

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)),
x_with_range AS (
    SELECT 
        id AS start_id,
        LEAD(id, 1, 999999999) OVER (ORDER BY id) AS end_id,
        val
    FROM x
)
SELECT y.id, x_with_range.val
FROM y
LEFT JOIN x_with_range ON y.id >= start_id AND y.id < end_id

内容的提问来源于stack exchange，提问作者Mickael B.