Re: Box query
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sun, 23 Apr 2006 03:51:50 GMT
Message-ID: <qRC2g.64510$VV4.1213665_at_ursa-nb00s0.nbnet.nb.ca>
>
>
> I'm changing the aggregation expression. Therefore, the result of
> aggregation is different now:
>
> Let tmp1 = { (box1,box2,exp(sum(ln(...*or*...)))) |
> (1,1,1), (1,2,1),
> (2,1,1), (2,2,1)
> }
>
>
> Anyway, the condition if the two intervals [b1.low,b1.high] and
> [b2.low,b2.high] intersect (for each dimension) is a simple expression
> that depends on 4 numbers. Kind of obvious, isn't it? All what is
> needed is aggregating it with the product aggregate -- which
> corresponds to informal idea of the intersection volume. Alternatively,
> we operate with 1s and 0s only, and could use boolean conjunction,
> which is arithmetic min aggregate.
Date: Sun, 23 Apr 2006 03:51:50 GMT
Message-ID: <qRC2g.64510$VV4.1213665_at_ursa-nb00s0.nbnet.nb.ca>
Mikito Harakiri wrote:
> Bob Badour wrote:
>
>>Mikito Harakiri wrote:
>>
>>
>>>Bob Badour wrote:
>>>
>>>
>>>>If you take a look at the second-to-last step, your query failed before
>>>>it got to the having clause:
>>>>
>>>>Let tmp1 = { (box1,box2,exp(sum(ln(...)))) |
>>>> (1,1,6), (1,2,3e-8),
>>>> (2,1,3e-8), (2,2,6)
>>>>}
>>>
>>>All right, let's make the case condition symmetric:
>>>
>>>having 1 = exp(sum(ln(
>>> case when b2.low between b1.low and b1.high
>>> *or* b1.low between b2.low and b2.high
>>>then 1 else 0.00000001 end)))
>>
>>Changing the having clause won't fix a problem that already happened
>>before the having clause is applied.
>
>
> I'm changing the aggregation expression. Therefore, the result of
> aggregation is different now:
>
> Let tmp1 = { (box1,box2,exp(sum(ln(...*or*...)))) |
> (1,1,1), (1,2,1),
> (2,1,1), (2,2,1)
> }
>
>
> Anyway, the condition if the two intervals [b1.low,b1.high] and
> [b2.low,b2.high] intersect (for each dimension) is a simple expression
> that depends on 4 numbers. Kind of obvious, isn't it? All what is
> needed is aggregating it with the product aggregate -- which
> corresponds to informal idea of the intersection volume. Alternatively,
> we operate with 1s and 0s only, and could use boolean conjunction,
> which is arithmetic min aggregate.
How do those 1's get you your volume? For instance, the volume of box 1 intersected with itself is 6 not 1. The volume of box 1 intersected with box 2 is 4 not 1. Received on Sun Apr 23 2006 - 05:51:50 CEST