Tabibitosan Method: Example 8


In the below example, the records with an id of 1-1 do not have any continuous records therefore the count of the continuous records would return nothing. The 2-2 record has two groups of continuous records, the first group has 4 continuous records and the
second group has 2 continuous records.

with ex11 as (
select '1-1' id,'SAND' lith,150 t_depth from dual union
select '1-1' id,'COAL' lith,200 t_depth from dual union
select '1-1' id,'SAND' lith,250 t_depth from dual union
select '1-1' id,'COAL' lith,300 t_depth from dual union
select '2-2' id,'SAND' lith, 75 t_depth from dual union
select '2-2' id,'COAL' lith,100 t_depth from dual union
select '2-2' id,'COAL' lith,150 t_depth from dual union
select '2-2' id,'COAL' lith,200 t_depth from dual union
select '2-2' id,'COAL' lith,250 t_depth from dual union
select '2-2' id,'SAND' lith,300 t_depth from dual union
select '2-2' id,'COAL' lith,400 t_depth from dual union
select '2-2' id,'COAL' lith,450 t_depth from dual)
select * from ex11;

Table created.

SQL> select * from ex11 order by lith,id;

ID  LITH    T_DEPTH
--- ---- ----------
1-1 COAL        200
1-1 COAL        300
1-1 SAND        150
1-1 SAND        250
2-2 COAL        100
2-2 COAL        150
2-2 COAL        200
2-2 COAL        250
2-2 COAL        400
2-2 COAL        450
2-2 SAND         75
2-2 SAND        300

12 rows selected.

SQL> select
  2   id,
  3   lith,
  4   row_number() over (partition by id  order by id,t_depth) rn1,
  5   t_depth
  6  from ex11;

ID  LITH        RN1    T_DEPTH
--- ---- ---------- ----------
1-1 SAND          1        150
1-1 COAL          2        200
1-1 SAND          3        250
1-1 COAL          4        300
2-2 SAND          1         75
2-2 COAL          2        100
2-2 COAL          3        150
2-2 COAL          4        200
2-2 COAL          5        250
2-2 SAND          6        300
2-2 COAL          7        400
2-2 COAL          8        450

12 rows selected.

Expected output:

1-1 null
2-2 4
2-2 2


SQL> select
  2   id,
  3   lith,
  4   row_number() over (partition by id  order by id,t_depth) r1,
  5   row_number() over (partition by id,lith order by id,t_depth) r2,
  6   row_number() over (partition by id  order by id,t_depth)
  7    - row_number() over (partition by id,lith order by id,t_depth) r3,
  8   t_depth
  9  from ex11;

ID  LITH         R1         R2         R3    T_DEPTH
--- ---- ---------- ---------- ---------- ----------
1-1 SAND          1          1          0        150
1-1 COAL          2          1          1        200
1-1 SAND          3          2          1        250
1-1 COAL          4          2          2        300
2-2 SAND          1          1          0         75
2-2 COAL          2          1          1        100
2-2 COAL          3          2          1        150
2-2 COAL          4          3          1        200
2-2 COAL          5          4          1        250
2-2 SAND          6          2          4        300
2-2 COAL          7          5          2        400
2-2 COAL          8          6          2        450

12 rows selected.


SQL> select id,
  2         cnt
  3   from  
  4   (
  5   select  id,
  6     lith,
  7     count(*) cnt    
  8   from  (
  9     select
 10      id,
 11      lith,
 12      --row_number() over (partition by id  order by id,t_depth) r1,
 13      --row_number() over (partition by id,lith order by id,t_depth) r2,
 14      row_number() over (partition by id  order by id,t_depth)
 15       - row_number() over (partition by id,lith order by id,t_depth) r3
 16      --t_depth
 17     from ex11
 18     )
 19   group by id, lith,r3 );

ID         CNT
--- ----------
1-1          1
1-1          1
1-1          1
1-1          1
2-2          4
2-2          2
2-2          1
2-2          1

8 rows selected.

sort cnt in descending order to get something like below

ID         CNT
--- ----------
1-1          1    1
1-1          1    2
1-1          1    3
1-1          1    4
2-2          4    1
2-2          2    2
2-2          1    3
2-2          1    4

SQL> select id,
  2         cnt,
  3      row_number() over ( partition by id order by cnt desc) rn
  4   from  
  5   (
  6   select  id,
  7     lith,
  8     count(*) cnt    
  9   from  (
 10     select
 11      id,
 12      lith,
 13      --row_number() over (partition by id  order by id,t_depth) r1,
 14      --row_number() over (partition by id,lith order by id,t_depth) r2,
 15      row_number() over (partition by id  order by id,t_depth)
 16       - row_number() over (partition by id,lith order by id,t_depth) r3
 17      --t_depth
 18     from ex11
 19     )
 20   group by id, lith,r3 );

ID         CNT         RN
--- ---------- ----------
1-1          1          1
1-1          1          2
1-1          1          3
1-1          1          4
2-2          4          1
2-2          2          2
2-2          1          3
2-2          1          4

8 rows selected.

consider rn=1 for both id and cnt >1

SQL>   select id, cnt
  2    from (
  3      select id,
  4      cnt,
  5      row_number() over ( partition by id order by cnt desc) rn
  6      from  
  7      (
  8      select  id,
  9     lith,
 10     count(*) cnt    
 11      from  (
 12     select
 13      id,
 14      lith,
 15      --row_number() over (partition by id  order by id,t_depth) r1,
 16      --row_number() over (partition by id,lith order by id,t_depth) r2,
 17      row_number() over (partition by id  order by id,t_depth)
 18       - row_number() over (partition by id,lith order by id,t_depth) r3
 19      --t_depth
 20     from ex11
 21     )
 22      group by id, lith,r3 ))
 23    where rn=1 or cnt>1;

ID         CNT
--- ----------
1-1          1
2-2          4
2-2          2


SQL>   select id, nullif(cnt,1) cnt
  2    from (
  3      select id,
  4      cnt,
  5      row_number() over ( partition by id order by cnt desc) rn
  6      from  
  7      (
  8      select  id,
  9     lith,
 10     count(*) cnt    
 11      from  (
 12     select
 13      id,
 14      lith,
 15      --row_number() over (partition by id  order by id,t_depth) r1,
 16      --row_number() over (partition by id,lith order by id,t_depth) r2,
 17      row_number() over (partition by id  order by id,t_depth)
 18       - row_number() over (partition by id,lith order by id,t_depth) r3
 19      --t_depth
 20     from ex11
 21     )
 22      group by id, lith,r3 ))
 23    where rn=1 or cnt>1;

ID         CNT
--- ----------
1-1
2-2          4
2-2          2

Tabibitosan Method: Example 7

Get consecutive date range (without any gap) per  group

SQL> create table ex8
  2  ( id number
  3  , dt date);

Table created.

SQL> insert into ex8 values (2, to_date( '01-01-2014','dd-mm-yyyy' ));

1 row created.

SQL> insert into ex8 values (2, to_date( '02-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '03-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '04-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '05-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '06-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '07-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '08-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '09-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '10-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '11-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '12-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (4, to_date( '13-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (5, to_date( '14-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '15-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '16-01-2014','dd-mm-yyyy'));

1 row created.

SQL> commit;

Commit complete.

Expected result:
         2 01-01-2014 03-01-2014
         3 04-01-2014 08-01-2014
         2 09-01-2014 10-01-2014
         3 11-01-2014 12-01-2014
         4 13-01-2014 13-01-2014
         5 14-01-2014 14-01-2014
         2 15-01-2014 16-01-2014




SQL> select id, min(dt), max(dt) from
  2  (
  3  select
  4   id,
  5   dt,
  6   dt - row_number() over (partition by id order by dt,id) grp
  7  from ex8)
  8  group by id,grp
  9  order by min(dt);

        ID MIN(DT)    MAX(DT)
---------- ---------- ----------
         2 01-01-2014 03-01-2014
         3 04-01-2014 08-01-2014
         2 09-01-2014 10-01-2014
         3 11-01-2014 12-01-2014
         4 13-01-2014 13-01-2014
         5 14-01-2014 14-01-2014
         2 15-01-2014 16-01-2014

7 rows selected.

Tabibitosan Method: Example 6
Get count of latest consecutive years per group

SQL>  create table ex8 as(
  2   select 1 as ID,2009 as yr from dual union
  3   select 1,2008 from dual union
  4   select 1,2007 from dual union
  5   select 1,2006 from dual union
  6   select 1,2005 from dual union
  7   select 1,2004 from dual union
  8   select 1,2002 from dual union
  9   select 1,2001 from dual union
 10   select 2,2009 from dual union
 11   select 2,2008 from dual union
 12   select 2,2005 from dual);

Table created.

SQL> select * from ex8;

        ID         YR
---------- ----------
         1       2001
         1       2002
         1       2004
         1       2005
         1       2006
         1       2007
         1       2008
         1       2009
         2       2005
         2       2008
         2       2009

11 rows selected.

Expected output:

1    6
2    2

SQL> select
  2   yr - row_number() over ( partition by id order by yr)
  3  from ex8;

YR-ROW_NUMBER()OVER(PARTITIONBYIDORDERBYYR)
-------------------------------------------
                                       2000
                                       2000
                                       2001
                                       2001
                                       2001
                                       2001
                                       2001
                                       2001
                                       2004
                                       2006
                                       2006

11 rows selected.

SQL> select  id
  2    yr,
  3    yr - row_number() over ( partition by id order by yr) seq
  4  from ex8;

        YR        SEQ
---------- ----------
         1       2000
         1       2000
         1       2001
         1       2001
         1       2001
         1       2001
         1       2001
         1       2001
         2       2004
         2       2006
         2       2006

11 rows selected.

SQL> select id,count(*) keep (dense_rank last order by grp) cnt
  2  from (
  3    select  id,
  4      yr,
  5      yr - row_number() over ( partition by id order by yr) grp
  6    from ex8)
  7  group by id;

        ID        CNT
---------- ----------
         1          6
         2          2

Tabibitosan Method: Example 5

Sequencial value range (without any gap) for same composite column groups

SQL> create table ex7 (sortKey,Val1,Val2) as
  2  select 1,'A','X' from dual union all
  3  select 2,'A','X' from dual union all
  4  select 3,'B','Y' from dual union all
  5  select 4,'B','Y' from dual union all
  6  select 5,'A','X' from dual union all
  7  select 5,'B','X' from dual union all
  8  select 6,'A','Y' from dual union all
  9  select 7,'B','Y' from dual union all
 10  select 7,'A','Y' from dual union all
 11  select 8,'A','Y' from dual;

Table created.

SQL> select * from ex7;

   SORTKEY V V
---------- - -
         1 A X
         2 A X
         ----------
         3 B Y
         4 B Y
         ------------
         5 A X
         -----------
         5 B X
         -----------------
         6 A Y
         -----------
         7 B Y
         --------------
         7 A Y
         8 A Y

10 rows selected.

Expected Result:

MIN(SORTKEY) MAX(SORTKEY) V V
------------ ------------ - -
           1            2 A X
           3            4 B Y
           5            5 A X
           5            5 B X
           6            8 A Y
           7            7 B Y


/* partition on val1, val2 and then sort on sortkey */

SQL> select sortkey,
  2         val1, val2 ,
  3         row_number() over ( partition by val1,val2 order by sortkey,val1,val2 ) diff,
  4      sortkey - row_number() over ( partition by val1,val2 order by sortkey,val1,val2 ) grp
  5  from ex7;

   SORTKEY V V       DIFF        GRP
---------- - - ---------- ----------
         1 A X          1          0
         2 A X          2          0
         5 A X          3          2
         6 A Y          1          5
         7 A Y          2          5
         8 A Y          3          5
         5 B X          1          4
         3 B Y          1          2
         4 B Y          2          2
         7 B Y          3          4

10 rows selected.

SQL> select min(sortkey),max(sortkey),val1,val2
  2      from (
  3      select sortkey,
  4            val1, val2 ,
  5             row_number() over ( partition by val1,val2 order by sortkey ) diff,
  6          sortkey - row_number() over ( partition by val1,val2 order by sortkey ) grp
  7     from ex7 )
  8    group by val1,val2,grp
  9  order by min(sortkey);

MIN(SORTKEY) MAX(SORTKEY) V V
------------ ------------ - -
           1            2 A X
           3            4 B Y
           5            5 A X
           5            5 B X
           6            8 A Y
           7            7 B Y

6 rows selected.

Aketi,in OTN has posted the solution as below:

SQL> select min(sortKey) as sta,max(sortKey),Val1,Val2 as end
  2      from (select sortKey,Val1,Val2,
  3             dense_rank() over(order by sortKey)
  4            -Row_Number() over(partition by Val1,Val2
  5                               order by sortKey)
  6            as distance
  7             from ex7)
  8      group by Val1,Val2,distance
  9      order by min(sortKey);

       STA MAX(SORTKEY) V E
---------- ------------ - -
         1            2 A X
         3            4 B Y
         5            5 A X
         5            5 B X
         6            8 A Y
         7            7 B Y

6 rows selected.

I think the addition dense_rank function is not required as already we have a sortkey column.

Tabibitosan Method: Example 4

Collecting date range based on group:

create table ex5 (date_worked,country)
as
select to_date('1-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('4-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('5-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('6-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('7-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('8-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('11-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('12-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('13-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('14-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('15-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('18-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('19-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('20-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('21-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('22-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('25-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('26-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('27-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('28-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('29-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual
/
SQL> select * from ex5;

DATE_WORK COUNTRY
--------- --------------
01-NOV-13 United Kingdom
04-NOV-13 United Kingdom
05-NOV-13 India
06-NOV-13 India
07-NOV-13 India
08-NOV-13 United Kingdom
11-NOV-13 United Kingdom
12-NOV-13 India
13-NOV-13 India
14-NOV-13 India
15-NOV-13 United Kingdom
18-NOV-13 United Kingdom
19-NOV-13 India
20-NOV-13 India
21-NOV-13 India
22-NOV-13 United Kingdom
25-NOV-13 United Kingdom
26-NOV-13 India
27-NOV-13 India
28-NOV-13 India
29-NOV-13 United Kingdom

21 rows selected.

Expected result:
    COUNTRY        START_DATE END_DATE
    -------------- ---------- ----------
    United Kingdom 01-11-2013 04-11-2013
    India          05-11-2013 07-11-2013
    United Kingdom 08-11-2013 11-11-2013
    India          12-11-2013 14-11-2013
    United Kingdom 15-11-2013 18-11-2013
    India          19-11-2013 21-11-2013
    United Kingdom 22-11-2013 25-11-2013
    India          26-11-2013 28-11-2013
    United Kingdom 29-11-2013 29-11-2013

SQL> select date_worked,
  2         country,
  3          row_number() over ( order by date_worked) rn,
  4         row_number() over ( order by date_worked)-row_number() over (partition by country order
by date_worked) grp
  5  from ex5;

DATE_WORK COUNTRY                RN        GRP
--------- -------------- ---------- ----------
01-NOV-13 United Kingdom          1          0
04-NOV-13 United Kingdom          2          0
05-NOV-13 India                   3          2
06-NOV-13 India                   4          2
07-NOV-13 India                   5          2
08-NOV-13 United Kingdom          6          3
11-NOV-13 United Kingdom          7          3
12-NOV-13 India                   8          4
13-NOV-13 India                   9          4
14-NOV-13 India                  10          4
15-NOV-13 United Kingdom         11          6
18-NOV-13 United Kingdom         12          6
19-NOV-13 India                  13          6
20-NOV-13 India                  14          6
21-NOV-13 India                  15          6
22-NOV-13 United Kingdom         16          9
25-NOV-13 United Kingdom         17          9
26-NOV-13 India                  18          8
27-NOV-13 India                  19          8
28-NOV-13 India                  20          8
29-NOV-13 United Kingdom         21         12

21 rows selected.

SQL> select min(date_worked),min(date_worked)
  2  from
  3  (
  4  select date_worked,
  5         country,
  6      row_number() over ( order by date_worked) rn,
  7         row_number() over ( order by date_worked)-row_number() over (partition by country order
by date_worked) grp
  8  from ex5
  9  )
 10  group by grp
 11  order by min(rn);

SQL> select country,min(date_worked),max(date_worked)
  2  from
  3  (
  4  select date_worked,
  5         country,
  6      row_number() over ( order by date_worked) rn,
  7         row_number() over ( order by date_worked)-row_number() over (partition by country order
by date_worked) grp
  8  from ex5
  9  )
 10  group by country,grp
 11  order by min(rn);

COUNTRY        MIN(DATE_ MAX(DATE_
-------------- --------- ---------
United Kingdom 01-NOV-13 04-NOV-13
India          05-NOV-13 07-NOV-13
United Kingdom 08-NOV-13 11-NOV-13
India          12-NOV-13 14-NOV-13
United Kingdom 15-NOV-13 18-NOV-13
India          19-NOV-13 21-NOV-13
United Kingdom 22-NOV-13 25-NOV-13
India          26-NOV-13 28-NOV-13
United Kingdom 29-NOV-13 29-NOV-13

9 rows selected.

SQL>  

TabibitoSan method: Example 3
Get Rows continued and grouping on ID (ID Equal)

create table Ex4 (ID,Val,SortKey) as
select 1, 5, 1 from dual union all
select 1,10, 2 from dual union all
select 2, 2, 3 from dual union all
select 2, 5, 4 from dual union all
select 1,15, 5 from dual union all
select 3,25, 6 from dual union all
select 3,10, 7 from dual union all
select 3, 5, 8 from dual union all
select 3,15, 9 from dual union all
select 4, 5,10 from dual;

SQL> select * from ex4;

        ID        VAL    SORTKEY
---------- ---------- ----------
         1          5          1
         1         10          2
         2          2          3
         2          5          4
         1         15          5
         3         25          6
         3         10          7
         3          5          8
         3         15          9
         4          5         10

10 rows selected.

Expected Result:

         1          5         10
         2          2          5
         1         15         15
         3          5         25
         4          5          5


SQL> select id, min(val), max(val) from
  2  (select id, val,  sortkey,
  3           sortkey - row_number() over ( partition by id order by sortkey) grp
  4  from ex4)
  5  group by id, grp
  6  order by min(sortkey);

        ID   MIN(VAL)   MAX(VAL)
---------- ---------- ----------
         1          5         10
         2          2          5
         1         15         15
         3          5         25
         4          5          5

Here is a similar post from www.Orafaq,com:

Problem definition:
I'm trying to work out how to take a table like this:

ID Date
125 02-Feb-07
125 16-Mar-07
125 23-May-07
125 24-May-07
125 25-May-07
333 02-Jan-09
333 03-Jan-09
333 04-Jan-09
333 17-Mar-09

And display the data like this:

ID Period Period Start Period End
125 1 02-Feb-07 02-Feb-07
125 2 16-Mar-07 16-Mar-07
125 3 23-May-07 25-May-07
333 1 02-Jan-09 04-Jan-09
333 2 17-Mar-09 17-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

Solution:

with mytable(ID,dayC) as (
select 125,date '2007-02-02' from dual union
select 125,date '2007-05-16' from dual union
select 125,date '2007-05-23' from dual union
select 125,date '2007-05-24' from dual union
select 125,date '2007-05-25' from dual union
select 333,date '2009-01-02' from dual union
select 333,date '2009-01-03' from dual union
select 333,date '2009-01-04' from dual union
select 333,date '2009-03-17' from dual)
select ID,Row_Number() over(partition by ID order by dis) as rn,
min(dayC) as staD,max(dayC) as endD
from (select ID,dayC,
      dayC-Row_Number() over(partition by ID order by dayC) as dis
      from mytable)
group by ID,dis
order by ID,dis;

 ID  RN  STAD      ENDD
---  --  --------  --------
125   1  07-02-02  07-02-02
125   2  07-05-16  07-05-16
125   3  07-05-23  07-05-25
333   1  09-01-02  09-01-04
333   2  09-03-17  09-03-17

Tabibitosan Method: Example 2

Grouping consecutive month range without gaps

SQL> create table ex100
  2  as
  3  select date '2013-01-01' dt from dual union all
  4  select date '2013-02-01' from dual union all
  5  select date '2013-03-01' from dual union all
  6  select date '2013-06-01' from dual union all
  7  select date '2013-07-01' from dual union all
  8  select date '2013-10-01' from dual union all
  9  select date '2013-12-01' from dual union all
 10  select date '2014-01-01' from dual
 11  /

Table created.

SQL> select * from ex100;

DT
---------
01-JAN-13
01-FEB-13
01-MAR-13
01-JUN-13
01-JUL-13
01-OCT-13
01-DEC-13
01-JAN-14

8 rows selected.

Expected result:


01-JAN-13 01-MAR-13
01-JUN-13 01-JUL-13
01-OCT-13 01-OCT-13
01-DEC-13 01-JAN-14


ie, group by consective month range without gaps.

SQL> select dt,
  2         extract(year  from dt)*12
  3         +extract(month from dt),
  4      row_number()over (order by dt) rn,
  5      extract(year  from dt)*12
  6         + extract(month from dt)
  7      - row_number()over (order by dt) grp
  8  from ex100;

DT        EXTRACT(YEARFROMDT)*12+EXTRACT(MONTHFROMDT)         RN        GRP
--------- ------------------------------------------- ---------- ----------
01-JAN-13                                       24157          1      24156
01-FEB-13                                       24158          2      24156
01-MAR-13                                       24159          3      24156
01-JUN-13                                       24162          4      24158
01-JUL-13                                       24163          5      24158
01-OCT-13                                       24166          6      24160
01-DEC-13                                       24168          7      24161
01-JAN-14                                       24169          8      24161

8 rows selected.

SQL> select min(dt), max(dt)
  2  from (
  3  select dt,
  4         extract(year  from dt)*12
  5         +extract(month from dt),
  6      row_number()over (order by dt) rn,
  7      extract(year  from dt)*12
  8         + extract(month from dt)
  9      - row_number()over (order by dt) grp
 10  from ex100)
 11  group by grp
 12  order by min(dt);

MIN(DT)       MAX(DT)
---------     ---------
01-JAN-13     01-MAR-13
01-JUN-13     01-JUL-13
01-OCT-13     01-OCT-13
01-DEC-13     01-JAN-14

or,get the count of months from an initial month say '01-01-1900'

SQL> select dt,
  2           months_between(dt, to_date('01/01/1900','dd/mm/yyyy')),
  3           row_number()over (order by dt) rn
  4    from ex100;

DT        MONTHS_BETWEEN(DT,TO_DATE('01/01/1900','DD/MM/YYYY'))             RN
--------- -----------------------------------------------------     ----------
01-JAN-13                                                  1356              1
01-FEB-13                                                  1357              2
01-MAR-13                                                  1358                3
01-JUN-13                                                  1361              4
01-JUL-13                                                  1362               5
01-OCT-13                                                  1365              6
01-DEC-13                                                  1367              7
01-JAN-14                                                  1368               8

8 rows selected.


SQL> select min(dt), max(dt)
  2    from (
  3    select dt,
  4           months_between(dt, to_date('01/01/1900','dd/mm/yyyy'))
  5           -row_number()over (order by dt) grp
  6    from ex100)
  7    group by grp
  8    order by min(dt);

MIN(DT)       MAX(DT)
---------     ---------
01-JAN-13     01-MAR-13
01-JUN-13     01-JUL-13
01-OCT-13     01-OCT-13
01-DEC-13     01-JAN-14

Tabibitosan Method: Example 1

"Tabibitosan" is one of math problem.The key of the technique is to map rows belonging to the same pattern to the same number, which can then be used for grouping or partitioning.

The Tabibitosan technique lets you group consecutive rows easily, using just one level of analytic functions. And with a bit of imagination, you can also do some primitive SQL pattern matching.

Example 1:

SQL>  create table t1000
  2   as
  3   select 1 col from dual union all
  4   select 2 from dual union all
  5   select 3 from dual union all
  6   select 4 from dual union all
  7   select 7 from dual union all
  8   select 8 from dual union all
  9   select 9 from dual union all
 10   select 11 from dual union all
 11   select 12 from dual union all
 12   select 21 from dual union all
 13   select 22 from dual union all
 14   select 25 from dual
 15  /

Table created.
SQL> select * from t1000;

       COL
----------
         1
         2
         3
         4
         7
         8
         9
        11
        12
        21
        22
        25

12 rows selected.

Expected result:
1-4
7-9
11-12
21-22
25-25

That indicates we have to get the min and maximum of gap free consecutive number groups.
Hence you need to have an identifier which groups the gap free number into same groups.
So, how you will assign a group value to each set of consecutive gap free numbers?

Tricky part here is, we are using gap free sorted sequence column ie row_number() over (order by col)
for column "col".To form a group, we can subtract from col value to row_number() over (order by col).

SQL> select col,
  2         row_number() over (order by col) RN
  3  from t1000;

       COL                     RN
---------- ----------------------------
         1                            1
         2                            2
         3                            3
         4                            4
         7                            5
         8                            6
         9                            7
        11                            8
        12                            9
        21                           10
        22                           11
        25                           12

12 rows selected.

SQL> select col,
  2         col-row_number() over (order by col) grp
  3  from t1000;

       COL        GRP
---------- ----------
         1          0
         2          0
         3          0
         4          0
         ------------
         7          2
         8          2
         9          2
         --------------
        11          3
        12          3
        --------------
        21         11
        22         11
        ----------------
        25         13

12 rows selected.

Now we can group each set of gap free consecutive numbers and take the minimum and maximum of it and
then concatenate the values to get the desired result.

SQL> select min(col)||'-'||max(col) result
  2  from
  3  (select col,
  4         col-row_number() over (order by col) grp
  5  from t1000)
  6  group by grp;

RESULT
---------------------------------------------------------
1-4
7-9
11-12
21-22
25-25

Example 2:

SQL> create table t1001 as
  2  ( select to_date('01-01-2014','dd-mm-yyyy') dt1 from dual
  3    union all
  4    select to_date('02-01-2014','dd-mm-yyyy') dt1 from dual
  5    union all
  6    select to_date('03-01-2014','dd-mm-yyyy') dt1 from dual
  7    union all
  8    select to_date('07-01-2014','dd-mm-yyyy') dt1 from dual
  9    union all
 10    select to_date('12-01-2014','dd-mm-yyyy') dt1 from dual
 11    union all
 12    select to_date('13-01-2014','dd-mm-yyyy') dt1 from dual
 13    union all
 14    select to_date('14-01-2014','dd-mm-yyyy') dt1 from dual
 15    union all
 16    select to_date('15-01-2014','dd-mm-yyyy') dt1 from dual
 17   );

Table created.

SQL> 
SQL> select * from t1001;

DT1
---------
01-JAN-14
02-JAN-14
03-JAN-14
07-JAN-14
12-JAN-14
13-JAN-14
14-JAN-14
15-JAN-14

8 rows selected.

Expected output is to display the date range between consecutive dates (without any gaps).
Hence, expected output is something like below

effective from  effective to
01-01-2014         03-01-2014
07-01-2014         07-01-2014
12-01-2014        15-01-2014

Trick: How you will get the min and max range between two consecutive date? you need to have an
additional identified that does the grouping for you.


SQL> select
  2        dt1,
  3        dt1 - row_number() over ( order by dt1) diff
  4  from t1001;  

DT1       DIFF
--------- ---------
01-JAN-14 31-DEC-13
02-JAN-14 31-DEC-13
03-JAN-14 31-DEC-13
-------------------
07-JAN-14 03-JAN-14
-------------------
12-JAN-14 07-JAN-14
13-JAN-14 07-JAN-14
14-JAN-14 07-JAN-14
15-JAN-14 07-JAN-14

SQL>     select
  2            dt1,row_number() over ( order by dt1 ) RN,
  3            dt1 - row_number() over ( order by dt1 ) DIFF
  4      from t1001; 

DT1                                                                              RN   DIFF
-------------------------------------------------- ---------------------------- ----------------------
01-JAN-14                                                                     1 31-DEC-13
02-JAN-14                                                                     2 31-DEC-13
03-JAN-14                                                                     3 31-DEC-13
-------------------------------------------------------------------------------------------------------
07-JAN-14                                                                     4 03-JAN-14
-------------------------------------------------------------------------------------------------------
12-JAN-14                                                                     5 07-JAN-14
13-JAN-14                                                                     6 07-JAN-14
14-JAN-14                                                                     7 07-JAN-14
15-JAN-14                                                                     8 07-JAN-14

8 rows selected.

Now in the above sql we can form a group to get the minimum and maximum date between consecutive dates
(without gaps).

SQL> select min(a.dt1) as "effective from",
  2      max(a.dt1) as "effective to"
  3  from   
  4   (
  5   select  dt1,
  6     dt1 - row_number() over ( order by dt1) diff
  7   from t1001
  8   ) a
  9  group by a.diff;

effective from                                     effective to
-------------------------------------------------- -------------
01-JAN-14                                          03-JAN-14
07-JAN-14                                          07-JAN-14
12-JAN-14                                          15-JAN-14

contd...

Remove Duplicate values from array

Oracle Database Version 10g and up allows to eliminate duplicate values in array elements using MULTISET operation.
Below is the example
Script
Set serveroutput on ;

DECLARE
   TYPE T_Sampleid IS TABLE OF VARCHAR2 (20);

   arr_sampleid    t_sampleid := T_Sampleid ();
   arr_sampleid2   t_sampleid := T_Sampleid ();
BEGIN
   --Adding 6 samples...
   arr_sampleid.EXTEND;
   arr_sampleid (1) := 'sample G001';
   arr_sampleid.EXTEND;
   arr_sampleid (2) := 'sample G002';
   arr_sampleid.EXTEND;
   arr_sampleid (3) := 'sample G003';
   arr_sampleid.EXTEND;
   arr_sampleid (4) := 'sample G001'; -- duplicate
   arr_sampleid.EXTEND;
   arr_sampleid (5) := 'sample G002'; -- duplicate.
   arr_sampleid.EXTEND;
   arr_sampleid (6) := 'sample G004';

   -- Let's display values
   DBMS_OUTPUT.put_line ('Display all samples...');

   FOR i IN arr_sampleid.FIRST .. arr_sampleid.LAST
   LOOP
      DBMS_OUTPUT.put_line (arr_sampleid (i));
   END LOOP;

   -- same array into array 2
   arr_sampleid2 := arr_sampleid;

   -- Identify distinct and return to main arr_sampleid
   arr_sampleid := arr_sampleid MULTISET UNION DISTINCT arr_sampleid2;

   DBMS_OUTPUT.put_line ('Display DISTINCT samples...');

   FOR i IN arr_sampleid.FIRST .. arr_sampleid.LAST
   LOOP
      DBMS_OUTPUT.put_line (arr_sampleid (i));
   END LOOP;
END;
/
Output
processing sample: G11-A15214-02-TA0001 1 1
Display all samples...
sample G001
sample G002
sample G003
sample G001
sample G002
sample G004
Display DISTINCT samples...
sample G001
sample G002
sample G003
sample G004

As you can see in the output, Second set don't have duplicates.

Source: http://oraenablement.wordpress.com/2011/06/21/remove-duplicate-values-from-array/ 

Splitting a comma delimited string

Splitting a comma delimited string

SQL> with test as

  2  (

  3  select 1 id, 'joey,anthony,marvin' str from dual union all

  4  select 5 id, 'tony,glenn' str from dual union all

  5  select 8 id, 'john' str from dual

  6  )

  7  select id

  8       , str

  9       , regexp_substr (str, '[^,]+', 1, rn) split

 10    from test

 11    cross

 12    join (select rownum rn

 13            from (select max (length (regexp_replace (str, '[^,]+'))) + 1 mx

 14                    from test

 15                 )

 16         connect by level <= mx

 17         )

 18   where regexp_substr (str, '[^,]+', 1, rn) is not null

 19   order by id

 20  ;

        ID STR                 SPLIT

---------- ------------------- -------------------

         1 joey,anthony,marvin joey

         1 joey,anthony,marvin marvin

         1 joey,anthony,marvin anthony

         5 tony,glenn          tony

         5 tony,glenn          glenn

         8 john                john

Source: http://nuijten.blogspot.in/2011/08/splitting-comma-delimited-string-regexp.html

 

Get number of records in all tables

if you're content with a possibly estimated count as of the last time statistics were gathered, you can do something like this:

SELECT    table_name
,         num_rows
FROM      all_tables
WHERE     owner  = 'SCOTT'
;

If you want the exact number at run-time, then you need dynamic SQL.  Here's one way to do that:

SELECT    table_name
,         EXTRACTVALUE ( XMLTYPE ( dbms_xmlgen.getxml (  'SELECT  COUNT (*) AS num '
                                                      || 'FROM ' || owner || '.' || table_name
                                                      )
                                 )
                       , '/ROWSET/ROW/NUM'
                       )     AS num_rows
FROM      all_tables
WHERE     owner  = 'SCOTT'
;

[   an XML document can look like this:

<ROWSET>
  <ROW num="1">
    <X>Y</X>
  </ROW>
</ROWSET>

or it can look like this:

<ROWSET><ROW num="1"><X>Y</X></ROW></ROWSET> ]

Either way, you get results like:

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
DEPT                                    4
EMP                                    14
BONUS                                   0
SALGRADE                                5

select  table_name,
        xmlcast(xmlquery(('count(ora:view("' || owner || '","' || table_name || '"))') returning content) as int) cnt
  from  dba_tables
  where owner = 'HR'
  order by table_name
/

TABLE_NAME                            CNT
------------------------------ ----------
COUNTRIES                              25
DEPARTMENTS                            27
EMPLOYEES                             107
JOBS                                   19
JOB_HISTORY                            10
LOCATIONS                              23
REGIONS                                 4
7 rows selected.