平均股票历史表

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的先前值，直到发生新变化。你有什么想法如何实现这一点？

- froque

---

这个表现在有多少行，以及在什么时间段内？ - Mike Sherrill 'Cat Recall'

请问您能告诉我如何准确计算平均值吗？您是如何获取像产品ID为10的3.6666这样的数值的呢？ - Hardik Parmar

你想对什么取平均值？是按天、月还是其他？你能更具体地说明一下你的计算应该是什么吗？目前，avg(s.value)会给出表中条目的真实平均值，但你想要平均的基准是什么类型的呢？ - user2858650

那么每个股票都应该保持不变，直到下一次输入，但在第一次输入之前不存在？你如何取消列出的项目？ - Erwin Brandstetter

Answer 1

这项任务的特殊难点在于，你不能仅仅选取时间范围内的数据点，还需要额外考虑时间范围之前的最新数据点和时间范围之后的最早数据点。每行数据的情况都不同，有些数据点可能存在，而有些则可能不存在。这需要一个精密的查询，因此使用索引变得困难。

你可以使用区间类型和运算符（Postgres9.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并使用重叠运算符&&将其与输入匹配，使用交集运算符*计算出结果日期范围。

这里的所有范围都具有排除上限。这就是为什么我要在输入范围中添加一天。这样我们就可以简单地从range的lower（range）中减去upper（range）以获得天数。

我假设“昨天”是有可靠数据的最新日期。在实际应用中，“今天”仍然可能会改变。因此，我将“今天”（now（）::date）作为开放范围的排除上限。

我提供两个结果：

• your_result与您显示的结果相符。
  您无条件地按日期范围内的天数进行划分。例如，如果某个项目仅在最后一天列出，则会得到一个非常低（误导性的！）“平均值”。

• my_result计算相同或更高的数字。
  我按实际列出物品的天数进行划分。例如，如果某个项目仅在最后一天列出，则将列出的值返回为平均值。

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

SQL Fiddle。

索引和性能

对于这种类型的数据，旧记录通常不会更改。这将成为材料化视图的一个绝佳案例：

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);

大型测试用例

我进行了一个更加现实的测试，使用了20万行数据。使用MV的查询速度是原始查询的6倍，而这个速度又比@Joop的查询快了约10倍。性能严重依赖于数据分布。MV对于大表和高频率的条目最有帮助。此外，如果表格有与此查询无关的列，MV可以更小。这是成本与收益的问题。

我将迄今为止所有发布的解决方案（并进行了调整）放在了一个大型fiddle中以供玩耍：

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

- Erwin Brandstetter

---

用DAYS_IN_RANGE加权间隔值的做法相当巧妙。 - Conrad Frix

@ErwinBrandstetter：在日历上的笛卡尔连接从来不是为了成为速度达人，而是旨在展示间隙和岛屿的简单情况可以用不同的方式处理。对于微不足道的情况（几千条记录），这通常足够快，并且更容易编写/阅读/重写。 - joop

@joop：没错，我其实很喜欢你的回答。（我自己也在多个答案中使用了类似的技巧。）我只是报告一下我的测试结果，因为我对性能非常感兴趣。 - Erwin Brandstetter

Answer 2

这种方法并不十分精确，但是可以避免使用复杂的时间间隔计算。只需要将数据表与日历表连接，并将它们全部加起来即可。

WITH calendar(zdate) AS ( SELECT generate_series('2013-01-01'::date, '2013-01-15'::date, '1 day'::interval)::date )
SELECT st.store_id,st.product_id
        , SUM(st.zvalue) AS sval
        , COUNT(*) AS nval
        , (SUM(st.zvalue)::decimal(8,2) / COUNT(*) )::decimal(8,2) AS wval
FROM calendar
JOIN stocks st ON calendar.zdate >= st.zdate
        AND NOT EXISTS ( -- this calendar entry belongs to the next stocks entry 
                SELECT * FROM stocks nx
                WHERE nx.store_id = st.store_id AND nx.product_id = st.product_id
                AND nx.zdate > st.zdate AND nx.zdate <= calendar.zdate
        )
GROUP BY st.store_id,st.product_id
ORDER BY st.store_id,st.product_id
        ;

- joop

---

@ConradFrix：你的样本数据多了一行。另外：我从不将“date”用作列名。有时它可以工作，但有时可能会有不同的效果。 - joop

我赞同不使用关键字作为列/对象名称，尽管我通常会尽力使用OP的名称。您也可以随意更改小提琴以适应您的需要。每次更改模式或SQL时，它都会生成一个新的URL。 - Conrad Frix

抱歉，我拒绝使用非标准的编辑工具。它们会浪费太多时间。对于网络而言，只需剪切和粘贴即可尽快完成。这个小工具不允许轻松地屏幕抓取，所以我尽可能避免使用它。 - joop

Answer 3

这个答案基于一个暗示，即您正在寻求每天的平均值，因此每天都计算为新行。虽然其他SQL引擎可以以行形式处理此问题，但通过拆分平均值（Sum（value）/ count（value）），并将其推广到该值的天数，这个引擎更容易实现。使用您的表格格式和这个目标，我想出了这个解决方案（ SQLFiddle）

select store_id, product_id, CASE WHEN sum(nextdate-date) > 0 THEN sum(Value*(nextdate-date)) / sum(nextdate-date) END as Avg_Value
from (
  select *
      , (
        select value
        from stocks b
        where a.store_id = b.store_id
          and a.product_id = b.product_id
          and a.date >= b.date
        order by b.date
        limit 1
      )*1.0 "value"
      , coalesce((
        select date
        from stocks b
        where a.store_id = b.store_id
          and a.product_id = b.product_id
          and a.date < b.date
        order by b.date
        limit 1
      ),case when current_date > '2013-01-12' then '2013-01-12' else current_date end) nextdate
  from (
    select store_id, product_id, min(case when date < '2013-01-07' then '2013-01-07' else date end) date
    from stocks z
    where date < '2013-01-12'
    group by store_id, product_id
    ) a
  union all
  select store_id, product_id, date, value*1.0 "value"
    , coalesce((
      select date
      from stocks b
      where a.store_id = b.store_id
        and a.product_id = b.product_id
        and a.date < b.date
      order by b.date
      limit 1
    ),case when current_date > '2013-01-12' then '2013-01-12' else current_date end) nextdate
  from stocks a
  where a.date between '2013-01-07' and '2013-01-12'
) t
group by store_id, product_id
;

查询在开始参数（'2013-01-07'）之前获取每个店铺/产品的第一个出现，如果参数大于表中记录的日期，则将其作为日期交换，并选择该早期条目的值和表中第一个更改的日期，以及保持下一个日期受限于结束参数（'2013-01-12'）。联合查询的第二部分抓取两个参数之间的所有更改以及下一个更改或当前日期，都受到结束参数的限制。最后，对结果进行计算，其中值通过日期差异相加后乘以日期差异之和来除以日期之和。由于查询中所有日期都受到限制，所以平均值将在作为参数传递的确切窗口上进行平均。

对于 PostgreSQL 不是很了解的人，我建议如果您打算在函数中实现这一点，那么复制此查询并将所有实例替换为你的起始参数名称'2013-01-07'，将所有实例替换为你的结束参数名称'2013-01-12'，将为您提供任何给定日期窗口的结果。

编辑：如果您想要不同时间单位的平均值，只需将两个nextdate-date实例替换为您要查找的日期间隔计算。 nextdate-date返回两个日期之间的天数。

- Jaaz Cole

---

PostgreSQL更像SQL Server 2012和Oracle，比你意识到的要多。 - Conrad Frix

我在一个更大的测试用例中遇到了“除以零”的错误。 - Erwin Brandstetter

@ErwinBrandstetter，嗯，你说得对。我加了一个case语句来模拟avg()在0条记录上的行为。 - Jaaz Cole