使用LEAD窗口函数时视图与子查询的结果差异问题

问题现象 #

使用LEAD窗口函数将下一条记录的load_timestamp设为effective_to列时，直接在CTE后过滤ID='234'的结果符合预期；但将相同逻辑创建为视图后再执行过滤查询，effective_to列的所有值均相同。

根因定位 #

视图定义中，内部CTE的名称与视图本身的名称重复（均为window_view），导致SQL解析器出现歧义，执行逻辑异常，进而计算出错误的effective_to值。

修正方案 #

方案1：移除不必要的CTE #

直接简化视图定义，避免名称冲突：

create or replace view window_view as
SELECT
    t1.*,
    t1.load_timestamp AS effective_from,
    LEAD(t1.load_timestamp) OVER (
        PARTITION BY t1.account_hash_id
        ORDER BY t1.load_timestamp
    ) AS effective_to
FROM base_table t1;

方案2：修改CTE名称 #

若需保留CTE结构，将内部CTE重命名为与视图不同的名称：

create or replace view window_view as
WITH temp_data AS (
    SELECT
        t1.*,
        t1.load_timestamp AS effective_from,
        LEAD(t1.load_timestamp) OVER (
            PARTITION BY t1.account_hash_id
            ORDER BY t1.load_timestamp
        ) AS effective_to
    FROM base_table t1
)
SELECT * FROM temp_data;

验证 #

执行以下查询即可得到正确结果：

SELECT EFFECTIVE_FROM, EFFECTIVE_TO 
FROM window_view
WHERE ID = '234';

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