LISTAGG函数聚合时如何排除Acc Nbr Not Enterered值

问题说明 #

使用LISTAGG函数做分组字符串拼接时，返回结果包含不需要展示的无效账号占位文本，未达到预期输出要求。

原执行SQL #

SELECT pid
       ,LISTAGG(DISTINCT acc_no, ',') WITHIN GROUP(ORDER BY acc_no) 
        AS acc_no_txt
FROM
(SELECT pid
      ,CASE WHEN acc_no_v01 IN ('not found','blank','nil-value','',' ','null') THEN 'Acc Nbr Not Enterered'
       ELSE acc_no_v01
       END
       AS acc_no
      ,MIN(TRY_TO_NUMBER(T2.vp_no)) AS vp_no
FROM table_1 T1
JOIN table_2 T2
ON T2.click_stream_integration_id = T1.click_stream_integration_id
WHERE T2.date = '2022-01-01'
AND pid='123456789'
GROUP BY pid,acc_no_v01
ORDER BY TRY_TO_NUMBER(vp_no)
)
GROUP BY pid;

实际运行结果 #

PID                          ACC_NO_TXT
123456789              12244059141,Acc Nbr Not Enterered

预期输出结果 #

PID                          ACC_NO_TXT
123456789              12244059141

调整方法 #

问题原因：内层子查询将无效账号统一转换为固定字符串Acc Nbr Not Enterered，该值会被LISTAGG识别为有效值参与拼接，导致结果不符合预期，可通过以下两种方式调整：

• 推荐方案：调整内层CASE逻辑，无效值返回NULL
  LISTAGG函数默认自动忽略NULL值，不会将NULL拼入最终结果，修改后SQL如下：

  SELECT pid
         ,LISTAGG(DISTINCT acc_no, ',') WITHIN GROUP(ORDER BY acc_no) 
          AS acc_no_txt
  FROM
  (SELECT pid
        ,CASE WHEN acc_no_v01 IN ('not found','blank','nil-value','',' ','null') THEN NULL -- 无效值返回NULL，不生成占位字符串
         ELSE acc_no_v01
         END
         AS acc_no
        ,MIN(TRY_TO_NUMBER(T2.vp_no)) AS vp_no
  FROM table_1 T1
  JOIN table_2 T2
  ON T2.click_stream_integration_id = T1.click_stream_integration_id
  WHERE T2.date = '2022-01-01'
  AND pid='123456789'
  GROUP BY pid,acc_no_v01
  )
  GROUP BY pid;
  

  子查询中原有的ORDER BY TRY_TO_NUMBER(vp_no)无实际作用，派生表的排序不会被外层分组逻辑继承，可删除减少不必要的计算开销。

• 兼容方案：外层聚合前过滤无效占位行
  如果业务逻辑需要在内层保留Acc Nbr Not Enterered字段做其他计算，可以在外层执行聚合前增加过滤条件，直接剔除占位值对应的行：

  SELECT pid
         ,LISTAGG(DISTINCT acc_no, ',') WITHIN GROUP(ORDER BY acc_no) 
          AS acc_no_txt
  FROM
  (SELECT pid
        ,CASE WHEN acc_no_v01 IN ('not found','blank','nil-value','',' ','null') THEN 'Acc Nbr Not Enterered'
         ELSE acc_no_v01
         END
         AS acc_no
        ,MIN(TRY_TO_NUMBER(T2.vp_no)) AS vp_no
  FROM table_1 T1
  JOIN table_2 T2
  ON T2.click_stream_integration_id = T1.click_stream_integration_id
  WHERE T2.date = '2022-01-01'
  AND pid='123456789'
  GROUP BY pid,acc_no_v01
  )
  WHERE acc_no != 'Acc Nbr Not Enterered' -- 聚合前过滤掉占位值记录
  GROUP BY pid;
  

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