如何将MSSQL中LAG函数计算的小时能耗写入原表Consumption列

步骤1：新增Consumption列 #

首先给原表添加用于存储能耗值的Consumption列，根据示例中Value的整数类型，选择对应的数据类型：

ALTER TABLE [DB_TEST].[dbo].[Meter1]
ADD Consumption INT NULL;

步骤2：更新现有数据的Consumption值 #

用CTE（公用表表达式）结合LAG函数计算能耗，再批量更新到原表：

WITH MeterConsumption AS (
    SELECT 
        [MeterID],
        [Timestamp],
        [Value],
        -- 按MeterID分组，确保计算的是同一个表计的上一条记录差值
        VALUE - LAG(VALUE) OVER (PARTITION BY MeterID ORDER BY TIMESTAMP) AS CalculatedConsumption
    FROM [DB_TEST].[dbo].[Meter1]
)
UPDATE m
SET m.Consumption = mc.CalculatedConsumption
FROM [DB_TEST].[dbo].[Meter1] m
JOIN MeterConsumption mc 
    ON m.MeterID = mc.MeterID 
    AND m.Timestamp = mc.Timestamp;

注：如果你的表中只有单个MeterID，PARTITION BY MeterID可以省略，但保留它能兼容多表计的场景。

步骤3：自动计算新插入数据的Consumption值 #

如果需要后续插入新记录时自动计算能耗，可创建触发器实现：

CREATE TRIGGER trg_Meter1_CalculateConsumption
ON [DB_TEST].[dbo].[Meter1]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    WITH NewRecordConsumption AS (
        SELECT 
            m.MeterID,
            m.Timestamp,
            m.Value - LAG(m.Value) OVER (PARTITION BY m.MeterID ORDER BY m.Timestamp) AS CalculatedConsumption
        FROM [DB_TEST].[dbo].[Meter1] m
        JOIN inserted i ON m.MeterID = i.MeterID AND m.Timestamp = i.Timestamp
    )
    UPDATE m
    SET m.Consumption = nrc.CalculatedConsumption
    FROM [DB_TEST].[dbo].[Meter1] m
    JOIN NewRecordConsumption nrc 
        ON m.MeterID = nrc.MeterID 
        AND m.Timestamp = nrc.Timestamp;
END

此后每次插入新的表计记录，触发器会自动计算该记录与同表计上一条记录的差值，写入Consumption列。

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