SQL每月计算库存

Question

我有特定的任务，不知道如何实现。我希望有人能帮助我=）

我有存货移动表：

product_id |location_id |location_dest_id |product_qty |date_expected       |
-----------|------------|-----------------|------------|--------------------|
327        |80          |84               |10          |2014-05-28 00:00:00 |
327        |80          |84               |10          |2014-05-23 00:00:00 |
327        |80          |84               |10          |2014-02-26 00:00:00 |
327        |80          |85               |10          |2014-02-21 00:00:00 |
327        |80          |84               |10          |2014-02-12 00:00:00 |
327        |84          |85               |20          |2014-02-06 00:00:00 |
322        |84          |80               |120         |2015-12-16 00:00:00 |
322        |80          |84               |30          |2015-12-10 00:00:00 |
322        |80          |84               |30          |2015-12-04 00:00:00 |
322        |80          |84               |15          |2015-11-26 00:00:00 |

即它的产品表从一个仓库移动到另一个仓库。

如果我使用这样的东西，我可以计算自定义日期的库存：

select
    coalesce(si.product_id, so.product_id) as "Product",
    (coalesce(si.stock, 0) - coalesce(so.stock, 0)) as "Stock"
from
    (
        select
            product_id
            ,sum(product_qty * price_unit) as stock
        from stock_move
        where
            location_dest_id = 80
            and date_expected < now()
        group by product_id
    ) as si
    full outer join (
        select
            product_id
            ,sum(product_qty * price_unit) as stock
        from stock_move
        where
            location_id = 80
            and date_expected < now()
        group by product_id
    ) as so
    on si.product_id = so.product_id

结果我有当前库存：

Product |Stock |
--------|------|
325     |1058  |
313     |34862 |
304     |2364  |

但是如果我每个月都需要库存该怎么办？像这样的东西？

Month   |Total Stock |
--------|------------|
Jan     |130238      |
Feb     |348262      |
Mar     |2323364     |

如何计算每个月开始到结束的产品数量？

我只有一个想法——它使用24个子查询每月获得库存（例如下面）

Jan |Feb | Mar |
----|----|-----|
123 |234 |345  |

在此之后结束旋转行和列？我觉得这很愚蠢，但我不知道其他方法。。。请帮帮我=）

Answer 1

这样的东西可以为您提供每月的"期末"库存快照。诀窍是，你的数据可能会省略某些部分的某些月份，但该部分仍然有余额（即1月份收到50个，2月份没有发生任何事情，但你仍然希望显示2月份的总数为50）。

处理这一问题的一种方法是想出所有可能的零件/日期组合。在本例中，我假设1/1/14+24个月，但在all_months子查询中很容易更改。例如，您可能只想从stock_move表中的最短日期开始。

with all_months as (
  select '2014-01-01'::date + interval '1 month' * generate_series(0, 23) as month_begin
),
stock_calc as (
  select
    product_id, date_expected,
    date_trunc ('month', date_expected)::date as month_expected,
    case
      when location_id = 80 then -product_qty * price_unit
      when location_dest_id = 80 then product_qty * price_unit
      else 0
    end as qty
  from stock_move
  union all
  select distinct
    s.product_id, m.month_begin::date, m.month_begin::date, 0
  from
    stock_move s
    cross join all_months m
),
running_totals as (
  select
    product_id, date_expected, month_expected,
    sum (qty) over (partition by product_id order by date_expected) as end_qty,
    row_number() over (partition by product_id, month_expected
      order by date_expected desc) as rn
  from stock_calc
)
select
  product_id, month_expected, end_qty
from running_totals
where
  rn = 1