在将LISTAGG函数与DISTINCT一起使用时需要帮助 [英] Need help in using LISTAGG function with DISTINCT

问题描述

Select ID, 
 LISTAGG(DISTINCT VIEW_NM, ',') WITHIN GROUP(ORDER BY CAST(VISIT_PAGE_NBR as INT)) 
 AS No_Of_Views
FROM db_name.schema_name.tbl_name 
WHERE FLG_COLUMN = '0'
AND SOURCE_CD NOT IN ('1','2','3','4')
AND DATE_CR = '2022-01-01'
GROUP BY ID;

我希望VIEW_NM列值以ASC或DESC顺序显示。但是当我执行不同的VIEW_NM时，我得到的是-它不是有效的ORDER BY表达式。在修复此问题时需要帮助。

此外，我不希望VIEW_NM列的值重复。请建议如何做到这一点..

推荐答案

使用CTE过滤每个ID多个VIEW_NM(选择VIST_PAGE_NBR最低的)：

WITH cte AS (
  SELECT ID, VIEW_NM, VISIT_PAGE_NBR::INT AS VISIT_PAGE_NBR
  FROM db_name.schema_name.tbl_name 
  WHERE FLG_COLUMN = '0'
    AND SOURCE_CD NOT IN ('1','2','3','4')
    AND DATE_CR = '2022-01-01'
  QUALIFY ROW_NUMBER() OVER(PARTITION BY ID,VIEW_NM ORDER BY VISIT_PAGE_NBR) = 1
)
SELECT ID,
    LISTAGG(VIEW_VM, ',') WITHIN GROUP(ORDER BY VISIT_PAGE_NBR) AS No_Of_Views
FROM cte
GROUP BY ID;

这篇关于在将LISTAGG函数与DISTINCT一起使用时需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！