根据其他列值计算视图中的列

This situation seems like it should be a simple task but I can't come up with the solution. Imagine eight columns, four of which are bit columns (on/off) and the other four are reading values.

这种情况似乎应该是一项简单的任务,但我无法提出解决方案。想象一下八列,其中四列是位列(开/关),另外四列是读取值。

I want to create a view from this table, with another column for the range of the four reading values, based on those where their corresponding sensor alarm <> 1..

我想从这个表创建一个视图,另外一列是四个读数值的范围,基于它们对应的传感器报警<> 1的那些。

For examples purposes, we can use the following values.

出于示例目的,我们可以使用以下值。

Column1 - 12.44
Column1Alarm - 0

Column2 - 99.43
Column2Alarm - 0

Column3 - 4.43
Column3Alarm - 1

Column4 - 43.33
Column4Alarm - 0

Column1 - 12.44 Column1Alarm - 0 Column2 - 99.43 Column2Alarm - 0 Column3 - 4.43 Column3Alarm - 1 Column4 - 43.33 Column4Alarm - 0

For this example, the only values included in the range should be those coming from Column1, 2, and 4.

对于此示例,范围中包含的唯一值应该是来自Column1,2和4的值。

Thanks, Tom

2 个解决方案

#1

Editted as per @HLGM comments to make it a bit more robust.

根据@HLGM评论编辑,使其更加健壮。

Note that in it's current form, I assume that when

请注意,在它的当前形式中,我假设当

• all alarms equal 1, the range should be NULL

所有报警均等于1,范围应为NULL

• only one alarm equals 0, the range is the value of this alarm.

只有一个警报等于0,范围是此警报的值。

If this does not suffice, OP might clarify what should be returned instead.

如果这还不够,OP可能会澄清应该返回的内容。

SQL Statement

    ;WITH Alarm (C1, C1Alarm, C2, C2Alarm, C3, C3Alarm, C4, C4Alarm) AS (
        SELECT  12.44, 0, 99.43, 0, 4.43, 1, 43.33, 0
        UNION ALL SELECT 12.44, 1, 99.43, 0, 4.43, 1, 43.33, 0
        UNION ALL SELECT 1, 0, 2, 1, 3, 1, 4, 1
        UNION ALL SELECT 1, 1, 2, 1, 3, 1, 4, 1
    )
    , AddRowNumbers AS (
        SELECT  rowNumber = ROW_NUMBER() OVER (ORDER BY C1)
                , C1, C1Alarm
                , C2, C2Alarm
                , C3, C3Alarm
                , C4, C4Alarm
        FROM    Alarm   
    )
    , UnPivotColumns AS (
        SELECT  rowNumber, value = C1 FROM AddRowNumbers WHERE C1Alarm = 0
        UNION ALL SELECT rowNumber, C2 FROM AddRowNumbers WHERE C2Alarm = 0
        UNION ALL SELECT rowNumber, C3 FROM AddRowNumbers WHERE C3Alarm = 0
        UNION ALL SELECT rowNumber, C4 FROM AddRowNumbers WHERE C4Alarm = 0
    )
    SELECT  C1, C1Alarm
            , C2, C2Alarm
            , C3, C3Alarm
            , C4, C4Alarm
            , COALESCE(range1.range, range2.range)
    FROM    AddRowNumbers rowNumber
            LEFT OUTER JOIN (SELECT rowNumber, range = MAX(value) - MIN(value) FROM UnPivotColumns GROUP BY rowNumber HAVING COUNT(*) > 1) range1 ON range1.rowNumber = rowNumber.rowNumber
            LEFT OUTER JOIN (SELECT rowNumber, range = AVG(value) FROM UnPivotColumns GROUP BY rowNumber HAVING COUNT(*) = 1) range2 ON range2.rowNumber = rowNumber.rowNumber  

Test script

;WITH Alarm (C1, C1Alarm, C2, C2Alarm, C3, C3Alarm, C4, C4Alarm) AS (
    SELECT  12.44, 0, 99.43, 0, 4.43, 1, 43.33, 0
    UNION ALL SELECT 12.44, 1, 99.43, 0, 4.43, 1, 43.33, 0
    UNION ALL SELECT 1, 0, 2, 1, 3, 1, 4, 1
    UNION ALL SELECT 1, 1, 2, 1, 3, 1, 4, 1
)
, AddRowNumbers AS (
    SELECT  rowNumber = ROW_NUMBER() OVER (ORDER BY C1)
            , C1, C1Alarm
            , C2, C2Alarm
            , C3, C3Alarm
            , C4, C4Alarm
    FROM    Alarm   
)
, UnPivotColumns AS (
    SELECT  rowNumber, value = C1 FROM AddRowNumbers WHERE C1Alarm = 0
    UNION ALL SELECT rowNumber, C2 FROM AddRowNumbers WHERE C2Alarm = 0
    UNION ALL SELECT rowNumber, C3 FROM AddRowNumbers WHERE C3Alarm = 0
    UNION ALL SELECT rowNumber, C4 FROM AddRowNumbers WHERE C4Alarm = 0
)
SELECT  C1, C1Alarm
        , C2, C2Alarm
        , C3, C3Alarm
        , C4, C4Alarm
        , COALESCE(range1.range, range2.range)
FROM    AddRowNumbers rowNumber
        LEFT OUTER JOIN (SELECT rowNumber, range = MAX(value) - MIN(value) FROM UnPivotColumns GROUP BY rowNumber HAVING COUNT(*) > 1) range1 ON range1.rowNumber = rowNumber.rowNumber
        LEFT OUTER JOIN (SELECT rowNumber, range = AVG(value) FROM UnPivotColumns GROUP BY rowNumber HAVING COUNT(*) = 1) range2 ON range2.rowNumber = rowNumber.rowNumber  

#2

The OP's comment "Even if I had 200 columns..." leads me to believe that similar functionality will be needed in multiple places. So I would create a function that accepts a "Reading" column and an "Alarm" bit, and returns NULL when Alarm is set. This allows one to take advantage of the way NULL values are treated by MIN and MAX.

OP的评论“即使我有200列......”也让我相信在多个地方都需要类似的功能。所以我会创建一个接受“读取”列和“报警”位的函数,并在设置警报时返回NULL。这允许人们利用MIN和MAX处理NULL值的方式。

CREATE FUNCTION UnalarmedReading
(
    @Value float,
    @Alarm bit
)
RETURNS float
AS
BEGIN
return case when @Alarm=1 then null else @Value end
END

Here is some test data:

这是一些测试数据:

create table Readings (
    KeyColumn int not null,
    Column1 float,
    Column1Alarm bit,
    Column2 float,
    Column2Alarm bit,
    Column3 float,
    Column3Alarm bit,
    Column4 float,
    Column4Alarm bit,
)

insert into Readings(
    KeyColumn,
    Column1,Column1Alarm,
    Column2,Column2Alarm,
    Column3,Column3Alarm,
    Column4,Column4Alarm
) values (
    1,
    12.44, 0,
    99.43, 0,
    4.43, 1,
    43.33, 0
)

insert into Readings(
    KeyColumn,
    Column1,Column1Alarm,
    Column2,Column2Alarm,
    Column3,Column3Alarm,
    Column4,Column4Alarm
) values (
    2,
    124.4, 0,
    994.3, 0,
    44.3, 1,
    433.3, 0
)

And to use MIN and MAX you unpivot:

要使用MIN和MAX,您可以忽略:

;with NonAlarmReadings as (
    select KeyColumn, dbo.UnalarmedReading(Column1, Column1Alarm) as C1,
        dbo.UnalarmedReading(Column2, Column2Alarm) as C2,
        dbo.UnalarmedReading(Column3, Column3Alarm) as C3,
        dbo.UnalarmedReading(Column4, Column4Alarm) as C4
    from Readings
),
Normalized as (
    select *
    from NonAlarmReadings
    unpivot (Reading for BaseColumn in (C1, C2, C3, C4)) as upvt
)
select KeyColumn, min(Reading) as MinReading, max(Reading) as MaxReading,
    abs(min(Reading) - max(Reading)) as ReadingRange
from Normalized
group by KeyColumn