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

  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相关问答推荐

在 Presto SQL 的 where 子句中使用 Max()

查询数据如何使用 kusto(Azure 数据资源管理器)KQL 中的偏移量进行分页

当此标识符不再在数据库中时,缺少特定标识符的日期而不添加额外的日期 SQL

使用 SQL Server 将列转换为多行

如何计算 PostgreSQL 中条件的出现次数?

js数量限制

将任意值插入自动编号列

在没有明显分区列的情况下使用多个分区的 Spark 窗口

SQL求最大值

行中的列名及其值 sql

row_number() 但仅在列中的特定值之后增加值

如何在此 SQL 查询中使用 WHERE 子句?

大表上的查询速度非常慢,如何优化?

搜索数据库中包含特定列的所有表

MS Access VBA:向表中添加了列,但现在 SQL“INSERT INTO”不再起作用

如何删除没有唯一标识符的重复行

如何在 SQL Server 中生成 Guid?

如何在 PostgreSQL 中使用 RETURNING 和 ON CONFLICT?

JSON 上的 PostgreSQL 索引

如何使用复制命令在 postgres 中将数据从一个表复制到另一个表