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

/*当颗粒度不同时，用聚合函数会更方便一些，有计算排名百分比的也用聚合函数*/
/*每类产品的总销售额，注意是每类产品不是每个产品*/
with totalsales as (
    select category
    ,sum(quantity * price) over(partition by category) as total_sales
    from products p
    join sales s on p.product_id = s.product_id
),

/*每类产品不同年龄组的销售额*/
agesales as (
    select category
    ,age_group
    /*注意保留两位小数*/
    ,round(sum(quantity * price) over(partition by category,age_group),2) as total_sales_amount
    from products p
    join sales s on p.product_id = s.product_id
    join customer_info c on s.sale_id = c.sale_id
)

/*主查询*/
select distinct t1.category as product_category
,age_group
,total_sales_amount
,round(total_sales_amount/total_sales,2) as purchase_percentage
from totalsales t1
join agesales t2 on t1.category = t2.category
order by product_category