⭐题解 | 对商品的销售情况进行深度分析

select 
    p.category as product_category,
    c.age_group,sum(s.quantity*s.price) as total_sales_amount,
    round(sum(quantity * price)/sum(sum(quantity * price)) over(partition by category) ,2) as purchase_percentage
from sales as s
left join customer_info as c
on s.sale_id = c.sale_id
left join products as p
on s.product_id = p.product_id
group by p.category,c.age_group
order by p.category,purchase_percentage desc

ROUND( SUM(quantity * price) / SUM(SUM(quantity * price)) OVER (PARTITION BY category)#使用一个窗口函数来计算当前这个商品所在分类的【总销售额】 , 2) AS purchase_percentage

它的作用是：计算【每个商品】在【它所属分类】里的销售额占比（百分比）并且结果四舍五入保留 2 位小数。