Postgresql возвращает 0, если возвращаемое значение равно null
У меня есть запрос, который возвращает avg (price)
select avg(price)
from(
select *, cume_dist() OVER (ORDER BY price desc) from web_price_scan
where listing_Type='AARM'
and u_kbalikepartnumbers_id = 1000307
and (EXTRACT(Day FROM (Now()-dateEnded)))*24 < 48
and price>( select avg(price)* 0.50
from(select *, cume_dist() OVER (ORDER BY price desc)
from web_price_scan
where listing_Type='AARM'
and u_kbalikepartnumbers_id = 1000307
and (EXTRACT(Day FROM (Now()-dateEnded)))*24 < 48
)g
where cume_dist < 0.50
)
and price<( select avg(price)*2
from( select *, cume_dist() OVER (ORDER BY price desc)
from web_price_scan
where listing_Type='AARM'
and u_kbalikepartnumbers_id = 1000307
and (EXTRACT(Day FROM (Now()-dateEnded)))*24 < 48
)d
where cume_dist < 0.50)
)s
having count(*) > 5
как сделать его возвратом 0, если значение не доступно?
Ответы
Ответ 1
используйте объединение
COALESCE(value [, ...])
The COALESCE function returns the first of its arguments that is not null.
Null is returned only if all arguments are null. It is often
used to substitute a default value for null values when data is
retrieved for display.
Изменить
Вот пример COALESCE с вашим запросом:
SELECT AVG( price )
FROM(
SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan
WHERE listing_Type = 'AARM'
AND u_kbalikepartnumbers_id = 1000307
AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
AND COALESCE( price, 0 ) > ( SELECT AVG( COALESCE( price, 0 ) )* 0.50
FROM ( SELECT *, cume_dist() OVER ( ORDER BY price DESC )
FROM web_price_scan
WHERE listing_Type='AARM'
AND u_kbalikepartnumbers_id = 1000307
AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
) g
WHERE cume_dist < 0.50
)
AND COALESCE( price, 0 ) < ( SELECT AVG( COALESCE( price, 0 ) ) *2
FROM( SELECT *, cume_dist() OVER ( ORDER BY price desc )
FROM web_price_scan
WHERE listing_Type='AARM'
AND u_kbalikepartnumbers_id = 1000307
AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
) d
WHERE cume_dist < 0.50)
)s
HAVING COUNT(*) > 5
IMHO COALESCE не следует использовать с AVG, потому что он изменяет значение. NULL означает неизвестное и больше ничего. Это не похоже на использование в SUM. В этом примере, если мы заменим AVG на SUM, результат не будет искажен. Добавление 0 к сумме никому не повредит, но вычисляя среднее с 0 для неизвестных значений, вы не получите реальное среднее.
В этом случае я бы добавил price IS NOT NULL в предложение WHERE, чтобы избежать этих неизвестных значений.
Ответ 2
(этот ответ был добавлен, чтобы предоставить более короткие и более общие примеры вопроса - без включения всех деталей конкретного случая в исходный вопрос).
Здесь есть две различные "проблемы": первая - если в таблице или подзапросе нет строк, вторая - если в запросе есть значения NULL.
Для всех версий, которые я тестировал, postgres и mysql будут игнорировать все значения NULL при усреднении и будут возвращать NULL, если нечего усреднять. Обычно это имеет смысл, поскольку NULL следует считать "неизвестным". Если вы хотите переопределить это, вы можете использовать коалесценцию (как предложено Люком М).
$ create table foo (bar int);
CREATE TABLE
$ select avg(bar) from foo;
avg
-----
(1 row)
$ select coalesce(avg(bar), 0) from foo;
coalesce
----------
0
(1 row)
$ insert into foo values (3);
INSERT 0 1
$ insert into foo values (9);
INSERT 0 1
$ insert into foo values (NULL);
INSERT 0 1
$ select coalesce(avg(bar), 0) from foo;
coalesce
--------------------
6.0000000000000000
(1 row)
конечно, "из foo" можно заменить на "из (... здесь любая сложная логика...) как foo"
Теперь, должна ли строка NULL в таблице считаться 0? Затем необходимо использовать объединение внутри вызова avg.
$ select coalesce(avg(coalesce(bar, 0)), 0) from foo;
coalesce
--------------------
4.0000000000000000
(1 row)
Ответ 3
Я могу придумать 2 способа добиться этого:
Синтаксис:
IFNULL(expression, alt_value)
Пример IFNULL() с вашим запросом:
SELECT AVG( price )
FROM(
SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan
WHERE listing_Type = 'AARM'
AND u_kbalikepartnumbers_id = 1000307
AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
AND IFNULL( price, 0 ) > ( SELECT AVG( IFNULL( price, 0 ) )* 0.50
FROM ( SELECT *, cume_dist() OVER ( ORDER BY price DESC )
FROM web_price_scan
WHERE listing_Type='AARM'
AND u_kbalikepartnumbers_id = 1000307
AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
) g
WHERE cume_dist < 0.50
)
AND IFNULL( price, 0 ) < ( SELECT AVG( IFNULL( price, 0 ) ) *2
FROM( SELECT *, cume_dist() OVER ( ORDER BY price desc )
FROM web_price_scan
WHERE listing_Type='AARM'
AND u_kbalikepartnumbers_id = 1000307
AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
) d
WHERE cume_dist < 0.50)
)s
HAVING COUNT(*) > 5
Синтаксис:
COALESCE(val1, val2, ...., val_n)
Пример COALESCE() с вашим запросом:
SELECT AVG( price )
FROM(
SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan
WHERE listing_Type = 'AARM'
AND u_kbalikepartnumbers_id = 1000307
AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
AND COALESCE( price, 0 ) > ( SELECT AVG( COALESCE( price, 0 ) )* 0.50
FROM ( SELECT *, cume_dist() OVER ( ORDER BY price DESC )
FROM web_price_scan
WHERE listing_Type='AARM'
AND u_kbalikepartnumbers_id = 1000307
AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
) g
WHERE cume_dist < 0.50
)
AND COALESCE( price, 0 ) < ( SELECT AVG( COALESCE( price, 0 ) ) *2
FROM( SELECT *, cume_dist() OVER ( ORDER BY price desc )
FROM web_price_scan
WHERE listing_Type='AARM'
AND u_kbalikepartnumbers_id = 1000307
AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
) d
WHERE cume_dist < 0.50)
)s
HAVING COUNT(*) > 5
