解决Google Charts中按日期聚合传感器数据的重复行问题

我正尝试从MySQL服务器获取传感器记录，需将数据格式化为Google Charts所需的data.addRows格式。当前PHP代码生成的图表行每个日期重复三次，而需求是每个日期仅显示一行，格式如[new Date(2025, 0, 1), 394, 432, 434]。

数据结构 #

sensorID | sensorValue | sensorDate
1 | 394 | 2025-01-01 00:01:00
2 | 432 | 2025-01-01 00:01:10
3 | 434 | 2025-01-01 00:01:15
1 | 405 | 2025-01-02 00:01:00
2 | 416 | 2025-01-02 00:01:10
3 | 328 | 2025-01-02 00:01:15
2 | 428 | 2025-01-03 00:01:10
1 | 421 | 2025-01-03 00:01:15
3 | 301 | 2025-01-03 00:01:20

原查询语句 #

$result0 = mysqli_query($conn, "SELECT DISTINCT sensorID, DATE_FORMAT(sensorDate, '%Y-%m-%d') as sensorDate_change, sensorValue FROM sensorRecords ORDER BY sensorDate, sensorID") ;

解决方案 #

1. 调整SQL查询，按日期聚合传感器数据 #

原查询返回的是每个传感器的单日独立记录，导致日期重复。使用条件聚合将同一日期的所有传感器值合并到一行：

SELECT
    DATE_FORMAT(sensorDate, '%Y-%m-%d') as sensorDate,
    MAX(CASE WHEN sensorID = 1 THEN sensorValue END) as sensor1,
    MAX(CASE WHEN sensorID = 2 THEN sensorValue END) as sensor2,
    MAX(CASE WHEN sensorID = 3 THEN sensorValue END) as sensor3
FROM sensorRecords
GROUP BY DATE_FORMAT(sensorDate, '%Y-%m-%d')
ORDER BY sensorDate;

注：由于单日每个传感器仅一条记录，用MAX/MIN/AVG均可，目的是取出对应传感器的唯一值。

2. PHP代码生成Google Charts格式数据 #

循环处理查询结果，将日期转换为Google Charts兼容的new Date()格式，组装成目标数组结构：

// 执行调整后的SQL查询
$result0 = mysqli_query($conn, "SELECT DATE_FORMAT(sensorDate, '%Y-%m-%d') as sensorDate, MAX(CASE WHEN sensorID = 1 THEN sensorValue END) as sensor1, MAX(CASE WHEN sensorID = 2 THEN sensorValue END) as sensor2, MAX(CASE WHEN sensorID = 3 THEN sensorValue END) as sensor3 FROM sensorRecords GROUP BY DATE_FORMAT(sensorDate, '%Y-%m-%d') ORDER BY sensorDate");

$rows = [];
while ($row = mysqli_fetch_assoc($result0)) {
    // 拆分日期，注意Google Charts月份从0开始，需减1
    list($year, $month, $day) = explode('-', $row['sensorDate']);
    $datePart = "new Date({$year}, " . ($month - 1) . ", {$day})";
    // 组装单条记录
    $rows[] = "[{$datePart}, {$row['sensor1']}, {$row['sensor2']}, {$row['sensor3']}]";
}

// 输出最终的data.addRows格式
echo "data.addRows([\n" . implode(",\n", $rows) . "\n]);";

处理后每个日期仅生成一行数据，完全符合[new Date(2025, 0, 1), 394, 432, 434]的要求。

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