主页 / coding / 问题 / 78429749
Accepted
Jelly
Asked: 2024-05-05 00:53:00 +0800 CST

PostgreSQL 窗口函数计算每个区间的总和

  • 772

该表由以下脚本表示：

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

但这是最优解吗？有没有办法通过使用不带子查询的窗口函数来解决这个问题？

sql

2 个回答

  1. WITH如果您想避免使用窗口函数（可能是因为其认知负荷），您还可以使用子句（又名 CTE）来解决问题。由于查询实际上不需要每个产品 id 超过单个阈值，因此您可以使用 CTE 在连接条件中表达过滤，如下所示：

    WITH latest_sales AS (
  SELECT product_id, max(sales_date) AS time 
  FROM sales 
  GROUP BY product_id
)
SELECT sales.product_id, sum(sales.quantity) as quantity_last_3_days
FROM sales INNER JOIN latest_sales ON 
    sales.product_id = latest_sales.product_id 
    AND sales.sales_date >= latest_sales.time - interval '2 days'
GROUP BY sales.product_id;
    • 1
  2. Best Answer

    但这是最优解吗？

    不，如果您只需要“将每个人最近 3 天的销售额相加”，quantityproduct_id则不需要。

    有没有办法通过使用不带子查询的窗口函数来解决这个问题？

    您通常可以将窗口函数换成相关子查询lateral或标量，但有一种更简单的方法可以加快查询速度并纠正查询。它正在做额外的工作，试图为您提供滚动/步进总和；该窗口不会尝试获取每个 的最近 3 天product_id。

    相反，对于每一行，它都会回顾product_id2 天前具有相同内容的行。您稍后将选择 总和最高的 3 天时间段quantity，这不一定是最近的 3 个日期。

    1.0s对于 400k 个样本，您的查询采用不带索引、0.7s带覆盖索引的整体，您可以从该索引向下转到0.4s不带或0.1s带覆盖索引。您只需要询问每个最近 3 个日期的总和product_id：demo at db<>fiddle

    select product_id,sum(quantity) as quantity_last_3_days
from(select *,row_number()over(PARTITION BY product_id ORDER BY sales_date DESC)
     from sales)_
where 3>=row_number
group by product_id;

    这里的技巧是窗口函数将使用 a 执行Run Condition: (3 >= row_number() OVER (?))，这意味着它只会获取最近的 3 个并退出。它甚至可以直接从覆盖索引的顶部获取它们，而无需访问表。
    您的原始查询必须扫描整个内容（整个表或整个索引，如果可用），然后对其进行排序以获取max().

    • 1

相关问题