将含关联子查询的MySQL语句转换为Presto/Athena兼容的ANSI SQL

原MySQL查询使用了关联子查询，但Presto/Athena不支持这类语法，需要改写为符合ANSI SQL标准的语句，原查询如下：

SELECT tA.uid, tA.dt, tA.val_A,
       AVG(val_B) AS val_C
   FROM 
(SELECT uid, dt, val_A,
       (SELECT dt FROM tableA ta1 
         WHERE ta1.uid=ta2.uid 
          AND ta1.dt > ta2.dt LIMIT 1) AS dtRg
  FROM tableA ta2) tA
LEFT JOIN tableB tB 
  ON tA.uid=tB.uid
AND tB.dt >= tA.dt
AND tB.dt < tA.dtRg
GROUP BY tA.uid, tA.dt, tA.val_A;

在Presto/Athena中运行时会抛出错误：

[Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 5:9: Given correlated subquery is not supported [Execution ID: 60a6f7e2-fb2b-44e0-a4f2-847ac669e905]

改写方案 #

原查询中关联子查询的作用是获取每个uid分组内，当前行dt之后的下一个最小日期作为dtRg，这可以用ANSI SQL标准的窗口函数LEAD()替代，改写后的语句如下：

SELECT 
    ta.uid, 
    ta.dt, 
    ta.val_A,
    AVG(tb.val_B) AS val_C
FROM (
    SELECT 
        uid, 
        dt, 
        val_A,
        -- 用LEAD窗口函数获取同uid下的下一个dt值
        LEAD(dt) OVER (PARTITION BY uid ORDER BY dt) AS dtRg
    FROM tableA
) ta
LEFT JOIN tableB tb 
    ON ta.uid = tb.uid
    AND tb.dt >= ta.dt
    AND (tb.dt < ta.dtRg OR ta.dtRg IS NULL)
GROUP BY ta.uid, ta.dt, ta.val_A;

说明 #

1. 窗口函数替代关联子查询：LEAD(dt) OVER (PARTITION BY uid ORDER BY dt)会按uid分组、dt排序，自动获取每组中当前行的下一行dt值，和原关联子查询的逻辑完全一致。
2. 处理最后一行的边界情况：当某uid的最后一行数据没有后续dt时，LEAD()会返回NULL，此时添加OR ta.dtRg IS NULL的条件，确保该行能关联tableB中所有dt >= ta.dt的数据，和原查询逻辑对齐。

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