sql - 平均股票历史表

Question

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

这是一个示例模式和一些测试数据:

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 的先前值，直到发生新的更改。关于如何实现这一目标的任何想法？

Answer 1

这个任务的特别难点:你不能只选择你时间范围内的数据点，而是要考虑之前的最新数据点> 时间范围和最早数据点之后时间范围。这因每一行而异，每个数据点可能存在也可能不存在。需要复杂的查询并且难以使用索引。

你可以使用 range types和 operators (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() 从每个项目的下一行获取日期，使用内置选项提供“今天” "默认情况下没有下一行。
有了这个，我形成了一个 daterange 并将其与使用 overlap operator && 的输入进行匹配，用 intersection 计算结果日期范围运算符 *。

此处的所有范围均带有独占上边界。这就是为什么我将一天添加到输入范围。这样我们就可以简单地从 upper(range) 中减去 lower(range) 得到天数。

我假设“昨天”是具有可靠数据的最近一天。 “今天”在现实生活中的应用还是可以改变的。因此，我使用“今天”(now()::date) 作为开放范围的唯一上边界。

我提供了两个结果:

• your_result 同意您显示的结果。
  您无条件地除以日期范围内的天数。例如，如果某个项目仅在最后一天列出，您会得到一个非常低(误导性!)的“平均值”。

• my_result 计算相同或更高的数字。
  我除以项目列出的实际天数。例如，如果某个项目仅在最后一天列出，我会将列出的值返回为平均值。

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

SQL Fiddle.

指标与性能

对于此类数据，旧行通常不会更改。这将成为实体化 View 的绝佳案例:

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 index which supports the relevant operator && :

CREATE INDEX mv_stock_range_idx ON mv_stock USING gist (day_range);

大测试用例

我用 20 万行进行了更真实的测试。使用 MV 的查询速度大约是 6 倍，这反过来又是@Joop 查询速度的 10 倍。性能在很大程度上取决于数据分布。 MV 对大表和高条目频率的帮助最大。此外，如果表中包含与此查询无关的列，则 MV 可以更小。成本与 yield 的问题。

我已经将到目前为止发布的所有解决方案(并改编)放在一个大提琴中来玩:

SQL Fiddle with big test case.
SQL Fiddle with only 40k rows - 避免 sqlfiddle.com 超时