When working on these problems, it's helpful to sketch out the algorithm you need to use first.
- Every fifth order number, increase the batch_number by one.
Hmm. That doesn't help much. We need to break it down further.
You can put numbers in groups of 5 by dividing by this and taking the floor. There are gaps between the order numbers though. We need to assign an order rank, a gap-free sequence that increases for each new order number. Here's an outline of the process:
- Order the rows by order_number
- Start the order rank at zero
- If the order_number for the current row is different from the previous row, increment the order rank by one. Otherwise keep the same order rank
- Divide this result by five
- Take the floor of this division. This will group the rows into batches of five.
- These are your batch numbers. It starts at zero however. Add one if you want these to start at that instead.
You could do this with analytic functions. I'm going to use the model clause instead:
select * from orders
model
dimension by (
-- this specifies how we're accessing rows
row_number() over (order by order_number, order_line) rn
)
measures (
order_number, order_line, 0 order_rank, 0 order_group, 0 batch_number
)
rules (
order_rank[any] =
case
when order_number[cv()] != order_number[cv()-1] then
-- we need to increment the order rank
order_rank[cv()-1]+1
else
-- nvl() to set the value of the first row, otherwise all null
nvl(order_rank[cv()-1], 0)
end,
order_group[any] = floor( (order_rank[cv()]) / 5 ),
batch_number[any] = order_group[cv()] + 1
);
RN ORDER_NUMBER ORDER_LINE ORDER_RANK ORDER_GROUP BATCH_NUMBER
---------- ------------ ---------- ---------- ----------- ------------
1 50000 1 0 0 1
2 50000 2 0 0 1
3 50000 3 0 0 1
4 50010 1 1 0 1
5 50010 2 1 0 1
6 50300 3 2 0 1
7 50301 1 3 0 1
8 50301 5 3 0 1
9 50302 1 4 0 1
10 50302 2 4 0 1
11 50302 3 4 0 1
12 50302 4 4 0 1
13 50302 5 4 0 1
14 50302 6 4 0 1
15 50304 1 5 1 2
16 50304 2 5 1 2
17 50304 3 5 1 2
18 50305 6 6 1 2
19 50305 7 6 1 2
20 50306 8 7 1 2
21 50307 1 8 1 2
22 50308 1 9 1 2
23 51913 1 10 2 3
24 51913 2 10 2 3You could simplify this (+1 in the order group is the batch_number). I've kept it split out to aid understanding.