Forum Stats

  • 3,825,204 Users
  • 2,260,482 Discussions
  • 7,896,443 Comments

Discussions

Request a new feature of SQL: RUNNING GROUP BY

User_EM2R6
User_EM2R6 Member Posts: 2 Green Ribbon

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:

  1. sort the records by the columns listed in ALONG clause,
  2. put consecutive rows with same values in columns listed in RUNNING GROUP BY clause into a group,
  3. apply aggregation functions listed in SELECT clause to each group and produce one record per group,
  4. sort the final records according to ORDER BY clause.

Thank you,

Dave

User_EM2R6Niels Hecker
2 votes

Active · Last Updated