使用LISTAGG聚合函数时无匹配数据却返回空行的原因

问题描述 #

查询语句如下：

SELECT  
    MAX(g.name) AS good_name,
    MAX(cp.goods_id) as c5good_id,
    LISTAGG(CASE 
                WHEN inf.id IS NULL 
                    THEN cp.catalogues_properties_description_id 
                    ELSE inf.name 
            END ,'-' ON OVERFLOW TRUNCATE) information, 
    LISTAGG(attr.name, '-' ON OVERFLOW TRUNCATE) attributes
FROM
    goods_cp cp 
LEFT JOIN 
    catalogues_properties attr ON cp.catalogues_properties_id=attr.id
LEFT JOIN 
    catalogues_properties inf ON cp.catalogues_properties_description_id = TRIM(to_char(inf.id))
JOIN
    goods g ON g.id = cp.goods_id
WHERE
    cp.goods_id = 123456  ---此ID无匹配数据

当WHERE子句中的ID无匹配数据时，本应返回无记录行，但使用聚合函数后却返回1条空记录行。

原因 #

这是聚合函数的特性导致的：

• 聚合函数（如MAX()、LISTAGG()）是对数据集进行分组计算，当WHERE条件没有匹配到任何行时，数据库会默认生成一个空分组来执行聚合计算。
• 对于空分组，MAX()会返回NULL，LISTAGG()在没有输入值时也会返回NULL，最终就会生成一行全为NULL的记录。

解决办法 #

可以通过以下两种方式避免返回空行：

方式1：添加HAVING子句过滤空分组 #

利用COUNT()函数判断分组内是否有实际数据，只有当存在匹配行时才返回结果：

SELECT  
    MAX(g.name) AS good_name,
    MAX(cp.goods_id) as c5good_id,
    LISTAGG(CASE 
                WHEN inf.id IS NULL 
                    THEN cp.catalogues_properties_description_id 
                    ELSE inf.name 
            END ,'-' ON OVERFLOW TRUNCATE) information, 
    LISTAGG(attr.name, '-' ON OVERFLOW TRUNCATE) attributes
FROM
    goods_cp cp 
LEFT JOIN 
    catalogues_properties attr ON cp.catalogues_properties_id=attr.id
LEFT JOIN 
    catalogues_properties inf ON cp.catalogues_properties_description_id = TRIM(to_char(inf.id))
JOIN
    goods g ON g.id = cp.goods_id
WHERE
    cp.goods_id = 123456
HAVING COUNT(*) > 0;  -- 仅当有匹配行时返回结果

方式2：用子查询先过滤数据，再聚合 #

先通过子查询获取匹配的行，再对结果进行聚合，如果子查询无数据，外层聚合也不会返回行：

SELECT  
    MAX(g.name) AS good_name,
    MAX(cp.goods_id) as c5good_id,
    LISTAGG(CASE 
                WHEN inf.id IS NULL 
                    THEN cp.catalogues_properties_description_id 
                    ELSE inf.name 
            END ,'-' ON OVERFLOW TRUNCATE) information, 
    LISTAGG(attr.name, '-' ON OVERFLOW TRUNCATE) attributes
FROM (
    SELECT cp.*, g.name, attr.name AS attr_name, inf.name AS inf_name
    FROM goods_cp cp 
    LEFT JOIN catalogues_properties attr ON cp.catalogues_properties_id=attr.id
    LEFT JOIN catalogues_properties inf ON cp.catalogues_properties_description_id = TRIM(to_char(inf.id))
    JOIN goods g ON g.id = cp.goods_id
    WHERE cp.goods_id = 123456
) t;

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