postgresql SQL:如何在考虑时间约束的情况下跨组求平均值

fhity93d  于 2022-12-03  发布在  PostgreSQL
关注(0)|答案(1)|浏览(97)

I have a table named orders in a Postgres database that looks like this:

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

Here's a SQL Fiddle: http://sqlfiddle.com/#!17/262fc/1
I'd like to return the average money spent by customers per product, but only consider orders within the first 12 months of a given customer's first purchase within the given product group. (yes, this is challenging!)
For example, for customer 1, order ID 2 and order ID 11 would be factored into the average for books (because order ID 11 took place less than 12 months after customer 1's first order for books , which was order ID 2), but order ID 16 would not be factored into the average (because 8/3/22 is more than 12 months from customer 1's first purchase for books , which took place on 3/5/21).
Here is a matrix showing which orders would be included within a given product (denoted by "yes"):

The desired output would look as follows:

average_spent
books    22.20
music    7.83
travel   1530.71 
food     82.50

How would I do this?
Thanks in advance for any assistance you can give!

qnyhuwrf

qnyhuwrf1#

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

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

相关问题