基于MySQL计算智能插座分时段kWh耗电量的查询方法

背景说明 #

我们通过Zabbix服务器的MySQL数据库，每分钟采集一次智能插座的瓦数（watts）功耗数据，数据存储在history表中，可通过以下语句获取原始数据：

SELECT FROM_UNIXTIME(clock) as ts, value FROM history WHERE itemid=54339

返回的原始数据格式示例：

2024-03-17 21:36:40 261
2024-03-17 21:37:39 271
2024-03-17 21:38:40 271
2024-03-17 21:39:40 268
2024-03-17 21:40:39 264

计算逻辑 #

耗电量核心公式：kWh = (瓦数 × 持续时间（小时）) / 1000
由于数据是每分钟采集一次，我们用每条数据的瓦数乘以它到下一条数据的时间间隔（转成小时），累加后得到时段总耗电量。

分时段查询实现 #

1. 每小时耗电量统计 #

SELECT
    DATE_FORMAT(ts, '%Y-%m-%d %H:00:00') AS hour_period,
    ROUND(SUM((value * TIMESTAMPDIFF(SECOND, ts, next_ts)) / 3600000), 4) AS total_kwh
FROM (
    SELECT
        FROM_UNIXTIME(clock) AS ts,
        value,
        LEAD(FROM_UNIXTIME(clock)) OVER (ORDER BY clock) AS next_ts
    FROM history
    WHERE itemid = 54339
) AS temp
WHERE next_ts IS NOT NULL
GROUP BY hour_period
ORDER BY hour_period;

用LEAD函数获取下一条数据的时间，计算每条数据的持续秒数，转成小时后计算单条耗电量，最后按小时分组汇总。

2. 每日耗电量统计 #

SELECT
    DATE_FORMAT(ts, '%Y-%m-%d') AS day_period,
    ROUND(SUM((value * TIMESTAMPDIFF(SECOND, ts, next_ts)) / 3600000), 4) AS total_kwh
FROM (
    SELECT
        FROM_UNIXTIME(clock) AS ts,
        value,
        LEAD(FROM_UNIXTIME(clock)) OVER (ORDER BY clock) AS next_ts
    FROM history
    WHERE itemid = 54339
) AS temp
WHERE next_ts IS NOT NULL
GROUP BY day_period
ORDER BY day_period;

逻辑与小时统计一致，仅分组格式改为按日期（%Y-%m-%d）。

3. 每月耗电量统计 #

SELECT
    DATE_FORMAT(ts, '%Y-%m') AS month_period,
    ROUND(SUM((value * TIMESTAMPDIFF(SECOND, ts, next_ts)) / 3600000), 4) AS total_kwh
FROM (
    SELECT
        FROM_UNIXTIME(clock) AS ts,
        value,
        LEAD(FROM_UNIXTIME(clock)) OVER (ORDER BY clock) AS next_ts
    FROM history
    WHERE itemid = 54339
) AS temp
WHERE next_ts IS NOT NULL
GROUP BY month_period
ORDER BY month_period;

分组格式改为年月（%Y-%m），汇总每月总耗电量。

4. 每年耗电量统计 #

SELECT
    DATE_FORMAT(ts, '%Y') AS year_period,
    ROUND(SUM((value * TIMESTAMPDIFF(SECOND, ts, next_ts)) / 3600000), 4) AS total_kwh
FROM (
    SELECT
        FROM_UNIXTIME(clock) AS ts,
        value,
        LEAD(FROM_UNIXTIME(clock)) OVER (ORDER BY clock) AS next_ts
    FROM history
    WHERE itemid = 54339
) AS temp
WHERE next_ts IS NOT NULL
GROUP BY year_period
ORDER BY year_period;

分组格式改为年份（%Y），汇总每年总耗电量。

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