首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >如果返回值为空,则postgresql返回0

如果返回值为空,则postgresql返回0
EN

Stack Overflow用户
提问于 2012-06-13 09:17:00
回答 3查看 168.3K关注 0票数 125

我有一个返回avg(price)的查询

代码语言:javascript
复制
  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?

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2012-06-13 09:23:44

使用coalesce

代码语言:javascript
复制
COALESCE(value [, ...])

函数返回第一个非null的参数。仅当所有参数都为null时才返回Null。当检索要显示的数据时,它通常用于替换空值的默认值。

编辑

下面是您的查询中的COALESCE示例:

代码语言:javascript
复制
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不能与AVG一起使用COALESCE,因为它会修改该值。NULL的意思是“未知”,没有其他意思。这与在SUM中使用它不同。在本例中,如果我们用SUM替换AVG,结果不会失真。总和加上0不会伤害任何人,但用0计算未知值的平均值,你得不到真正的平均值。

在这种情况下,我会在WHERE子句中添加price IS NOT NULL来避免这些未知值。

票数 213
EN

Stack Overflow用户

发布于 2012-06-13 14:38:22

(添加此答案是为了向问题提供更简短和更通用的示例-不包括原始问题中所有特定于案例的细节)。

这里有两个不同的“问题”,第一个是如果一个表或子查询没有行,第二个是如果查询中有空值。

对于我测试过的所有版本,postgres和mysql在求平均值时将忽略所有空值,如果没有要求平均值的值,它将返回空值。这通常是有意义的,因为NULL被认为是“未知的”。如果你想覆盖它,你可以使用coalesce (按照Luc M的建议)。

代码语言:javascript
复制
$ 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)

当然,"from foo“可以替换为"from (... any course logic here ...) as foo”

现在,表中的NULL行应该算作0吗?那么必须在avg调用内部使用coalesce。

代码语言:javascript
复制
$ select coalesce(avg(coalesce(bar, 0)), 0) from foo;
      coalesce      
--------------------
 4.0000000000000000
(1 row)
票数 36
EN

Stack Overflow用户

发布于 2019-08-22 14:35:17

我可以想到两种方法来实现这一点:

  • IFNULL():

()函数返回指定值如果表达式为NULL.If则表达式不为NULL,此函数返回表达式。

语法:

IFNULL(expression, alt_value)

带有查询的IFNULL()示例:

代码语言:javascript
复制
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()

函数的作用是:返回列表中的第一个非空值。

语法:

COALESCE(val1, val2, ...., val_n)

查询的COALESCE()示例:

代码语言:javascript
复制
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
票数 3
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/11007009

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档