FIRST_VALUE生成列嵌套LEAD报窗口函数不可嵌套错误如何解决

解决方案 #

SQL 不支持在同一层 SELECT 语句中嵌套调用窗口函数，你只需要把两层窗口计算拆分到不同查询层级即可，推荐使用 CTE 或者子查询实现：

基于 CTE 实现的修正代码

WITH base_data AS (
    SELECT 
        *,
        FIRST_VALUE(placed_at IGNORE NULLS) 
        OVER (PARTITION BY category ORDER BY timestamp ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
        AS purchased_at
    FROM `table`
)
SELECT 
    *,
    (condition1 > 0) AND (LEAD(purchased_at, 1) OVER (PARTITION BY category ORDER BY timestamp) IS NULL) 
    AS has_led_to_purchase
FROM base_data;

逻辑说明

• 内层 CTE 先完成purchased_at字段的计算，此时该字段已经是 CTE 输出的普通字段，不再是窗口函数的动态计算结果
• 外层查询调用 LEAD 函数时，直接读取已经计算完成的purchased_at值，不会触发嵌套窗口函数的报错
• 同时修正了你原代码中多余的括号和has_led_to_purchase后面的标点语法错误

如果你的SQL环境不支持CTE，也可以换成子查询写法，逻辑完全一致：

SELECT 
    *,
    (condition1 > 0) AND (LEAD(purchased_at, 1) OVER (PARTITION BY category ORDER BY timestamp) IS NULL) 
    AS has_led_to_purchase
FROM (
    SELECT 
        *,
        FIRST_VALUE(placed_at IGNORE NULLS) 
        OVER (PARTITION BY category ORDER BY timestamp ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
        AS purchased_at
    FROM `table`
) t

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