Hello,
It would be very helpful if Oracle could support a new feature of RUNNING GROUP BY in SQL.
For example, the following records exist in a table.
status time point
----------------------
A 00:00 3
A 01:00 4
B 02:00 4
B 03:00 2
A 04:00 3
B 05:00 4
C 06:00 4
C 07:00 2
C 08:00 1
I'd like to use this query
select status,
min(time) as start_time,
max(time) as end_time,
sum(point) as total_points,
count(point) as total_row_cnt
from some_table
RUNNING GROUP BY status
ALONG time, status
order by 2;
to return this data set:
status start_time end_time total_points total_row_cnt
----------------------------------------------------------
A 00:00 01:00 7 2
B 02:00 03:00 6 2
A 04:00 04:00 3 1
B 05:00 05:00 4 1
C 06:00 08:00 7 3
Basically what this query does is:
sort the records by the columns listed in ALONG clause,
put consecutive rows with same values in columns listed in RUNNING GROUP BY clause into a group,
apply aggregation functions listed in SELECT clause to each group and produce one record per group,
sort the final records according to ORDER BY clause.
Thank you,
Dave