MySQL查询需求：识别智能电表高功耗数据组并统计关键信息

需求说明 #

我在MySQL数据库中存储了按timestamp（时间戳）排序的智能电表记录，需要实现以下查询：

• 识别出watt now > 100的连续数据组，为每组分配递增的组ID
• 获取每组对应的月份、日期、首条记录的key值、末条记录的key值，以及该组的平均watt now值

示例数据 #

期望查询结果 #

查询方案 #

以下是基于MySQL 8.0+窗口函数的实现方案（如果是MySQL 5.x版本，可使用下方变量模拟方案）：

WITH filtered_data AS (
    SELECT
        `key`,
        timestamp,
        `watt now`,
        -- 将自定义时间戳格式转换为标准日期时间
        STR_TO_DATE(timestamp, '%Y-%m-%d-%H-%i-%s') AS dt,
        -- 标记符合高功率条件的记录
        CASE WHEN `watt now` > 100 THEN 1 ELSE 0 END AS is_high
    FROM meter_records
),
grouped_data AS (
    SELECT
        `key`,
        dt,
        `watt now`,
        -- 通过行号差值识别连续高功率组：同一连续组的差值保持一致
        ROW_NUMBER() OVER (ORDER BY dt, `key`) - 
        ROW_NUMBER() OVER (PARTITION BY is_high ORDER BY dt, `key`) AS group_id
    FROM filtered_data
    WHERE is_high = 1 -- 仅保留高功率记录
)
SELECT
    MONTH(dt) AS month,
    DAY(dt) AS day,
    -- 将组ID重新编号为从0开始的递增序列
    DENSE_RANK() OVER (ORDER BY MIN(dt)) - 1 AS `numbers of group`,
    MIN(`key`) AS `first id`,
    MAX(`key`) AS `last id`,
    ROUND(AVG(`watt now`), 0) AS `average watt`
FROM grouped_data
GROUP BY group_id, MONTH(dt), DAY(dt)
ORDER BY `numbers of group`;

关键逻辑说明 #

1. filtered_data CTE：转换非标准时间戳为MySQL可识别格式，同时标记高功率记录。
2. grouped_data CTE：利用两个ROW_NUMBER()的差值区分连续组——连续符合条件的记录差值相同，形成唯一组ID。
3. 最终聚合：按组ID、月份、日期分组，提取首尾记录key、计算平均功率，同时将组ID转为从0开始的递增序列。

MySQL 5.x适配方案 #

若使用不支持窗口函数的MySQL 5.x，可通过用户变量实现分组：

SELECT
    MONTH(dt) AS month,
    DAY(dt) AS day,
    @group_num := @group_num + 1 AS `numbers of group`,
    MIN(`key`) AS `first id`,
    MAX(`key`) AS `last id`,
    ROUND(AVG(`watt now`), 0) AS `average watt`
FROM (
    SELECT
        `key`,
        STR_TO_DATE(timestamp, '%Y-%m-%d-%H-%i-%s') AS dt,
        `watt now`,
        -- 用变量标记连续组：当前为高功率且上一条不是时，组ID递增
        @group_id := CASE 
            WHEN `watt now` > 100 AND (@prev_watt <= 100 OR @prev_watt IS NULL) 
            THEN @group_id + 1 
            ELSE @group_id 
        END AS group_id,
        @prev_watt := `watt now`
    FROM meter_records, (SELECT @group_id := -1, @prev_watt := NULL) vars
    ORDER BY timestamp, `key`
) t
WHERE `watt now` > 100
GROUP BY group_id, MONTH(dt), DAY(dt)
ORDER BY `numbers of group`;

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