Cool_Oracle Cool_Oracle - 4 months ago 9
SQL Question

Creation of sequence from unstructured table

How to split the below unstructured table into 500 records for each batch based on CREATED_DT < SYSDATE -2 considering there are more 90,000 records? I cannot just take the CREATED_DT as the date and the value will change

NUMBERID | MODEL| NAME | VALUE | CREATED_DT | LOADED_DT
1000 | BOE | AGT | 300 | 02-AUG-2016| 02-AUG-2016
1001 | AIR | ACT | 400 | 01-AUG-2016| 01-AUG-2016
1000 | EMB | ADT | 500 | 02-AUG-2016| 01-AUG-2016
1004 | REL | GTR | 140 | 01-AUG-2016| 01-AUG-2016
1001 | SIM | HJT | 140 | 01-JUL-2016| 28-JUL-2016
1002 | SIM | ACT | 540 | 04-JUL-2016| 04-JUL-2016


I can't use the difference of MAX(NUMBER_ID) MIN(NUMBER_ID) AND CREATED_DT as there is high risk of some data being lost. Can I use Oracle ROWID column (system column) column to split the records without missing any records?

Thanks for your help !

Answer

The analytic function ROW_NUMBER can help uniquely order and chunk the rows.

Sample Schema

drop table test1;

create table test1(numberid number, created_dt date);

insert into test1
select level, sysdate - level * interval '1' second
from dual connect by level <= 100000;

Sample Query and Results

select
    numberid,
    created_dt,
    ceil(row_number() over (order by created_dt desc) / 500) batch_id
from test1
order by created_dt desc;


NUMBERID   CREATED_DT               BATCH_ID
--------   ----------               --------
1          2016-08-02 11:42:48 PM   1
2          2016-08-02 11:42:47 PM   1
3          2016-08-02 11:42:46 PM   1
...
499        2016-08-02 11:34:30 PM   1
500        2016-08-02 11:34:29 PM   1
501        2016-08-02 11:34:28 PM   2
...