Mysql – Query to generate a new column containing the value of the “previous” column

MySQL

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?

Best Answer

For version 5.x

SELECT id, 
       CASE WHEN @item = item
            THEN @value
            ELSE @value:=NULL
       END prev, 
       @item:=item,
       @value:=current
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

SELECT id, 
       item, 
       current, 
       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+).

Related Solutions

Mysql – Speeding up a NOT IN subquery

In general you can replace a NOT IN (...) subquery with a NOT EXISTS (...) and the latter will be faster. Logically they are the same though some database optimizers handle the NOT EXISTS better as they can stop the subquery check after the first match.

In contrast, if the DB optimizer isn't smart enough to figure out what your doing and the subquery has a lot of rows, then it must buffer all the rows for each subquery before performing each NOT IN check.

Try out something like this:

SELECT *
FROM table1 a
WHERE NOT EXISTS(SELECT 1
                   FROM table2 b
                  WHERE b.user_id = 2
                    AND b.index_field = a.field1)

In this example the optimizer need only check if a single row exists for the matching subquery, including the additional filter on b.user_id = 2. It should use an index on the index_field and user_id if they are available.

Mysql – Replace value based on previous row / next row depending on conditions

Add this composite index if you don't already have it -- it should probably be the PRIMARY KEY.

INDEX(company_id, date_time)

Run this a few times (until it stops modifying any rows)

UPDATE    tbl a
    JOIN  tbl b
       ON  b.company_id = a.company_id
      AND  b.date_time  = a.date_time + INTERVAL 1 MINUTE
    SET a.price_open = b.price_open
    WHERE  a.price_open  = 0.00
      AND  b.price_open != 0.00;

In case I am wrong, suggest you copy the table over for testing:

CREATE TABLE tbl LIKE real_tbl;
ALTER TABLE tbl DROP PRIMARY KEY,
                ADD  PRIMARY KEY(company_id, date_time);  -- if needed
INSERT INTO tbl SELECT * FROM real_tbl;
run the update a few times
check the values
if satisfied, ...
RENAME TABLE real_tbl TO old, tbl TO real_tbl;
DROP TABLE old;

On the presumption that your SELECTs will be company-centric, it is better to have PRIMARY KEY(co, dt) than (dt, co).

Best Answer

Related Solutions

Mysql – Speeding up a NOT IN subquery

Mysql – Replace value based on previous row / next row depending on conditions

Related Question