可以使用如下的SQL查询来计算每个工厂每个月的产量总和、收入总和,并按照工厂ID和生产日期排序:

SELECT 
    factory_id,
    DATE_FORMAT(production_date, '%Y-%m') AS month,
    SUM(quantity) AS total_quantity,
    SUM(revenue) AS total_revenue
FROM 
    production
GROUP BY 
    factory_id,
    month
ORDER BY 
    factory_id,
    month;

为了计算每个工厂每月的产量环比增长率和收入环比增长率,可以使用子查询和窗口函数来实现。首先,使用子查询计算每个月的产量总和和收入总和,并按照工厂ID和生产日期排序。然后,使用窗口函数计算相邻两个月的产量总和和收入总和,并计算环比增长率。

SELECT 
    factory_id,
    month,
    total_quantity,
    total_revenue,
    (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,
    (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
FROM (
    SELECT 
        factory_id,
        DATE_FORMAT(production_date, '%Y-%m') AS month,
        SUM(quantity) AS total_quantity,
        SUM(revenue) AS total_revenue
    FROM 
        production
    GROUP BY 
        factory_id,
        month
    ORDER BY 
        factory_id,
        month
) AS subquery;

这个查询会返回每个工厂每个月的产量总和、收入总和,以及相邻两个月的产量环比增长率和收入环比增长率

假设有一个 production 表包含以下列:factory_id 工厂ID、production_date 生产日期、quantity 产量 和 revenue 收入。编写 SQL 查询计算每个工厂每个月的产量总和、收入总和并按照工厂ID和生产日期排序。同时计算每个工厂每月的产量环比增长率和收入环比增长率。产量环比增长率和收入环比增长率分别定义为相邻两个月的增长率。

原文地址: https://www.cveoy.top/t/topic/ilJL 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录