SQL:如何在考虑时间限制的情况下对各组进行平均

Question

我在 Postgres 数据库中有一个名为 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

这是一个 SQL fiddle :http://sqlfiddle.com/#!17/262fc/1

我想返回客户每种产品花费的平均金额，但只考虑给定客户首次购买给定产品后的前 12 个月内的订单组。 (是的，这很有挑战性!)

例如，对于客户 1，订单 ID 2 和订单 ID 11 将计入书籍的平均值(因为订单 ID 11 发生在客户 1 第一次订购 books，这是订单 ID 2)，但订单 ID 16 不会被计入平均值(因为 8/3/22 距离客户 1 首次购买超过 12 个月书籍，发生在 21 年 3 月 5 日)。

这是一个矩阵，显示给定产品中将包含哪些订单(用"is"表示):

所需的输出如下所示:

         average_spent
books    22.20
music    7.83
travel   1530.71 
food     82.50

我该怎么做？

在此先感谢您提供的任何帮助!

Answer 1

您可以使用子查询来检查是否在总和中包含产品的价格:

select o.product, sum(o.price)/count(*) val from orders o 
where o.order_date < (select min(o1.order_date) from orders o1 where 
      o1.product = o.product and o.user_id = o1.user_id) + interval '12 months' 
group by o.product

See fiddle