雪花云数据平台-在使用LISTAGG时需要帮助

Question

WITH CTE AS (
SELECT
id,
c_no,
TRY_CAST(B.p_no as INTEGER) as p_no
FROM db_name.schema_name.tbl_1 A
JOIN db_name.schema_name.tbl_2 B
ON B.id_col = A.id_col
WHERE flg_col = '0'
AND cd NOT IN ('1','2','3','4')
AND DATE = '2022-02-02'
AND id='12345678'
ORDER BY p_no
)
SELECT
id,
LISTAGG (distinct c_no , ',') WITHIN GROUP (ORDER BY c_no) AS c01
FROM CTE
GROUP BY id;

当我运行上面的查询时，我得到的结果是

Row           ID             C01
1             01            110,118,id_not_found,no_record
2             02            id_found
3             03            no_record

我只想在结果中显示C01列中的数值以及相应的ID。我想忽略这两个值-id_not_found&第一行无记录。

但在第二和第三阶段，数值应该保持不变。

有什么建议吗。

Answer 1

尝试使用Try_to_NUMBER函数：

WITH CTE AS (
SELECT
id,
c_no,
TRY_CAST(B.p_no as INTEGER) as p_no
FROM db_name.schema_name.tbl_1 A
JOIN db_name.schema_name.tbl_2 B
ON B.id_col = A.id_col
WHERE flg_col = '0'
AND cd NOT IN ('1','2','3','4')
AND DATE = '2022-02-02'
AND id='12345678'
ORDER BY p_no
)
SELECT
id,
IFNULL(LISTAGG(distinct TRY_TO_NUMBER(c_no) , ',') WITHIN GROUP(ORDER BY TRY_TO_NUMBER(c_no)), c_no) AS c01
FROM CTE
GROUP BY id;

Answer 2

描述它的方式是，如果"存在任意数量的包含号，其他所有数据都是有效的"；

使用演示代码的最小SQL，我们在CTE(可以移动到您的CTE(中进行一些预处理

WITH cte(id, c_no) AS (
SELECT * FROM VALUES
(01, '110'),
(01, '118'),
(01, 'id_not_found'),
(01, 'no_record'),
(02, 'id_found'),
(03, 'no_record')
), pre_condition AS (
SELECT *,
try_to_number(c_no) as c_no_as_num
FROM cte
)
SELECT
id,
count(c_no_as_num) as count_of_nums,
LISTAGG (distinct c_no , ',') WITHIN GROUP (ORDER BY c_no) AS c01_all,
LISTAGG (distinct c_no_as_num , ',') WITHIN GROUP (ORDER BY c_no_as_num) AS c01_nums,
IFF(count_of_nums>0, c01_nums, c01_all) AS c01
FROM pre_condition
GROUP BY id
ORDER BY id;

我们在C01:中得到您想要的答案

>C01_NUMS<101118>id-found

ID	COUNT_OF_NUMS	C01_ALL	C01
1	2	110118，id_not_found，no_record	110118
2	0	id_found
3	0	无记录