Time value value
The 273 00:00:01 2017-01-01
The 272 00:01:01 2017-01-01
The 271 00:02:01 2017-01-01
The 272 00:03:01 2017-01-01
The 271 00:04:01 2017-01-01
The 265 00:05:01 2017-01-01
The 272 00:06:01 2017-01-01
The 276 00:07:01 2017-01-01
The 272 00:08:01 2017-01-01
The 278 00:09:01 2017-01-01
The 279 00:10:01 2017-01-01
The 268 00:11:01 2017-01-01
The 272 00:12:01 2017-01-01
The 271 00:13:01 2017-01-01
The 268 00:14:01 2017-01-01
The 271 00:15:01 2017-01-01
The 268 00:16:01 2017-01-01
And the value is greater than 270, the duration is greater than or equal to 4 seconds, all the alarm time, such as the above example should return two records
The start time to end time time
The 2017-01-01 00:00:01 2017-01-01 00:04:01 4 seconds
The 2017-01-01 00:06:01 2017-01-01 00:10:01 4 seconds
Because the database access problem, cannot use stored procedures, cannot use the temporary table, simple SQL is complete, please everyone a great god
CodePudding user response:
Such as the great god by warmCodePudding user response:
Typo, sorry, it is 4 pointsCodePudding user response:
with t as (only equal to 4, greater than 4 well also didn't know
The select to_date (' 00:00:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 273 from dual union all
The select to_date (' 00:01:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 272 from dual union all
The select to_date (' 00:02:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 271 from dual union all
The select to_date (' 00:03:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 272 from dual union all
The select to_date (' 00:04:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 271 from dual union all
The select to_date (' 00:05:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 265 from dual union all
The select to_date (' 00:06:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 272 from dual union all
The select to_date (' 00:07:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 276 from dual union all
The select to_date (' 00:08:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 272 from dual union all
The select to_date (' 00:09:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 278 from dual union all
The select to_date (' 00:10:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 279 from dual union all
The select to_date (' 00:11:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 268 from dual union all
The select to_date (' 00:12:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 272 from dual union all
The select to_date (' 00:13:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 271 from dual union all
The select to_date (' 00:14:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 268 from dual union all
The select to_date (' 00:15:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 271 from dual union all
The select to_date (' 00:16:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 268 from dual
)
Select times, time2 (time2 - times) * 24 * 60 from
(select times, lead times, 4) over (order by times) time2, value,
Row_number () over (order by times) rn
The from t
Where the value & gt; 270)
The where (time2 - times) * 24 * 60=4
;
CodePudding user response:
with t as (
The select to_date (' 00:00:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 273 from dual union all
The select to_date (' 00:01:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 272 from dual union all
The select to_date (' 00:02:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 271 from dual union all
The select to_date (' 00:03:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 272 from dual union all
The select to_date (' 00:04:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 271 from dual union all
The select to_date (' 00:05:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 265 from dual union all
The select to_date (' 00:06:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 272 from dual union all
The select to_date (' 00:07:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 276 from dual union all
The select to_date (' 00:08:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 272 from dual union all
The select to_date (' 00:09:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 278 from dual union all
The select to_date (' 00:10:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 279 from dual union all
The select to_date (' 00:11:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 268 from dual union all
The select to_date (' 00:12:01 2017-01-01 ', '- dd yyyy - mm hh24: mi: ss') times, the value of 272 from dual union all
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull