PostgreSQL按每日4个6小时时段计算平均温度的视图创建需求

没问题，这个需求在PostgreSQL里用日期函数和分组就能轻松搞定。我先假设你的表结构大概是这样（你可以根据自己的实际表名、字段名调整）：

-- 示例表结构（替换成你的实际表）
CREATE TABLE temperature_readings (
    reading_time TIMESTAMP NOT NULL,
    temp NUMERIC NOT NULL
);

方案1：仅统计有数据的时段 #

如果只需要输出存在温度记录的6小时时段，直接分组计算即可。核心逻辑是把每个时间戳映射到所属的6小时时段起始时间，再按这个起始时间分组求平均：

CREATE OR REPLACE VIEW hourly_temp_stats AS
SELECT
    -- 保留一位小数，和你给出的示例格式对齐
    ROUND(AVG(temp), 1) AS avg_temp,
    -- 计算当前记录所属的6小时时段起始时间
    date_trunc('day', reading_time) 
    + INTERVAL '1 hour' * (FLOOR(DATE_PART('hour', reading_time) / 6) * 6) AS time
FROM temperature_readings
GROUP BY time
-- 按时间顺序输出结果，和示例一致
ORDER BY time;

代码细节解释： #

• date_trunc('day', reading_time)：把任意时间戳截断到当天的00:00:00
• FLOOR(DATE_PART('hour', reading_time) / 6) * 6：计算当前小时所属的6小时块起始小时（结果只能是0、6、12、18）
• 将起始小时转换成时间间隔，加到当天0点上，就得到了该时段的起始时间
• ROUND(AVG(temp),1)：对平均温度取一位小数，和示例的输出格式匹配

方案2：强制输出每日四个时段（含无数据时段） #

如果某天某个6小时时段没有数据，你也希望显示该时段（平均温度显示为NULL），可以用generate_series生成所有需要的时段，再和原表左连接：

CREATE OR REPLACE VIEW hourly_temp_stats AS
WITH date_ranges AS (
    -- 生成从最早记录的当天0点，到最晚记录的当天18点的所有6小时时段
    SELECT
        generate_series(
            (SELECT date_trunc('day', MIN(reading_time)) FROM temperature_readings),
            (SELECT date_trunc('day', MAX(reading_time)) + INTERVAL '18 hours' FROM temperature_readings),
            INTERVAL '6 hours'
        ) AS time
)
SELECT
    ROUND(AVG(tr.temp), 1) AS avg_temp,
    dr.time
FROM date_ranges dr
-- 左连接原表，匹配属于当前时段的温度记录
LEFT JOIN temperature_readings tr
    ON tr.reading_time >= dr.time 
    AND tr.reading_time < dr.time + INTERVAL '6 hours'
GROUP BY dr.time
ORDER BY dr.time;

代码细节解释： #

• date_ranges CTE：生成覆盖你数据全时间范围的所有6小时时段序列
• 左连接确保即使某个时段没有数据，也会出现在结果中，此时avg_temp会显示为NULL

你可以根据实际业务需求选择其中一种方案。

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