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 ---no data found for this id
id, который я использовал в своем предложении WHERE, недействителен, и запрос не должен возвращать ни одной строки, но когда я использую LISTAGG или любую функцию агрегирования, он вернет 1 нулевую строку.
Почему это происходит?
Ответы 3
Вот как это работает: агрегатные функции возвращают NULL, если строки не выбраны, за исключением COUNT(), возвращающего 0.
Это не имеет ничего общего с аналитической функцией LISTAGG(). При выборе Агрегации, где условие без совпадающих строк не приведет к тому, что строки не будут выбраны - предложение Наличие будет. Вот как это выглядит, и без LISTAGG() то же самое...
WITH -- S a m p l e D a t a :
tbl AS
( Select 1 "A_NUMBER", 'A' "A_LETTER" From Dual Union All
Select 2 "A_NUMBER", 'B' "A_LETTER" From Dual
)
- агрегация с предложением NO WHERE и предложением NO HAVING
-- 1. no Where condition and no Having condition
Select Max(A_NUMBER) "MAX_NUMBER",
Max(A_LETTER) "MAX_LETTER",
ListAgg(A_LETTER, ', ') WITHIN GROUP (Order By A_LETTER) "LETTERS"
From tbl
/*
MAX_NUMBER MAX_LETTER LETTERS
---------- ---------- -----------
2 B A, B */
- агрегация с предложением WHERE, исключающая все строки (From) (как в вашем вопросе)
-- 2. where condition excluding all rows
Select Max(A_NUMBER) "MAX_NUMBER",
Max(A_LETTER) "MAX_LETTER",
ListAgg(A_LETTER, ', ') WITHIN GROUP (Order By A_LETTER) "LETTERS"
From tbl
Where A_NUMBER = 0
/* R e s u l t : (1 row with nulls)
MAX_NUMBER MAX_LETTER LETTERS
---------- ---------- -----------
Null Null Null */
- агрегирование с предложением HAVING, исключающее все строки
-- 3. having condition excluding all (aggregated) rows
Select Max(A_NUMBER) "MAX_NUMBER",
Max(A_LETTER) "MAX_LETTER",
ListAgg(A_LETTER, ', ') WITHIN GROUP (Order By A_LETTER) "LETTERS"
From tbl
Having Max(A_NUMBER) = 0
/* R e s u l t :
no rows selected */
Чтобы не получать строк в случае, подобном вопросу, вам, вероятно, следует использовать предложение WHERE и HAVING, как показано ниже.
Select Max(A_NUMBER) "MAX_NUMBER",
Max(A_LETTER) "MAX_LETTER",
ListAgg(A_LETTER, ', ') WITHIN GROUP (Order By A_LETTER) "LETTERS"
From tbl
Where A_NUMBER = 0
Having Count(*) != 0
Если вы используете функции агрегации (MAX, SUM, LISTAGG и т. д.) без включения предложения GROUP BY, тогда запрос всегда будет выводить одну строку, поскольку он работает со всем набором результатов.
Если вы добавите предложение GROUP BY, то запрос вернет по одной строке на группу при условии, что для этой группы существует набор входных строк.
Поэтому, если вы хотите, чтобы на выходе было ноль строк, когда на входе ноль строк, добавьте предложение GROUP BY:
SELECT MAX(g.name) AS good_name,
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
) AS information,
LISTAGG(
attr.name,
'-' ON OVERFLOW TRUNCATE
) AS attributes
FROM goods_cp cp
LEFT OUTER JOIN catalogues_properties attr
ON cp.catalogues_properties_id=attr.id
LEFT OUTER JOIN catalogues_properties inf
ON cp.catalogues_properties_description_id = TRIM(to_char(inf.id))
INNER JOIN goods g
ON g.id = cp.goods_id
WHERE cp.goods_id = 123456
GROUP BY cp.goods_id