如果返回的值为null,则PostgreSQL返回0 [英] postgresql return 0 if returned value is null
问题描述
我有一个查询返回avg(price)
I have a query that returns 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?
how to make it return 0 if no value is available?
推荐答案
使用 coalesce
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
恕我直言 COALESCE
不应与 AVG
,因为它会修改值。 NULL
表示未知,仅此而已。它不像在 SUM
中使用它。在此示例中,如果我们将 AVG
替换为 SUM
,则结果不会失真。将0加到总和上并不会伤害任何人,但计算未知值的平均值为0时,您不会获得真实的平均值。
IMHO COALESCE
should not be use with AVG
because it modifies the value. NULL
means unknown and nothing else. It's not like using it in SUM
. In this example, if we replace AVG
by SUM
, the result is not distorted. Adding 0 to a sum doesn't hurt anyone but calculating an average with 0 for the unknown values, you don't get the real average.
在这种情况下,我会在 WHERE
子句中添加 price IS NOT NULL
以避免这些未知值。
In that case, I would add price IS NOT NULL
in WHERE
clause to avoid these unknown values.
这篇关于如果返回的值为null,则PostgreSQL返回0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!