Vertica中多LISTAGG语句报错求助：含DISTINCT及WITHIN GROUP问题

问题1：多LISTAGG(DISTINCT)报错处理 #

Vertica限制同一查询中不能同时使用多个带DISTINCT的用户自定义聚合函数（LISTAGG属于此类），直接编写多个LISTAGG(DISTINCT)会触发5366错误，可通过以下两种方案解决：

方法1：子查询先去重再聚合 #

先对每个ID对应的serv_yr和serv_yrmo分别去重，再关联结果完成聚合：

WITH distinct_yr AS (
    SELECT ID, serv_yr
    FROM table_x
    GROUP BY ID, serv_yr
),
distinct_yrmo AS (
    SELECT ID, serv_yrmo
    FROM table_x
    GROUP BY ID, serv_yrmo
),
agg_yr AS (
    SELECT ID, LISTAGG(serv_yr) AS SRV_YR
    FROM distinct_yr
    GROUP BY ID
),
agg_yrmo AS (
    SELECT ID, LISTAGG(serv_yrmo) AS SRV_YRMO
    FROM distinct_yrmo
    GROUP BY ID
)
SELECT a.ID, a.SRV_YR, b.SRV_YRMO
FROM agg_yr a
JOIN agg_yrmo b ON a.ID = b.ID;

方法2：窗口函数实现去重聚合 #

通过窗口函数标记去重行，再结合LISTAGG完成聚合：

WITH ranked_data AS (
    SELECT 
        ID,
        serv_yr,
        serv_yrmo,
        ROW_NUMBER() OVER (PARTITION BY ID, serv_yr ORDER BY serv_yr) AS rn_yr,
        ROW_NUMBER() OVER (PARTITION BY ID, serv_yrmo ORDER BY serv_yrmo) AS rn_yrmo
    FROM table_x
),
aggregated AS (
    SELECT 
        ID,
        LISTAGG(CASE WHEN rn_yr = 1 THEN serv_yr END) OVER (PARTITION BY ID) AS SRV_YR,
        LISTAGG(CASE WHEN rn_yrmo = 1 THEN serv_yrmo END) OVER (PARTITION BY ID) AS SRV_YRMO
    FROM ranked_data
)
SELECT DISTINCT ID, SRV_YR, SRV_YRMO
FROM aggregated;

问题2：WITHIN GROUP (ORDER BY)语法适配 #

Vertica的LISTAGG不支持Oracle风格的WITHIN GROUP (ORDER BY)语法，要实现聚合排序需求，可采用以下两种方式：

方式1：窗口函数中指定排序 #

使用窗口函数形式的LISTAGG，直接在窗口子句中添加排序规则：

-- 替换Oracle的WITHIN GROUP语法
SELECT DISTINCT
    ID,
    LISTAGG(serv_yr, ',') OVER (PARTITION BY ID ORDER BY serv_yr) AS SRV_YR
FROM table_x;

方式2：子查询先排序再聚合 #

先在子查询中对数据按目标规则排序，再执行聚合操作：

WITH sorted_data AS (
    SELECT ID, serv_yr
    FROM table_x
    ORDER BY ID, serv_yr
)
SELECT ID, LISTAGG(serv_yr) AS SRV_YR
FROM sorted_data
GROUP BY ID;

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