SQL高级用法之前90%销售额的商品

要求占据前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. 累加计算占比，找到占据前90%销售总额的商品类型。

  在上一步的基础上，使用累加计算的方式，找到占据前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;

  ```