Synapse计算复合累计值报错：递归CTE当前版本不支持

问题背景 #

需要计算复合累计值，公式为：(value + 1) * (previous_value + 1) - 1。尝试使用递归CTE实现时，收到报错：Recursive common table expressions are not supported in this version（递归公共表表达式在此版本中不受支持）。

原尝试的SQL代码：

WITH cte AS (
    SELECT date, value, ROW_NUMBER() OVER (ORDER BY date) AS seqnum
    FROM table_name
), cte2 AS (
    SELECT cte.date, cte.value, cte.seqnum
    FROM cte
    WHERE seqnum = 1
    UNION ALL
    SELECT cte.date, (cte.value + 1) * (cte2.value + 1) - 1, cte.seqnum
    FROM cte2
    JOIN cte ON cte.seqnum = cte2.seqnum + 1
)
SELECT cte2.date, cte2.value
FROM cte2;

解决方案 #

Azure Synapse Analytics的SQL池不支持递归CTE，可通过以下几种方式替代实现：

方法1：数学转换法（推荐，适用于value+1>0的场景） #

原公式本质是对value + 1做累计乘积后减1。利用对数与指数的转换，将累计乘积转为累计求和，SQL池支持这种窗口函数操作：

SELECT 
    date,
    value,
    -- 计算累计乘积后减1
    EXP(SUM(LN(value + 1)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) - 1 AS compound_value
FROM table_name
ORDER BY date;

注意：若存在value + 1 <= 0的情况，对数运算会报错，此时需改用其他方法。

方法2：自定义聚合函数 #

若需处理value + 1 <= 0的场景，可在Synapse中创建CLR自定义聚合函数实现累计乘积逻辑，之后在查询中调用该函数完成计算。

方法3：切换到Synapse Spark池 #

若工作负载允许，Synapse Spark池支持递归CTE，原代码可直接运行；也可使用Spark SQL的aggregate函数简化实现：

WITH ordered_data AS (
    SELECT date, value, 
           ROW_NUMBER() OVER (ORDER BY date) AS seqnum
    FROM table_name
)
SELECT 
    date,
    value,
    aggregate(
        collect_list(value + 1) OVER (ORDER BY date),
        1.0,
        (acc, x) -> acc * x
    ) - 1 AS compound_value
FROM ordered_data
ORDER BY date;

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