Re: Box query
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 22 Apr 2006 18:52:05 GMT
Message-ID: <pXu2g.64297$VV4.1208215_at_ursa-nb00s0.nbnet.nb.ca>
>
>
> Yes, it is tall. The whole business turning columns in rows from
> self-correlated queries into rows of columns is messy. (And hard to
> describe, too!) And, yes, it seems complex -- lots of JOINs.
>
> Regarding Mikito's solution: do you mean this?
>
> select b1.id as box1, b2.id as box2
> from boxes b1, boxes b2
> where b1.dim = b2.dim
> group by b1.id, b2.id
> having 0.1 < exp(sum(ln(
> case when b2.low between b1.low and b1.high then b1.high-b2.low
> else 0.00000001 end
> )))
>
> Two things: I don't see that this gives volume and I can't see how
> he avoids "ln(0)" when b1.high = b2.low -- when it might be if
> 'b2.low between b1.low and b1.high' is true.
>
> Or did I miss something in the discussion?
Date: Sat, 22 Apr 2006 18:52:05 GMT
Message-ID: <pXu2g.64297$VV4.1208215_at_ursa-nb00s0.nbnet.nb.ca>
J M Davitt wrote:
>> J M Davitt wrote: >> >>> [snip] >>> I believe this gives volumes: >> >> >> I don't know whether it does, but it seems rather complex compared to >> using exp, ln and sum as Mikito's solution does. His solution makes >> the volume easier to express because he orders box1 and box2 by low >> instead of by id. That would seem to make my query somewhat less >> excellent vis a vis his.
>
>
> Yes, it is tall. The whole business turning columns in rows from
> self-correlated queries into rows of columns is messy. (And hard to
> describe, too!) And, yes, it seems complex -- lots of JOINs.
>
> Regarding Mikito's solution: do you mean this?
>
> select b1.id as box1, b2.id as box2
> from boxes b1, boxes b2
> where b1.dim = b2.dim
> group by b1.id, b2.id
> having 0.1 < exp(sum(ln(
> case when b2.low between b1.low and b1.high then b1.high-b2.low
> else 0.00000001 end
> )))
>
> Two things: I don't see that this gives volume and I can't see how
> he avoids "ln(0)" when b1.high = b2.low -- when it might be if
> 'b2.low between b1.low and b1.high' is true.
>
> Or did I miss something in the discussion?
I found the same flaws in his solution when I examined it closer. I replied to the message with what I think is a correct solution. Received on Sat Apr 22 2006 - 20:52:05 CEST