RaqForum 28 No.
397 View  • 
     ...

Order-based Calculations: Calculations over Subgroups

 
SQL(179)

【Question】

I have a table that records start time and end time of alarm calls happened in multiple locations. I need to count the alarm calls and sum the durations of alarm calls for each location (TotalDuration = ETime – Stime). For each location, if the interval between the start times of two alarm calls is greater than 10 minutes, I need to output the location the second time to the result set; if there are multiple alarm calls within 10 minutes, count the number and calculate the total duration (TotalDuration is the sum of all alarm durations).

Is there any fast way to do this?

 

ID   LocationID   STime   ETime 

1    1    2015/6/8 0:01:00    2015/6/8 0:02:00 

2    1    2015/6/8 0:13:00    2015/6/8 0:14:00 

3    1    2015/6/8 0:14:00    2015/6/8 0:15:00 

4    1    2015/6/8 0:15:00    2015/6/8 0:16:00 

5    2    2015/6/8 0:02:00    2015/6/8 0:03:00 

6    2    2015/6/8 0:06:00    2015/6/8 0:07:00 

7    2    2015/6/8 0:37:00    2015/6/8 0:38:00 

 

Expected output:

 

Location  ID  AlarmCount 

1     1    1 

1     3    3 

2     2    2 

2     1    1

 

【Answer】

It’s roundabout to implement an order-based calculation in SQL. If your running environment is JAVA+SQL, I think it’s much simpler and clearer to phrase the computation in SPL (Structured Process Language):

A

B

1

$select LocationID,STime,ETime   from tb order by LocationID,STime

2

=create(LocationID,Duration,AlarmCount)

3

=A1.group(LocationID)

4

for A3

=A4.STime

5

=A4.group(interval@s(B4,STime)\600)

6

=A2.insert(0:B5,~.LocationID,~.sum(interval@s(STime,ETime))\60,~.len())

A1: Retrieve data from the source table and sort it by location and start time with a SQL-like query.

A2: Create the target empty result set.

A3: Group A1’s records by locations. The SPL’s group() function can retain members of each subgroup for further processing.

A4-B6: Group records in each group again. B4 gets the first start time from each subgroup. B5 groups the current group according to the condition that if the current start time is 10 minutes later than the first start time. B6 populates the grouping and sum results to A2’s table sequence.


SQL(179)
 
 •  397 View  •