该表由以下脚本表示:
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
product_id INTEGER,
sales_date DATE,
quantity INTEGER,
price NUMERIC
);
INSERT INTO sales (product_id, sales_date, quantity, price) VALUES
(1, '2023-01-01', 10, 10.00),
(1, '2023-01-02', 12, 12.00),
(1, '2023-01-03', 15, 15.00),
(2, '2023-01-01', 8, 8.00),
(2, '2023-01-02', 10, 10.00),
(2, '2023-01-03', 12, 12.00);
任务是计算每个 Product_id 最近 3 天的销售数量。该期间必须从每个product_id 的最大(最后)日期开始向后计算。因此,对于 1 来说,最大值是 2023-01-03,对于 2 来说也是如此。但是对于 Product_id 2,最后一天可能与 1 不同 - 比如说 2023-01-05。
通过在子查询中使用窗口函数应用此查询:
select product_id, max(increasing_sum) as quantity_last_3_days
from
(SELECT product_id,
SUM(quantity) OVER (PARTITION BY product_id ORDER BY sales_date RANGE BETWEEN INTERVAL '2 days'
PRECEDING AND CURRENT ROW) AS increasing_sum
FROM sales) as s
group by product_id;
我收到预期的输出:
| product_id | quantity_last_3_days |
|____________|______________________|
|_____1______|___________37_________|
|_____2______|___________30_________|
但这是最优解吗?有没有办法通过使用不带子查询的窗口函数来解决这个问题?