SQL Join same table based on time stamp and inventory level

Question

I am working with inventory data that tells me the current inventory level every minute and stores it in the DB.

I want to find each instance where item_count fell to 0, take that row with timestamp, and then join it to the next row where the item_count rose above 0. This will then tell me how long that product was out of stock.

I came up with the following, but it doesn't return anything.

SELECT `inventories`.* from `inventories` inner join 
    (SELECT id, item_count, pusher_id, created_at as in_stock_at
                FROM inventories
                GROUP BY pusher_id) inv2 
ON `inventories`.`created_at` < `inv2`.`in_stock_at` 
    AND `inv2`.`item_count` > `inventories`.`item_count` 
    AND `inventories`.`pusher_id` = `inv2`.`pusher_id` 
WHERE `inventories`.`item_count` <= 0 
    AND `inventories`.`product_id`=9

Structure::

CREATE TABLE IF NOT EXISTS `inventories` (
  `id` int(10) unsigned NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `pusher_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `reader_id` int(10) unsigned NOT NULL,
  `tags_blocked` double(6,2) NOT NULL,
  `item_count` double(6,2) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2881 ;

Data::

INSERT INTO `inventories` (`id`, `client_id`, `pusher_id`, `product_id`, `reader_id`, `tags_blocked`, `item_count`, `active`, `created_at`, `updated_at`, `deleted_at`) VALUES
    (1, 1, 1, 9, 1, 0.00, 0.00, 1, '2015-10-22 04:45:47', '2015-10-23 04:45:47', NULL),
    (2, 1, 1, 9, 1, 0.00, 0.00, 1, '2015-10-22 04:55:47', '2015-10-23 04:45:47', NULL),
    (3, 1, 1, 9, 1, 0.00, 0.00, 1, '2015-10-22 05:05:47', '2015-10-23 04:45:47', NULL),
    ...
    (10, 1, 1, 9, 1, 0.00, 0.00, 1, '2015-10-22 06:15:47', '2015-10-23 04:45:47', NULL),
    (11, 1, 1, 9, 1, 10.00, 10.00, 1, '2015-10-22 06:25:47', '2015-10-23 04:45:47', NULL),
    (12, 1, 1, 9, 1, 9.00, 9.00, 1, '2015-10-22 06:35:47', '2015-10-23 04:45:47', NULL),
    (13, 1, 1, 9, 1, 8.00, 8.00, 1, '2015-10-22 06:45:47', '2015-10-23 04:45:47', NULL),

Desired Result::

Given the data above, I want to join row with ID 1 and row with ID 11. 1. Search the table for the first row with item_count=0, find a row (with same product_id and pusher_id) that has item_count > 0 and created_at > firstRow.created_at. and join them together. Then, find the next instance of this occurrence.

I hope that clarifies the question.

Add sample data and desired results. Your query has things like `pusher_id` that don't make any sense. — Gordon Linoff, Oct 26 '15 at 14:26
This approach isn't going to work because you need to pair the rows where stock level falls to zero with the *first* following row where the level is non-zero. I feel fairly confident that this is very hard to accomplish in mysql. If it was MS SQL it could be achieved using CTEs (and I believe other DBMS should have viable solutions too). — mzedeler, Oct 26 '15 at 14:37

dnoeth · Accepted Answer · 2015-10-26T21:31:22.007

Translating into SQL isn't that hard, but performance might be bad. This will get you the timestamp when the product was back in stock:

SELECT inv.*,
 ( SELECT MIN(`inv2`.`in_stock_at`)
   FROM inventories AS inv2
   WHERE inv2.`product_id` = inv.`product_id`   -- same product
     AND inv2.`pusher_id` = `inv`.`pusher_id`   -- same pusher
     AND `inv2`.`created_at` > inv.`created_at` -- later timestamp
     AND `inv2`.`item_count` > 0                -- in stock
 ) AS inStockAgain_at
from `inventories` AS inv
WHERE inv.`item_count` <= 0   -- out of stock
 -- AND inv.`product_id`=9

Edit:

Removing consecutive rows with zero stock is more complicated:

SELECT inv.*, dt.inStockAgain_at
FROM inventories AS inv
JOIN
 ( 
   SELECT product_id, pusher_id, 
      MIN(created_at) AS min_created_at,
     inStockAgain_at
   FROM
    (
      SELECT product_id, pusher_id, created_at,
       ( SELECT MIN(inv2.created_at)
         FROM inventories AS inv2
         WHERE inv2.product_id = inv.product_id -- same product
           AND inv2.pusher_id = inv.pusher_id   -- same pusher
           AND inv2.created_at > inv.created_at -- later timestamp
           AND inv2.item_count > 0              -- in stock
       ) AS inStockAgain_at
      FROM inventories AS inv
      WHERE inv.item_count <= 0  
    ) AS dt
   GROUP BY product_id, pusher_id, inStockAgain_at
 ) AS dt
ON inv.product_id = dt.product_id
AND inv.pusher_id = dt.pusher_id 
AND inv.created_at = dt.min_created_at

See fiddle

This worked 99.9%. The only thing I would like to change/fix would be the following: This returns row 1 and row 11 joined (out of stock at 1, in stock at 11) but then it returns row 2 and row 11 joined (because it is still out of stock 10 minutes later) Is there a way I can make it skip to row 11 when it continues searching for the next row that has item_count of 0? — KiaiFighter, Oct 26 '15 at 16:32
Excellent =D That was exactly what I was looking for! Now I just need to break it down to make sure I understand it =) — KiaiFighter, Oct 27 '15 at 14:27

SQL Join same table based on time stamp and inventory level

1 Answers1

Linked