从SQL Server按2分钟间隔提取水位传感器数据的SQL查询需求

Hey Julia, let's work through this SQL query for your water level sensor data together!

First, let's recap your requirements to make sure I'm on the same page:

• Your Sensor table saves 3 measurements every single minute
• You need to pull data in 2-minute intervals
• Over a 10-minute window, this should result in 5 groups totaling 15 rows

I'll cover two practical scenarios based on how you might define "2-minute intervals":

Scenario 1: Extract data from every other minute (15 rows total) #

If you want exactly 3 rows per 2-minute interval (e.g., grab all measurements from even minutes like 00, 02, 04... or odd minutes), this query will do the trick. It filters for minutes divisible by 2, giving you 5 minutes over 10 minutes with 3 rows each:

DECLARE @LastTime DATETIME = DATEADD(minute, -10, GETDATE()); -- Adjust to your target end time

SELECT 
    MeasurementTime,
    WaterLevel -- Swap this with your actual measurement column name
FROM Sensor
WHERE 
    MeasurementTime >= @LastTime
    AND DATEPART(minute, MeasurementTime) % 2 = 0 -- Use %2=1 if you want odd minutes instead
ORDER BY MeasurementTime;

Scenario 2: Group data into 2-minute windows (all data per window) #

If you instead want to group every measurement from each 2-minute block (like 00:00-00:01, 00:02-00:03, etc.), this query organizes data by the start of each 2-minute interval. Note: This would give you 6 rows per window (3 per minute) for a total of 30 rows over 10 minutes, but it's a common way to interpret "2-minute intervals":

DECLARE @LastTime DATETIME = DATEADD(minute, -10, GETDATE());

SELECT 
    DATEADD(minute, DATEDIFF(minute, 0, s.MeasurementTime) / 2 * 2, 0) AS IntervalStart,
    s.MeasurementTime,
    s.WaterLevel -- Replace with your actual column name
FROM Sensor s
WHERE s.MeasurementTime >= @LastTime
ORDER BY IntervalStart, s.MeasurementTime;

Quick Tips: #

• Swap WaterLevel with your actual sensor measurement column name
• Tweak @LastTime to match your desired time range (e.g., use a fixed time like '2024-05-20 10:00:00' instead of relative to now)
• If your time column includes seconds/milliseconds, the grouping logic still works—DATEDIFF(minute, 0, MeasurementTime) truncates to the nearest minute before calculating the interval

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