基于5分钟时间间隔关联两表并计算平均值（使用CTE实现）

嘿，这问题我熟！咱们可以用两个CTE配合5分钟时间分组的方式来搞定这个近似时间戳匹配的需求，我给你捋清楚怎么做👇

核心思路很简单：把两个表的时间戳统一对齐到最近的5分钟节点，以此作为分组键计算每个时间段的平均温度，最后通过这个统一的时间分组键+冷冻柜ID来关联两个表的结果。

具体SQL实现 #

下面是基于你提供的示例数据写的查询，用两个CTE分别处理两张表，逻辑清晰还方便维护：

WITH FreezerTemp1 AS (
    SELECT
        Freezer,
        -- 把时间戳向下取整到最近的5分钟间隔
        DATE_TRUNC('minute', Timestamp) 
        - INTERVAL 'EXTRACT(SECOND FROM Timestamp) second' 
        - INTERVAL '(EXTRACT(MINUTE FROM Timestamp) % 5) minute' AS FiveMinuteInterval,
        AVG(Temperature_1) AS AvgTemp1
    FROM Table1
    GROUP BY Freezer, FiveMinuteInterval
),
FreezerTemp2 AS (
    SELECT
        Freezer,
        -- 用完全相同的逻辑处理表2的时间戳，保证分组键一致
        DATE_TRUNC('minute', Timestamp) 
        - INTERVAL 'EXTRACT(SECOND FROM Timestamp) second' 
        - INTERVAL '(EXTRACT(MINUTE FROM Timestamp) % 5) minute' AS FiveMinuteInterval,
        AVG(Temperature_2) AS AvgTemp2
    FROM Table2
    GROUP BY Freezer, FiveMinuteInterval
)
-- 关联两个CTE，匹配同一冷冻柜、同一5分钟时间段的数据
SELECT
    ft1.Freezer,
    ft1.FiveMinuteInterval,
    ft1.AvgTemp1,
    ft2.AvgTemp2
FROM FreezerTemp1 ft1
JOIN FreezerTemp2 ft2
    ON ft1.Freezer = ft2.Freezer
    AND ft1.FiveMinuteInterval = ft2.FiveMinuteInterval
ORDER BY ft1.Freezer, ft1.FiveMinuteInterval;

关键逻辑说明 #

• 时间对齐逻辑：先把时间戳截断到分钟，再减去秒数和“分钟数除以5的余数”，这样所有时间都会被对齐到XX:00、XX:05、XX:10这类5分钟节点上，保证两个表的时间分组完全匹配。
• CTE的作用：每个CTE单独负责一张表的时间分组和平均温度计算，把复杂逻辑拆成简单模块，后续改需求也更容易调整。
• 关联规则：通过冷冻柜ID和统一的5分钟时间间隔关联，确保同一时间段同一设备的温度数据能对应上。

示例数据测试 #

假设表2的示例数据是：

1 2018-04-25 09:46:00 9；1 2018-04-25 09:51:00 10；1 2018-04-25 09:56:00 12

跑上面的SQL后会得到这样的结果：

完美实现近似时间戳的匹配关联啦！

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