mysqlself-joinmysql-dependent-subquery

mysql Retrieve and manipulate current row and previous row from table


I have looked several places, and maybe I'm just not phrasing the search correctly. I have found similar questions, but none of them answer the question.

I have a table of Sight Inventories (where users walk through the storage area and physically check how many products are on hand). The table handles multiple locations. The table structure (partial, only the information needed) is:

create table location_inventory (
    id                int unsigned not null auto_increment,
    location_id       int unsigned references location(location_id),
    inventory_item_id int unsigned references inventory_item (inventory_item_id),
    inventory_date    date comment 'Date the sight inventory was taken',
    quantity          decimal( 15,2 ) comment 'Number of items found during inventory',
    primary key ( id ),
    unique            (location_id,inventory_item_id,inventory_date)
);

It should be a query of the form:

select
    a.location_id location,
    a.inventory_item_id inventory_item,
    a.inventory_date curr_date,
    a.quantity curr_quant,
    b.inventory_date prev_date,
    b.quantity prev_quant,
    a.quantity - b.quantity num_used
from
    location_inventory a
    left outer join
       (
         select
            location_id,
            inventory_item_id,
            inventory_date,
            quantity
          from
            location_inventory
          where
           something
      ) b
      on ( location_id,inventory_item_id )
  where
      a.inventory_date between DATEA and DATEB

But I haven't gotten it to work.

It is that whole subquery that I'm missing. I've seen several answers where we get the previous date, but none where I can actually retrieve the rest of the values from the previous row; it ends up retrieving the values for the most recent entry in the entire table.


Solution

  • When you choose to only reveal part of a table structure you can omit things we may need. Below I have assumed you have a column id as a unique identifier for each row

    SELECT
        a.location_id           location
      , a.inventory_item_id     inventory_item
      , a.inventory_date        curr_date
      , a.quantity              curr_quant
      , b.inventory_date        prev_date
      , b.quantity              prev_quant
      , a.quantity - b.quantity num_used
    FROM (
        SELECT
            *
          , (SELECT id
             FROM location_inventory
             WHERE location_id = t.location_id
               and inventory_item_id = t.inventory_item_id
               and inventory_date < t.inventory_date
             ORDER BY inventory_date DESC
             LIMIT 1
            ) prev_id
        FROM location_inventory t
        ) a
    LEFT OUTER JOIN location_inventory b ON a.prev_id = b.id
    WHERE a.inventory_date BETWEEN DATEA AND DATEB
    

    Another method would be to use correlated subqueries for each of the wanted values:

    SELECT
        a.location_id           location
      , a.inventory_item_id     inventory_item
      , a.inventory_date        curr_date
      , a.quantity              curr_quant
      , (SELECT inventory_date
             FROM location_inventory
             WHERE location_id = t.location_id and inventory_item_id = t.inventory_item_id and inventory_date < t.inventory_date
             ORDER BY inventory_date DESC
             LIMIT 1
            )                   prev_date
      , (SELECT quantity
             FROM location_inventory
             WHERE location_id = t.location_id and inventory_item_id = t.inventory_item_id and inventory_date < t.inventory_date
             ORDER BY inventory_date DESC
             LIMIT 1
            )                   prev_quant
      , a.quantity 
        - (SELECT quantity
             FROM location_inventory
             WHERE location_id = t.location_id and inventory_item_id = t.inventory_item_id and inventory_date < t.inventory_date
             ORDER BY inventory_date DESC
             LIMIT 1
            )
    FROM location_inventory t
    WHERE a.inventory_date BETWEEN DATEA AND DATEB
    

    Since release of version 8.0 MySQL supports window functions such as lag() which makes this far easier and more efficient.

    SELECT
        a.location_id           location
      , a.inventory_item_id     inventory_item
      , a.inventory_date        curr_date
      , a.quantity              curr_quant
      , lag(inventory_date,1) over(partiton by location_id,inventory_item_id order by inventory_date DESC)  prev_date
      , lag(quantity,1) over(partiton by location_id,inventory_item_id order by inventory_date DESC)        prev_quant
      , a.quantity 
        - lag(quantity,1) over(partiton by location_id,inventory_item_id order by inventory_date DESC)       num_used
    FROM location_inventory a
    WHERE a.inventory_date BETWEEN DATEA AND DATEB
    

    final note: I am not in favour of aliasing schemes that are dependent on sequence in a query