题解 | 统计每个产品的销售情况

#先计算价格、总销售额、总销售量
with total as (select 
o.product_id,unit_price,sum(quantity*unit_price) total_sales,sum(quantity) as total_quantity
from orders o left join products p on (o.product_id = p.product_id)
where year(order_date) = 2023
group by o.product_id,unit_price)
#计算每个商品每月的总销售额和总销售量
,sales_month as (
    select 
o.product_id,sum(quantity*unit_price) total_sales_momnth,sum(quantity) as total_quantity_month
from orders o left join products p on (o.product_id = p.product_id)
where year(order_date) = 2023
group by o.product_id,month(order_date)
)
#计算每个商品月均总销售额和最大购买量
,avg_max_sales as (
    select
    product_id,
    round(sum(total_sales_momnth)/12,2) as avg_monthly_sales,
    max(total_quantity_month) as max_monthly_quantity
    from sales_month
    group by product_id
)
#总计每一个商品在每个年龄段上的购买数量，当购买数量相同时，按照avg(customer_age)升序排序选择年龄偏小的群体
,age as (
    select 
    o.product_id,
    case 
        when customer_age between 1 and 10 then '1-10'
        when customer_age between 11 and 20 then '11-20'
        when customer_age between 21 and 30 then '21-30'
        when customer_age between 31 and 40 then '31-40'
        when customer_age between 41 and 50 then '41-50'
        when customer_age between 51 and 60 then '51-60'
        else '61+'
    end as customer_age_group,
    avg(customer_age),
    sum(quantity),
    row_number()over(partition by o.product_id order by sum(quantity) desc,avg(customer_age)) as ranking2
    from orders o left join customers c on (o.customer_id = c.customer_id)
    where year(order_date) = 2023
    group by o.product_id,customer_age_group
)

#选择购买数量最多的年龄群体
,max_quantity_age as (select *
    from age
    where ranking2 = 1
)

#表连接
select
t.product_id,
total_sales,
unit_price,
total_quantity,
avg_monthly_sales,
max_monthly_quantity,
customer_age_group
from total t left join avg_max_sales a on(t.product_id = a.product_id)
    left join max_quantity_age m on (t.product_id = m.product_id)
order by total_sales desc,product_id