I have a MySQL table that has data similar to this:

id   #        current
--   -----    -------
1    item1    50            
2    item2    60            
3    item1    70            
4    item2    40            
5    item2    80            
6    item2    33            
7    item1    55            

I need to analyze the data per each item and compare it to the previous value of the same item.

So how can I write query in order to retrieve the data like shown below?

id   #        current       prev
--   -----    -------       ----
1    item1    50            -
2    item2    60            -
3    item1    70            50
4    item2    40            60
5    item2    80            40
6    item2    33            80
7    item1    55            70

If no, then what is the most efficient way to achieve this?

The only thing that comes to my mind is query all and for loop on the results keeping array for previous value of each item. I am a little worried about performance as this is going to be a big table with a lot of rows and a lot of items. Any other option?


For version 5.x

       CASE WHEN @item = item
            THEN @value
            ELSE @value:=NULL
       END prev, 
FROM tablename, (SELECT @item:='', @value:=0) variables
ORDER BY item, id

The query firstly processes all item1 in the order of id growth, and then processes all item2 (due to ORDER BY expression). CASE in prev's expression checks the moment when item1 finished and item2 started, and resets the variable to NULL (because first record for item have no previous record).

For version 8.x

       LAG(current) OVER (PARTITION BY item 
                          ORDER BY id) prev
FROM tablename

PS. Column name `#` replaced with `item`.

PPS. If you need in final sorting by `id` then wrap the query with proper ordering outer query (for 5+) or add ordering clause (for 8+).

| improve this answer | |

You can do that by joining table with itself and two user-defined variables:

SELECT IF( w.`#` = 'item1'
         , @prev1 := w.id
         , @prev2 := w.id
         ) AS id
     , w.`#`
     , w.current
     , IFNULL( z.current, '-', z.current ) AS prev

  FROM      table AS w
  LEFT JOIN table AS z  ON z.id < w.id 
                       AND z.`#` = w.`#`
                       AND ((w.`#` = 'item1' AND z.id = @prev1) OR 
                            (w.`#` = 'item2' AND z.id = @prev2))

I hope the main idea is clear enough. You need an index (id, #) and/or (#, id) for max performance.

N.B. Do not use reserved symbols (hash '#' is used for commenting) as column names.

| improve this answer | |
  • Too complex. And 2nd table copy is excess - proper ordering is enough. – Akina Jul 31 '19 at 5:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.