sql-server - 计算每组的差异总和

Question

我有下表:

Sensor | building | Date_time  | Current_value
1      |   1      | 20.08.2017 |   20
1      |   1      | 21.08.2017 |   25
1      |   1      | 22.08.2017 |   35
2      |   1      | 20.08.2017 |  120
2      |   1      | 21.08.2017 |  200
2      |   1      | 22.08.2017 |  210
3      |   2      | 20.08.2017 |   20
3      |   2      | 21.08.2017 |   25
3      |   2      | 22.08.2017 |   85
5      |   2      | 20.08.2017 |  320
5      |   2      | 21.08.2017 |  400
5      |   2      | 22.08.2017 |  410

假定传感器 ID 是唯一的，建筑物 ID 也是如此。

我需要通过从每个传感器的最大值中减去最小值来计算任何给定时间范围内每个建筑物的总值，然后按每个建筑物对总和进行分组。

在上面的例子中是

Sensor 1: (35 - 20)=15
Sensor 2: (210-120)=90
Building 1 = 15+90 = 105
(...)
Building 2 = 65+90 = 155

非常感谢任何指向正确方向的指示!

Answer 1

您问的是如何计算每个传感器的最小值和最大值之间的差异，然后聚合每个建筑物的差异。

with diffs as (
    SELECT Building,Sensor, MAX(Current_Value)-MIN(Current_Value) as diff
    FROM SomeTable
    GROUP BY Building, Sensor 
)
SELECT Building,sum(diff)
FROM diffs
GROUP BY Building

如果你想限制时间段，你必须在 CTE 中这样做:

with diffs as (
    SELECT Building,Sensor, MAX(Current_Value)-MIN(Current_Value) as diff
    FROM SomeTable
    WHERE Date_Time between @start and @end
    GROUP BY Building, Sensor 
)
SELECT Building,sum(diff)
FROM diffs
GROUP BY Building

您可以将此查询转换为可用于其他查询的用户定义函数:

create function fn_TotalDiffs(@start datetime2(0), @end datetime2(0))
returns table 
as 
Return (
    with diffs as (
        select Building,Sensor, MAX(Current_Value)-MIN(Current_Value) as diff
        from SomeTable
        Group by Building, Sensor 
    )
    select Building,sum(diff) as Total
    from diffs
    Group by Building
)