SQL条件带限制和先前值的位置

admin

SQL条件带限制和先前值的位置

sql

我有一个包含传感器数据的表格data1和用于传感器数据允许（警报）的限制data1high和data1low。我希望创建一个视图，其中仅列出那些data1值，它们是第一个移出限制（即警报条件）或移回“安全”限制内（不再存在警报条件）的值。

这是一个典型的表：

| id | data1 | data1high | data1low | 
|----|-------|-----------|----------|
|  1 |    60 |       200 |      100 |
|  2 |    80 |       200 |      100 |
|  3 |   123 |       200 |      100 |
|  4 |   150 |       200 |      100 |
|  5 |    60 |       200 |      100 |
|  6 |    60 |       200 |      100 | 
|  7 |   150 |       200 |      100 |
|  8 |    40 |       200 |      100 |
|  9 |    58 |       200 |      100 |
| 10 |    62 |       200 |      100 |
| 11 |   300 |       200 |      100 |

逻辑是其中的值处于where data1 < data1low OR data1 > data1high警报状态，因此应列出。

例如，

| id | data1 |
|----|-------|
|  1 |    60 |
|  2 |    80 |
|  5 |    60 |
|  6 |    60 |
|  8 |    40 |
|  9 |    58 |
| 10 |    62 |
| 11 |   300 |

上表显示了警报状态下的所有值。我不希望这样，仅那些刚刚转变为该状态的数据以及data1返回到安全范围内的第一个值的那些数据，所以我的理想观点是：

| id | data1 | data1high | data1low | 
|----|-------|-----------|----------|
|  1 |    60 |       200 |      100 |
|  3 |   123 |       200 |      100 |
|  5 |    60 |       200 |      100 | 
|  7 |   150 |       200 |      100 |
|  8 |    40 |       200 |      100 |
| 11 |   300 |       200 |      100 |

id 1处于警报状态，因此被列出； id 2被省略，因为它仍处于警报状态； id 3被列出是因为它是下一个返回限值的值； id
4被省略，因为它仍在限值内，id列出了5，因为那超出了限制等…

阅读 215

2021-06-07

共1个答案

admin

您可以使用递归CTE遍历各行，并将一行与上一行进行比较，并应用归类为过渡的逻辑。

不过，查看您所需的输出，我认为id = 10它不会出现在列表中，因为它尚未过渡。

您可以单独运行以下示例进行测试：

CREATE TABLE #Data1
    (
      [id] INT ,
      [data1] INT ,
      [data1high] INT ,
      [data1low] INT
    );

INSERT  INTO #Data1
        ( [id], [data1], [data1high], [data1low] )
VALUES  ( 1, 60, 200, 100 ),
        ( 2, 80, 200, 100 ),
        ( 3, 123, 200, 100 ),
        ( 4, 150, 200, 100 ),
        ( 5, 60, 200, 100 ),
        ( 6, 60, 200, 100 ),
        ( 7, 150, 200, 100 ),
        ( 8, 40, 200, 100 ),
        ( 9, 58, 200, 100 ),
        ( 10, 62, 200, 100 ),
        ( 11, 300, 200, 100 );
WITH    cte
          AS ( SELECT TOP 1
                        id ,
                        data1 ,
                        data1high ,
                        data1low ,
                        CASE WHEN data1 < data1low
                                  OR data1 > data1high THEN 1
                             ELSE 0
                        END AS Transitioned
               FROM     #Data1
               ORDER BY id
               UNION ALL
               SELECT   #Data1.id ,
                        #Data1.data1 ,
                        #Data1.data1high ,
                        #Data1.data1low ,
                        CASE WHEN cte.data1 < cte.data1low
                                  AND #Data1.data1 < #Data1.data1low THEN 0
                             WHEN cte.data1 > cte.data1high
                                  AND #Data1.data1 < #Data1.data1high THEN 0
                             WHEN cte.data1 BETWEEN cte.data1low AND cte.data1high
                                  AND #Data1.data1 BETWEEN #Data1.data1low
                                                   AND     #Data1.data1high
                             THEN 0
                             WHEN cte.Transitioned = 1
                                  AND #Data1.data1 BETWEEN #Data1.data1low
                                                   AND     #Data1.data1high
                             THEN 1
                             ELSE 1
                        END AS Transitioned
               FROM     #Data1
                        INNER JOIN cte ON cte.id + 1 = #Data1.id
             )
    SELECT  *
    FROM    cte
    WHERE   cte.Transitioned = 1

DROP TABLE #Data1

在CTE中，将添加一列以标记已转换的行。这些CASE WHEN子句包含我可以评估的逻辑，如果与上一行相比发生过渡，则需要评估这些逻辑。

在CTE的末尾，您只需选择其中的所有行Transitioned = 1即可产生：

id  data1   data1high   data1low    Transitioned
1   60      200         100         1
3   123     200         100         1
5   60      200         100         1
7   150     200         100         1
8   40      200         100         1
11  300     200         100         1

sqlfiddle

2021-06-07