create a non-unique ID column, where a new ID is created every time a numeric sequence resets-CodePudding

I have the following table:

product price   date
banana  90  2022-01-01
banana  90  2022-01-02
banana  90  2022-01-03
banana  95  2022-01-04
banana  90  2022-01-05
banana  90  2022-01-06

I need to add a non-unique ID column to the table. Every time the price changes, I want the ID to change. This would result in the following table.

id  product price   date
A   banana  90  2022-01-01
A   banana  90  2022-01-02
A   banana  90  2022-01-03
B   banana  95  2022-01-04
C   banana  90  2022-01-05
C   banana  90  2022-01-06

By searching for answers in SO and Google, I was able to create a column (my_seq) that contains a sequence that resets every time (see sql fiddle for my query) the price changes. But I still don't know how to create an ID column that resets every time the my_seq starts over.

my_seq  rn1 rn2 product price   date
1   1   1   banana  90  2022-01-01
2   2   2   banana  90  2022-01-02
3   3   3   banana  90  2022-01-03
1   1   4   banana  95  2022-01-04
1   4   5   banana  90  2022-01-05
2   5   6   banana  90  2022-01-06

sql-fiddle with DDL and my query

thanks

CodePudding user response：

You are half-there already with the query in your Fiddle.

Consider what you get if you subtract your rn1 & rn2 values - you get the values you need to group by.

If you want an increasing sequence you can then apply a dense rank:

with cte as (
  select *, 
    Row_Number() over(order by date)
    - Row_Number() over(partition by product, price order by date) rn
  from my_table
)
select Dense_Rank() over(order by rn) as my_seq,
  product, price, date
from cte;

Modified fiddle

CodePudding user response：

It will generate sequence number whenever price change.

with cte as (
select 
row_number  () over( w) as price_change,

product,price,date
from 
my_table_1
window w as (partition by product,price order by date)
)
select   
product,price,date,
row_number()over(partition by price_change order by price_change) as seq_no
from cte
order by seq_no,price_change

output:

product|price|date      |seq_no|
------- ----- ---------- ------ 
banana |   90|2022-01-01|     1|
banana |   90|2022-01-02|     1|
banana |   90|2022-01-03|     1|
banana |   90|2022-01-05|     1|
banana |   90|2022-01-06|     1|
banana |   95|2022-01-04|     2|