postgresql - SQL计算每月库存

Question

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

我有 stock_move 表:

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