Athena SQL需求：根据predicted值匹配最大original值的id与revenue

需求 #

将每行的predicted值与所有original值对比，找到不大于该predicted值的最大original值，并获取其对应的id和revenue，分别命名为pred_id、pred_revenue。

样本数据 #

original      id     revenue       predicted
---------------------------------------------
13106.21      3      20000           16206.50 
17852.21      4      30000           21206.50 
20542         1      70000           50365
40000         7      80000           18563 

期望输出 #

original       id     revenue   predicted  pred_id    pred_revenue
-------------------------------------------------------------------
13106.21      3     20000      16206.50    4            300000
17852.21      4     30000      21206.50    7            80000
20542         1     70000      50365       7            80000
40000         7     80000       18563      1            70000

尝试的SQL（未得到正确结果） #

SELECT
    original, id, revenue, predicted
FROM   
    (SELECT 
         *,
         MAX(original) OVER () AS max_original
     FROM   
         test)
WHERE 
    original >= predicted  

问题分析 #

原SQL仅筛选出original >= predicted的行，没有针对每行的predicted找到最大的不超过它的original值，也未关联对应的id和revenue，无法满足需求。

解决方案 #

以下两种方式均适用于Athena（基于Presto引擎）：

方式一：使用LATERAL JOIN（高效推荐） #

通过LATERAL子查询，为每行单独匹配符合条件的最大original对应数据：

SELECT
    t.original,
    t.id,
    t.revenue,
    t.predicted,
    matched.id AS pred_id,
    matched.revenue AS pred_revenue
FROM test t
CROSS JOIN LATERAL (
    -- 筛选出当前行predicted对应的最大original数据
    SELECT id, revenue
    FROM test
    WHERE original <= t.predicted
    ORDER BY original DESC
    LIMIT 1
) matched;

方式二：使用窗口函数排序筛选 #

通过笛卡尔积关联所有行，再用窗口函数为每组匹配结果排序取第一：

WITH ranked_matches AS (
    SELECT
        t.original,
        t.id,
        t.revenue,
        t.predicted,
        m.id AS pred_id,
        m.revenue AS pred_revenue,
        -- 按当前行分组，按original降序排序
        ROW_NUMBER() OVER (PARTITION BY t.id, t.predicted ORDER BY m.original DESC) AS rn
    FROM test t
    CROSS JOIN test m
    WHERE m.original <= t.predicted
)
-- 取每组排序后的第一行（最大original）
SELECT original, id, revenue, predicted, pred_id, pred_revenue
FROM ranked_matches
WHERE rn = 1;

注意事项 #

• 若存在多个original值相同且均为符合条件的最大值，ROW_NUMBER()会随机返回其中一行；若需保留所有符合条件的行，可替换为RANK()。

内容的提问来源于stack exchange，提问作者user19