Question 231867 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – How to resolve floating point precision issue in Athena?

I’m trying to find a explode_percent column by dividing a column to sum of the bucket.

Below is my query –

    SELECT 
        amount,
        sum(amount) over (
            partition by col1,
            col2,
            col3,
            col4
            order by col2,
                col3,
                col4
        ) as explode_amount,
        COALESCE(
            amount / NULLIF(
                SUM(amount) over(
                    partition by col1,
                    col2,
                    col3
                    order by col2,
                        col3,
                        col4
                ),
                0
            ),
            0
        ) as explode_percent
    FROM my_table

However this is giving me different values in each run. I’ve tried casting both numerator and denominator to decimal(38,7), decimal(38,15), double, round till 7 decimal places but no luck.

Please help.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks for the inputs.

    There was some issue with the input data itself and apparently the floating point was not giving correct results. Resolved now.


  2. 0

    Instead of using floating-point data types like double, consider using the decimal data type, which allows you to specify a fixed precision and scale also you can round both the numerator and the denominator to a specific number of decimal places before performing the division. Rounding can help in cases where the floating-point representation introduces small errors.

    WITH normalized_data AS (
    SELECT 
        col1,
        col2,
        col3,
        col4,
        amount,
        SUM(amount) OVER (
            PARTITION BY col1, col2, col3
            ORDER BY col2, col3, col4
        ) AS total_amount
    FROM my_table
)
SELECT 
    col1,
    col2,
    col3,
    col4,
    amount,
    total_amount,
    COALESCE(
        ROUND(CAST(amount AS DECIMAL(38, 10)) / NULLIF(CAST(total_amount AS DECIMAL(38, 10)), 0), 7),
        0
    ) AS explode_percent
FROM normalized_data;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search