我有一个查询返回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？
使用
coalesce
原文链接：https://www.f2er.com/postgresql/193605.htmlCOALESCE(value [,...])
06001
编辑
以下是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意味着unknow和没有别的。它不像在SUM中使用它。在此示例中，如果我们用SUM替换AVG，结果不会失真。将0添加到总和中不会伤害任何人，除非计算未知值的平均值为0，否则不会获得实际平均值。
在这种情况下，我会在WHERE子句中添加价格IS NOT NULL，以避免这些未知值。
