missing information and aggregates
Date: Thu, 4 Sep 2003 19:53:54 -0400
Message-ID: <8rR5b.528$0o6.49213552_at_mantis.golden.net>
"Christopher Browne" <cbbrowne_at_acm.org> wrote in message news:bj8fnb$ghfhj$1_at_ID-125932.news.uni-berlin.de...
> Quoth Lee Fesperman <firstsql_at_ix.netcom.com>:
> > Bob Badour wrote:
> >> If the user expects a number but gets a NULL, the user will find the
> >> unexpectedness obvious. If the user expects one number but gets another
> >> number, the user will find the unexpectedness less obvious.
> >
> > Ok, I guess I was being dense. I understand what you are getting at.
> >
> > However, it does not cover the general case, making it a bad choice.
> >
> > For example,
> >
> > select AVG(A), AVG(B) from T;
> >
> > ... where I want AVG(A) computed only for rows where A definitely has a
value and the
> > same for AVG(B).
>
> In a "contagious NULLs" scenario, that means that if you don't want
> the results to be potentially all NULL, you need to select...
>
> select AVG(A), AVG(B) from T where A is not null and B is not null;
>
> The other query that might be wanted would be:
>
> select 'A', AVG(A) from T where A is not null
> union
> select 'B', AVG(B) from T where B is not null;
>
> > Note: for the show-offs out there, here's a meater example:
> >
> > select C, AVG(A), AVG(B) from T group by C;
>
> The more "behind-your-back" work that goes on, the less likely this is
> to actually be correct. The "magically right thing" for this one
> probably isn't... STDDEV() or VARIANCE() are liable to be even more
> troublesome...
>
> > I readily admit that I have not studied TTM, so I don't know D&D's
current take on
> > missing information. The last I heard they were still clinging to the
Default Values
> > solution, which is bashed on www.firstsql.com.
>
> The right Default Value for addition aggregates would be 0.
>
> The right Default Value for multiplicative aggregates would be 1.
I think you are confusing default values with identity elements. 0 is the
identity element for addition and 1 is the identity element for
multiplication.
> One might use this to indicate that DV = 0 and DV = 1, and that
> therefore 0 = 1. (Takes me back to the UW "MathNews: Proof of the
> Week" feature :-) ...)
>
> Which doesn't bode very well for Default Values.
>
> I find that I increasingly approve of languages that put Downright
> Ridiculous Defaults into uninitialized variables. :-)
> --
> wm(X,Y):-write(X),write('_at_'),write(Y). wm('cbbrowne','cbbrowne.com').
> http://cbbrowne.com/info/linuxxian.html
> Jumping off a cliff doesn't kill you! It's only when you hit the
> ground...
I always knew that one as: "It's not the fall that kills you; it's the sudden stop." Received on Fri Sep 05 2003 - 01:53:54 CEST