How to create intervals of 100 based on a column in a query?

Question

I have a table with below structure:

create table item_test
(item_id varchar2(10),
 item_row number)

Sample data is like below , please note that numbers in Item_Row column starts at 1 and increases by 1 to a number without any gaps. What I need to do is to create intervals of 100 ([1-100] , [101-200] , [201-300] ,......) for each Item_Id.

 Item_Id Item_Row 
 ------------ ----------- 
 A 1 
 A 2 
 A ...
 A ...
 A 236 /* Item_Row starts from 1 and continues to 236 */
 B 1
 B ...
 B ...
 B 173 /* Item_Row starts from 1 and continues to 173 */
 
 C 1
 C 2
 C ...
 C ...
 C 300 /* Item_Row starts from 1 and continues to 300 */ 

The result will be like below:

 Item_Id RowNum_From RowNum_From
 ------------ ----------- ----------- 
 A 1 100
 A 101 200
 A 201 236
 B 1 100
 B 101 173
 
 C 1 100 
 C 101 200
 C 201 300 

Is there any specific function for this?

Answer 1

The trick is to use the trunc function.

Here's a solution using pure SQL:

create table item_test
(item_id varchar2(10),
 item_row number);
insert into item_test
select 'A', level from dual connect by level <= 236
union all
select 'B', level from dual connect by level <= 173
union all
select 'C', level from dual connect by level <= 300;
commit;
select item_id, min(item_row) as item_row_from, max(item_row) as item_row_to
 from item_test
 group by item_id, trunc(item_row-1, -2)
 order by item_id, trunc(item_row-1, -2);

And the result:

ITEM_ID ITEM_ROW_FROM ITEM_ROW_TO
---------- ------------- -----------
A 1 100
A 101 200
A 201 236
B 1 100
B 101 173
C 1 100
C 101 200
C 201 300
8 rows selected.

Answer 2

Really nice question - thought provoking - +1!

To solve this, I did the following (all the code below is available on the fiddle here):

• I borrowed the setup code from the accepted answer here.

insert into test
select 'A', level from dual connect by level <= 25
union all
select 'B', level from dual connect by level <= 18
UNION ALL
SELECT 'C', LEVEL FROM DUAL CONNECT BY LEVEL <= 25;

• You really should be providing fiddles with sample data - you have > 1,300 points on dba.se so you should know this! It's a courtesy to those trying to help - it eliminates duplication of effort and provides a single point of truth for the question and would have eliminated the problem I had.

SELECT
 c, fval1, lval2
FROM
(
 SELECT
 c, i, 
 FIRST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 10) 
 ORDER BY c, i ASC) AS fval1,
 
 -- the default frame clause for window functions
 
 FIRST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 10) ORDER BY c, i ASC
 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS fval2,
 -- the default doesn't work for the LAST_VALUE() fuction - you have to do it
 -- this way. 
 LAST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 10) ORDER BY c, i ASC
 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lval2
 FROM 
 test
) TAB
GROUP BY c, fval1, lval2
ORDER BY c, fval1;

Result:

C FVAL1 LVAL2
A 1 10
A 11 20
A 21 25
B 1 10
B 11 18
C 1 10
C 11 20
C 21 25

The interest here is that this could potentially provide more scope for analysis - with different parameters and/or different window functions.

Change the 10 in TRUNC((i - 1) / 10 to a 7 and you can do a weekly analysis for example - see the last snippet here.