> Please someone show me what is the correct value of this avg() in practice.
There are a number of answers to this.
To the level of precision that you specified, all answer are
completely fine; the error is, in all cases, very small relative to
the variance of your input data.
It is an interesting exercise, though. It would be a good idea to sort
the input data on absolute magnitude:
SELECT AVG(data) FROM TryAvg ORDER BY ABS(data) DESC;
But apparently, SQLite ignores the ORDER BY clause because of the
AVG() function, perhaps -erroneously- assuming AVG() is commutative.
This works, but isn't guaranteed to work:
SELECT AVG(data) FROM (SELEFT data FROM TryAvg ORDER BY ABS(data) DESC);
Funnily enough, in Postgres, the test seems to expose an actual bug,
or at least a strange error message:
sidney=# select AVG(data) from TryAvg ORDER BY data DESC;
ERROR: column "tryavg.data" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 1: select AVG(data) from TryAvg ORDER BY data DESC;
^
Now, to address your actual point. The fact that we cannot control
evaluation order in SQL does mean that one has to be wary in case
one's data is badly conditioned. The problem is in SQL: it assumes
commutativity for aggregate functions, and that is a property that no
floating point format conceived can deliver. This is an interesting
fact (as is the fact that, as a consequence of this, identical
invocations of aggregate functions can yield different results under
seemingly identical circumstances in SQL), but I disagree that this
disqualifies using SQL for use in a scientific setting. There are
preciously little types of measurements where one has to calculate
with 16 orders of magnitude as your example does, and in such
circumstances, the scientist needs to be exceedingly on his or her
guard to know what she is doing in ANY language, with ANY storage
mechanism. I think therefore that this example, interesting as it is,
does not provide a conclusive argument in the discussion at hand.
-S
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
