我有一个查询返回avg(price)" {' L* h2 h% n% _% R& K0 ?
select avg(price) from( select *,cume_dist() OVER (ORDER BY price desc) from web_price_scan where listing_Type='AARM and u_kbalikepartnumbers_id = and (EXTRACT(Day FROM (Now()-dateEnded)))*24 ( select avg(price)* 0.505050 from(select *,cume_dist() OVER (ORDER BY price desc) from web_price_scan where listing_Type='AARM and u_kbalikepartnumbers_id = and (EXTRACT(Day FROM (Now()-dateEnded)))*24 5如果没有可用值,如何返回0? 1 @) S3 _/ D. c8 j$ ^$ X - T8 S3 }: ^& m$ n& Y解决方案: + l4 H# D/ G5 {) y
使用合并 ; Y' Z' g* t+ W1 _0 i% [$ ICOALESCE(value [,...])The COALESCE function returns the first of its arguments that is notnull.1 I0 ^+ ~0 P# x* j# g" L) @
Null is returned only if all arguments are null. It is often 7 c+ W; q6 k0 X5 [( \ used to substitute a default value for null values when data is 7 U# ?$ u: X: M- v' Q retrieved for display.1 E9 `0 c- ^% s8 ?" p2 W6 x 编辑 1 @$ n# D9 [9 e. t0 u2 Q O这是COALESCE查询示例: " k; M- X# ~4 tSELECT AVG( price )FROM( SELECT *,cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan WHERE listing_Type = 'AARM AND u_kbalikepartnumbers_id = AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) * 24 ( SELECT AVG( COALESCE( price,0 ) )* 0.505050 FROM ( SELECT *,cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan WHERE listing_Type='AARM AND u_kbalikepartnumbers_id = AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) * 24 5IMHOCOALESCE不应与之一起使用,AVG因为它会修改值。NULL这意味着未知,没有别的。这不像在里面用。SUM。在这个例子中,如果我们替换它,AVG为SUM,结果不会扭曲。总和加0不会伤害任何人,但当未知值计算为0时,你不会得到真正的平均值。$ F& q# Y* c: x" H# S% K8 y
在那种情况下,我将添加price IS NOT NULLinWHERE避免这些未知值。