Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!border1.nntp.dca.giganews.com!nntp.giganews.com!nx01.iad01.newshosting.com!newshosting.com!69.28.186.75.MISMATCH!hwmnpeer01.lga!hwmedia!news.highwinds-media.com!news-server.columbus.rr.com!tornado.ohiordc.rr.com.POSTED!53ab2750!not-for-mail From: J M Davitt User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040803 X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.theory Subject: Re: Box query References: <1145665760.147411.87360@e56g2000cwe.googlegroups.com> <1145666153.540076.254910@j33g2000cwa.googlegroups.com> <1145671131.059290.281130@i40g2000cwc.googlegroups.com> <1145671466.386925.321640@j33g2000cwa.googlegroups.com> In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 111 Message-ID: Date: Sun, 23 Apr 2006 13:22:29 GMT NNTP-Posting-Host: 24.123.195.58 X-Complaints-To: abuse@rr.com X-Trace: tornado.ohiordc.rr.com 1145798549 24.123.195.58 (Sun, 23 Apr 2006 09:22:29 EDT) NNTP-Posting-Date: Sun, 23 Apr 2006 09:22:29 EDT Organization: Road Runner High Speed Online http://www.rr.com Xref: dp-news.maxwell.syr.edu comp.databases.theory:38576 Bob Badour wrote: > J M Davitt wrote: > >> Mikito Harakiri wrote: >> [snip] >> >>> >>> To add more, there is a next question that I was going to ask: "find >>> the intersection volume". And your query even have the correct "group >>> by" clause in place. I'm really puzzled if you already knew the answer, >>> or put "group by" incidentally instead of "distinct":-) >> >> >> >> Wrapping a view declaration around Bob's excellent query >> >> create view intersects as >> select b1.id as box1 >> , b2.id as box2 >> from boxes b1 >> , boxes b2 >> where b1.id < b2.id >> and not exists ( >> select 1 >> from boxes b3 >> , boxes b4 >> where b3.id = b1.id >> and b4.id = b2.id >> and b4.dim = b3.dim >> and ( b4.high <= b3.low or b4.low >= b3.high ) ) >> group by 1, 2 ; >> >> and creating one which hauls out the lengths of the intersected spaces >> >> create view intersections as >> select box1 >> , box2 >> , dim >> , case when one.low < two.low >> then two.low else one.low end as lo >> , case when one.high > two.high >> then two.high else one.high end as hi >> from ( select box1 >> , box2 >> , dim >> , low >> , high >> from boxes >> join ( select box1 >> , box2 >> from intersects ) isects on (id = box1) ) one >> join ( select box1 >> , box2 >> , dim >> , low >> , high >> from boxes >> join ( select box1 >> , box2 >> from intersects ) isects on (id = box2) ) two >> using (box1, box2, dim) ; >> >> select box1 >> , box2 >> , x.len * y.len * z.len as volume >> from ( select box1 >> , box2 >> , hi - lo as len >> from intersections >> where 'x' = dim ) x >> join ( select box1 >> , box2 >> , hi - lo as len >> from intersections >> where 'y' = dim ) y using (box1, box2) >> join ( select box1 >> , box2 >> , hi - lo as len >> from intersections >> where 'z' = dim ) z using (box1, box2) ; >> >> 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. I agree. We would be happier with something like this: select box1 , box2 , product(hi - lo) as volume from intersections group by 1, 2 ; So I wrote one... an aggregate PRODUCT(), that is. But, you know, a truck-load of other issues crop up. 1. It ain't SQL any more 2. I can do this in Teradata or postgres; I'm not sure about others. 3. Even in the systems in which I can do this, I can see no way to avoid using "magic numbers" - using the provided "high level" languages - and "code-in" a potential flaw and maintain SQL-like behavior. (It has to do with NULL wrangling.) 4. Being SQL-like, I see no way to return lengths, areas, and volumes - like "inches", "square inches," "cubic inches," &c. - from this UDF.