How do you use Snowflake RATIO_TO_REPORT to be precise?

Question

I'm having a problem with Snowflake function RATIO_TO_REPORT and it's rounding. It seems like there is default rounding behavior, which causes a sum to be different from 1.

How would you address this issue?

RATIO_TO_REPORT Issue

Cheers,

Joe

Answer 1

Nothing going on here is technically "wrong" - as you've identified, this is a rounding issue. Each of the RATIO_TO_REPORT results is correct, and the sum of the values is also correct.

The best way to get around this is to force RATIO_TO_REPORT to output a more precise number by casting the input to NUMBER rather than INTEGER. In my testing, the below worked well:

-- Create the table for testing
CREATE TABLE R2R_TEST (activity_count integer);

-- Insert the values from the example screenshot.
INSERT INTO R2R_TEST VALUES (210),(11754),(3660),(66);

-- Create the test RATIO_TO_REPORT query with the cast to NUMBER
SELECT RATIO_TO_REPORT(activity_count::number(32,8))  OVER (ORDER BY activity_count) r2r FROM R2R_TEST;

-- Check our work.
with test as (SELECT RATIO_TO_REPORT(activity_count::number(32,8))  OVER (ORDER BY activity_count) r2r FROM R2R_TEST)
SELECT SUM(r2r) FROM test; -- 1.0000000000[...]

You can see that all I've done is cast activity_count to a NUMBER, and this gives the unrounded result.