PostgreSQL高效统计：无需多CTE关联计算数量与平均值

表结构与测试数据 #

在PostgreSQL 14.8数据库中，存在orders表，结构及测试数据如下：

CREATE TABLE orders (
  user_id int
, order_id int
, order_date date
, quantity int
, revenue float
, product text
);

INSERT INTO orders VALUES
(1, 1, '2021-03-05', 1, 15, 'books'),
(1, 2, '2022-03-07', 1, 3, 'music'),
(1, 3, '2022-06-15', 1, 900, 'travel'),
(1, 4, '2021-11-17', 2, 25, 'books'),
(2, 5, '2022-08-03', 2, 32, 'books'),
(2, 6, '2021-04-12', 2, 4, 'music'),
(2, 7, '2021-06-29', 3, 9, 'books'),
(2, 8, '2022-11-03', 1, 8, 'music'),
(3, 9, '2022-11-07', 1, 575, 'food'),
(3, 10, '2022-11-20', 2, 95, 'food'),
(3, 11, '2022-11-20', 1, 95, 'food'),
(4, 12, '2022-11-20', 2, 95, 'books'),
(4, 13, '2022-11-21', 1, 95, 'food'),
(4, 14, '2022-11-23', 4, 17, 'books'),
(5, 15, '2022-11-20', 1, 95, 'food'),
(5, 16, '2022-11-25', 2, 95, 'books'),
(5, 17, '2022-11-29', 1, 95, 'food');

需求描述 #

需要完成两个指标计算，前提是筛选出**首次购买（按order_date排序）产品为books**的用户（即user_id为1和4的用户）：

• A）该群体购买books的平均quantity（示例结果：2.25）
• B）这些books购买记录的总revenue（示例结果：152）

原方案性能问题 #

原实现采用多个CTE嵌套，测试数据集上可正常运行，但在40万条记录的生产环境中性能不佳，原因是全表计算ROW_NUMBER()会产生大量中间数据，后续关联操作也会增加计算开销。

优化后的实现方案 #

方案1：通过NOT EXISTS定位目标用户，减少中间数据量 #

WITH first_book_users AS (
    SELECT user_id
    FROM orders o
    WHERE NOT EXISTS (
        -- 检查是否存在更早的订单
        SELECT 1
        FROM orders o2
        WHERE o2.user_id = o.user_id
          AND o2.order_date < o.order_date
    )
    AND o.product = 'books'
)
SELECT
    AVG(quantity) AS avg_books_quantity,
    SUM(revenue) AS total_books_revenue
FROM orders
WHERE user_id IN (SELECT user_id FROM first_book_users)
  AND product = 'books';

该方案直接通过NOT EXISTS找到每个用户的最早订单，并过滤出首次购买为books的用户，避免了全表计算窗口函数，大幅减少中间数据处理量。

方案2：用FIRST_VALUE窗口函数简化逻辑 #

SELECT
    AVG(quantity) AS avg_books_quantity,
    SUM(revenue) AS total_books_revenue
FROM (
    SELECT
        *,
        -- 获取每个用户的首个购买产品
        FIRST_VALUE(product) OVER (PARTITION BY user_id ORDER BY order_date) AS first_product
    FROM orders
) t
WHERE first_product = 'books'
  AND product = 'books';

此方案用FIRST_VALUE直接获取每个用户的首次购买产品，然后仅筛选符合条件的记录进行统计，逻辑更紧凑，减少了CTE的嵌套层级。

索引优化建议 #

为进一步提升查询性能，建议创建以下复合索引：

-- 加速获取每个用户的首个订单及产品
CREATE INDEX idx_orders_user_date_product ON orders(user_id, order_date, product);
-- 快速筛选用户的书籍订单
CREATE INDEX idx_orders_user_product ON orders(user_id, product);

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