Nimble Coder

Percentage Normalization in SQL

I had a little fun with SQL Server last week trying to normalize a gas composition function that was returning a molar percentage of the gas. During testing we found several cases where the sum of the components did not add up to 100.0%. Of course due to floating point inaccuracies it is quite possible that rounding errors could also contribute to the issue. The values we were seeing were a couple of percentage points different and so we decided to normalize the data to ensure that it added up to 100.0%. At the same time, I rounded the components (to two decimal places in molar %) since there are contractual documents specifying the precision that will be used during final calculation.

One interesting point is that if you simply round the components after the summation, the total percentage may not be 100% either. Here is an example using purely theoretical values:

To account for this, I rounded all of the values except the final (chosen) component and then applied the remainder to that component. So the final normalization and rounding might look like this:

IF (ABS(100.0 - @sumComponents) <= @threshold)
BEGIN
    SET @diff = 100.0 - @sumComponents
    SET @factor = 1.0 + (@diff / 100.0)
END
ELSE
BEGIN
    -- No correction factor
    SET @factor = 1.0
END

-- Round the components to 2 decimal places for molar percent
SELECT  @C2  = ROUND(@C2  * @factor, 2)
       ,@C3  = ROUND(@C3  * @factor, 2)
       ,@iC4 = ROUND(@iC4 * @factor, 2)
       ,@nC4 = ROUND(@nC4 * @factor, 2)
       ,@iC5 = ROUND(@iC5 * @factor, 2)
       ,@nC5 = ROUND(@nC5 * @factor, 2)
       ,@C6  = ROUND(@C6  * @factor, 2)
       ,@N2  = ROUND(@N2  * @factor, 2)
       ,@CO2 = ROUND(@CO2 * @factor, 2)
       ,@O2  = ROUND(@O2  * @factor, 2)

-- Apply error/remainder to methane
SELECT @diff = ROUND(@C2 + @C3 + @iC4 + @nC4 + @iC5 + @nC5 + @C6 + @N2 + @CO2 + @O2, 2)
SELECT @C1   = ROUND(100.0 - @diff, 2)

-- Verify total
SELECT  @sumComponents = (@C1 + @C2 + @C3 + @iC4 + @nC4 + @iC5 + @nC5 + @C6 + @N2 + @CO2 + @O2)

Technorati tags: SQL

Print | posted on Monday, September 17, 2007 11:40 AM | Filed Under [ SQL ]