postgresql - Postgresql 中的日期绑定(bind)运行平均值 - 如何划分为四个星期

Question

我有一个包含销售日期(每周:每个星期六)、商品 ID 和销售额的表格。我想要一个 4 周的运行平均值。我需要将我的数据按 ID 分成四个星期的部分，但我不知道如何使用语法。

示例表:

ID  date    value   four_wk_average
<p>123  04/05/2019  1640.88 1640.88
<p>123  11/05/2019  2093.4  1867.14
<p>123  18/05/2019  2671.57 2135.28
<p>123  25/05/2019  2034.6  2110.11
<p>123  01/06/2019  1564.62 2001.01
<p>123  08/06/2019  2143.29 2024.73
<p>123  15/06/2019  2007    2022.19
<p>123  22/06/2019  2329.35 2060.59
<p>123  29/06/2019  2794.32 2142.11
<p>123  06/07/2019  3380.05 2265.91

当我运行下面的代码时，我得到了最右边的列 - 它是一个运行平均值，但它包含所有行，而不仅仅是我想要的当前行和前三行。

select
id,
date,
value,
avg(value) over (partition by id, (x.date between x.date and x.date - '22 days'::interval) order by date)

from
(select id, date::date, value from sales where date >= '2019-05-01') x

正确的值应该是

ID  date    value   four_wk_average
<p>123  04/05/2019  1640.88 1640.88
<p>123  11/05/2019  2093.4  1867.14
<p>123  18/05/2019  2671.57 2135.28
<p>123  25/05/2019  2034.6  2110.11
<p>123  01/06/2019  1564.62 2091.05
<p>123  08/06/2019  2143.29 2103.52
<p>123  15/06/2019  2007    1937.38
<p>123  22/06/2019  2329.35 2011.07
<p>123  29/06/2019  2794.32 2318.49
<p>123  06/07/2019  3380.05 2627.68

我认为我的分区将我的间隔部分作为 bool 测试并返回 TRUE - 我不知道如何在窗口函数中表示日期范围。

谢谢!

Answer 1

demo:db<>fiddle

方式 1:这适用于 Postgres 11+ 和每周多条记录

SELECT
    *,
    AVG(value) OVER (ORDER BY the_date RANGE BETWEEN interval '4 weeks - 1 day' PRECEDING AND CURRENT ROW)
FROM
    sales

时间间隔为 4 周:4 周减去 1 天加上当前日期。

---

方式 2:这也适用于 Postgres <11，并且仅当确实每周只有一条记录时:

SELECT
    *,
    AVG(value) OVER (ORDER BY the_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM
    sales

---

Further reading关于窗口函数和 ROWS 和 RANGE 子句

---

方式三:这是Postgres版本<11和每周多条记录的方式:

SELECT 
    s1.*,
    (SELECT AVG(s2.value)
     FROM sales s2
     WHERE s2.the_date <= s1.the_date
          AND s2.the_date > s1.the_date - interval '4 weeks')
FROM sales s1;