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

# 确保每一个字段符合聚合函数的规则，所以先子查询一次
# sum()求每一行的汇总值
# sum()over(partition by x)求分区的汇总值,分区相同的每一行是一样的值
# sum()over(partition by x order by y)求累积汇总值,，任何一行都是一样的值
SELECT 
    product_category,
    age_group,
    total_sales total_sales_amount,
    ROUND(total_sales / SUM(total_sales) OVER(PARTITION BY product_category), 2) as purchase_percentage
FROM (
    SELECT 
        category as product_category,
        age_group,
        SUM(quantity * price) as total_sales
    FROM sales s
    join products p
    on s.product_id= p.product_id
    join customer_info c
    on c.sale_id =s.sale_id
    GROUP BY category, age_group
) t1
order by product_category ,purchase_percentage desc