SQL 查询：计算每个工厂每月产量、收入总和及环比增长率

使用如下的SQL查询来计算每个工厂每个月的产量总和、收入总和，并按照工厂ID和生产日期排序：\n\nsql\nSELECT \n factory_id,\n DATE_FORMAT(production_date, '%Y-%m') AS month,\n SUM(quantity) AS total_quantity,\n SUM(revenue) AS total_revenue\nFROM \n production\nGROUP BY \n factory_id,\n month\nORDER BY \n factory_id,\n month;\n\n\n为了计算每个工厂每月的产量环比增长率和收入环比增长率，可以使用子查询和窗口函数来实现。首先，使用子查询计算每个月的产量总和和收入总和，并按照工厂ID和生产日期排序。然后，使用窗口函数计算相邻两个月的产量总和和收入总和，并计算环比增长率。\n\nsql\nSELECT \n factory_id,\n month,\n total_quantity,\n total_revenue,\n (total_quantity - LAG(total_quantity) OVER (PARTITION BY factory_id ORDER BY month)) / LAG(total_quantity) OVER (PARTITION BY factory_id ORDER BY month) AS quantity_growth_rate,\n (total_revenue - LAG(total_revenue) OVER (PARTITION BY factory_id ORDER BY month)) / LAG(total_revenue) OVER (PARTITION BY factory_id ORDER BY month) AS revenue_growth_rate\nFROM (\n SELECT \n factory_id,\n DATE_FORMAT(production_date, '%Y-%m') AS month,\n SUM(quantity) AS total_quantity,\n SUM(revenue) AS total_revenue\n FROM \n production\n GROUP BY \n factory_id,\n month\n ORDER BY \n factory_id,\n month\n) AS subquery;\n\n\n这个查询会返回每个工厂每个月的产量总和、收入总和，以及相邻两个月的产量环比增长率和收入环比增长率。