RE: [HACKERS] Re: bug on aggregate function AVG()
От | Jackson, DeJuan |
---|---|
Тема | RE: [HACKERS] Re: bug on aggregate function AVG() |
Дата | |
Msg-id | F10BB1FAF801D111829B0060971D839F4D7369@cpsmail обсуждение исходный текст |
Список | pgsql-hackers |
> > Is there any reason for not use these functions on SUM() and AVG() > on > > official release ? > > It sounds like a good idea. The only hesitation I have at the moment > is > that not all platforms have int8 support, and I'm not certain which > these are. Also, accumulating int4 into int8 is probably pretty slow > since on 32-bit machines the "long long" is usually done in a s/w > library, not in machine code. > > float8 might be a better choice for accumulating AVG(), but I'm > worried > about incorrect results with large tables (> 1M entries) which have > pathological distributions of numbers (e.g. 1M entries with MAXINT and > 1M entries with zero). int4 gives ~9.2 decimal places, float8 gives > ~15 > decimal places, so there is only about ~6 decimal places of headroom. > > Of course, why am I worried? That is much better than what we have > currently. And someone reported that at least one commercial system > (Sybase?) returns float8 for avg() (and sum()?) as I recall. > > So, your suggestion is that for AVG() at least we return something > other > than the input type; how about returning float8 for any input type? > Don't know if SUM() could/should behave similarly... > > - Tom > I think the issue could be address if a float8 sum would be affected by an ORDER BY. If so just make a not in the DOCS and FAQ about significant digits in SUM and AVG. And maybe an example to get the most exact SUM and AVG from a table. If SUM and AVG aren't affected by an ORDER BY I'd say stick with the highest range integer type implemented by the system (in hardware if possible).Just my $0.02,-DEJ
В списке pgsql-hackers по дате отправления: