Snowflake SELECT与FROM子句列名解析冲突的解决方法问询

先看这段存在逻辑问题的Snowflake SQL代码：

WITH SAMPLE_DATA AS (
    SELECT * FROM VALUES 
        ('Voldemort', NULL),
        ('Harry','Potter'),
        ('Hagrid','') 
    AS PEOPLE(FIRST_NAME,SURNAME)
) SELECT 
    FIRST_NAME,
    COALESCE(SURNAME,'') SURNAME,
    IFF(SURNAME='',FIRST_NAME,CONCAT(FIRST_NAME,' ',SURNAME)) FULL_NAME, 
    CONCAT('Dear ',FULL_NAME,',') SALUTATION
FROM SAMPLE_DATA SD
ORDER BY SURNAME, FIRST_NAME;

问题根源 #

在SELECT列列表中，COALESCE(SURNAME,'') SURNAME之后对SURNAME的引用，会被Snowflake优先解析为CTE SAMPLE_DATA中的原列，而非刚计算出的清洗后列。比如Voldemort的原SURNAME是NULL，后续IFF(SURNAME='')判断的是原NULL值（NULL不等于空字符串），导致FULL_NAME错误拼接成Voldemort NULL，不符合预期。

临时修复的局限 #

若给清洗后的列起不同别名（比如SURNAME_CLEANED），虽然能解决解析问题，但结果集列名会变成SURNAME_CLEANED，无法满足「隐藏数据清洗过程、对外保持列名为SURNAME」的需求：

WITH SAMPLE_DATA AS (
    SELECT * FROM VALUES 
        ('Voldemort', NULL),
        ('Harry','Potter'),
        ('Hagrid','') 
    AS PEOPLE(FIRST_NAME,SURNAME)
) SELECT 
    FIRST_NAME,
    COALESCE(SURNAME,'') SURNAME_CLEANED,
    IFF(SURNAME_CLEANED='',FIRST_NAME,CONCAT(FIRST_NAME,' ',SURNAME_CLEANED)) FULL_NAME, 
    CONCAT('Dear ',FULL_NAME,',') SALUTATION
FROM SAMPLE_DATA SD
ORDER BY SURNAME_CLEANED, FIRST_NAME;

满足需求的可行方案 #

要在保持列名SURNAME的同时，让SELECT子句内正确引用清洗后的列，可采用以下几种方法：

1. 新增中间CTE/子查询

把数据清洗逻辑放到中间CTE中，后续查询直接引用处理后的列，对外仍保留SURNAME列名：

WITH SAMPLE_DATA AS (
    SELECT * FROM VALUES 
        ('Voldemort', NULL),
        ('Harry','Potter'),
        ('Hagrid','') 
    AS PEOPLE(FIRST_NAME,SURNAME),
    CLEANED_DATA AS (
        SELECT 
            FIRST_NAME,
            COALESCE(SURNAME,'') SURNAME
        FROM SAMPLE_DATA
    )
SELECT 
    FIRST_NAME,
    SURNAME,
    IFF(SURNAME='',FIRST_NAME,CONCAT(FIRST_NAME,' ',SURNAME)) FULL_NAME, 
    CONCAT('Dear ',FULL_NAME,',') SALUTATION
FROM CLEANED_DATA
ORDER BY SURNAME, FIRST_NAME;

2. 使用CROSS APPLY预计算清洗列

利用Snowflake支持的CROSS APPLY，在FROM子句中预先计算清洗后的列，SELECT里的SURNAME可直接引用处理后的值，同时对外列名保持SURNAME：

WITH SAMPLE_DATA AS (
    SELECT * FROM VALUES 
        ('Voldemort', NULL),
        ('Harry','Potter'),
        ('Hagrid','') 
    AS PEOPLE(FIRST_NAME,SURNAME)
) SELECT 
    SD.FIRST_NAME,
    CLEANED.SURNAME,
    IFF(CLEANED.SURNAME='',SD.FIRST_NAME,CONCAT(SD.FIRST_NAME,' ',CLEANED.SURNAME)) FULL_NAME, 
    CONCAT('Dear ',FULL_NAME,',') SALUTATION
FROM SAMPLE_DATA SD
CROSS APPLY (SELECT COALESCE(SD.SURNAME,'') SURNAME) CLEANED
ORDER BY CLEANED.SURNAME, SD.FIRST_NAME;

3. 重复清洗表达式（简单但冗余）

如果清洗逻辑不复杂，可直接在需要引用的地方重复COALESCE(SURNAME,'')，虽然代码冗余，但能快速实现需求：

WITH SAMPLE_DATA AS (
    SELECT * FROM VALUES 
        ('Voldemort', NULL),
        ('Harry','Potter'),
        ('Hagrid','') 
    AS PEOPLE(FIRST_NAME,SURNAME)
) SELECT 
    FIRST_NAME,
    COALESCE(SURNAME,'') SURNAME,
    IFF(COALESCE(SURNAME,'')='',FIRST_NAME,CONCAT(FIRST_NAME,' ',COALESCE(SURNAME,''))) FULL_NAME, 
    CONCAT('Dear ',FULL_NAME,',') SALUTATION
FROM SAMPLE_DATA SD
ORDER BY COALESCE(SURNAME,''), FIRST_NAME;

核心逻辑说明 #

Snowflake的SQL解析规则中，SELECT子句内的列别名优先级低于原表列，无法被同层级的其他表达式直接引用，因此必须通过中间层（CTE/子查询）、APPLY预计算或重复表达式的方式绕开这个限制。

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