为什么这些百分比之和不等于100％？ [英] Why doesn't this sum of percentages add up to 100%?

问题描述

我在DB2 SQL DB中有一系列计算时间，这些计算时间存储为float，默认值为0.0。

I have a series of calculation times in a DB2 SQL DB that are stored as float with a default value of 0.0.

要更新的表如下：

CREATE TABLE MY_CALC_DATA_TABLE
(
    CALCDATE                 TIMESTAMP,
    INDIV_CALC_DURATION_IN_S FLOAT WITH DEFAULT 0.0,
    CALC_TIME_PERCENTAGE     FLOAT WITH DEFAULT 0.0
)

使用存储过程。我正在计算总和，如下所示：

Using a sproc. I am calculating the sum as follows:

CREATE OR REPLACE PROCEDURE MY_SCHEMA.MY_SPROC (IN P_DATE TIMESTAMP)
    LANGUAGE SQL
    NO EXTERNAL ACTION
BEGIN
    DECLARE V_TOTAL_CALC_TIME_IN_S FLOAT DEFAULT 0.0;

    -- other stuff setting up and joining data

    -- Calculate the total time taken to perform the
    -- individual calculations

    SET V_TOTAL_CALC_TIME_IN_S =
        (
            SELECT
                SUM(C.INDIV_CALC_DURATION_IN_S)
            FROM
                MY_SCHEMA.MY_CALC_DATA_TABLE C
            WHERE
                C.CALCDATE = P_DATE
        )

    -- Now calculate each individual calculation's percentage
    -- of the toal time.

    UPDATE
        MY_SCHEMA.MY_CALC_DATA_TABLE C
    SET
        C.CALC_TIME_PERCENTAGE =
            (C.INDIV_CALC_DURATION_IN_S / V_TOTAL_CALC_TIME_IN_S) * 100
    WHERE
        C.CALCDATE = P_DATE;

END@

麻烦的是，当我将所有指定CALC_DATE的CALC_TIME_PERCENTAGE值始终小于100％，而不同CALC_DATES的值之和为80％或70％。

Trouble is, when I do a sum of all the CALC_TIME_PERCENTAGE values for the specified CALC_DATE it is always less than 100% with the sum being values like 80% or 70% for different CALC_DATES.

我们在35k和55k之间进行讨论此处计算的结果是最大的单个计算在总计算中所占的百分比为11％，并且很多在0.00000N％范围内。

We are talking between 35k and 55k calculations here with the maximum individual calculation's percentage of the total, as calculated above, being 11% and lots of calculations in the 0.00000N% range.

要使用简单查询来计算总百分比：

To calculate the total percentage I am using the simple query:

SELECT
    SUM(C.CALC_TIME_PERCENTAGE)
FROM
    MY_SCHEMA.MY_CALC_DATA_TABLE C
WHERE
    C.CALCDATE = P_DATE;

有什么建议吗？

更新：：重新排列计算。如建议解决该问题。谢谢。 BTW在DB2中，FLOAT和DOUBLE是相同的类型。现在，请阅读有关浮点数的建议文章。

Update: Rearranging the calc. as suggested fixed the problem. Thanks. BTW In DB2 FLOAT and DOUBLE are the same type. And now to read that suggested paper on floats.

推荐答案

如果字段 C.INDIV_CALC_DURATION_IN_S 是Integer，我认为这是一个舍入错误。再次阅读，这不是问题，因为数据类型为 FLOAT 。

If the field C.INDIV_CALC_DURATION_IN_S were Integer, I would assume it's a rounding error. Reading again, that is not the problem as the datatype is FLOAT.

您仍然可以尝试使用它。如果此方法（与以前的方法）产生（略微）不同的结果，我不会感到惊讶：

You can still try using this. I wouldn't be surprised if this yileded (slighly) different results than the previous method:

SET
    C.CALC_TIME_PERCENTAGE =
        (C.INDIV_CALC_DURATION_IN_S * 100.0 / V_TOTAL_CALC_TIME_IN_S)

---

但是您提到在某个日期的计算中有很多行，因此可能会导致舍入错误。尝试在两个字段中都使用 DOUBLE 数据类型（或至少在 CALC_TIME_PERCENTAGE 字段中），看看是否与 100％变小。

---

But you mention that there a lot of rows in a calculation for a certain date, so it may be a rounding error due to that. Try with DOUBLE datatype in both fields (or at least the CALC_TIME_PERCENTAGE field) and see if the difference from 100% gets smaller.

我不确定 DB2 具有 DECIMAL（x，y）数据类型。

I'm not sure if DB2 has DECIMAL(x,y) datatype. It may be more appropriate in this case.

另一个问题是如何找到的总和CALC_TIME_PERCENTAGE 。我想您（和其他所有人）将使用：

Another problem is how you find the sum of CALC_TIME_PERCENTAGE. I suppose you (and everyone else) would use the:

        SELECT
            P_DATE, SUM(CALC_TIME_PERCENTAGE)
        FROM
            MY_SCHEMA.MY_CALC_DATA_TABLE C
        GROUP BY P_DATE

这样，您无法确定以什么顺序进行求和。甚至可能无法确定，但是您可以尝试：

This way, you have no way to determine in what order the summation will be done. It may not be even possible to determine that but you can try:

        SELECT
            P_DATE, SUM(CALC_TIME_PERCENTAGE)
        FROM
          ( SELECT
                P_DATE, CALC_TIME_PERCENTAGE
            FROM
                MY_SCHEMA.MY_CALC_DATA_TABLE C
            ORDER BY P_DATE
                   , CALC_TIME_PERCENTAGE ASC
          ) AS tmp
        GROUP BY P_DATE

优化器可能会忽略内部 ORDER BY ，但值得一试。

The optimizer may disregard the interior ORDER BY but it's worth a shot.

这种大差异的另一种可能性是行是从 UPDATE 和 SHOW％SUM 操作之间的表中删除。

Another possibility for this big difference is that rows are deleted from the table between the UPDATE and the SHOW percent SUM operations.

您可以通过运行计算（不执行UPDATE）并进行总结来测试这种情况是否发生：

You can test if that happens by running the calculations (without UPDATE) and summing up:

        SELECT
            P_DATE
          , SUM( INDIV_CALC_DURATION_IN_S * 100.0 / T.TOTAL )                   
            AS PERCENT_SUM
        FROM
            MY_SCHEMA.MY_CALC_DATA_TABLE C
          , ( SELECT SUM(INDIV_CALC_DURATION_IN_S) AS TOTAL
              FROM MY_SCHEMA.MY_CALC_DATA_TABLE
            ) AS TMP
        GROUP BY P_DATE

这篇关于为什么这些百分比之和不等于100％？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！