时间戳

首页 > 解决方案 > SQL在根据条件将一列拆分为2时将表自身连接起来

sql - SQL在根据条件将一列拆分为2时将表自身连接起来

问题描述

我有一个看起来像这样的表：

+-----------------------------+
|tag|state|time               |
+-----------------------------+
|AAA|GOOD |2020/03/19 16:00:00|
+-----------------------------+
|BBB|GOOD |2020/03/19 16:00:00|
+-----------------------------+
|BBB|BAD  |2020/03/19 15:00:00|
+-----------------------------+
|CCC|HLTHY|2020/03/19 14:00:00|
+-----------------------------+
|AAA|BAD  |2020/03/19 13:00:00|
+-----------------------------+
|CCC|UNHLT|2020/03/19 12:00:00|
+-----------------------------+
etc...

我需要进行一个查询，显示标签的状态何时变坏以及何时再次恢复正常，因此输出应如下所示：

+-------------------------------------------------+
|tag|state|startTime          |stopTime           |
+-------------------------------------------------+
|AAA|BAD  |2020/03/19 13:00:00|2020/03/19 16:00:00|
+-------------------------------------------------+
|BBB|BAD  |2020/03/19 15:00:00|2020/03/19 16:00:00|
+-------------------------------------------------+
|CCC|UNHLT|2020/03/19 12:00:00|2020/03/19 14:00:00|
+-------------------------------------------------+
etc...

我现在的查询如下所示：

SELECT A.tag, A.state, A.startTime, B.stopTime 
FROM
    (SELECT tag, state, time as startTime 
     FROM table
     WHERE state LIKE 'BAD' or state LIKE 'UNHLT')A
JOIN
    (SELECT tag, state, time as stopTime
     FROM table
     WHERE state LIKE 'GOOD' or state like 'HLTHY')B
ON (A.tag = B.tag and A.time < B.time)

但是，此查询显示无序结果，其中 startTimes 完全无序，并且大量 stopTimes 重复，如下所示：

+-------------------------------------------------+
|tag|state|startTime          |stopTime           |
+-------------------------------------------------+
|AAA|BAD  |2020/03/12 08:00:00|2020/03/19 16:00:00|
+-------------------------------------------------+
|AAA|BAD  |2020/03/12 09:00:00|2020/03/19 16:00:00|
+-------------------------------------------------+
|AAA|BAD  |2020/03/12 08:00:00|2020/03/18 14:00:00|
+-------------------------------------------------+
|BBB|BAD  |2020/03/12 11:00:00|2020/03/19 16:00:00|
+-------------------------------------------------+
|BBB|BAD  |2020/03/13 07:00:00|2020/03/19 16:00:00|
+-------------------------------------------------+
etc...

我需要对我的查询进行哪些更改，以使其输出变得像所需的那样？

标签： sqlsql-server

解决方案

我想你只是想要lag()：

select t.*
from (select t.*,
             lag(state) over (partition by tag order by time) as prev_state,
             min(case when state not in ('BAD', 'UNHLT') then time end) over (partition by tag order by time desc) as next_good_time
      from t
     ) t
where state in ('BAD', 'UNHLT') and
      prev_state not in ('BAD', 'UNHLT')

推荐阅读