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

# 在窗口函数排序时需要注意如果遇到聚合函数的话，要进行group by
select a.product_id,
        sum(month_sales) as total_sales, 
        unit_price,
        sum(month_quantity) as total_quantity,
        round(sum(month_sales)/12,2) as avg_monthly_sales, 
        max(month_quantity) as max_monthly_quantity,
        customer_age_group
from(
    select o.product_id,
    unit_price,
    date_format(order_date,'%Y-%m'),
    sum(quantity) as month_quantity,
    sum(unit_price*quantity) as month_sales
    from orders as o
    left join products as p on o.product_id=p.product_id
    left join customers as c on o.customer_id=c.customer_id
    where year(order_date)=2023
    group by o.product_id, unit_price,date_format(order_date,'%Y-%m')
) as a
left join (
    select product_id,customer_age_group
    from(
        select o1.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' 
        when customer_age>60 then '61+' 
        end
        ) as customer_age_group,
        rank()over(partition by o1.product_id order by sum(quantity) desc,
        (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' 
        when customer_age>60 then '61+' 
        end
        ) ) as posn
        from orders as o1
        left join customers as c1 on o1.customer_id=c1.customer_id
        where year(order_date)=2023
        group by o1.product_id,customer_age_group
    ) as b
    where b.posn=1
) as c2 on a.product_id=c2.product_id
group by a.product_id,unit_price,customer_age_group
order by total_sales desc,a.product_id