Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Request a new feature of SQL: RUNNING GROUP BY

User_EM2R6Apr 8 2021

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

Comments

Post Details

Added on Apr 8 2021
0 comments
149 views