PostgreSQL按产品统计近3天销量：窗口函数方案优化与无SUBQUERY问询

表结构与测试数据 #

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天内的销量总和（不同产品的最晚销售日期可能不同）。

当前实现方案 #

通过带窗口函数的子查询实现，查询语句如下：

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_________|     

问题解答 #

1. 当前方案是不是最优的？

当前方案逻辑清晰，但算不上最优。原因很直接：

• 窗口函数会给每一行都计算累计值，之后再用MAX()取最大值，做了大量冗余计算——我们其实只需要每个产品最晚销售日期那一行的窗口求和结果，其他行的累计值都是多余的。
• 更高效的写法可以先锁定每个产品的最晚销售日期，再关联原表筛选出该日期前3天内的数据，最后直接聚合求和。比如：

SELECT s.product_id, SUM(s.quantity) AS quantity_last_3_days
FROM sales s
JOIN (
    SELECT product_id, MAX(sales_date) AS latest_date
    FROM sales
    GROUP BY product_id
) t ON s.product_id = t.product_id
AND s.sales_date >= t.latest_date - INTERVAL '2 days'
GROUP BY s.product_id;

这种写法避免了全量累计计算，数据量较大时性能更优，同时逻辑也更直观：先找到每个产品的最晚销售节点，再精准抓取时间范围内的销量求和。

2. 能不能不用子查询，仅靠窗口函数实现？

当然可以，有两种可行的写法：
第一种是用FILTER结合窗口函数，只针对每个产品的最晚销售日期行计算窗口内的销量总和：

SELECT DISTINCT
    product_id,
    SUM(quantity) OVER (
        PARTITION BY product_id 
        ORDER BY sales_date 
        RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
    ) FILTER (WHERE sales_date = MAX(sales_date) OVER (PARTITION BY product_id)) AS quantity_last_3_days
FROM sales;

第二种更简洁，用LAST_VALUE()直接取每个产品分组中，最晚销售日期对应的窗口累计值：

SELECT DISTINCT
    product_id,
    LAST_VALUE(
        SUM(quantity) OVER (
            PARTITION BY product_id 
            ORDER BY sales_date 
            RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
        )
    ) OVER (PARTITION BY product_id) AS quantity_last_3_days
FROM sales;

这两种写法都不需要子查询，纯窗口函数就能搞定。注意用LAST_VALUE()时，因为按sales_date升序排序，分组里的最后一行正好是最晚销售日期的行，所以直接取最后一个值即可。

内容的提问来源于stack exchange，提问作者Jelly