如果返回值为null,postgresql返回0

概述我有一个查询返回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 我有一个查询返回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

COALESCE(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)     )sHAVING COUNT(*) > 5

IMHO COALESCE不应与AVG一起使用，因为它会修改值。 NulL意味着unkNow和没有别的。它不像在SUM中使用它。在此示例中，如果我们用SUM替换AVG，结果不会失真。将0添加到总和中不会伤害任何人，除非计算未知值的平均值为0，否则不会获得实际平均值。

在这种情况下，我会在WHERE子句中添加价格IS NOT NulL，以避免这些未知值。

总结

以上是内存溢出为你收集整理的如果返回值为null,postgresql返回0全部内容，希望文章能够帮你解决如果返回值为null,postgresql返回0所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。