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

发布时间：2020-12-13 16:48:59 所属栏目：百科来源：网络整理

导读：我有一个查询返回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 pric

我有一个查询返回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)
     )s
HAVING COUNT(*) > 5

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!