SQL根据时间戳和清单级别联接同一表 [英] SQL Join same table based on time stamp and inventory level
问题描述
我正在使用库存数据,每分钟告诉我当前的库存水平并将其存储在数据库中.
I am working with inventory data that tells me the current inventory level every minute and stores it in the DB.
我想查找item_count降至0的每个实例,带时间戳的那一行,然后将其加入到item_count高于0的下一行.这将告诉我该产品缺货了多长时间.
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
结构::
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 ;
数据::
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),
期望的结果::
鉴于上面的数据,我想加入ID为1的行和ID为11的行. 1.在表中搜索item_count = 0的第一行,找到item_count> 0且created_at> firstRow.created_at的行(具有相同的product_id和pusher_id).并加入他们的行列. 然后,找到该事件的下一个实例.
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.
推荐答案
翻译成SQL并不难,但是性能可能很差.当您退货时,这将为您提供时间戳:
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
删除零库存的连续行更为复杂:
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
请参见小提琴
这篇关于SQL根据时间戳和清单级别联接同一表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!