sql - 在将 LISTAGG 或 REGEXP_REPLACE 与 DISTINCT 函数结合使用时需要帮助

Question

SELECT  distinct ID,LISTAGG(to_varchar(CREATE_TS, 'mm/dd/yyyy hh:mi:ss'), ',') 
WITHIN GROUP(ORDER BY CAST(PAGE_NBR as INT)) AS No_Of_Views
FROM db_name.schema_name.tbl
WHERE date_created = '2022-01-21'
AND flg_col = '0'
AND src_id NOT IN ('0','3','4','5')
AND ID='0127435345345'
group by ID;

当我运行上面的查询时，我得到了不同的值

21/01/2022 20:21:17,21/01/2022 20:21:31,21/01/2022 20:22:23,21/01/2022 20:22:33

当我尝试使用 CTE 运行相同的查询时，我没有得到不同的结果。有没有办法使用 CTE 形成一个查询，该查询将获取不同的值:

WITH CTE AS (
SELECT
 ID,CREATE_TS,
PAGE_NBR::INT AS PAGE_NBR
FROM db_name.schema_name.tbl 
WHERE flg_col = '0'
AND src_id NOT IN ('0','3','4','5')
AND CREATE_DT = ''2022-21-01''
AND ID='0127435345345'
ORDER BY PAGE_NBR
)
SELECT
 ID,
LISTAGG(to_varchar(CREATE_TS, 'mm/dd/yyyy hh:mi:ss'), ',') WITHIN GROUP(ORDER BY CREATE_TS) AS No_Of_Views 
FROM CTE
GROUP BY ID;

当我在 LISTAGG 中使用不同的 ie 时。 LISTAGG(to_varchar(CREATE_TS, 'mm/dd/yyyy hh:mi:ss'), ',')我收到 SQL 编译错误:[CTE.CREATE_TS] 不是有效的表达式顺序

我希望得到与第一个查询相同的结果，但我需要尝试第二种方法..

Answer 1

首先，您的第二个陈述与第一个不同。第一个有:

LISTAGG(to_varchar(CREATE_TS, 'mm/dd/yyyy hh:mi:ss'), ',') 
WITHIN GROUP(ORDER BY CAST(PAGE_NBR as INT)) AS No_Of_Views

第二个有:

LISTAGG(to_varchar(CREATE_TS, 'mm/dd/yyyy hh:mi:ss'), ',') WITHIN GROUP(ORDER BY CREATE_TS) AS No_Of_Views 

关于 DISTINCT 错误，如文档中所述，如果您指定 DISTINCT 和 WITHIN GROUP，两者必须引用同一列。

https://docs.snowflake.com/en/sql-reference/functions/listagg.html#usage-notes

所以你最后的陈述应该是这样的:

...
SELECT
 ID,
LISTAGG(to_varchar(CREATE_TS, 'mm/dd/yyyy hh:mi:ss'), ',') WITHIN GROUP(ORDER BY to_varchar(CREATE_TS, 'mm/dd/yyyy hh:mi:ss')) AS No_Of_Views 
FROM CTE
GROUP BY ID;