Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Last_value over partition by

I have a table that contains column Fruit,Date,Price,Flag.

I use First_value(Price) over (partition by Fruit order by Fruit, Date) to generate Column Price First

last_value(Price) over (partition by Fruit order by Fruit,Date rows between unbounded preceding and unbounded following) to generate Column Price Last

Fruit   Date       Price    Flag    Price first   Price Last
Apple   6/1/2015    1.4     Yes       1.4           1.9
Apple   6/2/2015    1.5     Yes       1.4           1.9
Apple   6/3/2015    2.5     Yes       1.4           1.9
Apple   6/4/2015    3       Yes       1.4           1.9
Apple   6/5/2015    1.7     Yes       1.4           1.9 
Apple   6/6/2015    1.9     No        1.4           1.9

But I need another column "Price Last Flag Yes" to give me the last updated price for fruit Apple that don't have flag No,In this case, it should be the price at 6/5/2015

Fruit   Date       Price    Flag    Price first  Price Last  Price Last Flag Yes
Apple   6/1/2015    1.4     Yes       1.4           1.9        1.7
Apple   6/2/2015    1.5     Yes       1.4           1.9        1.7
Apple   6/3/2015    2.5     Yes       1.4           1.9        1.7
Apple   6/4/2015    3       Yes       1.4           1.9        1.7
Apple   6/5/2015    1.7     Yes       1.4           1.9        1.7 
Apple   6/6/2015    1.9     No        1.4           1.9        1.7

I don't know how to query the condition that filters out the Flag "no" within the functions. I don't want to go through subqueries. It is possible to add conditions and filters within the function? Thanks

I don't want to use where clause because then I need to use subquery. I also tried last_value(case when Flag = 'Yes' then Price end) over (partition by fruit order by Fruit,date), it doesn't work either. For some reason it messes the data and some rows are empty.

like image 274
Sailormoon Avatar asked Sep 02 '25 17:09

Sailormoon


1 Answers

There's the IGNORE NULLS option:

last_value(case when Flag = 'Yes' then Price end ignore nulls) 
over (partition by Fruit order by Date ...

Btw, partition by Fruit, Date order by Fruit,Date will not work, it's partition by Fruit order by Date instead

like image 171
dnoeth Avatar answered Sep 04 '25 12:09

dnoeth