Db2中在公共表表达式(CTE)内使用窗口函数报错的咨询

我在Db2 11.5.7的公共表表达式(CTE)中尝试编写窗口函数时遇到了一系列意外错误，以下是具体场景及问题：

可正常运行的查询 #

WITH dummy AS(
    SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
    UNION ALL
    SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1

),
solution(rowValue, phase) AS (
    SELECT b.rowValue, phase FROM dummy a,
        TABLE(
        select a.rowValue AS rowValue
        from dummy as b
        ) b
    WHERE phase = 0
) 
SELECT * FROM solution WHERE phase = 1;

执行报错的查询（聚合函数引用外层列） #

WITH dummy AS(
    SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
    UNION ALL
    SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1

),
solution(rowValue, phase) AS (
    SELECT b.rowValue, phase FROM dummy a,
        TABLE(
        select max(a.rowValue) AS rowValue
        from dummy as b
        ) b
    WHERE phase = 0
) 
SELECT * FROM solution WHERE phase = 0;

错误信息：

SQL0206N "A.ROWVALUE" is not valid in the context where it is used.
SQLSTATE=42703

可正常运行的变体查询 #

标量运算版本 #

WITH dummy AS(
    SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
    UNION ALL
    SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1

),
solution(rowValue, phase) AS (
    SELECT b.rowValue, phase FROM dummy a,
        TABLE(
        select max(a.rowValue*b.phase) AS rowValue
        from dummy as b
        ) b
    WHERE phase = 0
) 
SELECT * FROM solution WHERE phase = 0;

带GROUP BY版本 #

WITH dummy AS(
    SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
    UNION ALL
    SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1

),
solution(rowValue, phase) AS (
    SELECT b.rowValue, phase FROM dummy a,
        TABLE(
        select max(a.rowValue*b.phase) AS rowValue
        from dummy as b
        group by a.phase
        ) b
    WHERE phase = 0
) 
SELECT * FROM solution WHERE phase = 0;

尝试窗口函数的报错查询 #

WITH dummy AS(
    SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
    UNION ALL
    SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1

),
solution(rowValue, phase) AS (
    SELECT b.rowValue, phase FROM dummy a,
        TABLE(
        select max(a.rowValue*b.phase) over (partition by a.phase) AS rowValue
        from dummy as b
        ) b
    WHERE phase = 0
) 
SELECT * FROM solution WHERE phase = 0;

错误信息：

SQL0206N "ROWVALUE" is not valid in the context where it is used.
SQLSTATE=42703

a. 报错原因解释 #

• 聚合函数引用外层列的错误：Db2中，当子查询（此处为TABLE()内的子查询）使用聚合函数时，外层表的列（如a.rowValue）若未参与分组或作为聚合函数的参数，会被判定为无效。第一个可运行的查询未使用聚合函数，属于关联子查询，Db2允许直接引用外层列；但使用max()后子查询变为聚合查询，a.rowValue既不在GROUP BY中，也不是聚合函数的参数，违反了Db2的聚合查询规则，因此报错。
• 标量运算可运行的原因：当a.rowValue与b.phase进行乘法运算后作为max()的参数时，Db2会将a.rowValue视为外层传入的常量值，允许其在聚合函数中参与运算。
• 窗口函数报错的原因：窗口函数的OVER()子句中引用a.phase时，Db2无法正确解析外层表列在窗口上下文的有效性；同时，窗口函数生成的列命名为rowValue，与外层表的rowValue产生命名冲突，导致Db2无法区分，进而报错"ROWVALUE"无效（这也是报错信息与预期不同的原因）。

b. 替代语法实现 #

方案1：通过关联条件传递外层列值

WITH dummy AS(
    SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
    UNION ALL
    SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1
),
solution(rowValue, phase) AS (
    SELECT b.calculated_rowValue, a.phase 
    FROM dummy a
    CROSS JOIN TABLE(
        SELECT MAX(d.rowValue * d.phase) OVER (PARTITION BY d.phase) AS calculated_rowValue
        FROM dummy d
        WHERE d.phase = a.phase -- 通过关联条件传递外层phase值
    ) b
    WHERE a.phase = 0
) 
SELECT * FROM solution WHERE phase = 0;

方案2：提前在CTE中计算窗口聚合结果

WITH dummy AS(
    SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
    UNION ALL
    SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1
),
agg_dummy AS(
    SELECT 
        MAX(rowValue * phase) OVER (PARTITION BY phase) AS calculated_rowValue,
        phase
    FROM dummy
),
solution(rowValue, phase) AS (
    SELECT ad.calculated_rowValue, a.phase
    FROM dummy a
    JOIN agg_dummy ad ON a.phase = ad.phase
    WHERE a.phase = 0
) 
SELECT * FROM solution WHERE phase = 0;

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