求助：在SQL Server中按设备计算电能统计指标的SQL语句

原始数据 #

统计规则 #

按DeviceId分组执行以下计算：

• kWh：分组内最后一条记录的kWh值减去第一条记录的kWh值
• kW：分组内kW列的总和
• A：分组内A列的平均值（取整）

期望结果 #

SQL Server实现语句 #

以下两种写法均可满足需求，可根据实际场景选择：

方法1：ROW_NUMBER()子查询方式 #

SELECT
    DeviceId,
    (MAX(CASE WHEN rn_desc = 1 THEN kWh END) - MAX(CASE WHEN rn_asc = 1 THEN kWh END)) AS kWh,
    SUM(kW) AS kW,
    CAST(AVG(A * 1.0) AS INT) AS A
FROM (
    SELECT
        DeviceId,
        kWh,
        kW,
        A,
        ROW_NUMBER() OVER(PARTITION BY DeviceId ORDER BY Time) AS rn_asc,
        ROW_NUMBER() OVER(PARTITION BY DeviceId ORDER BY Time DESC) AS rn_desc
    FROM YourTableName -- 替换为你的实际表名
) t
GROUP BY DeviceId
ORDER BY DeviceId;

方法2：FIRST_VALUE/LAST_VALUE窗口函数方式 #

SELECT DISTINCT
    DeviceId,
    (LAST_VALUE(kWh) OVER(PARTITION BY DeviceId ORDER BY Time 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
     - FIRST_VALUE(kWh) OVER(PARTITION BY DeviceId ORDER BY Time 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS kWh,
    SUM(kW) OVER(PARTITION BY DeviceId) AS kW,
    CAST(AVG(A * 1.0) OVER(PARTITION BY DeviceId) AS INT) AS A
FROM YourTableName -- 替换为你的实际表名
ORDER BY DeviceId;

注意事项 #

• 需将语句中的YourTableName替换为存储原始数据的真实表名
• CAST(AVG(A * 1.0) AS INT)用于将平均值取整，与示例结果保持一致
• 两种方法均基于Time字段排序确定分组内的首尾记录，确保计算逻辑准确

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