SQL实现15分钟数据转日均值并关联日度生产数据表

要实现将15分钟间隔的设备读数按日计算均值，同时关联日度生产数据，核心是先对设备读数做按日分组聚合，再关联其他表。以下是调整后的SQL语句：

WITH DailyDeviceAvg AS (
    SELECT
        r.DeviceId,
        CAST(r.logtime AS DATE) AS ReadingDate,
        r.FieldName,
        AVG(r.datavalue) AS AvgDatavalue
    FROM X_DW.elx.DeviceReadings r
    WHERE
        r.logtime > GETDATE() - 5
        AND r.FieldName IN ('DischargePress_PSI', 'IntakePress_PSI','MotorTemp_F','Power_Amps','Frequency_HZ')
    GROUP BY
        r.DeviceId,
        CAST(r.logtime AS DATE),
        r.FieldName
)
SELECT
    w.XID,
    w.wellname,
    d.FieldName,
    d.ReadingDate AS logtime,
    d.AvgDatavalue AS datavalue,
    p.proddate,
    p.OilProd_BBL AS OIL,
    p.GasProd_MCF AS Gas,
    p.WaterProd_BBL AS Water
FROM DailyDeviceAvg d
INNER JOIN X_VIEWS.dbo.Wells w
    ON d.DeviceId = w.ScadaID
INNER JOIN [X_VIEWS].[dbo].[Production] p
    ON w.XID = p.XID
    AND d.ReadingDate = p.proddate
WHERE
    w.XID = 'QW081588';

关键逻辑说明 #

1. 设备读数聚合：通过CTE DailyDeviceAvg 将15分钟粒度的logtime转换为日粒度（CAST(logtime AS DATE)），按设备ID、日期、字段名分组，计算每个字段的日均值。
2. 表关联逻辑：关联Production表时，用聚合后的日期ReadingDate与proddate匹配，确保生产数据与设备均值数据的日粒度完全对齐。
3. 过滤条件拆分：时间范围、字段列表这类针对设备读数的过滤放在CTE中，油井ID的过滤放在最终查询的WHERE子句，逻辑更清晰。

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