...

Order-based Calculations – Group by Comparing Neighboring Values & Find Cumulated Time

 
SQL(179)

【Question】

obj_id     obj_date      value
1   2015-07-30 15:00:00.000  1
1   2015-07-30 15:02:00.000  1
1   2015-07-30 15:04:00.000  1
1   2015-07-30 15:06:00.000  0
1   2015-07-30 15:08:00.000  0
1   2015-07-30 15:10:00.000  1
1   2015-07-30 15:20:00.000  0
2   2015-07-30 15:02:00.000  1
Accumulate time periods from the first value 1 to the first value 0, and then begin the next round… If value 0 doesn’t appear, compare the current time with the system time. Suppose the system time is 15:30.
The expected result:
id   STime   CumulatedTime
1   2015-07-30 15:00:00.000  6
1   2015-07-30 15:10:00.000  10
2   2015-07-30 15:02:00.000  28

 

【Answer】

Group records by the change of value and get the first records of every two groups to find the start time and the cumulated time. SQL code of handling this order-based calculation is hard to understand. It’s easy to get it done in SPL (Structured Process Language) if the data volume is small:

A

1

$select * from tb order by  obj_id,obj_date

2

=A1.group(obj_id).(~.group@o1(value)|[null])

3

=A2.news(~.len()\2;A2.~.obj_id:id,(s=A2.~(#*2-1).obj_date):STime,   interval@s(s,ifn(A2.~(#*2).obj_date,now()))/60:CumulatedTime)

A1: Retrieve data by obj_id and obj_date in SQL.

A2: Group data again by obj_id and then group records in each group by the change of value and get the first records of each subgroups; append null to every first-level group for convenient computation.

A3: Get obj_date in the odd-numbered record from each group as the start time and that in the even-numbered record as the end time to calculate the cumulated time. If the value in even-numbered record is null, then get the system time as the end time.

About calling an SPL script from another application, see How to Call an SPL Script in Java.

 


SQL(179)
 
 •  363 View  •