mysql - SQL 查询产生如下所示的输出

Question

我必须可视化预计缺货商品及其在库存中缺货的日期。

我有一个表格，显示该商品何时可能缺货。此表显示一周中每个星期一的预计库存。

Item    Location    Projected     Inventory Date
A1         L1           0          Aug 20, 2018
A1         L1           0        August 27, 2018
A1         L1           54        Sep 03,20-18
A1         L1           49        Sep 10, 2018
A1         L1           44          Sep 17
A1         L1           39          Sep 24
A1         L1           32          Oct 1
A1         L1           25          Oct 8
A1         L1           18          Oct 15
A1         L1           12          Oct 22
A1         L1           5           Oct 29
A1         L1           55          Nov 5
A1         L1           45          Nov 12

如果商品符合以下条件，则视为缺货: Projected Invenotry<20 。由于数据是每周一的数据，因此该商品可能需要几周时间才能恢复库存(即 PI > 20 )。

从表中可以看出，商品 A1 于 2018 年 8 月 20 日缺货，并于 2018 年 9 月 3 日恢复库存。所需的输出表为:

Item    Location    PI    Date Out of Stock
A1       L1         0       Aug 20, 2018
A1       L1         18      Oct 15, 2018

这对我来说相当复杂。任何帮助将不胜感激。

问候，普拉吉瓦尔

Answer 1

您可以使用(MySQL 8.0+):

WITH cte AS (
  SELECT *, SUM(CASE WHEN Projected < 20 THEN 0 ELSE 1 END)
        OVER(PARTITION BY Item, Location ORDER BY Inventory_Date) AS subgrp
  FROM tab
), cte2 AS (
  SELECT *, ROW_NUMBER() 
          OVER(PARTITION BY Item, Location, subgrp ORDER BY Inventory_Date) AS rn
  FROM cte
)
SELECT *
FROM cte2
WHERE (Item, Location, subgrp,rn) IN (SELECT Item, Location, subgrp, MIN(rn) 
                                      FROM cte2 c 
                                      WHERE Projected < 20
                                      GROUP BY Item, Location, subgrp)
ORDER BY Inventory_Date;

<强> DBFiddle Demo

它将选择每组中第一个低于 20 的值，该组由 Item/Location/Streak_of_values_below_20 组成。