oracle sql - numbering group of rows

Question

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.

Read [this](http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html) before you ask next question. — neshkeev, Sep 16 '14 at 08:32
many thanks for all your posts & solutions. they are very helpful to me :) — Peter, Sep 16 '14 at 10:07

score 5 · Accepted Answer · answered Sep 16 '14 at 09:14

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.

Definitely simpler than my answer ;-) But just one suggestion: add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to both analytic functions. The default is RANGE not ROWS, which in this case gives same result as W is unique, but in general can cause the optimizer to be slightly less optimal when using RANGE instead of ROWS. I give a more detailed example of this at: http://dspsd.blogspot.dk/2013/02/rows-versus-default-range-in-analytic.html — Kim Berg Hansen, Sep 16 '14 at 10:00
Interesting blog post. And food for some extra investigation. To be continued. — Rob van Wijk, Sep 17 '14 at 06:15

score 2 · Answer 2 · answered Sep 16 '14 at 08:56

You can nest your analytic functions using inline views, so you first group the consecutive weeks with same prices and then dense_rank using those groups:

select w
     , price
     , r1
     , dense_rank() over (
          order by first_w_same_price
       ) drank
  from (
   select w
        , price
        , r1
        , last_value(w_start_same_price) ignore nulls over (
             order by w
             rows between unbounded preceding and current row
          ) first_w_same_price
     from (
      select w
           , price
           , r1
           , case lag(price) over (order by w)
                when price then null
                else w
             end w_start_same_price
        from your_table
     )
  )
 order by w

The innermost inline view with LAG function lets the starting week of every consecutive group get it's own week number, but every consecutive week with same price gets null (weeks 12 and 19 in your data.)

The middle inline view with LAST_VALUE function then use the IGNORE NULLS feature to give the consecutive weeks the same value as the first week within each group. So week 11 and 12 both gets 11 in first_w_same_price and week 18 and 19 both gets 18 in first_w_same_price.

And finally the outer query use DENSE_RANK to give the desired result.

+1, for using a single table access, although a bit more complex than needed — Rob van Wijk, Sep 16 '14 at 09:19

score 0 · Answer 3 · answered Sep 16 '14 at 08:43

For each row you should count previous rows where (w-1) row price isn't the same as (w) price:

select T1.*,
(SELECT count(*) 
  FROM T T2
    JOIN T T3 ON T2.w-1=T3.w
    WHERE T2.Price<>T3.Price
          AND T2.W<=T1.W)+1 rn

from t T1

SQLFiddle demo

score 0 · Answer 4 · answered Sep 16 '14 at 09:23

Try this:

with tt as (
  select t.*, decode(lag(price) over(order by w) - price, 0, 1, 0) diff
    from t
)
select w
     , price
     , r1
     , row_number() over (order by w) - sum(diff) over(order by w rows between UNBOUNDED PRECEDING and current row) need
  from tt

score -1 · Answer 5 · answered Sep 16 '14 at 08:28

-1

SELECT  w, price, r1, 
       ROW_NUMBER ()  OVER (PARTITION BY price ORDER BY price) row_column
FROM TABLE

answered Sep 16 '14 at 08:28

Alex Peta

1,407
1
15
26

It won't return the same values for the same price. You need to check your solutions before posting – neshkeev Sep 16 '14 at 08:30

oracle sql - numbering group of rows

5 Answers5