Decimal Precision in Snowflake: Window Function vs Simple Aggregation

user_9947159 0
Viewed 206

In Snowflake, these queries output the same integer values, but with different decimal precisions. Is this behavior expected?

with cte (num) as

(select 2356 union all
 select 3456 union all
 select 9999)
 
select distinct avg(num) over()
from cte;

Outputs: 5270.333

with cte (num) as

(select 2356 union all
 select 3456 union all
 select 9999)

select avg(num)
from cte;

Outputs: 5270.333333

snowflake-cloud-data-platform sql
2 Answers

Interesting addition to Greg's insight:

with cte (num) as
(
  select * from values (2356::number), (3456::number), (9999::number)
)
select distinct avg(num)
from cte;

gives 5270.333333, but adding the OVER() drives the answer to 3dp

and dropping the distinct shows this in action

with cte (num) as
(
  select * from values (2356::number), (3456::number), (9999::number)
)
select avg(num) OVER()
from cte;

giving:

AVG(NUM) OVER()
5270.333
5270.333
5270.333

Originally snowflake only yielded 3DP for floating point math results, and then in ~2018 they moved to 6DP. I suspect the OVER() function was missed as part of that change, therefore I would suggest open a bug report with support.

user_439920

This appears to be because of implicit casting with the literals and functions. You can force consistency by explicitly casting the literal values:

-- Returns 5270.333333333
with cte (num) as

(select 2356::float union all
 select 3456::float union all
 select 9999::float)
 
select distinct avg(num) over()
from cte;


-- Returns 5270.333333333
with cte (num) as

(select 2356::float union all
 select 3456::float union all
 select 9999::float)

select avg(num)
from cte;
user_127563810
Related