Re: SQL for intervals

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Mon, 18 Dec 2006 00:05:47 GMT
Message-ID: <vXkhh.34245$cz.508628@ursa-nb00s0.nbnet.nb.ca>

Tonkuma wrote:

> Bob Badour wrote:
>

>>Tonkuma wrote:
>>
>>>a)
>>>select id_lwr, min_t as lwr_bound, min(max_t) as upr_bound
>>>from (
>>>    select id as id_lwr, t as min_t from W W1 where not exists (
>>>        select 1 from W W2 where W1.t = W2.t + 1 and W1.id = W2.id
>>>    )
>>>) WL,
>>>(
>>>    select id as id_upr, t as max_t from W W3 where not exists (
>>>        select 1 from W W4 where W3.t = W4.t - 1 and W3.id = W4.id
>>>    )
>>>) WU
>>>where id_lwr = id_upr and max_t > min_t
>>>group by id_lwr, min_t
>>>;
>>>
>>>b)
>>>SELECT lbound.id
>>>     , CHAR(lbound.t,USA)      "LowerBound"
>>>     , CHAR(MIN(ubound.t),USA) "UpperBound"
>>>  FROM W lbound
>>>     , W ubound
>>> WHERE NOT EXISTS
>>>       (SELECT *
>>>          FROM lower
>>>         WHERE lower.id = lbound.id
>>>           AND lower.t  = lbound.t - 1 day
>>>       )
>>>   AND ubound.id = lbound.id
>>>   AND ubound.t >= lbound.t
>>>   AND NOT EXISTS
>>>       (SELECT *
>>>          FROM W upper
>>>         WHERE upper.id = lbound.id
>>>           AND upper.t  = ubound.t + 1 day
>>>       )
>>> GROUP BY
>>>       lbound.id, lbound.t
>>>
>>>Compared a) and b), at least b) doesn't need "(select id as id_lwr, t
>>>as min_t from", "(select id as id_upr, t as max_t from", ") WL" and ")
>>>WU".
>>
>>And a) doesn't need (SELECT * FROM W lower...) or (SELECT * FROM W
>>upper...) [Not to mention a) doesn't need NOT EXISTS either.]
>>

>
> In a), followings were wrote in nested table expressions(WL and WU) in
> FROM clause.
> not exists (
> select 1 from W W2 where W1.t = W2.t + 1 and W1.id = W2.id
> )
> AND
> not exists (
> select 1 from W W4 where W3.t = W4.t - 1 and W3.id = W4.id
> )
> The place written NOT EXISTS is FROM caluse in a) and WHERE clause in
> b).
> But, both are same except correlation names.

D'Oh! You are right. A) does use NOT EXISTS. I replied too quickly without paying sufficient attention. I am sorry.

> If I use same correlation name in b) as a) as much as possible and make
> near a) begin of new line and renamed-colums in final SELECT (and
> change sequence of predicate in WHERE). These are not change the
> meaning of query b) nor change the result of query b).
> It will be follwing.
> b')
> SELECT WL.id, WL.t as lwr_bound, MIN(WU.t) as upr_bound
> FROM W WL, W WU
> WHERE NOT EXISTS (SELECT * FROM W W2 WHERE W2.id = WL.id AND W2.t =
> WL.t - 1 day)
> AND NOT EXISTS (SELECT * FROM W W4 WHERE W4.id = WU.id AND W4.t =
> WU.t + 1 day)
> AND WU.id = WL.id AND WU.t >= WL.t
> GROUP BY WL.id, WL.t
>
>

>>I repeat: How is nesting in the where clause any less nesting than in
>>the from clause?

>
> in a) two parenthses. One for (...) WL and (...) WU and both incude not
> exists(...).
> in b) one parentheses for each NOT EXISTS(...).
> So, in this case, in where clause is one less nesting than in from
> clause.

Ah, now I see. One query nests two levels deep.

> Note:(for wordings)
> NOT EXISTS(...) in WHERE clause in b) is not nested query. It is simply
> fullselect (Please see Manual SQL Reference Vol 1 ---> Chapter 2.

I don't know what manual you are referring to. But if the authors think a sub-query is not nested, the authors are deluded.

> Language element ---> Predicate ---> EXISTS predicate) and same things
> are appeared in nested table expressions(WL and WU) in FROM clause (For
> "nested table expression", please refer Manual SQL Reference Vol 1 --->
> Chapter 4. Queries ---> Subselect ---> from-clause and table-reference.
> It is written that "A fullselect in parentheses FOLLOWED BY A
> CORRELATION NAME is called a nested table expression.").
> ("FOLLOWED BY A CORRELATION NAME" is capitalized by me.)

Again, the authors are deluded if they think a name magically transforms a nested expression into a nested expression.

>>After you finish pondering that question, ask yourself:
>>
>>Why should we avoid nesting at all?

>
> Not always, but in this case, SQL statement get shorter (and simpler).

'Simple' is in the eye of the beholder, and shorter is not always better. I agree that your solution is every bit as imaginative, useful and elegant as mine and perhaps even more so. Plus, your solution does not suffer from the careless, sloppy little mistakes mine has.

Nesting, though, is not anything that concerns me. It is a wonderful, brilliant, useful derivative of closure. Received on Sun Dec 17 2006 - 18:05:47 CST