Snowflake中结合Substring等条件使用List_Agg未得预期结果排查

现有一张业务表，col_1、col_2字段样例数据如下：

需要新增result_col字段，计算规则如下：

• 按col_2字段分区计算
• 若当前行col_1值与col_2值相等，result_col赋值为NULL
• 其余行的result_col取同分区下所有col_1 != col_2的记录，提取col_1前2位字符，去重后用逗号拼接

期望输出样例如下：

原有实现SQL如下，执行后无法得到预期结果：

with result AS (
SELECT 
DISTINCT
P.col_1,
    P.col_2 ,
    CASE WHEN TRIM(P.col_1)=TRIM(P.col_2) THEN 'NULL'
   WHEN  trim(P.col_1) != trim(P.col_2) and P.col_1 NOT IN (SELECT DISTINCT s.col_2 FROM 
   sample s) 
   THEN   LISTAGG(DISTINCT SUBSTR(col_1,1,2)  ,', ') OVER(PARTITION BY P.col_2) 
   END   result_col
   FROM  table P )select * from result where i_part='6817'

• 空值语义错误：将SQL原生的NULL空值写为字符串'NULL'，后者是普通文本值，不符合需求要求的空值输出。
• 多余无效判断：添加了无业务逻辑的P.col_1 NOT IN (SELECT DISTINCT s.col_2 FROM sample s)条件，需求仅需判断当前行col_1与col_2是否相等，不需要关联其他表校验col_1的取值，该条件会导致合法行无法进入拼接逻辑。
• 拼接逻辑未过滤无效行：LISTAGG开窗时没有排除同分区下col_1 = col_2的记录，会把这类行的col_1前两位也拼入结果，比如6817分区会错误拼接出68,AB,CD。
• 拼接分隔符错误：LISTAGG指定的分隔符为, （逗号+空格），与需求要求的无空格逗号分隔不符，会输出带空格的结果。
• 过滤字段不存在：末尾查询使用了原表不存在的i_part字段做过滤，会直接触发字段不存在的报错。
• 语法风险：将表名写为SQL保留关键字table，且SUBSTR(col_1,1,2)未指定表别名，容易触发语法错误或字段歧义。

WITH valid_prefix AS (
    SELECT 
        col_1,
        col_2,
        LISTAGG(DISTINCT SUBSTR(col_1, 1, 2), ',') 
            OVER(PARTITION BY col_2) AS result_col
    FROM your_table -- 替换为实际业务表名
    WHERE TRIM(col_1) != TRIM(col_2)
)
SELECT 
    t1.col_1,
    t1.col_2,
    CASE WHEN TRIM(t1.col_1) = TRIM(t1.col_2) THEN NULL
         ELSE t2.result_col
    END AS result_col
FROM your_table t1
LEFT JOIN valid_prefix t2 
ON t1.col_1 = t2.col_1 AND t1.col_2 = t2.col_2
-- 若需要过滤指定分区，放开下方注释即可
-- WHERE t1.col_2 = '6817'
;

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