CODEWITHSUNDEEP
sqlprestoamazon-athena
drazen90909
drazen90909

Reputation: 75

Allocation via SQL - Retaining repeating decimals for the sum()

I am allocating a single unit across multiple rows using a calculation and storing the results into a table. I am then sum() the allocations and the sums are resulting in numbers that are not whole numbers. What is going on is that some of the allocations are ending up as numbers with repeating decimals, and then the sum of those not adding back up to the whole number (ala 1/3 + 1/3 + 1/3 != 1).

I have tried casting the numbers into different formats, however, Athena keep rounding the decimals at some arbitrary precision resulting in the problem.

I would like the sum of the allocations to equal the sum of the original units.

My Database is AWS Athena which I understand to use the Presto SQL language.

Example of my allocation:

case
  when count_of_visits = 1 then 1
  when count_of_visits = 2 then .5
  when count_of_visits >= 3 then
       case
         when visit_seq_number = min_visit_seq_number then .4
         when visit_seq_number = max_visit_seq_number then .4
         else .2 / (count_of_visits - 2 )
        end
   else 0
end as u_shp_alloc_leads

In this allocation, the first and last visits get 40% of the allocation and all visits in between split 20%

A unit that is being allocated to 29 visits ends up dividing the 20% by 27 which equals 0.00740Repeating. The table is storing 0.007407407407407408 which when I go to sum the numbers the result is 1.0000000000000004 I would like the result to be 1

Upvotes: 1

Views: 275

Answers (1)

Alex
Alex

Reputation: 136

This is a limitation of databases or computers in general. When you work with fractions like that, some sort of rounding will always take place.

I would apply a reasonable degree of rounding to the x-th decimal on the sums you retrieve from your table, that will just cut off these residual decimals at the end.

If that's not sufficient for you, something you can do to at least theoretically have full precision is to store numerator and denominator separately in two columns. When computing sum( numerator_column/denominator_column ) you will see the same rounding effects, so summing up the numbers would be something a little more complicated like this:

SELECT sum(numerator_sum/denominator)
FROM (
  SELECT
    denominator,
    sum(numerator) as numerator_sum
  FROM your_allocation_table
  GROUP BY denominator
)

Upvotes: 1

Related Questions