如何查询近24小时文档并按小时分组，计算PM10相关指标？

Got it, let's break this down—you're halfway there since you already have the hourly average sorted. To add the 24-hour max and overall average alongside each hourly group, we can use window functions (the cleanest approach) or a subquery join. Here's how to do both:

Using Window Functions (Recommended) #

Window functions let you calculate aggregate values across the entire filtered dataset while still keeping your hourly groups intact. Let's assume your table is named sensor_readings with columns createdate (timestamp) and pm10 (numeric value):

SELECT
  -- Group by hour (adjust date function based on your database)
  DATE_TRUNC('hour', createdate) AS hour_window,
  -- Hourly average PM10 (you already have this part)
  AVG(pm10) AS hourly_avg_pm10,
  -- 24-hour maximum PM10 across all readings in the time range
  MAX(pm10) OVER () AS daily_max_pm10,
  -- 24-hour overall average PM10 across all readings
  AVG(pm10) OVER () AS daily_avg_pm10
FROM sensor_readings
-- Filter for the last 24 hours
WHERE createdate >= NOW() - INTERVAL '24 hours'
GROUP BY hour_window
ORDER BY hour_window;

Notes on Date Functions: #

• PostgreSQL: Use DATE_TRUNC('hour', createdate)
• MySQL: Use DATE_FORMAT(createdate, '%Y-%m-%d %H:00:00')
• SQL Server: Use DATEADD(hour, DATEDIFF(hour, 0, createdate), 0)

The OVER () clause without any partitioning means the MAX and AVG are calculated over the entire set of rows filtered by the WHERE clause (the last 24 hours), so each hourly row will show the same daily max and average.

Alternative: Subquery Join #

If window functions aren't available in your database, you can calculate the daily aggregates in a subquery and join it to your hourly results:

WITH daily_aggregates AS (
  SELECT
    MAX(pm10) AS daily_max_pm10,
    AVG(pm10) AS daily_avg_pm10
  FROM sensor_readings
  WHERE createdate >= NOW() - INTERVAL '24 hours'
)
SELECT
  DATE_TRUNC('hour', sr.createdate) AS hour_window,
  AVG(sr.pm10) AS hourly_avg_pm10,
  da.daily_max_pm10,
  da.daily_avg_pm10
FROM sensor_readings sr
CROSS JOIN daily_aggregates da
WHERE sr.createdate >= NOW() - INTERVAL '24 hours'
GROUP BY hour_window, da.daily_max_pm10, da.daily_avg_pm10
ORDER BY hour_window;

This works by first computing the daily max and average in a CTE, then joining that single row to every hourly group result.

Either approach will give you exactly what you need: hourly averages paired with the overall 24-hour max and average for the same time period.

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