isAlarm

isAlarm

关注
发信
关注(28)粉丝(399)

SQL选择项，使日期时间在标志之间切换

说我有一张这样的 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/

10-11 11:54
Powered by LMLPHP ©2024 isAlarm 0.045357