Home » SQL & PL/SQL » SQL & PL/SQL » how to incrementally get all numbers incremented by n between 2 columns values (oracle 19c)  () 1 Vote
how to incrementally get all numbers incremented by n between 2 columns values [message #687342] Mon, 27 February 2023 22:36 Go to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
HI, i have below query and it returns below values incremented by 1. but i need to get incremented by 2 or 3 not able to figure out how to do.

select inst_num, start_snap_id, end_snap_id
from (
select s.instance_number as inst_num,
s.snap_id as start_snap_id,
lead(s.snap_id,1,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id
from dba_hist_snapshot s
where
s.snap_id >= 165952
and s.snap_id <= 165959
)
where end_snap_id is not null
order by inst_num, start_snap_id;
1 165952 165953
1 165953 165954
1 165954 165955
1 165955 165956
1 165956 165957
1 165957 165958
2 165952 165953
2 165953 165954
2 165954 165955
2 165955 165956
2 165956 165957
2 165957 165958




i want values like below

1 165952 165954
1 165954 165956
1 165956 165958
1 165958 165959
2 165952 165954
2 165954 165956
2 165956 165958
2 165958 165959
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687343 is a reply to message #687342] Tue, 28 February 2023 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

You are very close to the solution just number the rows in the subquery and restrict the result to 1 out of 2:
SQL> with
  2    data as (
  3      select s.instance_number as inst_num, s.snap_id as start_snap_id,
  4             lead(s.snap_id,1,null) over
  5               (partition by s.instance_number order by s.snap_id)
  6               as end_snap_id,
  7             row_number() over (partition by s.instance_number order by s.snap_id)
  8               as rn
  9      from dba_hist_snapshot s
 10      where s.snap_id between 53100 and 53110
 11    )
 12  select inst_num, start_snap_id, end_snap_id
 13  from data
 14  where end_snap_id is not null and mod(rn,2) = 1
 15  order by inst_num, start_snap_id
 16  /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53100       53101
         1         53102       53103
         1         53104       53105
         1         53108       53109

4 rows selected.
Extending the solution to <n>:
SQL> def n=2
SQL> with
  2    data as (
  3      select s.instance_number as inst_num, s.snap_id as start_snap_id,
  4             lead(s.snap_id,&n-1,null) over
  5               (partition by s.instance_number order by s.snap_id)
  6               as end_snap_id,
  7             row_number() over (partition by s.instance_number order by s.snap_id)
  8               as rn
  9      from dba_hist_snapshot s
 10      where s.snap_id between 53100 and 53110
 11    )
 12  select inst_num, start_snap_id, end_snap_id
 13  from data
 14  where end_snap_id is not null and mod(rn,&n) = 1
 15  order by inst_num, start_snap_id
 16  /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53100       53101
         1         53102       53103
         1         53104       53105
         1         53108       53109

4 rows selected.

SQL> def n=3
SQL> /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53100       53102
         1         53103       53105
         1         53108       53110

3 rows selected.

SQL> def n=4
SQL> /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53100       53103
         1         53104       53109

2 rows selected.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687344 is a reply to message #687343] Tue, 28 February 2023 09:07 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
Hi Michel thanks for replying. but it is not what i needed. your version returns

1 53100 53102
1 53103 53105
1 53108 53110

but i need like below

1 53100 53102
1 53102 53104
1 53104 53106
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687345 is a reply to message #687344] Tue, 28 February 2023 09:27 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
if i use your query like below i am getting exactly what i wanted but then issue i am facing last number
def n=3
with
data as (
select s.instance_number as inst_num, s.snap_id as start_snap_id,
lead(s.snap_id,&n-1,null) over
(partition by s.instance_number order by s.snap_id)
as end_snap_id,
row_number() over (partition by s.instance_number order by s.snap_id)
as rn
from dba_hist_snapshot s
where s.snap_id between 165952 and 165959
)
select inst_num, start_snap_id, end_snap_id
from data
where end_snap_id is not null and mod(rn,&n-1) = 1
order by inst_num, start_snap_id
/

1 165952 165954
1 165954 165956
1 165956 165958


but it does not returns the last rows i mean
1 165958 165959

Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687346 is a reply to message #687345] Tue, 28 February 2023 10:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
  2  /

INSTANCE_NUMBER    SNAP_ID
--------------- ----------
              1     165949
              1     165950
              1     165951
              1     165952
              1     165953
              1     165954
              1     165955
              1     165956
              1     165957
              1     165958
              1     165959
              2     165949
              2     165950
              2     165951
              2     165952
              2     165953
              2     165954
              2     165955
              2     165956
              2     165957
              2     165958
              2     165959

22 rows selected.

SCOTT@orcl_12.1.0.2.0> -- queries using test_data instead of dba_hist_snapshot
SCOTT@orcl_12.1.0.2.0> def n = 2
SCOTT@orcl_12.1.0.2.0> select inst_num, start_snap_id, end_snap_id
  2  from   (select s.instance_number as inst_num,
  3  		    s.snap_id as start_snap_id,
  4  		    lead(s.snap_id,&n,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id,
  5  		    row_number() over (partition by s.instance_number order by s.snap_id) as rn
  6  	    from   test_data s
  7  	    where  s.snap_id between 165952 and 165959)
  8  where  end_snap_id is not null and mod(rn,&n) = 1
  9  order  by inst_num, start_snap_id
 10  /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165954
         1        165954      165956
         1        165956      165958
         2        165952      165954
         2        165954      165956
         2        165956      165958

6 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 3
SCOTT@orcl_12.1.0.2.0> select inst_num, start_snap_id, end_snap_id
  2  from   (select s.instance_number as inst_num,
  3  		    s.snap_id as start_snap_id,
  4  		    lead(s.snap_id,&n,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id,
  5  		    row_number() over (partition by s.instance_number order by s.snap_id) as rn
  6  	    from   test_data s
  7  	    where  s.snap_id between 165952 and 165959)
  8  where  end_snap_id is not null and mod(rn,&n) = 1
  9  order  by inst_num, start_snap_id
 10  /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165955
         1        165955      165958
         2        165952      165955
         2        165955      165958

4 rows selected.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687347 is a reply to message #687346] Tue, 28 February 2023 10:40 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
Barbara Boehmer wrote on Tue, 28 February 2023 10:35
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
  2  /

INSTANCE_NUMBER    SNAP_ID
--------------- ----------
              1     165949
              1     165950
              1     165951
              1     165952
              1     165953
              1     165954
              1     165955
              1     165956
              1     165957
              1     165958
              1     165959
              2     165949
              2     165950
              2     165951
              2     165952
              2     165953
              2     165954
              2     165955
              2     165956
              2     165957
              2     165958
              2     165959

22 rows selected.

SCOTT@orcl_12.1.0.2.0> -- queries using test_data instead of dba_hist_snapshot
SCOTT@orcl_12.1.0.2.0> def n = 2
SCOTT@orcl_12.1.0.2.0> select inst_num, start_snap_id, end_snap_id
  2  from   (select s.instance_number as inst_num,
  3  		    s.snap_id as start_snap_id,
  4  		    lead(s.snap_id,&n,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id,
  5  		    row_number() over (partition by s.instance_number order by s.snap_id) as rn
  6  	    from   test_data s
  7  	    where  s.snap_id between 165952 and 165959)
  8  where  end_snap_id is not null and mod(rn,&n) = 1
  9  order  by inst_num, start_snap_id
 10  /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165954
         1        165954      165956
         1        165956      165958
         2        165952      165954
         2        165954      165956
         2        165956      165958

6 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 3
SCOTT@orcl_12.1.0.2.0> select inst_num, start_snap_id, end_snap_id
  2  from   (select s.instance_number as inst_num,
  3  		    s.snap_id as start_snap_id,
  4  		    lead(s.snap_id,&n,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id,
  5  		    row_number() over (partition by s.instance_number order by s.snap_id) as rn
  6  	    from   test_data s
  7  	    where  s.snap_id between 165952 and 165959)
  8  where  end_snap_id is not null and mod(rn,&n) = 1
  9  order  by inst_num, start_snap_id
 10  /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165955
         1        165955      165958
         2        165952      165955
         2        165955      165958

4 rows selected.

it is the same issue.  it does not covered last number from the list. last line has to be
1 165958 165959    but it is not.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687348 is a reply to message #687345] Tue, 28 February 2023 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

Quote:
but it is not what i needed. your version returns
I have non consecutive snap_id, some have been deleted.

Quote:
but it does not returns the last rows i mean...
This row does not fit your rule: grouping by 3 rows then selecting those where end_date is not null.
If you want incomplete groups then you have to either dynamically create the query if <n> is dynamic.
For instance using SQL*Plus, you build the query in substitution variables then execution this later.
First I create, in sql_beg, the beginning of the final query which does not depend on <n>:
SQL> col sql_beg new_value sql_beg
SQL> select q'[  data as (
  2      select s.instance_number as inst_num, s.snap_id as start_snap_id,
  3             coalesce(
  4  ]' sql_beg
  5  from dual
  6  /
SQL_BEG
--------------------------------------------------------------------------
  data as (
    select s.instance_number as inst_num, s.snap_id as start_snap_id,
           coalesce(
These parts are independent of the value of <n>.
Then I create the parts that depend on <n>: the parameters of the COALESCE function and the end of the query:
SQL> col sql_mid new_value sql_mid
SQL> col sql_end new_value sql_end
SQL> def n=3
SQL> select listagg('lead(s.snap_id,&n-'||level||',null) over
  2                 (partition by s.instance_number order by s.snap_id)',',
  3  ') within group (order by null) sql_mid
  4  from dual
  5  connect by level < &n
  6  /
SQL_MID
--------------------------------------------------------------------------------------
lead(s.snap_id,3-1,null) over
               (partition by s.instance_number order by s.snap_id),
lead(s.snap_id,3-2,null) over
               (partition by s.instance_number order by s.snap_id)

SQL> select q'[) as end_snap_id,
  2             row_number() over (partition by s.instance_number order by s.snap_id)
  3               as rn
  4      from dba_hist_snapshot s
  5      where s.snap_id between 53100 and 53111
  6    )
  7  select inst_num, start_snap_id, end_snap_id
  8  from data
  9  where end_snap_id is not null and mod(rn,&n) = 1
 10  order by inst_num, start_snap_id
 11  ]' sql_end
 12  from dual
 13  /
SQL_END
--------------------------------------------------------------------------------------
) as end_snap_id,
           row_number() over (partition by s.instance_number order by s.snap_id)
             as rn
    from dba_hist_snapshot s
    where s.snap_id between 53100 and 53111
  )
select inst_num, start_snap_id, end_snap_id
from data
where end_snap_id is not null and mod(rn,3) = 1
order by inst_num, start_snap_id
Then I execute the query inside these variables:
SQL> with &sql_beg &sql_mid &sql_end
  2  /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53100       53102
         1         53103       53105
         1         53108       53110

3 rows selected.
If I change <n> from 3 to 4, I just have to generate the middle and end parts and execute the same expression:
SQL> def n=4
SQL> select listagg('lead(s.snap_id,&n-'||level||',null) over
  2                 (partition by s.instance_number order by s.snap_id)',',
  3  ') within group (order by null) sql_mid
  4  from dual
  5  connect by level < &n
  6  /
SQL_MID
----------------------------------------------------------------------------------------------
lead(s.snap_id,4-1,null) over
               (partition by s.instance_number order by s.snap_id),
lead(s.snap_id,4-2,null) over
               (partition by s.instance_number order by s.snap_id),
lead(s.snap_id,4-3,null) over
               (partition by s.instance_number order by s.snap_id)

SQL> select q'[) as end_snap_id,
  2             row_number() over (partition by s.instance_number order by s.snap_id)
  3               as rn
  4      from dba_hist_snapshot s
  5      where s.snap_id between 53100 and 53111
  6    )
  7  select inst_num, start_snap_id, end_snap_id
  8  from data
  9  where end_snap_id is not null and mod(rn,&n) = 1
 10  order by inst_num, start_snap_id
 11  ]' sql_end
 12  from dual
 13  /
SQL_END
----------------------------------------------------------------------------------------------
) as end_snap_id,
           row_number() over (partition by s.instance_number order by s.snap_id)
             as rn
    from dba_hist_snapshot s
    where s.snap_id between 53100 and 53111
  )
select inst_num, start_snap_id, end_snap_id
from data
where end_snap_id is not null and mod(rn,4) = 1
order by inst_num, start_snap_id

SQL> with &sql_beg &sql_mid &sql_end
  2  /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53100       53103
         1         53104       53109
         1         53110       53111

3 rows selected.
You can see that I have now the last group which is incomplete.

[Edit] Note: You can do it as well in PL/SQL if you don't need "pure" SQL.
As Barbara is there, maybe she would like to provide the solution. Smile

[Updated on: Tue, 28 February 2023 10:56]

Report message to a moderator

Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687349 is a reply to message #687348] Tue, 28 February 2023 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's a PL/SQL version.
As I don't like dbms_output, I return the result in a cursor, you can print with SQL*Plus or with your client program (begin and end snap ids are also in substitution variables):
SQL> def snap_beg = 53100
SQL> def snap_end = 53111
SQL> def n = 2
SQL> var c refcursor
SQL> Declare
  2    sql_beg varchar2(256) :=
  3  q'[with
  4    data as (
  5      select s.instance_number as inst_num, s.snap_id as start_snap_id,
  6             coalesce(
  7  ]';
  8    sql_mid varchar2(1000);
  9    sql_end varchar2(500) :=
 10  q'[) as end_snap_id,
 11             row_number() over (partition by s.instance_number order by s.snap_id)
 12               as rn
 13      from dba_hist_snapshot s
 14      where s.snap_id between &snap_beg and &snap_end
 15    )
 16  select inst_num, start_snap_id, end_snap_id
 17  from data
 18  where end_snap_id is not null and mod(rn,&n) = 1
 19  order by inst_num, start_snap_id
 20  ]';
 21    t number;   -- test variable
 22  begin
 23    t := to_number('&n');
 24    if &n != trunc(&n) then
 25      raise_application_error (-20000, 'n should be an integer');
 26    end if;
 27    if &n < 2 then
 28      raise_application_error (-20001, 'n should be greater than or equal to 2');
 29    end if;
 30    if &n = 2 then
 31      open :c for
 32  q'[with
 33    data as (
 34      select s.instance_number as inst_num, s.snap_id as start_snap_id,
 35             lead(s.snap_id,1,null) over
 36               (partition by s.instance_number order by s.snap_id)
 37               as end_snap_id,
 38             row_number() over (partition by s.instance_number order by s.snap_id)
 39               as rn
 40      from dba_hist_snapshot s
 41      where s.snap_id between 53100 and 53110
 42    )
 43  select inst_num, start_snap_id, end_snap_id
 44  from data
 45  where end_snap_id is not null and mod(rn,2) = 1
 46  order by inst_num, start_snap_id]';
 47    else
 48      for i in 1..&n-1 loop
 49        sql_mid := sql_mid ||
 50                   'lead(s.snap_id,&n-'||i||',null) over
 51                 (partition by s.instance_number order by s.snap_id),';
 52      end loop;
 53      sql_mid := rtrim(sql_mid,',');
 54      open :c for sql_beg||sql_mid||sql_end;
 55    end if;
 56  end;
 57  /

PL/SQL procedure successfully completed.

SQL> print c
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53100       53101
         1         53102       53103
         1         53104       53105
         1         53108       53109

4 rows selected.

SQL> def n=3
SQL> /

PL/SQL procedure successfully completed.

SQL> print c
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53100       53102
         1         53103       53105
         1         53108       53110

3 rows selected.

SQL> def n=4
SQL> /

PL/SQL procedure successfully completed.

SQL> print c
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53100       53103
         1         53104       53109
         1         53110       53111

3 rows selected.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687350 is a reply to message #687348] Tue, 28 February 2023 13:14 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
Thank you so much Michel you are awesome!!!. i changed your query like below and i am getting exactly what i wanted.

with data as (
select s.instance_number as inst_num, s.snap_id as start_snap_id,
coalesce(lead(s.snap_id,&n,null) over(partition by s.instance_number order by s.snap_id),lead(s.snap_id,mod(165967-165952,&n-1),null) over (partition by s.instance_number order by s.snap_id)
) as end_snap_id,
row_number() over (partition by s.instance_number order by s.snap_id)
as rn
from dba_hist_snapshot s
where s.snap_id between 165952 and 165967
)
select inst_num, start_snap_id, end_snap_id
from data
where end_snap_id is not null and mod(rn,&n) = 1
order by inst_num, start_snap_id
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687351 is a reply to message #687350] Tue, 28 February 2023 13:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I see some problems with that. Please see my tests below using various number values for n, including 1, which returns no rows.

SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
  2  /

INSTANCE_NUMBER    SNAP_ID
--------------- ----------
              1     165949
              1     165950
              1     165951
              1     165952
              1     165953
              1     165954
              1     165955
              1     165956
              1     165957
              1     165958
              1     165959
              2     165949
              2     165950
              2     165951
              2     165952
              2     165953
              2     165954
              2     165955
              2     165956
              2     165957
              2     165958
              2     165959

22 rows selected.

SCOTT@orcl_12.1.0.2.0> -- queries using test_data instead of dba_hist_snapshot
SCOTT@orcl_12.1.0.2.0> def n = 1
SCOTT@orcl_12.1.0.2.0> with data as (
  2  select s.instance_number as inst_num, s.snap_id as start_snap_id,
  3  coalesce(lead(s.snap_id,&n,null) over(partition by s.instance_number order by s.snap_id),lead(s.snap_id,mod(165967-165952,&n-1),null) over (partition by s.instance_number order by s.snap_id)
  4  ) as end_snap_id,
  5  row_number() over (partition by s.instance_number order by s.snap_id)
  6  as rn
  7  from test_data s
  8  where s.snap_id between 165952 and 165967
  9  )
 10  select inst_num, start_snap_id, end_snap_id
 11  from data
 12  where end_snap_id is not null and mod(rn,&n) = 1
 13  order by inst_num, start_snap_id
 14  /

no rows selected

SCOTT@orcl_12.1.0.2.0> def n = 2
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165954
         1        165954      165956
         1        165956      165958
         1        165958      165958
         2        165952      165954
         2        165954      165956
         2        165956      165958
         2        165958      165958

8 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 3
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165955
         1        165955      165958
         1        165958      165959
         2        165952      165955
         2        165955      165958
         2        165958      165959

6 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 4
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165956
         1        165956      165956
         2        165952      165956
         2        165956      165956

4 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 5
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165957
         2        165952      165957

2 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 6
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165958
         1        165958      165958
         2        165952      165958
         2        165958      165958

4 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 7
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165959
         2        165952      165959

2 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 8
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165953
         2        165952      165953

2 rows selected.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687352 is a reply to message #687349] Tue, 28 February 2023 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or, if you prefer, creating a function taking the beginning and end snap ids and the number of rows in the group:
SQL> Create or replace function get_snap_groups
  2    (snap_beg integer, snap_end integer, n integer)
  3  return sys_refcursor
  4  is
  5    sql_beg varchar2(256);
  6    sql_mid varchar2(1000);
  7    sql_end varchar2(500);
  8    res sys_refcursor;
  9  begin
 10    if n < 2 then
 11      raise_application_error (-20001, 'n should be greater than or equal to 2');
 12    end if;
 13    if n = 2 then
 14      open res for
 15  'with
 16    data as (
 17      select s.instance_number as inst_num, s.snap_id as start_snap_id,
 18             lead(s.snap_id,1,null) over
 19               (partition by s.instance_number order by s.snap_id)
 20               as end_snap_id,
 21             row_number() over (partition by s.instance_number order by s.snap_id)
 22               as rn
 23      from dba_hist_snapshot s
 24      where s.snap_id between '||snap_beg||' and '||snap_end||'
 25    )
 26  select inst_num, start_snap_id, end_snap_id
 27  from data
 28  where end_snap_id is not null and mod(rn,2) = 1
 29  order by inst_num, start_snap_id';
 30    else
 31      sql_beg :=
 32  'with
 33    data as (
 34      select s.instance_number as inst_num, s.snap_id as start_snap_id,
 35             coalesce(
 36  ';
 37      sql_mid := '';
 38      for i in 1..n-1 loop
 39        sql_mid := sql_mid ||
 40                   'lead(s.snap_id,'||to_char(n-i)||',null) over
 41                 (partition by s.instance_number order by s.snap_id),';
 42      end loop;
 43      sql_mid := rtrim(sql_mid,',');
 44      sql_end :=
 45  ') as end_snap_id,
 46             row_number() over (partition by s.instance_number order by s.snap_id)
 47               as rn
 48      from dba_hist_snapshot s
 49      where s.snap_id between '||snap_beg||' and '||snap_end||'
 50    )
 51  select inst_num, start_snap_id, end_snap_id
 52  from data
 53  where end_snap_id is not null and mod(rn,'||n||') = 1
 54  order by inst_num, start_snap_id
 55  ';
 56      open res for sql_beg||sql_mid||sql_end;
 57    end if;
 58    return res;
 59  end;
 60  /

Function created.

SQL> exec :c := get_snap_groups(53110, 53123, 2)

PL/SQL procedure successfully completed.

SQL> print c
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53110       53111
         1         53112       53113
         1         53114       53115
         1         53116       53117
         1         53118       53119
         1         53120       53121
         1         53122       53123

7 rows selected.

SQL> exec :c := get_snap_groups(53110, 53123, 3)

PL/SQL procedure successfully completed.

SQL> print c
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53110       53112
         1         53113       53115
         1         53116       53118
         1         53119       53121
         1         53122       53123

5 rows selected.

SQL> exec :c := get_snap_groups(53110, 53123, 4)

PL/SQL procedure successfully completed.

SQL> print c
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53110       53113
         1         53114       53117
         1         53118       53121
         1         53122       53123

4 rows selected.
(Note: this is not the same snap ids than in my other posts as the cleaning job ran and deleted the previously selected rows.)
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687353 is a reply to message #687350] Tue, 28 February 2023 13:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is another possible version with tests of various number values for n.

SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
  2  /

INSTANCE_NUMBER    SNAP_ID
--------------- ----------
              1     165949
              1     165950
              1     165951
              1     165952
              1     165953
              1     165954
              1     165955
              1     165956
              1     165957
              1     165958
              1     165959
              2     165949
              2     165950
              2     165951
              2     165952
              2     165953
              2     165954
              2     165955
              2     165956
              2     165957
              2     165958
              2     165959

22 rows selected.

SCOTT@orcl_12.1.0.2.0> -- queries using test_data instead of dba_hist_snapshot
SCOTT@orcl_12.1.0.2.0> def n = 1
SCOTT@orcl_12.1.0.2.0> select inst_num, start_snap_id, end_snap_id
  2  from   (select instance_number as inst_num, snap_id as start_snap_id,
  3  		    lead(snap_id) over (partition by instance_number order by snap_id) as end_snap_id
  4  	     from   (select instance_number, snap_id
  5  		     from   (select instance_number, snap_id,
  6  				    row_number() over (partition by instance_number order by snap_id) rn
  7  			     from   test_data
  8  			     where  snap_id between 165952 and 165959)
  9  		     start  with rn = 1
 10  		     connect by prior instance_number = instance_number and prior rn = rn - &n
 11  		     union
 12  		     select instance_number, max(snap_id) as snap_id
 13  		     from   test_data
 14  		     group  by instance_number))
 15  where  end_snap_id is not null
 16  order  by inst_num, start_snap_id
 17  /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165953
         1        165953      165954
         1        165954      165955
         1        165955      165956
         1        165956      165957
         1        165957      165958
         1        165958      165959
         2        165952      165953
         2        165953      165954
         2        165954      165955
         2        165955      165956
         2        165956      165957
         2        165957      165958
         2        165958      165959

14 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 2
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165954
         1        165954      165956
         1        165956      165958
         1        165958      165959
         2        165952      165954
         2        165954      165956
         2        165956      165958
         2        165958      165959

8 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 3
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165955
         1        165955      165958
         1        165958      165959
         2        165952      165955
         2        165955      165958
         2        165958      165959

6 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 4
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165956
         1        165956      165959
         2        165952      165956
         2        165956      165959

4 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 5
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165957
         1        165957      165959
         2        165952      165957
         2        165957      165959

4 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 6
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165958
         1        165958      165959
         2        165952      165958
         2        165958      165959

4 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 7
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165959
         2        165952      165959

2 rows selected.

SCOTT@orcl_12.1.0.2.0> def n = 8
SCOTT@orcl_12.1.0.2.0> /

  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1        165952      165959
         2        165952      165959

2 rows selected.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687354 is a reply to message #687352] Tue, 28 February 2023 13:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or with a pipelined function:
SQL> Create or replace type snap_group_type as object (
  2    inst_num      integer,
  3    start_snap_id integer,
  4    end_snap_id   integer
  5    )
  6  /

Type created.

SQL> Create or replace type snap_group_array is table of snap_group_type
  2  /

Type created.

SQL> Create or replace function get_snap_groups
  2    (snap_beg integer, snap_end integer, n integer)
  3  return snap_group_array pipelined
  4  is
  5    sql_beg varchar2(256);
  6    sql_mid varchar2(1000);
  7    sql_end varchar2(500);
  8    sql_str varchar2(2000);
  9    res     sys_refcursor;
 10    rec     snap_group_type;
 11  begin
 12    if n < 2 then
 13      raise_application_error (-20001, 'n should be greater than or equal to 2');
 14    end if;
 15    if n = 2 then
 16      sql_str :=
 17  'with
 18    data as (
 19      select s.instance_number as inst_num, s.snap_id as start_snap_id,
 20             lead(s.snap_id,1,null) over
 21               (partition by s.instance_number order by s.snap_id)
 22               as end_snap_id,
 23             row_number() over (partition by s.instance_number order by s.snap_id)
 24               as rn
 25      from dba_hist_snapshot s
 26      where s.snap_id between '||snap_beg||' and '||snap_end||'
 27    )
 28  select snap_group_type(inst_num, start_snap_id, end_snap_id)
 29  from data
 30  where end_snap_id is not null and mod(rn,2) = 1
 31  order by inst_num, start_snap_id';
 32    else
 33      sql_beg :=
 34  'with
 35    data as (
 36      select s.instance_number as inst_num, s.snap_id as start_snap_id,
 37             coalesce(
 38  ';
 39      sql_mid := '';
 40      for i in 1..n-1 loop
 41        sql_mid := sql_mid ||
 42                   'lead(s.snap_id,'||to_char(n-i)||',null) over
 43                 (partition by s.instance_number order by s.snap_id),';
 44      end loop;
 45      sql_mid := rtrim(sql_mid,',');
 46      sql_end :=
 47  ') as end_snap_id,
 48             row_number() over (partition by s.instance_number order by s.snap_id)
 49               as rn
 50      from dba_hist_snapshot s
 51      where s.snap_id between '||snap_beg||' and '||snap_end||'
 52    )
 53  select snap_group_type(inst_num, start_snap_id, end_snap_id)
 54  from data
 55  where end_snap_id is not null and mod(rn,'||n||') = 1
 56  order by inst_num, start_snap_id
 57  ';
 58      sql_str := sql_beg||sql_mid||sql_end;
 59    end if;
 60    open res for sql_str;
 61    loop
 62      fetch res into rec;
 63      exit when res%notfound;
 64      pipe row(rec);
 65    end loop;
 66    close res;
 67  end;
 68  /

Function created.

SQL> select * from table(get_snap_groups(53110, 53123, 2))
  2  /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53110       53111
         1         53112       53113
         1         53114       53115
         1         53116       53117
         1         53118       53119
         1         53120       53121
         1         53122       53123

7 rows selected.

SQL> select * from table(get_snap_groups(53110, 53123, 3))
  2  /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53110       53112
         1         53113       53115
         1         53116       53118
         1         53119       53121
         1         53122       53123

5 rows selected.

SQL> select * from table(get_snap_groups(53110, 53123, 4))
  2  /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53110       53113
         1         53114       53117
         1         53118       53121
         1         53122       53123

4 rows selected.

[Updated on: Wed, 01 March 2023 00:07]

Report message to a moderator

Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687356 is a reply to message #687354] Tue, 28 February 2023 22:36 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
still having issue Sad((
select * from table(get_snap_groups(53110, 53123, 3))
2 /
INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
1 53110 53112
1 53113 53115 ---> in this line i want 53112 53114
1 53116 53118 ---> again 53114 53116
1 53119 53121
1 53122 53123

is it possible you can do like this? i am sorry giving you trouble.

[Updated on: Tue, 28 February 2023 22:37]

Report message to a moderator

Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687357 is a reply to message #687356] Wed, 01 March 2023 00:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ FORMAT your post
2/ Provide a COMPLETE specification with WORDS

Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687358 is a reply to message #687356] Wed, 01 March 2023 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If I understand you, you don't want each group starting with the next snap but each group starting with the previous end snap, so:
SQL> Create or replace function get_snap_groups
  2    (snap_beg integer, snap_end integer, n integer)
  3  return snap_group_array pipelined
  4  is
  5    sql_beg varchar2(256);
  6    sql_mid varchar2(1000);
  7    sql_end varchar2(1000);
  8    sql_str varchar2(2000);
  9    res     sys_refcursor;
 10    rec     snap_group_type;
 11  begin
 12    if n < 2 then
 13      raise_application_error (-20001, 'n should be greater than or equal to 2');
 14    end if;
 15    if n = 2 then
 16      sql_str :=
 17  'with
 18    data as (
 19      select s.instance_number as inst_num, s.snap_id as start_snap_id,
 20             lead(s.snap_id,1,null) over
 21               (partition by s.instance_number order by s.snap_id)
 22               as end_snap_id,
 23             row_number() over (partition by s.instance_number order by s.snap_id)
 24               as rn
 25      from dba_hist_snapshot s
 26      where s.snap_id between '||snap_beg||' and '||snap_end||'
 27    )
 28  select snap_group_type(inst_num, start_snap_id, end_snap_id)
 29  from data
 30  where end_snap_id is not null
 31  order by inst_num, start_snap_id';
 32    else
 33      sql_beg :=
 34  'with
 35    data as (
 36      select s.instance_number as inst_num, s.snap_id as start_snap_id,
 37             coalesce(';
 38      sql_mid := '';
 39      for i in 1..n-1 loop
 40        sql_mid := sql_mid ||
 41                   '
 42  lead(s.snap_id,'||to_char(n-i)||',null) over
 43                 (partition by s.instance_number order by s.snap_id),';
 44      end loop;
 45      sql_mid := rtrim(sql_mid,',');
 46      sql_end :=
 47  ') as end_snap_id,
 48             row_number() over (partition by s.instance_number order by s.snap_id)
 49               as rn
 50      from dba_hist_snapshot s
 51      where s.snap_id between '||snap_beg||' and '||snap_end||'
 52    )
 53  select snap_group_type(inst_num, start_snap_id, end_snap_id)
 54  from data
 55  where end_snap_id is not null
 56    and mod(rn,'||to_char(n-1)||') = 1
 57  order by inst_num, start_snap_id
 58  ';
 59      sql_str := sql_beg||sql_mid||sql_end;
 60    end if;
 61    open res for sql_str;
 62    loop
 63      fetch res into rec;
 64      exit when res%notfound;
 65      pipe row(rec);
 66    end loop;
 67    close res;
 68  end;
 69  /

Function created.

SQL> select * from table(get_snap_groups(53110, 53123, 2))
  2  /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53110       53111
         1         53111       53112
         1         53112       53113
         1         53113       53114
         1         53114       53115
         1         53115       53116
         1         53116       53117
         1         53117       53118
         1         53118       53119
         1         53119       53120
         1         53120       53121
         1         53121       53122
         1         53122       53123

13 rows selected.

SQL> select * from table(get_snap_groups(53110, 53123, 3))
  2  /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53110       53112
         1         53112       53114
         1         53114       53116
         1         53116       53118
         1         53118       53120
         1         53120       53122
         1         53122       53123

7 rows selected.

SQL> select * from table(get_snap_groups(53110, 53123, 4))
  2  /
  INST_NUM START_SNAP_ID END_SNAP_ID
---------- ------------- -----------
         1         53110       53113
         1         53113       53116
         1         53116       53119
         1         53119       53122
         1         53122       53123

5 rows selected.

[Updated on: Wed, 01 March 2023 00:46]

Report message to a moderator

Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687359 is a reply to message #687356] Wed, 01 March 2023 02:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
avtaritet,

Did you try the last query that I posted? It provides everything you have asked for and works for all values of n, and I posted the test results to prove it. Here it is again without line numbers and using dba_hist_snapshot, so you can just copy and paste it and run it.

def n = 1
select inst_num, start_snap_id, end_snap_id
from   (select instance_number as inst_num, snap_id as start_snap_id,
               lead(snap_id) over (partition by instance_number order by snap_id) as end_snap_id
        from   (select instance_number, snap_id
                from   (select instance_number, snap_id,
                               row_number() over (partition by instance_number order by snap_id) rn
                        from   dba_hist_snapshot
                        where  snap_id between 165952 and 165959)
                start  with rn = 1 
                connect by prior instance_number = instance_number and prior rn = rn - &n
                union  
                select instance_number, max(snap_id) as snap_id
                from   dba_hist_snapshot
                group  by instance_number))
where  end_snap_id is not null
order  by inst_num, start_snap_id
/
def n = 2
/
def n = 3
/
def n = 4
/
def n = 5
/
def n = 6
/
def n = 7
/
def n = 8
/
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687360 is a reply to message #687359] Wed, 01 March 2023 10:05 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
Thank you everyone i am able to figure out thanks again for your help.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687365 is a reply to message #687359] Wed, 01 March 2023 13:44 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
Barbara Boehmer wrote on Wed, 01 March 2023 02:34
avtaritet,

Did you try the last query that I posted? It provides everything you have asked for and works for all values of n, and I posted the test results to prove it. Here it is again without line numbers and using dba_hist_snapshot, so you can just copy and paste it and run it.

def n = 1
select inst_num, start_snap_id, end_snap_id
from   (select instance_number as inst_num, snap_id as start_snap_id,
               lead(snap_id) over (partition by instance_number order by snap_id) as end_snap_id
        from   (select instance_number, snap_id
                from   (select instance_number, snap_id,
                               row_number() over (partition by instance_number order by snap_id) rn
                        from   dba_hist_snapshot
                        where  snap_id between 165952 and 165959)
                start  with rn = 1 
                connect by prior instance_number = instance_number and prior rn = rn - &n
                union  
                select instance_number, max(snap_id) as snap_id
                from   dba_hist_snapshot
                group  by instance_number))
where  end_snap_id is not null
order  by inst_num, start_snap_id
/
def n = 2
/
def n = 3
/
def n = 4
/
def n = 5
/
def n = 6
/
def n = 7
/
def n = 8
/
whatever you posted above i used it generates some wrong result.

1	165952	165954
1	165954	165956
1	165956	165958
1	165958	166156   ---> wrong generated rows 165959 also does not exists
2	165952	165954
2	165954	165956
2	165956	165958
2	165958	166156   ---> wrong generated rows and 165959 also does not exists

Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687366 is a reply to message #687365] Wed, 01 March 2023 13:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It looks like your data has gaps that I did not anticipate. I assumed, apparently incorrectly, that it was all consecutive values. Would you please post what you figured out that did work? I would like to see it and I imagine others would too.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687367 is a reply to message #687366] Wed, 01 March 2023 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sure.
Till now we both assume OP wants values from real rows not generated ones.

As I said:
Quote:
2/ Provide a COMPLETE specification with WORDS.

And I add with a clear test case.


Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687368 is a reply to message #687367] Thu, 02 March 2023 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If this has nothing to do with rows in dba_hist_snapshot and you just want number groups between 2 values but taking into account the number of instances then:
SQL> def beg_num=53110
SQL> def end_num=53119
SQL> def n=2
SQL> with
  2    instances as (select inst_id from gv$instance),
  3    numbers as (
  4      select &&beg_num+(&&n-1)*(level-1) start_num,
  5             least(&&beg_num+(&&n-1)*(level),&&end_num) end_num
  6      from dual
  7      connect by level <= trunc((&&end_num-&&beg_num)/(&&n-1))+1
  8    )
  9  select inst_id, start_num, end_num
 10  from instances, numbers
 11  where start_num < &&end_num
 12  order by inst_id, start_num
 13  /

   INST_ID  START_NUM    END_NUM
---------- ---------- ----------
         1      53110      53111
         1      53111      53112
         1      53112      53113
         1      53113      53114
         1      53114      53115
         1      53115      53116
         1      53116      53117
         1      53117      53118
         1      53118      53119

9 rows selected.

SQL> def n=3
SQL> /

   INST_ID  START_NUM    END_NUM
---------- ---------- ----------
         1      53110      53112
         1      53112      53114
         1      53114      53116
         1      53116      53118
         1      53118      53119

5 rows selected.

SQL> def n=4
SQL> /

   INST_ID  START_NUM    END_NUM
---------- ---------- ----------
         1      53110      53113
         1      53113      53116
         1      53116      53119

3 rows selected.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687369 is a reply to message #687368] Thu, 02 March 2023 15:00 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
thank you so much Michel this one works as i needed thanks a lot
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687381 is a reply to message #687369] Sun, 05 March 2023 20:34 Go to previous messageGo to next message
mathguy
Messages: 106
Registered: January 2023
Senior Member
This problem is a nice application of MATCH_RECOGNIZE, available since Oracle 12.1.

The input is a set of rows in two columns; the first is a group identifier (in your case, the instance number) and the second is a non-NULL number, unique within the group (in your case, the snapshot id). The task, for a given integer n > 0, is to order the values in each group, and to "mark" the first, the (n+1)st, the (2n+1)st, ... values (and in all cases the last value as well), and then to present the result in the format you requested.

For example, if in a group the values are 1,4,5,89,91,92,93,94 and n = 3, then you want the first, the fourth, the seventh, and the last (the eighth) values; these are 1, 89, 93 and 94. And then you want the result shown like this:

start end
    1  89
   89  93
   93  94
If in the same example we delete the last value (94), so that there are only seven values, you want only the first two rows of the above output to be the new output. (However, if the entire group consists of exactly one row, for one value, then the output should have only one row, showing that unique value as both the start and the end.)

Correct?

The problem has nothing to do with "instance number" and "snapshot id" - I will treat it in full generality.

Here is how MATCH_RECOGNIZE can solve this problem. I use n = 2 for illustration. Alas for some reason I can't understand, the syntax doesn't allow a bind variable in the relevant place (in the PATTERN clause of MATCH_RECOGNIZE); you can use a substitution variable, as was proposed in other solutions in this thread, but be mindful of potential risks.

I included an extensive test case to verify that the query returns the correct result for all cases.

create table inputs (grp, num) as
  select 1, 1000 from dual union all
  select 2, 2001 from dual union all
  select 2, 2002 from dual union all
  select 3, 3100 from dual union all
  select 3, 3200 from dual union all
  select 3, 3300 from dual union all
  select 4, 4111 from dual union all
  select 4, 4222 from dual union all
  select 4, 4333 from dual union all
  select 4, 4444 from dual union all
  select 5, 5001 from dual union all
  select 5, 5002 from dual union all
  select 5, 5003 from dual union all
  select 5, 5004 from dual union all
  select 5, 5005 from dual union all
  select 6, 1111 from dual union all
  select 6, 2222 from dual union all
  select 6, 3333 from dual union all
  select 6, 4444 from dual union all
  select 6, 5555 from dual union all
  select 6, 6666 from dual
;
Query and output:

select grp, start_num, end_num
from   inputs
match_recognize(
  partition by grp
  order     by num
  measures  min(num) as start_num, nvl(next(num), max(num)) as end_num, match_number() as mn
  pattern   ( x{1,2} )       --  replace 2 with another hard-coded integer, or substitution variable &n
  define    x as null is null
)
where  start_num != end_num or mn = 1
;

       GRP  START_NUM    END_NUM
---------- ---------- ----------
         1       1000       1000
         2       2001       2002
         3       3100       3300
         4       4111       4333
         4       4333       4444
         5       5001       5003
         5       5003       5005
         6       1111       3333
         6       3333       5555
         6       5555       6666
Note the WHERE clause; it (and the inclusion of MATCH_NUMBER() in the MEASURES list) is needed to handle the exceptional case of a group that has only one value (one row) in the inputs. This is the only case in which the last value in the group should ever appear in the START_NUM column.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687388 is a reply to message #687381] Mon, 06 March 2023 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
This problem is a nice application of MATCH_RECOGNIZE

Not at all, it is just a simple row generator.

You just make up a new problem that has nothing to do with OP's one.

Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687394 is a reply to message #687388] Mon, 06 March 2023 08:16 Go to previous messageGo to next message
mathguy
Messages: 106
Registered: January 2023
Senior Member
Michel Cadot wrote on Mon, 06 March 2023 00:42

Quote:
This problem is a nice application of MATCH_RECOGNIZE
Not at all, it is just a simple row generator.

You just make up a new problem that has nothing to do with OP's one.

How so?

I can't read people's minds, and in particular I don't know what you mean by "a simple row generator". (By the way, this is why when I answer a question with a not 100% clear task description, I include all the assumptions I made - I don't expect others to read my mind either.)

I may be mistaken, but it seems to me that your assumption is that for a given instance, the snapshot id's are consecutive integers. Is that what you mean?

If so, perhaps you missed the following clarification from the OP:

Quote:
I have non consecutive snap_id, some have been deleted.
Any strictly increasing sequence of integers can be obtained from a sequence of consecutive integers, by deleting some of the elements. So, my problem is exactly what the OP requested. If you feel otherwise, please explain. Or, perhaps, let's let the OP read the answer and tell us if it is what he needed, or not.
Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687396 is a reply to message #687381] Mon, 06 March 2023 08:38 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
mathguy wrote on Sun, 05 March 2023 20:34
This problem is a nice application of MATCH_RECOGNIZE, available since Oracle 12.1.

The input is a set of rows in two columns; the first is a group identifier (in your case, the instance number) and the second is a non-NULL number, unique within the group (in your case, the snapshot id). The task, for a given integer n > 0, is to order the values in each group, and to "mark" the first, the (n+1)st, the (2n+1)st, ... values (and in all cases the last value as well), and then to present the result in the format you requested.

For example, if in a group the values are 1,4,5,89,91,92,93,94 and n = 3, then you want the first, the fourth, the seventh, and the last (the eighth) values; these are 1, 89, 93 and 94. And then you want the result shown like this:

start end
    1  89
   89  93
   93  94
If in the same example we delete the last value (94), so that there are only seven values, you want only the first two rows of the above output to be the new output. (However, if the entire group consists of exactly one row, for one value, then the output should have only one row, showing that unique value as both the start and the end.)

Correct?

The problem has nothing to do with "instance number" and "snapshot id" - I will treat it in full generality.

Here is how MATCH_RECOGNIZE can solve this problem. I use n = 2 for illustration. Alas for some reason I can't understand, the syntax doesn't allow a bind variable in the relevant place (in the PATTERN clause of MATCH_RECOGNIZE); you can use a substitution variable, as was proposed in other solutions in this thread, but be mindful of potential risks.

I included an extensive test case to verify that the query returns the correct result for all cases.

create table inputs (grp, num) as
  select 1, 1000 from dual union all
  select 2, 2001 from dual union all
  select 2, 2002 from dual union all
  select 3, 3100 from dual union all
  select 3, 3200 from dual union all
  select 3, 3300 from dual union all
  select 4, 4111 from dual union all
  select 4, 4222 from dual union all
  select 4, 4333 from dual union all
  select 4, 4444 from dual union all
  select 5, 5001 from dual union all
  select 5, 5002 from dual union all
  select 5, 5003 from dual union all
  select 5, 5004 from dual union all
  select 5, 5005 from dual union all
  select 6, 1111 from dual union all
  select 6, 2222 from dual union all
  select 6, 3333 from dual union all
  select 6, 4444 from dual union all
  select 6, 5555 from dual union all
  select 6, 6666 from dual
;
Query and output:

select grp, start_num, end_num
from   inputs
match_recognize(
  partition by grp
  order     by num
  measures  min(num) as start_num, nvl(next(num), max(num)) as end_num, match_number() as mn
  pattern   ( x{1,2} )       --  replace 2 with another hard-coded integer, or substitution variable &n
  define    x as null is null
)
where  start_num != end_num or mn = 1
;

       GRP  START_NUM    END_NUM
---------- ---------- ----------
         1       1000       1000
         2       2001       2002
         3       3100       3300
         4       4111       4333
         4       4333       4444
         5       5001       5003
         5       5003       5005
         6       1111       3333
         6       3333       5555
         6       5555       6666
Note the WHERE clause; it (and the inclusion of MATCH_NUMBER() in the MEASURES list) is needed to handle the exceptional case of a group that has only one value (one row) in the inputs. This is the only case in which the last value in the group should ever appear in the START_NUM column.
Thank you so much mathguy this is so good script i did not heard before. thanks a lot you guys. You are just awesome!!

[Updated on: Mon, 06 March 2023 08:38]

Report message to a moderator

Re: how to incrementally get all numbers incremented by n between 2 columns values [message #687397 is a reply to message #687394] Mon, 06 March 2023 08:38 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think you did not read clearly the whole topic.
I admit I, myself, didn't see that start of a group s end of previous group.
I did answer to OP's first answer to my posts that my snap ids are not consecutive this is why number of ids in groups seem to not be equal and this is why I numbered the rows (row_number...).

Then during the posts it appears (from his complaint about wrong results when there are non consecutive ids in dba_hist_snapshots) that OP does not care about what's inside a table in the database but wants groups of consecutive numbers.

In the end, he clearly said that my last query, with a simple row generator, fits his needs (although I have some doubt about the instance ids but kept them to match the first post).

Previous Topic: Need Help with Table Type
Next Topic: Grouping with the previous one if identical
Goto Forum:
  


Current Time: Fri Mar 29 07:01:17 CDT 2024