平均库存历史记录表

一尘不染

平均库存历史记录表

sql

我有一个表，可以跟踪某些商店和产品随时间变化的库存变化。该值是绝对库存，但是我们仅在库存发生变化时才插入新行。这种设计是为了使桌子保持较小，因为它有望快速增长。

这是一个示例架构和一些测试数据：

CREATE TABLE stocks (
  id serial NOT NULL,
  store_id integer NOT NULL,
  product_id integer NOT NULL,
  date date NOT NULL,
  value integer NOT NULL,
  CONSTRAINT stocks_pkey PRIMARY KEY (id),
  CONSTRAINT stocks_store_id_product_id_date_key 
    UNIQUE (store_id, product_id, date)
);

insert into stocks(store_id, product_id, date, value) values
(1,10,'2013-01-05', 4),
(1,10,'2013-01-09', 7),
(1,10,'2013-01-11', 5),
(1,11,'2013-01-05', 8),
(2,10,'2013-01-04', 12),
(2,11,'2012-12-04', 23);

我需要能够确定每个产品和商店的开始和结束日期之间的平均库存，但是我的问题是简单的avg（）并没有考虑到更改之间的库存保持不变。

我想要的是这样的：

select s.store_id,  s.product_id , special_avg(s.value) 
from stocks s where s.date between '2013-01-01' and '2013-01-15'
group by s.store_id,  s.product_id

结果是这样的：

store_id  product_id  avg
1         10          3.6666666667
1         11          5.8666666667
2         10          9.6
2         11          23

为了使用SQL平均函数，我需要及时“传播” store_id和product_id的先前值，直到发生新的更改为止。有什么想法可以实现这一目标吗？

阅读 190

2021-03-17

共1个答案

一尘不染

此任务的 特殊困难 ：您不能只选择时间范围内的数据点，而必须考虑时间范围之前的最新数据点和时间范围之后的最早
数据点。这对于每一行都不同，并且每个数据点可能存在也可能不存在。需要复杂的查询，因此很难使用索引。 __

您可以使用 范围类型 和
运算符 （Postgres 9.2+ ）简化计算：

WITH input(a,b) AS (SELECT '2013-01-01'::date  -- your time frame here
                         , '2013-01-15'::date) -- inclusive borders
SELECT store_id, product_id
     , sum(upper(days) - lower(days))                    AS days_in_range
     , round(sum(value * (upper(days) - lower(days)))::numeric
                    / (SELECT b-a+1 FROM input), 2)      AS your_result
     , round(sum(value * (upper(days) - lower(days)))::numeric
                    / sum(upper(days) - lower(days)), 2) AS my_result
FROM (
   SELECT store_id, product_id, value, s.day_range * x.day_range AS days
   FROM  (
      SELECT store_id, product_id, value
           , daterange (day, lead(day, 1, now()::date)
             OVER (PARTITION BY store_id, product_id ORDER BY day)) AS day_range 
      FROM   stock
      ) s
   JOIN  (
      SELECT daterange(a, b+1) AS day_range
      FROM   input
      ) x ON s.day_range && x.day_range
   ) sub
GROUP  BY 1,2
ORDER  BY 1,2;

请注意，我使用的是列名，day而不是date。我从不使用基本类型名称作为列名称。

在子查询中，sub我lead()使用window函数使用内置功能从下一行获取每个项目的下一天的日期，并使用内置选项将默认情况下的“今天”提供给没有下一行的地方。
这样，我形成adaterange并将其与 重叠运算符&&与输入匹配，并使用 相交运算符*计算结果日期范围。

这里的所有范围都具有 唯一的
上边框。这就是为什么我要在输入范围内增加一天的原因。这样，我们可以简单地lower(range)从中减去upper(range)以获得天数。

我认为“昨天”是拥有可靠数据的最新一天。“ Today”在现实生活中仍然可以更改。因此，我将“今天”（now()::date）用作开放范围的唯一上边界。

我提供两个结果：

your_result同意您显示的结果。
您将日期条件无条件地除以天数。例如，如果某项仅在最后一天列出，那么您将获得非常低（误导！）的“平均值”。
my_result计算相同或更高的数字。
我被分在实际项目中列出的天数。例如，如果某个项目仅在最后一天列出，我将列出的值作为平均值返回。

为了理解差异，我添加了列出该项目的天数： days_in_range

SQL小提琴 。

指标与表现

对于此类数据，旧行通常不会更改。对于 物化视图， 这将是一个很好的例子：

CREATE MATERIALIZED VIEW mv_stock AS
SELECT store_id, product_id, value
     , daterange (day, lead(day, 1, now()::date) OVER (PARTITION BY store_id, product_id
                                                       ORDER BY day)) AS day_range
FROM   stock;

然后，您可以添加一个支持相关运算符&&的GiST索引：

CREATE INDEX mv_stock_range_idx ON mv_stock USING gist (day_range);

大测试用例

我对200k行进行了更实际的测试。使用MV进行查询的速度约为6倍，这是@Joop查询的10倍。性能在很大程度上取决于数据分发。MV对于大型表和高频率条目最有帮助。另外，如果表中的列与此查询无关，则MV可能会更小。成本与收益的问题。

我把到目前为止（和改编过的）所有解决方案都摆在了摆弄着：

带有大型测试用例的SQL Fiddle。
只有40k行的SQL Fiddle-避免在sqlfiddle.com上超时

2021-03-17