我有一个返回平均价格的查询

  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 [, ...])
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.

Edit

下面是一个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意味着未知,没有别的.这不像在SUM年使用它.在本例中,如果我们将AVG替换为SUM,结果不会失真.把0加到一个总和上不会伤害任何人,但是用0来计算未知值的平均值,你不会得到真正的平均值.

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

Sql相关问答推荐

如何在snowflake中进行SQL的反向填充

用于过滤嵌套对象或数组中的JSON数据的WHERE条件

我怎样才能得到列值对应的最大值在另一个?

SQL:查询作为子查询或CTE写入的最大和数失败

如何根据几个条件 Select 值:如果满足一个范围的SUM,则对另一个范围求和

如何使用jsonn_populate_record()插入到包含IDENTITY列的表中

特殊条件计算小计

用户定义的标量值函数是否仍然会阻止并行性?

如何根据 SQL Server 中 1 条语句中 SELECT 的结果进行 INSERT 或 UPDATE

如何在sparksql查询中使用日期值?

基于字符串的SQL查询

如何仅在满足条件时才按顺序在 SQL 中计数?

As400 (IBM i) SQL 表 QSYS2.SYSTABLES 上的元数据

奇怪的甲骨文行为

SQL Group By 然后映射出是否存在值

在sql server中创建唯一标识符列

SQL:有没有办法根据另一列的数据细节过滤和形成另一列?

在 SQL 中将行显示为列

遍历数据,计算每个月最后三天的总和

压缩或合并具有空值的行而不使用分组依据