i have the following table with different prices in every week and need a numbering like in the last column. consecutive rows with same prices should have the same number like in weeks 11/12 or 18/19. but on the other side weeks 2 and 16 have the same prices but are not consecutive so they should get a different number.
w | price | r1 | need =========================== 1 167,93 1 1 2 180 1 2 3 164,72 1 3 4 147,42 1 4 5 133,46 1 5 6 145,43 1 6 7 147 1 7 8 147,57 1 8 9 150,95 1 9 10 158,14 1 10 11 170 1 11 12 170 2 11 13 166,59 1 12 14 161,06 1 13 15 162,88 1 14 16 180 2 15 17 183,15 1 16 18 195 1 17 19 195 2 17
i have already experimented with the analytics functions (row_number, rank, dens_rank), but didn't found a solution for this problem so far.
(oracle sql 10,11)
does anyone have a hint? thanks.
Simulating your table first:
SQL> create table mytable (w,price,r1)
2 as
3 select 1 , 167.93, 1 from dual union all
4 select 2 , 180 , 1 from dual union all
5 select 3 , 164.72, 1 from dual union all
6 select 4 , 147.42, 1 from dual union all
7 select 5 , 133.46, 1 from dual union all
8 select 6 , 145.43, 1 from dual union all
9 select 7 , 147 , 1 from dual union all
10 select 8 , 147.57, 1 from dual union all
11 select 9 , 150.95, 1 from dual union all
12 select 10, 158.14, 1 from dual union all
13 select 11, 170 , 1 from dual union all
14 select 12, 170 , 2 from dual union all
15 select 13, 166.59, 1 from dual union all
16 select 14, 161.06, 1 from dual union all
17 select 15, 162.88, 1 from dual union all
18 select 16, 180 , 2 from dual union all
19 select 17, 183.15, 1 from dual union all
20 select 18, 195 , 1 from dual union all
21 select 19, 195 , 2 from dual
22 /
Table created.
Your need column is calculated in two parts: first compute a delta column which denotes whether the previous price-column differs from the current rows price column. If you have that delta column, the second part is easy by computing the sum of those deltas.
SQL> with x as
2 ( select w
3 , price
4 , r1
5 , case lag(price,1,-1) over (order by w)
6 when price then 0
7 else 1
8 end delta
9 from mytable
10 )
11 select w
12 , price
13 , r1
14 , sum(delta) over (order by w) need
15 from x
16 /
W PRICE R1 NEED
---------- ---------- ---------- ----------
1 167.93 1 1
2 180 1 2
3 164.72 1 3
4 147.42 1 4
5 133.46 1 5
6 145.43 1 6
7 147 1 7
8 147.57 1 8
9 150.95 1 9
10 158.14 1 10
11 170 1 11
12 170 2 11
13 166.59 1 12
14 161.06 1 13
15 162.88 1 14
16 180 2 15
17 183.15 1 16
18 195 1 17
19 195 2 17
19 rows selected.