Is there an analytical function in mysql to solve this issue?

Question

I have a mysql table like below

id   trader price
111   abc    5
222   xyz    5.20
333   abc    5.70
444   xyz    5
555   abc    5.20

I want to have a result to find the number of times each trader increased or decreased price as below

id   trader price  lagged_price   flag
111   abc    5
222   xyz    5.20   5        
333   abc    5.70   5.20        increased 
444   xyz    5      5.70        increased  
555   abc    5.20   5           reduced
                    5.20        increased

lagged_price is the column which lags the original column price. I need to have a flag saying that at a particular time the trader increased or decreased price in the above case the final result is

trader abc increased price twice trader xyz increased price ones and decreased price ones

There are no analytical functions in MySQL, so no. There are ways round this though. You don't seem to be lagging the price how you describe though. It's not being done per trader but for the entire result set. Is this intentional? — Ben, Nov 03 '12 at 15:29
possible duplicate of [Simulate lag function in MySQL](http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql) — Lukas Eder, Nov 03 '12 at 15:29
Or http://stackoverflow.com/q/9894092/521799, http://stackoverflow.com/q/5483319/521799, ... etc — Lukas Eder, Nov 03 '12 at 15:30

score 0 · Answer 1 · edited May 23 '17 at 11:43

I see you have asked this question differently at post: Compare rows in same table in mysql

Nevertheless, here is a solution for the above: (assuming your table is called 'tab', and the IDs actually decrease, and so 'lag' means 'before' as it looks like in your example data)

SELECT a.id, a.trader, a.price, b.price as lagged_price
     , case when a.price > b.price then 'increased'
            when a.price < b.price then 'decreased'
            else 'same'
       end as flag
  FROM tab a
  JOIN tab b 
    ON a.trader = b.trader AND a.id > b.id
  LEFT OUTER JOIN tab c 
    ON a.trader = c.trader AND a.id > c.id AND b.id < c.id
 WHERE c.id IS NULL

You can see the details of why this works in my answer at your other question.

Hope this helps!

john...

Is there an analytical function in mysql to solve this issue?

1 Answers1