说我有一张这样的 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'
我自己的解决方案(如下)看起来很丑陋，运行速度惊人(约1分钟)，即使TESTTABLE的数据集相对较小，只有约2500条记录(使用Firebird2.5和Postgresql进行了测试；我对DB优化也不满意) ;“在TESTTABLE上创建索引IDX_TESTTABLE1(ATMOMENT，ISALARM)”很有帮助，但效果不是很大。
对我来说，这很奇怪，因为在将ISALARM字段与以前的记录之一进行比较时，对所有TESTTABLE记录(按ATMOMENT排序)进行简单的线性迭代可以使我想要的范围更快。
是否有任何优雅的解决方案可以使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与以下解决方案一起使用...
最佳答案
这已经在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
关于SQL选择项，使日期时间在标志之间切换，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40084052/