All geek questions in one place

SQL selects elements that make the datetime range between flag switching

Let's say I have a table like this:

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

It has an ISALARM flag that switches between 0 and 1 at random ATMOMENT moments, as in this example: dataset:

 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');

I need to select all alarm ranges, i.e. ATMOMENT ranges, where ISALARM is set to 1 (the first time after closing the previous range) at the beginning of the range and reset back to 0 at the end of the range. Say, for clarity, reset first is enough to close that range; let's also say that the simultaneous dialing of ISALARM and reset are considered as the end of the range (possibly as the beginning). An example dataset is supposed to look something like this:

  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'

My own solution for this (below) looks pretty ugly and works amazingly slow (about 1 minute), even if TESTTABLE has a relatively small data set with only 2500 records (tested it with Firebird2.5 and Postgresql; with DB optimization: "CREATE INDEX IDX_TESTTABLE1 ON TESTTABLE (ATMOMENT, ISALARM) "helps, but not much).

This is rather strange for me, because a simple linear iteration in all TESTTABLE records (sorted by ATMOMENT) when comparing the ISALARM field with one of the previous records gives me ranges that I want much faster.

Is there an elegant solution to make SQL faster and cleaner?

 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

Thanks.

Update 1

Thanks to the solutions of Gordon Linoff and Jayvee (which are very good with Firebird3.0 and PostgreSQL), I decided to rely on the efficiency of ordering Firebird2.5 and inflated the “choice”, which is even uglier than the previous one, but works much faster. For those who need it, with 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

Update 2 ... but my samples seem to show quadratic growth (or at least faster than linear) of the sample number, depending on the total record number; it is better to use a single pass linear iteration procedure for FB2.5. Or use the FB30 with the solutions below ...

+5
source share
2 answers

This has been tested in PostgreSQL, the idea is to create 3 ordered common tables for beginnings, last beginnings and ends respectively, and then join 3 tables.

This can be done with less code by creating only one CTE and putting lines with the case argument, and then self-awareness, which you can do later, but in this way the code is more understandable and should be quite efficient.

 ; 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

result:

 > 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
+4
source

I think you can do it in Firebird 3.0 using row_number() :

 select alarm, min(atmoment), max(atmoment) from (select t.*, row_number() over (order by atmoment) as seqnum, row_number() over (partition by alarm order by atmoment) as seqnum_a from testtable t ) t group by alarm, (seqnum - seqnum_a);

It's a little hard to explain how this works. But if you run the subquery, you will see how the difference identifies the groups of interest.

+2
source

Source: https://habr.com/ru/post/1258325/

More articles:


All Articles