时间戳

首页 > 解决方案 > postgresql 计算每个年龄组的库存数量

ruby-on-rails - postgresql 计算每个年龄组的库存数量

问题描述

我想计算表中股票的年龄<30 days，并按年龄30 days to 60 days exclusive、、、60 days to 90 days exclusive和对它们进行分组。这是我的库存示例表：90 days to 6 months exclusive> 6 months

<id:1, item_id:1, adjustment: 5,  amount:5,  date:"2020-01-01T00:00:00-04:00">
<id:2, item_id:1, adjustment: 1,  amount:6,  date:"2020-01-01T10:00:00-04:00">
<id:3, item_id:2, adjustment: 10, amount:10, date:"2020-01-01T00:00:00-04:00">
<id:4, item_id:1, adjustment: 5,  amount:11, date:"2020-02-01T00:00:00-04:00">    
<id:5, item_id:1, adjustment: -1, amount:10, date:"2020-03-02T00:00:00-04:00">
<id:6, item_id:2, adjustment: 1,  amount:11, date:"2020-03-02T00:00:00-04:00">

查询将被赋予一个日期参数，日期将应用于该参数。因此，在这种情况下，假设日期参数是2020-03-02T00:00:00，如果我们查看 <30 天，这将意味着 2020-03-02T00:00:00 之前的 30 天。这里要注意的重要一点：每当调整小于 0 时，就意味着一件商品已售出，并且该库存已从最旧的库存中删除。此外，对于每个日期类别，您都需要获取最新的 item_id，因为这将代表该项目的最新库存。更清楚地说，查询上表的示例响应是：

result = {
  "<30 days": 1,
  "30 days to 60 days exclusive": 5,
  "60 days to 90 days exclusive": 15, 
  "90 days to 6 months exclusive": 0,
  "> 6 months": 0 
}

请允许我解释一下结果：

  1. 少于 30 天的库存量取自id:6（表中最后一条记录）的量。这个非常简单，它是在 3 月初添加一个额外项目的结果。
  2. 30 到 60 天的库存数量是在 2020-02-01T00:00:00 (id: 4, item_id: 1) 添加的数量。此数量为 5，因为这是该日期添加的数量。此值不包括 2020 年 1 月 1 日的项目，因为这些项目正好有 60 天的历史。
  3. 60 至 90 天的库存量是 2020-01-01 增加的库存量。重要提示：所以这需要从那时起每个 item_id 的最新记录（因此对于 item_id:1 它将是：id:2而对于 item_id:2 它将是id:3）。此外，item_id:1我们可以看到，在记录中id:5，一个项目被删除了，因为我们假设它是最旧的库存，它将从那批项目中删除。因此，这个值是通过 6(id:2) + 10(id:3) - 1(id​​:5) = 25 来计算的。

这里需要注意的重要一点是，当调整为负数时（意味着一个项目被移除/出售），我们将假设它是最旧的被移除的项目，这就是为什么我们没有从我们之前减去 1 个移除的id:4项目计算 30 至 60 天（不含）的库存。

所以我正在尝试编写一个 sql 查询并使用Model.find_by_sql. 任何帮助将不胜感激。为了测试目的，我在这里创建了小提琴表。谢谢。

标签： ruby-on-railspostgresql

解决方案

这可能是一个带有硬编码日期的解决方案'2020-03-02T00:00:00'。

但需要用一些对冲案例来检验，主要是负调整。

例如，如果调整日期2020-03-02T00:00:00-04:00而不是-1它-7，当然是针对不同的输入日期。

最难处理的情况是当您对 a 进行负调整而item_id对另一个 进行正调整时item_id。

我不确定这些情况下的行为可能是什么。因此，不仅仅是一个完美的工作解决方案，还可以将其视为一个起点，一个粗略的草图。

SELECT
  SUM (
    CASE
    WHEN grouped_item.difference < interval '30 days' and grouped_item.difference >= interval '0 days' THEN
        grouped_item.sum
      ELSE
        0
    END
  ) AS "<30 days",
  SUM (
    CASE
    WHEN grouped_item.difference >= interval '30 days' and grouped_item.difference < interval '60 days' THEN
      grouped_item.sum
    ELSE
      0
    END
  ) AS "30 days to 60 days exclusive",
  SUM (
    CASE
    WHEN grouped_item.difference >= interval '60 days' and grouped_item.difference < interval '90 days' THEN
      grouped_item.sum
    ELSE
      0
    END
  ) AS "60 days to 90 days exclusive",
  SUM (
    CASE
    WHEN grouped_item.difference >= interval '90 days' and grouped_item.difference < interval '6 month' THEN
      grouped_item.sum
    ELSE
      0
    END
  ) AS "90 days to 6 months exclusive",
  SUM (
    CASE
    WHEN grouped_item.difference >= interval '6 month' THEN
      grouped_item.sum
    ELSE
      0
    END
  ) AS "> 6 months"
FROM
  (
    select inventory.item_id, sum(inventory.adjustment),
    case
      when inventory.adjustment >= 0 then
        '2020-03-02T00:00:00'::timestamp - inventory.date::timestamp
      else (
        select '2020-03-02T00:00:00'::timestamp - min(I.date::timestamp)
        from inventory I
        where I.item_id = inventory.item_id
        )
    end difference
    from inventory
    where inventory.date::timestamp <= '2020-03-02T00:00:00'::timestamp
    group by inventory.item_id, difference
    ) as grouped_item;

/*
 <30 days | 30 days to 60 days exclusive | 60 days to 90 days exclusive | 90 days to 6 months exclusive | > 6 months 
----------+------------------------------+------------------------------+-------------------------------+------------
        1 |                            5 |                           15 |                             0 |          0

*/

推荐阅读