连续两个日期的 SQL 窗口聚合

Question

我不是 SQL 专家，觉得这有点挑战性。想象一下，我有下表，但有更多用户:

+---------+--------+--------+-------------+
| user_id | amount |  date  | sum_per_day |
+---------+--------+--------+-------------+
| user8   |   300  | 7/2/20 |     300     |
| user8   |   150  | 6/2/20 |     400     |
| user8   |   250  | 6/2/20 |     400     |
| user8   |   25   | 5/2/20 |     100     |
| user8   |   25   | 5/2/20 |     100     |
| user8   |   25   | 5/2/20 |     100     |
| user8   |   25   | 5/2/20 |     100     |
| user8   |   50   | 2/2/20 |     50      |
+---------+--------+--------+-------------+

如您所见，它们按 user_id 分组.现在我喜欢做的是添加一个名为 sum_over_two_day 的列。满足以下条件:

按 user_id 分组

对于每个用户，它按 date 分组

然后每两个连续日历日计算 amount 的总和。 (今天 + 前一个日历日)

所以输出将是这样的:

    +---------+--------+--------+-------------+------------------+
    | user_id | amount |  date  | sum_per_day | sum_over_two_day |
    +---------+--------+--------+-------------+------------------+
    | user8   |   300  | 7/2/20 |     300     |       700        |
    | user8   |   150  | 6/2/20 |     400     |       500        |
    | user8   |   250  | 6/2/20 |     400     |       500        |
    | user8   |   25   | 5/2/20 |     100     |       100        |
    | user8   |   25   | 5/2/20 |     100     |       100        |
    | user8   |   25   | 5/2/20 |     100     |       100        |
    | user8   |   25   | 5/2/20 |     100     |       100        |
    | user8   |   50   | 2/2/20 |     50      |       50         |
    +---------+--------+--------+-------------+------------------+

Answer 1

正确的方法是使用带有 RANGE 的窗口函数条款:

SELECT user_id,
       amount,
       date,
       sum(amount) OVER (PARTITION BY user_id
                         ORDER BY date
                         RANGE BETWEEN INTERVAL '1 day' PRECEDING
                               AND CURRENT ROW)
          AS sum_over_two_day
FROM atable
ORDER BY user_id, date;

 user_id | amount |    date    | sum_over_two_day 
---------+--------+------------+------------------
 user8   |     50 | 2020-02-02 |               50
 user8   |     25 | 2020-02-05 |              100
 user8   |     25 | 2020-02-05 |              100
 user8   |     25 | 2020-02-05 |              100
 user8   |     25 | 2020-02-05 |              100
 user8   |    250 | 2020-02-06 |              500
 user8   |    150 | 2020-02-06 |              500
 user8   |    300 | 2020-02-07 |              700
(8 rows)