Oracle SQL - group only by nearby same records-CodePudding

I need to sum delay time in seconds for records that are same by value column. Problem is I need them only grouped by the same chunks and not all together. For example, for below data I would need sum of 3 records for value 3 and separately for 2 records further down, and not to sum records for value 4 as they are not together. Is there a way to do this?

ID     Value   Timestamp       Delay(s)
166549627   4   19-OCT-21 11:00:19  11.4
166549450   8   19-OCT-21 11:00:27  7.5
166549446   3   19-OCT-21 11:00:34  7.1
166549625   3   19-OCT-21 11:00:45  10.9
166549631   3   19-OCT-21 11:00:58  13.3
166550549   3   19-OCT-21 11:01:03  4.5
166549618   7   19-OCT-21 11:01:14  8.8
166549627   4   19-OCT-21 11:01:23  11.4
166550549   3   19-OCT-21 11:01:45  4.5
166550549   3   19-OCT-21 11:01:59  4.5

CodePudding user response：

If you want to use PL/SQL you can loop over all records orderd by timestamp.

Just remember the last value und sum up if it is the same as the current value. If not, just save the sum somewehre else and continue.

You can also write this as a pipelined function to use a query to access the data.

declare
    l_sum      number := 0;
    l_last_val number;
begin
    for rec in (select * from your_table order by timestamp) loop
        if l_last_val = rec.value then
            l_sum := l_sum   rec.delay;
            continue;
        elsif l_last_val is not null then
            dbms_output.put_line('value: ' || l_last_val || ' sum: ' || l_sum); -- save last_val and sum
        end if;
            l_last_val := rec.val;
            l_sum      := rec.delay;
    end loop;
    dbms_output.put_line('value: ' || l_last_val || ' sum: ' || l_sum); -- save last_val and sum
end;

CodePudding user response：

You don't even need to use plsql for that purpose. Only SQL can suffice. Below solution uses the recursive common table expression (CTE) technic to create sub groups according to value column and timestamp column.

with ranked_rows (ID, VALUE, TIMESTAMP, DELAY, RNB) as (
select ID, VALUE, TIMESTAMP, DELAY, row_number()over(order by TIMESTAMP) rnb
from YourTable
)
, cte (ID, VALUE, TIMESTAMP, DELAY, RNB, grp) as (
select ID, VALUE, TIMESTAMP, DELAY, RNB, 1 grp
from ranked_rows
where rnb = 1
union all
select t.ID, t.VALUE, t.TIMESTAMP, t.DELAY, t.RNB, case when c.VALUE = t.VALUE then c.grp else c.grp   1 end
from ranked_rows t
join cte c on c.rnb   1 = t.rnb
)
select VALUE, sum(DELAY) sum_consecutive_DELAY, min(TIMESTAMP) min_TIMESTAMP, max(TIMESTAMP) max_TIMESTAMP, count(*)nb_rows
from cte
group by VALUE, GRP
order by min_TIMESTAMP
;

demo