SQL高級用法之前90%銷售額的商品
要求占據(jù)前90%銷售總額的商品類型，可以按照以下步驟進行計算：
1. 計算每個商品類型的銷售總額。
??首先，對tb_sale_amount表按照商品類型（good_category）進行分組，然后計算每個商品類型的銷售總額。
??```sql
??SELECT good_category, SUM(amount) AS total_amount
??FROM tb_sale_amount
??GROUP BY good_category;
??```
2. 計算銷售總額的百分比并排序。
??在上一步的基礎上，計算每個商品類型銷售總額在銷售總額中的占比，并按照占比降序排序。
??```sql
??SELECT good_category, total_amount,
????total_amount / (SELECT SUM(amount) FROM tb_sale_amount) AS percentage
??FROM (
????SELECT good_category, SUM(amount) AS total_amount
????FROM tb_sale_amount
????GROUP BY good_category
??) AS subquery
??ORDER BY percentage DESC;
??```
3. 累加計算占比，找到占據(jù)前90%銷售總額的商品類型。
??在上一步的基礎上，使用累加計算的方式，找到占據(jù)前90%銷售總額的商品類型。
??```sql
??SELECT good_category, total_amount, percentage
??FROM (
????SELECT good_category, total_amount, percentage,
??????SUM(percentage) OVER (ORDER BY percentage DESC) AS cumulative_percentage
????FROM (
??????SELECT good_category, SUM(amount) AS total_amount,
????????total_amount / (SELECT SUM(amount) FROM tb_sale_amount) AS percentage
??????FROM tb_sale_amount
??????GROUP BY good_category
????) AS subquery
??) AS subquery2
??WHERE cumulative_percentage <= 0.9;
??```
