如何查询指定sensor_id每日最大值所在完整行并保留首次记录

嘿，这个问题我太熟悉了！你想要拿到指定传感器每天最大值对应的完整记录，还得处理同一天多个相同最大值时取首次出现的情况，其实有两种靠谱的方案，看你用的MySQL版本来选就行～

首先先明确你的表结构（方便大家理解问题）：

sensor_id | timestamp | value
-----------|------------|-------
1          | 1516905900 | 100.1
4          | 1516916920 | 90.4
...        | ...        | ...

方法一：用窗口函数（MySQL 8.0+ 首选） #

这是最简洁高效的方案，利用ROW_NUMBER()窗口函数给每条记录排序，直接筛选出我们要的那条：

SELECT sensor_id, timestamp, value, date_str
FROM (
    SELECT 
        sensor_id,
        timestamp,
        value,
        DATE_FORMAT(FROM_UNIXTIME(timestamp), '%m/%d/%Y') AS date_str,
        -- 按传感器+日期分区，先按值降序、再按时间升序排序
        ROW_NUMBER() OVER (
            PARTITION BY sensor_id, DATE_FORMAT(FROM_UNIXTIME(timestamp), '%m/%d/%Y')
            ORDER BY value DESC, timestamp ASC
        ) AS rn
    FROM sensor_log
    WHERE sensor_id = 6
) AS ranked_data
-- 只保留每个分区里排第一的记录（也就是当天最大值的首次出现）
WHERE rn = 1;

为啥这么写？

• PARTITION BY把数据按传感器ID和日期分成一个个小组，确保我们只处理指定传感器每天的数据
• ORDER BY value DESC, timestamp ASC先把最大值排在前面，要是同一天有多个相同的最大值，就把最早出现的那条放第一
• 外层查询筛选rn=1的记录，刚好就是我们要的每天目标数据

方法二：用JOIN（兼容MySQL 5.x等旧版本） #

如果你的MySQL版本不支持窗口函数，那就用JOIN的思路：先算出每天的最大值，再关联回原表找到对应记录，同时处理重复最大值的情况：

SELECT 
    sl.sensor_id,
    MIN(sl.timestamp) AS timestamp, -- 取同一天相同最大值里最早的时间戳
    sl.value,
    DATE_FORMAT(FROM_UNIXTIME(sl.timestamp), '%m/%d/%Y') AS date_str
FROM sensor_log sl
-- 关联子查询得到的每天最大值数据
JOIN (
    SELECT 
        DATE_FORMAT(FROM_UNIXTIME(timestamp), '%m/%d/%Y') AS date_str,
        MAX(value) AS max_val
    FROM sensor_log
    WHERE sensor_id = 6
    GROUP BY date_str
) AS daily_max 
    ON DATE_FORMAT(FROM_UNIXTIME(sl.timestamp), '%m/%d/%Y') = daily_max.date_str 
    AND sl.value = daily_max.max_val
WHERE sl.sensor_id = 6
GROUP BY sl.sensor_id, date_str, sl.value;

思路拆解：

1. 子查询先算出指定传感器每天的最大值
2. JOIN原表，匹配日期和最大值，找到所有符合条件的记录
3. 用MIN(timestamp)和GROUP BY，确保同一天相同最大值只保留最早出现的那条

小优化建议 #

如果对日期格式没有特别要求，建议把DATE_FORMAT(FROM_UNIXTIME(timestamp), '%m/%d/%Y')换成DATE(FROM_UNIXTIME(timestamp))，这样得到的是YYYY-MM-DD格式的日期，性能会更好（字符串格式化相对耗时），分组效果是一样的。

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