库存instocks的模式

Question

我在工作中使用SQL来收集信息，但不是很高级(目前还没有)。

我需要确定库存、库存和脱销的模式。

我有一个表，其中显示了项目，位置，库存和日期。我想看看当商品/位置脱销和库存随着时间的推移是否有规律可循。该文件包含连续三周的数据。如果一件商品在三周内一直缺货或脱销，那么我需要知道它的情况，以便进一步研究。

如果库存大于零，则项目/位置为库存。如果库存为零或为负，则项目/位置已脱销。

谢谢你的帮助。

样本数据

Item, location, inventory, date
1243, 10,       2,         3/12/2012
1243, 10,       0,         3/13/2012
1243, 10,      -2,         3/14/2012
1243, 10,      -2,         3/15/2012
1243, 10,       4,         3/16/2012

然后是附加项目、位置、库存、日期记录。

对输出建议持开放态度。我只需要查看商品/位置，以及在三周的数据中，它在库存和脱销之间切换了多少次。

Answer 1

像这样的东西可能就是你要找的。这将计算item和location的每个组合从“库存”移动到“缺货”的次数，反之亦然。请注意，每个item和location的第一行不能算作转换，因为我们不知道给定此逻辑的先前状态。

SELECT item,
       location,
       SUM( CASE WHEN status = 'In Stock' AND prior_status = 'Out of Stock'
                 THEN 1
                 ELSE 0
             END) moved_to_out_of_stock,
       SUM( CASE WHEN status = 'Out of Stock' AND prior_status = 'In Stock'
                 THEN 1
                 ELSE 0 
              END) moved_to_in_stock
  FROM (SELECT item, 
               location,
               status,
               lag( status ) over (partition by item, location
                                       order by dt) prior_status
          FROM (SELECT item, 
                       location, 
                       (case when inventory <= 0 
                             then 'Out of Stock'
                             else 'In Stock'
                         end) status,
                       dt
                  FROM your_table))
  GROUP BY item, location

这表明，在您发布的示例数据中，该项目从缺货到库存一次，从库存到缺货一次。

SQL> ed
Wrote file afiedt.buf

  1  with your_table as (
  2    select 1243 item, 10 location, 2 inventory, date '2012-03-12' dt from dual union all
  3    select 1243, 10, 0, date '2012-03-13' from dual union all
  4    select 1243, 10, -2, date '2012-03-14' from dual union all
  5    select 1243, 10, -2, date '2012-03-15' from dual union all
  6    select 1243, 10, 4, date '2012-03-16' from dual
  7  )
  8  SELECT item,
  9         location,
 10         SUM( CASE WHEN status = 'In Stock' AND prior_status = 'Out of Stock'
 11                   THEN 1
 12                   ELSE 0
 13               END) moved_to_out_of_stock,
 14         SUM( CASE WHEN status = 'Out of Stock' AND prior_status = 'In Stock'
 15                   THEN 1
 16                   ELSE 0
 17                END) moved_to_in_stock
 18    FROM (SELECT item,
 19                 location,
 20                 status,
 21                 lag( status ) over (partition by item, location
 22                                         order by dt) prior_status
 23            FROM (SELECT item,
 24                         location,
 25                         (case when inventory <= 0
 26                               then 'Out of Stock'
 27                               else 'In Stock'
 28                           end) status,
 29                         dt
 30                    FROM your_table))
 31*   GROUP BY item, location
SQL> /

      ITEM   LOCATION MOVED_TO_OUT_OF_STOCK MOVED_TO_IN_STOCK
---------- ---------- --------------------- -----------------
      1243         10                     1                 1

Answer 2

SELECT Item
      ,location
      ,SUM(CASE WHEN inventory>0 THEN 1 END) AS INSTOCK
      ,SUM(CASE WHEN inventory<=0 THEN 1 END) AS OUTSTOCK
FROM TABLE
GROUP BY Item,location;