如何按产品分组计算符合时间约束的客户平均消费额？

我在PostgreSQL数据库中有一张名为orders的表，结构与数据如下：

customer_id    order_id    order_date    price    product
1              2           2021-03-05    15       books
1              13          2022-03-07    3        music
1              14          2022-06-15    900      travel
1              11          2021-11-17    25       books
1              16          2022-08-03    32       books
2              4           2021-04-12    4        music
2              7           2021-06-29    9        music
2              20          2022-11-03    8        music
2              22          2022-11-07    575      travel
2              24          2022-11-20    95       food
3              3           2021-03-17    25       books
3              5           2021-06-01    650      travel
3              17          2022-08-17    1200     travel
3              19          2022-10-02    6        music
3              23          2022-11-08    70       food
4              9           2021-08-20    3200     travel
4              10          2021-10-29    2750     travel
4              15          2022-07-15    1820     travel
4              21          2022-11-05    8000     travel
4              25          2022-11-29    27       books
5              1           2021-01-04    3        music
5              6           2021-06-09    820      travel
5              8           2021-07-30    19       books
5              12          2021-12-10    22       music
5              18          2022-09-19    20       books

我需要按产品分组返回平均消费额，但仅统计客户在该产品组首次购买后12个月内的订单。

举个例子：客户1的books类订单中，订单ID2和11会被纳入计算（因为订单11在首次购买日期2021-03-05的12个月内），但订单16不会（2022-08-03距离首次购买已超过12个月）。

期望输出如下：

average_spent
books    22.20
music    7.83
travel   1530.71 
food     82.50

请问如何实现这个SQL查询？

实现方案 #

可以通过CTE（公共表表达式）先计算每个客户对每个产品的首次购买日期，再关联原表筛选出符合时间范围的订单，最后按产品分组计算平均价格：

WITH customer_product_first_purchase AS (
    SELECT 
        customer_id,
        product,
        MIN(order_date) AS first_purchase_date
    FROM orders
    GROUP BY customer_id, product
)
SELECT 
    o.product,
    ROUND(AVG(o.price), 2) AS average_spent
FROM orders o
JOIN customer_product_first_purchase cpfp 
    ON o.customer_id = cpfp.customer_id 
    AND o.product = cpfp.product
WHERE o.order_date <= cpfp.first_purchase_date + INTERVAL '12 months'
GROUP BY o.product
ORDER BY o.product;

逻辑说明 #

1. CTE部分：customer_product_first_purchase 通过分组customer_id和product，使用MIN(order_date)得到每个客户对每个产品的首次购买日期。
2. 关联筛选：将原表orders与CTE关联，通过customer_id和product匹配，并用WHERE子句筛选出订单日期在首次购买日期后12个月内的记录。
3. 分组计算：按product分组，使用AVG(o.price)计算符合条件的订单平均价格，并用ROUND保留两位小数，最后按产品排序输出。

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