CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Rajat
Rajat

Reputation: 5803

Decimal Precision in Snowflake: Window Function vs Simple Aggregation

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

Upvotes: 2

Views: 658

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25938

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.

Upvotes: 1

Greg Pavlik
Greg Pavlik

Reputation: 11046

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;

Upvotes: 0

Related Questions