postgresql - 多个窗函数的累加和

Question

我有一个结构表:

id | date       | player_id | score
--------------------------------------
1  | 2019-01-01 | 1         | 1
2  | 2019-01-02 | 1         | 1
3  | 2019-01-03 | 1         | 0
4  | 2019-01-04 | 1         | 0
5  | 2019-01-05 | 1         | 1
6  | 2019-01-06 | 1         | 1
7  | 2019-01-07 | 1         | 0
8  | 2019-01-08 | 1         | 1
9  | 2019-01-09 | 1         | 0
10 | 2019-01-10 | 1         | 0
11 | 2019-01-11 | 1         | 1

我想再创建两个列，“total_score”、“last_seven_days”。

total_score 是 player_id 得分的滚动总和

last_seven_days 是过去 7 天的得分，包括日期之前和之前

我编写了以下 SQL 查询:

SELECT id,
    date,
    player_id,
    score,
    sum(score) OVER all_scores AS all_score,
    sum(score) OVER last_seven AS last_seven_score
   FROM scores
  WINDOW all_scores AS (PARTITION BY player_id ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 
last_seven AS (PARTITION BY player_id ORDER BY id ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING);

得到如下输出:

id | date       | player_id | score | all_score | last_seven_score   
------------------------------------------------------------------
1  | 2019-01-01 | 1         | 1     |           | 
2  | 2019-01-02 | 1         | 1     | 1         | 1
3  | 2019-01-03 | 1         | 0     | 2         | 2
4  | 2019-01-04 | 1         | 0     | 2         | 2
5  | 2019-01-05 | 1         | 1     | 2         | 2
6  | 2019-01-06 | 1         | 1     | 3         | 3
7  | 2019-01-07 | 1         | 0     | 4         | 4
8  | 2019-01-08 | 1         | 1     | 4         | 4
9  | 2019-01-09 | 1         | 0     | 5         | 4
10 | 2019-01-10 | 1         | 0     | 5         | 3
11 | 2019-01-11 | 1         | 1     | 5         | 3

我意识到我需要改变这个

last_seven AS(PARTITION BY player_id ORDER BY id ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING)

而不是 7，使用某种日期格式，因为只有数字 7 会引入错误。

即能够执行 date - 2days 或 date - 6days

会很好

我还想添加 3 个月、6 个月、12 个月等列，因此需要它能够动态显示。

DEMO

Answer 1

demo:db<>fiddle

---

Postgres 11+ 的解决方案:

像@LaurenzAlbe 那样使用RANGE 间隔

---

Postgres <11 的解决方案:

(只呈现“days”部分，“all_scores”部分相同)

在 player_id 和相关的 date 范围内加入针对自身的表格:

SELECT s1.*,
    (SELECT SUM(s2.score)
     FROM scores s2
     WHERE s2.player_id = s1.player_id
         AND s2."date" BETWEEN s1."date" - interval '7 days' AND s1."date" - interval '1 days')
FROM scores s1