how to add a chronological identifier to data

Database
Enthusiast

how to add a chronological identifier to data

Hi, I am not sure whether the title clearly explains my question. Here's the data I have

2.PNG

 

 

The data shows the price change and quantity change for an item (with item_id 331314221355) over time. I only care price change, my goal is to find the last date of each price over time. The result should look like this:

3.PNG

The difficult part is the first and third price are the same. If I aggregate only by item_id and price, I will only obtain two rows like

3.PNG

I am considering whether I can add some chronological identifier to solve this problem. Could anyone give any suggestions?

 

Thanks a lot!

 


Accepted Solutions
Junior Contributor

Re: how to add a chronological identifier to data

 

select * from tab
qualify
   min(price) -- next row's price
   over (partition by item_id
         order by date
         rows between 1 following and 1 following) 
   <> coalesce(price, -99999) -- different from current price or NULL

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: how to add a chronological identifier to data

 

select * from tab
qualify
   min(price) -- next row's price
   over (partition by item_id
         order by date
         rows between 1 following and 1 following) 
   <> coalesce(price, -99999) -- different from current price or NULL

 

Enthusiast

Re: how to add a chronological identifier to data

Hi Dieter,


Thanks a lot! That works prefectly and now I have more sense of how to apply OLAP.