Databricks SQL中如何基于同列前序计算值实现列值计算？

需求 #

需要编写Databricks SQL查询，实现用同列中已计算的前序值来计算当前列的值。

第一次尝试及报错 #

最初的查询试图用窗口函数LAG引用刚定义的列别名，但Databricks不支持这种写法：

Select ID
,n
,CASE WHEN n=1 THEN col1 ELSE col1+col3 END AS col2
,LAG(col2) OVER (PARTITION BY ID ORDER BY n) AS col3
from table

报错信息翻译为：

不支持该特性：在窗口表达式"LAG(lateralAliasReference(col2)) OVER (PARTITION BY ID ORDER BY n ASC NULLS FIRST unspecifiedframe$())"中引用横向列别名col2。

第二次尝试（递归CTE）及报错 #

改用递归CTE的方式，但因缺少必要关键字导致无法识别递归表：

table2 AS (SELECT *
,col1  AS col2
FROM table
WHERE n=1
UNION ALL
SELECT curr.*, curr.col1+prev.col2 AS col2
FROM table AS curr
INNER JOIN table2 AS prev
ON curr.Id = prev.Id AND curr.n= prev.n+ 1
)
SELECT *
FROM table2

报错提示：在“INNER JOIN table2 AS prev”处无法找到table2。

正确解决方案 #

Databricks SQL的递归CTE必须用WITH RECURSIVE关键字声明，调整后的查询如下：

WITH RECURSIVE table2 AS (
    -- 基础部分：初始化每个ID的第一条记录（n=1）的col2为col1
    SELECT 
        ID,
        n,
        col1,
        col1 AS col2
    FROM table
    WHERE n = 1
    UNION ALL
    -- 递归部分：关联前一条记录，用前序col2计算当前col2
    SELECT 
        curr.ID,
        curr.n,
        curr.col1,
        curr.col1 + prev.col2 AS col2
    FROM table AS curr
    INNER JOIN table2 AS prev
        ON curr.ID = prev.ID 
        AND curr.n = prev.n + 1
)
SELECT ID, n, col1, col2
FROM table2
ORDER BY ID, n;

关键说明 #

• 必须通过WITH RECURSIVE明确声明递归CTE，否则Databricks无法识别递归引用的表。
• 递归的基础项和递归项必须保证列数、数据类型完全一致，避免类型不兼容错误。
• 查询结果按ID和n排序，确保计算顺序正确。

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