如何在MySQL中计算月度总费用

问题背景 #

数据库每分钟记录一次电力数据，示例数据如下：

TIME                    KWHI        EUR
2023-12-02 23:00:15     8852.892    12.8040
2023-12-02 22:59:13     8852.892    13.4496
2023-12-02 22:58:11     8852.892    13.4496
2023-12-02 22:57:09     8852.892    13.4496

当前SQL查询可计算每小时费用，但EUR价格每小时变动，需修改查询以计算月度总费用：

SELECT  TIME,
     ((max(KWHI) - min(KWHI)) * EUR)/100 AS HOUREUR
FROM LOG WHERE 1    
GROUP BY 
    YEAR(TIME),
    MONTH(TIME),
    DAY(TIME),
    HOUR(TIME)
ORDER BY TIME DESC LIMIT 24

解决方案 #

先按小时统计各时段的用电量与对应单价，再累加所有小时费用得到月度总和，修改后的查询如下：

SELECT
    YEAR(TIME) AS YEAR,
    MONTH(TIME) AS MONTH,
    SUM(HOUR_COST) AS MONTHLY_TOTAL_EUR
FROM (
    -- 计算每个小时的费用
    SELECT
        YEAR(TIME) AS YEAR,
        MONTH(TIME) AS MONTH,
        DAY(TIME) AS DAY,
        HOUR(TIME) AS HOUR,
        ((MAX(KWHI) - MIN(KWHI)) * AVG(EUR)) / 100 AS HOUR_COST
    FROM LOG
    GROUP BY
        YEAR(TIME),
        MONTH(TIME),
        DAY(TIME),
        HOUR(TIME)
) AS HOURLY_COSTS
GROUP BY
    YEAR,
    MONTH
ORDER BY
    YEAR DESC,
    MONTH DESC;

关键说明 #

• 内层子查询HOURLY_COSTS：保留原小时费用计算逻辑，用AVG(EUR)取小时内平均单价（若同小时内EUR固定，也可用MAX(EUR)或MIN(EUR)替代）。
• 外层查询：按年、月分组，累加所有小时费用得到月度总费用，结果按年月倒序排列。

内容的提问来源于stack exchange，提问作者Maario