CODEWITHSUNDEEP
sqloracle-database
Walter_T
Walter_T

Reputation: 21

SQL Select counter when values change

I need to make a counter for everytime a value changes in time. I have this table:

  Date      |   Quantity
2017-02-01  |    10000
2017-02-02  |    20000
2017-02-03  |    20000
2017-02-04  |    20000
2017-02-05  |    10000
2017-02-06  |    10000

I want to make something like this:

  Date      |   Quantity   | Counter
2017-02-01  |    10000     |    1
2017-02-02  |    20000     |    2
2017-02-03  |    20000     |    2
2017-02-04  |    20000     |    2
2017-02-05  |    10000     |    3
2017-02-06  |    10000     |    3

I tried using dense_rank and other functions but I couldn't make it look like that because it would give the same Counter number when the quantity is 10000.

Is what I'm asking even possible?

Thank you!

Upvotes: 2

Views: 125

Answers (2)

user5683823
user5683823

Reputation:

Simple solution for Oracle 12 and above only, using the MATCH_RECOGNIZE clause:

with
     test_data ( dt, quantity ) as (
       select date '2017-02-01', 10000 from dual union all
       select date '2017-02-02', 20000 from dual union all
       select date '2017-02-03', 20000 from dual union all
       select date '2017-02-04', 20000 from dual union all
       select date '2017-02-05', 10000 from dual union all
       select date '2017-02-06', 10000 from dual
     )
-- end of test data, for illustration only; WITH clause is NOT part of the query
-- solution (SQL query) begins BELOW THIS LINE
select dt, quantity, mn as counter
from   test_data
match_recognize (
  order by dt
  measures match_number() as mn
  all rows per match
  pattern ( a b* )
  define b as b.quantity = a.quantity
)
;

DT           QUANTITY    COUNTER
---------- ---------- ----------
2017-02-01      10000          1
2017-02-02      20000          2
2017-02-03      20000          2
2017-02-04      20000          2
2017-02-05      10000          3
2017-02-06      10000          3

6 rows selected.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270993

A simple method is to use lag() and a cumulative sum:

select t.date, t.quantity,
       sum(case when quantity = prev_quantity then 0 else 1 end) over (order by date) as counter
from (select t.*, lag(quantity) over (order by date) as prev_quantity
      from t
     ) t;

These are ANSI standard functions and available in most databases.

Upvotes: 4

Related Questions