query suggestion pls. the windowing thing.

From: kazelot <kazelot_at_kulci.prv.pl>
Date: Fri, 8 Aug 2003 14:15:54 +0200
Message-ID: <bh04c7$hi1$1_at_inews.gazeta.pl>


Hello!

I have a problem with my SQL query.

First, let's set up the stages.


  1. oracle version SQL> select * from v$version;

BANNER



Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for IBM/AIX RISC System/6000: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production

2. the table
CREATE TABLE P
(

  ID    NUMBER(10)                              NOT NULL,
  EQN   VARCHAR2(25)                            NOT NULL,
  NR    NUMBER(10)                              NOT NULL,
  STAN  NUMBER(1)                               NOT NULL
);

3. the data
declare
 i number;

begin
  for i in 1 .. 10 loop
    insert into p(id, nr, eqn, stan) values (i, i-1, 'aa'||(i-1), 0);   end loop;
end;
/
commit;

FIRST VERSION


Ok. In the first version of our application I had a table that hold continuos numbers, starting from 0.
My task was to write sql (sinlge query) to find all continous blocks of data
(of given length and given condition)

and return the first element for the blocks.

For example, I have to find 5 rows that have stan = 0 and are next to eachother.
The ilustration below shows the mechanism
(the digits represent the numbers of a row -- nr column,
|---| is the "results window")

<pre>
|---|....
0123456789

..|---|...
0123456789

.....|---|
0123456789
</pre>

If all the rows match the condition (stan = 0) the query should return NR

--
0
1
2
4
5

6 is not to be returned, because the block starting with 6 is 4 items long,
etc.

The query looks like this:
SELECT   nr, eqn
    FROM (SELECT ID, nr, eqn,
                 SUM(stan) OVER(ORDER BY nr ROWS BETWEEN CURRENT ROW AND 5 -
1 FOLLOWING)

AS zajety
            FROM p)
   WHERE nr <= (SELECT (MAX(nr) - 5 + 1)
                  FROM p) AND zajety = 0
ORDER BY nr;

        NR EQN
---------- ------------
         0 aa0
         1 aa1
         2 aa2
         3 aa3
         4 aa4
         5 aa5

If the nr 7 had stan = 1, the query should only return nr = 0, 1, 2
<pre>
0123456789
|---|..x..
.|---|.x..
..|---|x..
</pre>

Let's see:
UPDATE p
   SET stan = 1
 WHERE nr = 7;

commit;

SQL> SELECT   nr, eqn
  2      FROM (SELECT ID, nr, eqn,
  3                   SUM(stan) OVER(ORDER BY nr ROWS BETWEEN CURRENT ROW
AND 5 - 1 FOLLOWING)
  4
AS zajety

  5              FROM p)
  6     WHERE nr <= (SELECT (MAX(nr) - 5 + 1)
  7                    FROM p) AND zajety = 0
  8  ORDER BY nr;

        NR EQN
---------- -------------------------
         0 aa0
         1 aa1
         2 aa2


SECOND VERSION
--------------
So far so good. All worked fine. But then the requirements changed. Now
1. the numbers in the table may be not continous
2. the first number can be <> 0

My english is very poor, so again I use an ilustration

(dots mean that the row is present in the table,
x means stan = 1, |---| is the result window) <pre> 0000000000111111111122222 nr: 0123456789012345678901234 ....x.......x........ |---| |---| |---| |---| |---| |---| 0000000000111111111122222 nr: 0123456789012345678901234 </pre> resulting rows should be: 9, 10, 11, 17, 18, 19, 20 Let's prepare data from this ilustration. delete from p; declare i number; begin for i in 4 .. 24 loop insert into p(id, nr, eqn, stan) values (i+1, i, 'aa'|| i, 0); end loop; end; / update p set stan = 1 where nr in (8, 16); commit; And test the query gives anticipated results: NR EQN ---------- ---------- 9 aa9 10 aa10 11 aa11 17 aa17 18 aa18 19 aa19 20 aa20 But when you delete the rows with stan = 1 you got rubbish. "Rows between" don't work anymore. delete from p where stan = 1 ; commit; NR EQN ---------- ------ 4 aa4 5 aa5 6 aa6 7 aa7 9 aa9 10 aa10 11 aa11 12 aa12 13 aa13 14 aa14 15 aa15 17 aa17 18 aa18 19 aa19 20 aa20 21 and above are added because max() "lies". SUGGESTION PLEASE ----------------- Is there any way to do what I want in one query? Thanks for any information, kazelot
Received on Fri Aug 08 2003 - 14:15:54 CEST

Original text of this message