如何基于最新时间戳与1分钟间隔对双Arduino设备传感数据求平均？

Hey there! Let's get that averaging query working for your Arduino sensor data. First, let's break down what's wrong with your original attempt and then build a correct, robust solution.

Key Issues in Your Original Query #

Your initial SQL had a few syntax and logical gaps:

• Missing the FROM clause (you didn't specify which table to pull data from)
• No GROUP BY clause—aggregation functions like AVG() need a grouping to calculate per-interval averages
• Incorrect syntax for handling time intervals (SQL doesn't use interval =... like that; we need database-specific date truncation functions)

Solution: Per-Minute Averaging Queries #

Since you're using a Timestamp(3) (millisecond-precision) field, we need to "truncate" each timestamp to its minute-level window first, then calculate averages for each window. Below are examples for the most common SQL databases:

1. For SQL Server

SELECT
    -- Truncate Timestamp(3) to the start of its minute window
    DATEADD(minute, DATEDIFF(minute, 0, time), 0) AS minute_window,
    AVG(humidity) AS avg_humidity,
    AVG(temperature) AS avg_temperature,
    AVG(rainfall) AS avg_rainfall
FROM your_sensor_data_table -- Replace with your actual table name
-- Optional: Filter to data up to your last input timestamp
WHERE time <= (SELECT MAX(time) FROM your_sensor_data_table)
GROUP BY DATEADD(minute, DATEDIFF(minute, 0, time), 0)
ORDER BY minute_window DESC; -- Sort newest intervals first

2. For MySQL/MariaDB

SELECT
    DATE_FORMAT(time, '%Y-%m-%d %H:%i:00') AS minute_window,
    AVG(humidity) AS avg_humidity,
    AVG(temperature) AS avg_temperature,
    AVG(rainfall) AS avg_rainfall
FROM your_sensor_data_table
WHERE time <= (SELECT MAX(time) FROM your_sensor_data_table)
GROUP BY DATE_FORMAT(time, '%Y-%m-%d %H:%i:00')
ORDER BY minute_window DESC;

3. For PostgreSQL

SELECT
    date_trunc('minute', time) AS minute_window,
    AVG(humidity) AS avg_humidity,
    AVG(temperature) AS avg_temperature,
    AVG(rainfall) AS avg_rainfall
FROM your_sensor_data_table
WHERE time <= (SELECT MAX(time) FROM your_sensor_data_table)
GROUP BY date_trunc('minute', time)
ORDER BY minute_window DESC;

If You Need Per-Device Averages (Per Minute) #

If you want separate averages for each of your two Arduino devices (instead of a combined average across both), just add the device ID to the SELECT and GROUP BY clauses:

Example for SQL Server:

SELECT
    device_id, -- Make sure your table has a device identifier column
    DATEADD(minute, DATEDIFF(minute, 0, time), 0) AS minute_window,
    AVG(humidity) AS avg_humidity,
    AVG(temperature) AS avg_temperature,
    AVG(rainfall) AS avg_rainfall
FROM your_sensor_data_table
WHERE time <= (SELECT MAX(time) FROM your_sensor_data_table)
GROUP BY device_id, DATEADD(minute, DATEDIFF(minute, 0, time), 0)
ORDER BY minute_window DESC, device_id;

Quick Notes #

• Replace your_sensor_data_table with the actual name of your SQL table storing the Arduino data
• The WHERE clause filters to all data up to your most recent input timestamp—adjust this if you want to target a specific date range (e.g., time BETWEEN '2024-05-01 00:00:00' AND '2024-05-01 23:59:59')

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