sql - 关于 having 子句的 Postgresql 奇怪结果

Question

SELECT
T . ID,
T . NAME,
T .feed_id,
T .status,
T .budget,
(
    SELECT
        COUNT (*)
    FROM
        segment
    WHERE
        segment.campaign_id = T . ID
) AS total_segments,
(
    SELECT
        SUM (revenue)
    FROM
        TRANSACTION
    WHERE
        TRANSACTION .campaign_id = T . ID
    AND TRANSACTION .status = 3
    AND deleted = 0
) AS projected_revenue
  FROM
campaign T
 WHERE
(T .deleted = 0)
AND (site_id = 3)
GROUP BY
T ."id"
  HAVING
  (
    SELECT
        SUM (revenue)
    FROM
        TRANSACTION
    WHERE
        TRANSACTION .campaign_id = T . ID
    AND TRANSACTION .status = 3
    AND deleted = 0
    ) > 242.45
      ORDER BY
  T . ID DESC

当 having 时查询工作正常子查询大于 ( > ) 或小于 ( < ) 但当等于时它不显示任何结果。但它适用于:

HAVING (
  SELECT SUM (revenue)
  FROM TRANSACTION 
  WHERE TRANSACTION.campaign_id = T.ID 
    AND TRANSACTION.status = 3
    AND deleted = 0
)::NUMERIC = 242.45

Answer 1

据推测，收入是float4或float8，即float或 double 值。

正如@GáborBakos 准确指出的那样，一些值(包括 242.45)没有精确的二进制浮点表示。观察:

regress=> SET extra_float_digits = 3;
SET
regress=> SELECT FLOAT4 '242.45';
   float4   
------------
 242.449997
(1 row)

一般来说，您不应该比较浮点值是否相等。请改用小范围。

参见:

• http://floating-point-gui.de/errors/comparison/
• How should I do floating point comparison?
• Floating point equality and tolerances
• http://randomascii.wordpress.com/category/floating-point/

您的查询适用于转换为 NUMERIC，因为与 float4 或 float8 不同，NUMERIC 是二进制- 具有任意精度和比例的编码十进制类型。它可以精确地表示 242.45，并且不受舍入伪像和 float 学的其他怪异问题的影响。但是，该类型转换并不总是足够的 - 其他值可能仍无法按照您的预期工作。考虑:

regress=> select (float4 '0.3' * 100000000) :: NUMERIC;
     numeric      
------------------
 30000001.1920929
(1 row)

如您所见，这些舍入误差会累积。

与其依赖于与numeric 的比较，您应该真正使用小范围比较而不是比较是否相等。或者，最重要的是，始终使用 numeric 来表示财务和精确数量，从不 float 。