"Database" "algorithm" gets the time interval that matches a certain condition from a string of data

Algorithmic background

In a project, it is often a requirement to see that a set of data that is arranged according to the time required to calculate a string of data that satisfies a certain condition, and to find the start and end time of this string of data.

For example, using millet bracelet to collect a group of user's heart rate data, asked to calculate how long the user's heart rate >100.

For example, by GPS positioning to obtain the vehicle's driving data, according to the required speed of more than 80 even if speeding , then in this set of data, how long the vehicle is speeding .

In the era of data collection is very convenient, like the above scenes and requirements abound.

To figure out the required results, of course, you can write a calculator, one for the analysis of the past, but the efficiency is very slow.

More often than not, the results are required to be calculated directly from SQL in the database.

So I think this algorithm is very valuable.

Assuming a specific scenario

A specific point, such as the following string of data:

Segment the above data according to the following conditions,

Speed >= 0 and Speed < 30

Speed >= 30 and Speed < 60

Speed >= 60 and Speed < 80

Speed >= 80

And calculate the start time and end time for each piece of data, and start the SOC with the end Soc.

Ideas

1. First fetch the original data

; With temp as (SELECT row_number () over (ORDER by Car,time ASC) as ID,  TIME,CAR,SOC,SPEEDFLG = case when Speed  &  gt;= 0 and Speed <-then 1-when speed >=-and Speed < 2-then-speed  >= < 3 when speed  >= 4  endfrom origdata WHERE Car = ' abcdefk10nz000001 ' and Time >= ' 2 016-06-01 ' and Time <= GETDATE ())

First take the whole string of data above.

2. Sort the data, calculate the start time of each segment, and filter out the data in the middle of the segment, preserving the beginning and ending data of the segment

Final as (select Row_number () over (ORDER by A.car) as Tid,*from (select a.car,a.speedflg,a.soc,begintime = case when a.id = 1 OR B.SPEEDFLG is null and a.time end,endtime = case when C.SPEEDFLG was null then a.time endfrom  temp aleft JOIN te MP B on a.id = b.ID +1 and B.SPEEDFLG = a.speedflgleft JOIN temp c on a.id = C.id-1  

According to the above algorithm, the following data can be obtained

3. Merge the start-end data for each segment to get a complete data

Select A.CAR,SPEEDFLG = Case A.SPEEDFLG If 1 then ' 0-30 ' while 2 Then ' 30-60 ' when 3 Then ' 60-80 ' when 4 Then ' 80 ' end,a.be Gintime,b.endtime,a.soc as BEGINSOC, B.soc as Endsocfrom final Ainner JOIN final B on a.car = B.car and A.SPEEDFLG = B.spe EDFLG and A.tid = b.tid-1

Finally get the following string of data

Click to view the full code:

; With temp as (SELECT row_number () over (ORDER by Car,time ASC) as ID, TIME,CAR,SOC,SPEEDFLG = case when Speed >= 0 an   D Speed < 1 if speed >=, and speed < 2, when speed >=, speed < the 3 when Speed >= 4 endfrom origdata WHERE Car = ' abcdefk10nz000001 ' and Time >= ' 2016-06-01 ' and time <= GETDATE ()), Final as (select Row_number () over (ORDER by A.car) as Tid,*from (select a.car,a.speedflg,a.soc,begintime = case when a. ID = 1 OR b.speedflg is null and a.time end,endtime = case when C.SPEEDFLG was null then A.time endfrom temp aleft JOIN t  EMP B on a.id = b.ID +1 and B.SPEEDFLG = a.speedflgleft JOIN temp c on a.id = c.id-1 and C.SPEEDFLG = A.SPEEDFLG) awhere BeginTime is isn't nullor endtime is isn't null) Select A.CAR,SPEEDFLG = Case A.SPEEDFLG If 1 then ' 0-30 ' when 2 Then ' 30-60 ' When 3 and ' 60-80 ' when 4 Then ' 80 above ' End,a.begintime,b.endtime,a.soc as Beginsoc, B.soc as endsocfrom final Ainner JOIN F Inal b on a.car = B.car and A.SPEEDFLG = B.SPEEDFLG and A.tid = b.tid-1 

　　

"Database" "algorithm" gets the time interval that matches a certain condition from a string of data