SQL选择使日期时间范围在标志切换之间的项目

Question

假设我有一张这样的 table :

CREATE TABLE TESTTABLE (
  ID Integer NOT NULL,
  ATMOMENT Timestamp NOT NULL,
  ISALARM Integer NOT NULL,
  CONSTRAINT PK_TESTTABLE PRIMARY KEY (ID)
);

它具有 ISALARM 标志，可以在 ATMOMENT 的随机时刻在 0 和 1 之间切换，就像在这个示例数据集中一样:

INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('1', '01.01.2016, 00:00:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('2', '01.01.2016, 00:01:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('3', '01.01.2016, 00:02:00.000', '0');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('4', '01.01.2016, 00:02:00.000', '0');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('10', '02.01.2016, 00:00:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('11', '02.01.2016, 00:00:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('12', '02.01.2016, 00:01:00.000', '0');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('20', '03.01.2016, 00:00:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('21', '03.01.2016, 00:01:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('22', '03.01.2016, 00:02:00.000', '0');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('23', '03.01.2016, 00:02:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('30', '04.01.2016, 00:00:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('31', '04.01.2016, 00:00:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('32', '04.01.2016, 00:00:00.000', '0');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('33', '04.01.2016, 00:00:00.000', '0');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('40', '05.01.2016, 00:00:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('41', '05.01.2016, 00:00:00.000', '1');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('42', '05.01.2016, 00:00:00.000', '0');
INSERT INTO TESTTABLE (ID, ATMOMENT, ISALARM) VALUES ('43', '05.01.2016, 00:00:00.000', '0');

我需要选择所有警报范围，即在范围开始时将 ISALARM 设置为 1 的 ATMOMENT 范围(上一个范围关闭后的第一次)，并在范围结束时重置回 0。为了清楚起见，第一次重置就足以关闭这样的范围；还要说同时设置和重置的 ISALARM 被视为范围结束(虽然可能是开始)。
上面的示例数据集预计会产生如下内容:

 ALARMBEGIN                |  LASTALARMBEGIN            |  ALARMEND
-------------------------- | -------------------------- | --------
'01.01.2016, 00:00:00.000' | '01.01.2016, 00:01:00.000' | '01.01.2016, 00:02:00.000'
'02.01.2016, 00:00:00.000' | '02.01.2016, 00:00:00.000' | '02.01.2016, 00:01:00.000'
'03.01.2016, 00:00:00.000' | '03.01.2016, 00:02:00.000' | '03.01.2016, 00:02:00.000'
'04.01.2016, 00:00:00.000' | '04.01.2016, 00:00:00.000' | '04.01.2016, 00:00:00.000'
'05.01.2016, 00:00:00.000' | '05.01.2016, 00:00:00.000' | '05.01.2016, 00:00:00.000'

即使 TESTTABLE 的数据集相对较小，只有约 2500 条记录(用 Firebird2.5 和 Postgresql 测试过；我不擅长数据库优化; "CREATE INDEX IDX_TESTTABLE1 ON TESTTABLE (ATMOMENT,ISALARM)"有帮助但不是很多)。

这对我来说很奇怪，因为对所有 TESTTABLE 记录(按 ATMOMENT 排序)进行简单的线性迭代，同时将 ISALARM 字段与以前的记录之一进行比较，可以更快地获得我想要的范围。

是否有任何优雅的解决方案可以让 SQL 以更快、更清洁的方式选择它？

SELECT DISTINCT a1.ATMOMENT AS ALARMBEGIN, a2.ATMOMENT AS LASTALARMBEGIN, a3.ATMOMENT AS ALARMEND
FROM TESTTABLE a1
JOIN TESTTABLE a2 ON 
    (a1.ATMOMENT<a2.ATMOMENT
        AND NOT EXISTS(SELECT * FROM TESTTABLE x WHERE 
            x.ISALARM=0 AND a1.ATMOMENT<=x.ATMOMENT AND x.ATMOMENT<a2.ATMOMENT))
    OR (a1.ATMOMENT=a2.ATMOMENT)
JOIN TESTTABLE a3 ON 
    (a2.ATMOMENT<a3.ATMOMENT
        AND NOT EXISTS(SELECT * FROM TESTTABLE x WHERE 
            (x.ISALARM=0 AND a2.ATMOMENT<=x.ATMOMENT AND x.ATMOMENT<a3.ATMOMENT)
            OR (x.ISALARM=1 AND a2.ATMOMENT<x.ATMOMENT AND x.ATMOMENT<=a3.ATMOMENT)))
    OR (a2.ATMOMENT=a3.ATMOMENT)
WHERE a1.ISALARM<>0 AND a2.ISALARM<>0 AND a3.ISALARM=0
    AND (NOT EXISTS(SELECT * FROM TESTTABLE x1 WHERE
            x1.ATMOMENT<a1.ATMOMENT)
        OR EXISTS(SELECT * FROM TESTTABLE x1 WHERE
            x1.ISALARM=0
            AND x1.ATMOMENT<a1.ATMOMENT
            AND NOT EXISTS(SELECT * FROM TESTTABLE x2 WHERE
                x1.ATMOMENT<x2.ATMOMENT AND x2.ATMOMENT<a1.ATMOMENT)))
ORDER BY a1.ATMOMENT

谢谢你。

更新 1

感谢 Gordon Linoff 和 Jayvee 的解决方案(对 Firebird3.0 和 PostgreSQL 来说非常好)，我决定依靠 Firebird2.5 的排序效率，并设计了“select”，它比我以前的更难看，但运行速度明显更快.对于那些需要使用 Firebird2.5 完成的人:

WITH 
GROUPEDTABLE_TT (ATMOMENT, NOTISALARMRESET, ISALARMSET)
AS(
SELECT a.ATMOMENT, MIN(a.ISALARM), MAX(a.ISALARM)
FROM TESTTABLE a
GROUP BY a.ATMOMENT),

INTERVALBEGIN_TT 
AS(
SELECT a1.ATMOMENT
FROM GROUPEDTABLE_TT a1
WHERE 
    a1.ISALARMSET<>0
    AND (NOT EXISTS (SELECT * FROM GROUPEDTABLE_TT x WHERE
            x.ATMOMENT<a1.ATMOMENT)
        OR (SELECT FIRST 1 x.NOTISALARMRESET FROM GROUPEDTABLE_TT x WHERE
            x.ATMOMENT<a1.ATMOMENT
            ORDER BY x.ATMOMENT DESC)=0)),

INTERVALLAST_TT 
AS(
SELECT a2.ATMOMENT FROM GROUPEDTABLE_TT a2
WHERE a2.ISALARMSET=1
    AND (a2.NOTISALARMRESET=0
        OR (a2.NOTISALARMRESET=1 
            AND (SELECT FIRST 1 x.NOTISALARMRESET FROM GROUPEDTABLE_TT x WHERE
                x.ATMOMENT>a2.ATMOMENT
                ORDER BY x.ATMOMENT ASC)=0
            AND (SELECT FIRST 1 x.ISALARMSET FROM GROUPEDTABLE_TT x WHERE
                x.ATMOMENT>a2.ATMOMENT
                ORDER BY x.ATMOMENT ASC)=0))),

INTERVALEND_TT 
AS(
SELECT a1.ATMOMENT
FROM GROUPEDTABLE_TT a1
WHERE 
    a1.NOTISALARMRESET=0
    AND (a1.ISALARMSET=1 
        OR (a1.ISALARMSET=0 
            AND (SELECT FIRST 1 x.ISALARMSET FROM GROUPEDTABLE_TT x WHERE
                x.ATMOMENT<a1.ATMOMENT
                ORDER BY x.ATMOMENT DESC)=1
            AND (SELECT FIRST 1 x.NOTISALARMRESET FROM GROUPEDTABLE_TT x WHERE
                x.ATMOMENT<a1.ATMOMENT
                ORDER BY x.ATMOMENT DESC)=1))),

ENCLOSEDINTERVALS_TT (BEGINMOMENT, LASTBEGINMOMENT, ENDMOMENT)
AS(
SELECT ib.ATMOMENT, 
    (SELECT FIRST 1 il.ATMOMENT FROM INTERVALLAST_TT il WHERE 
        ib.ATMOMENT<=il.ATMOMENT ORDER BY il.ATMOMENT ASC),
    (SELECT FIRST 1 ie.ATMOMENT FROM INTERVALEND_TT ie WHERE 
        ib.ATMOMENT<=ie.ATMOMENT ORDER BY ie.ATMOMENT ASC)
FROM INTERVALBEGIN_TT ib)

SELECT * FROM ENCLOSEDINTERVALS_TT
ORDER BY BEGINMOMENT

更新 2
...但我的选择似乎显示出取数的二次增长(或至少比线性快)取决于总记录数； FB2.5最好使用单程线性迭代程序。或者将 FB30 与以下解决方案一起使用...

Answer 1

这已经在PostgreSQL中进行了测试，想法是分别为开始、最后开始和结束创建3个有序的公共(public)表，然后将3个表连接起来。

可以通过仅创建一个 CTE 并使用 case 语句标记行然后使用 selfjoin 来使用更少的代码来完成，您可以稍后再执行此操作，但通过这种方式，代码更加自我解释并且应该也相当有效。

;
with beginnings
as
(
    select atmoment, row_number() over(order by atmoment) rn from
    (
        select *, lag(atmoment,1) over(order by atmoment,isalarm desc) prevtime, 
          lag(isalarm,1) over(order by atmoment,isalarm desc) prevstatus
        from testtable 
    ) t
    where coalesce(prevstatus,0)=0 and isalarm=1
),
ends 
as
(
    select atmoment, row_number() over(order by atmoment) rn from
    (
        select *, lead(atmoment,1) over(order by atmoment,isalarm) nexttime, 
          lead(isalarm,1) over(order by atmoment,isalarm) nextstatus
        from testtable 
    ) t
    where coalesce(nextstatus,1)=1 and isalarm=0
),
lastbeginnings
as
(
    select atmoment, row_number() over(order by atmoment) rn from
    (
        select *, lead(atmoment,1) over(order by atmoment,isalarm desc) nexttime, 
          lead(isalarm,1) over(order by atmoment,isalarm desc) nextstatus
        from testtable 
    ) t
    where coalesce(nextstatus,0)=0 and isalarm=1
)

select b.atmoment ALARMBEGIN, lb.atmoment LASTALARMBEGIN, e.atmoment  ALARMEND 
from beginnings b
join lastbeginnings lb on lb.rn=b.rn
join ends e on e.rn=b.rn

结果:

> 2016-01-01 00:00:00 | 2016-01-01 00:01:00 | 2016-01-01 00:02:00
> 2016-01-02 00:00:00 | 2016-01-02 00:00:00 | 2016-01-02 00:01:00
> 2016-01-03 00:00:00 | 2016-01-03 00:02:00 | 2016-01-03 00:02:00
> 2016-01-04 00:00:00 | 2016-01-04 00:00:00 | 2016-01-04 00:00:00
> 2016-01-05 00:00:00 | 2016-01-05 00:00:00 | 2016-01-05 00:00:00