1

Topic: Grouping of repeating lines going successively

Good afternoon!
Prompt how to group going successively repeating lines and to count the total and an amount of lines on them.
I.e. there is a result of request:
ID, VAL, SUMMA
10,1,10
15,1,10
20,22,100
24,22,100
28,22,100
33,1,10
44,22,10
52,333,100
53,333,100
57,333,100
59,333,100
It is necessary to group going successively repeating lines on VAL with sorting on ID
And to deduce min (ID), max (ID), sum (SUMMA), count (*)
I.e. to receive such table:
VAL, min (ID), max (ID), sum (SUMMA), count (*)
1,10,15,20,2
22,20,28,300,3
1,33,33,10,1
22,44,44,10,1
333,52,59,400,4
min (ID) and max (ID) to find it turned out:

select t.val, t.min_id, t.max_id
from (
select t. *;
t.id min_id;
case when row_last = 1 then id else lead (t.id) over (order by t.id) end max_id
from (
select t. *;
decode (t.val, lag (t.val) over (order by t.id), 0, 1) row_first;
decode (t.val, lead (t.val) over (order by t.id), 0, 1) row_last
from (
select 10 id, 1 val, 10 summa from dual union all
select 15 id, 1 val, 10 summa from dual union all
select 20 id, 22 val, 100 summa from dual union all
select 24 id, 22 val, 100 summa from dual union all
select 28 id, 22 val, 100 summa from dual union all
select 33 id, 1 val, 10 summa from dual union all
select 44 id, 22 val, 10 summa from dual union all
select 52 id, 333 val, 100 summa from dual union all
select 53 id, 333 val, 100 summa from dual union all
select 57 id, 333 val, 100 summa from dual union all
select 59 id, 333 val, 100 summa from dual
)t
)t
where not (row_first = 0 and row_last = 0)
)t
where t.row_first = 1

And how to receive the total and an amount?

2

Re: Grouping of repeating lines going successively

The total and kol-in can be found by means of nested select:

with tab as (
select 10 id, 1 val, 10 summa from dual union all
select 15 id, 1 val, 10 summa from dual union all
select 20 id, 22 val, 100 summa from dual union all
select 24 id, 22 val, 100 summa from dual union all
select 28 id, 22 val, 100 summa from dual union all
select 33 id, 1 val, 10 summa from dual union all
select 44 id, 22 val, 10 summa from dual union all
select 52 id, 333 val, 100 summa from dual union all
select 53 id, 333 val, 100 summa from dual union all
select 57 id, 333 val, 100 summa from dual union all
select 59 id, 333 val, 100 summa from dual
)
select t.val, t.min_id, t.max_id;
(select sum (summa) from tab t1 where t1.val = t.val and t1.id between t.min_id and t.max_id) sum_summa;
(select count (*) from tab t1 where t1.val = t.val and t1.id between t.min_id and t.max_id) count_row
from (
select t. *;
t.id min_id;
case when row_last = 1 then id else lead (t.id) over (order by t.id) end max_id
from (
select t. *;
decode (t.val, lag (t.val) over (order by t.id), 0, 1) row_first;
decode (t.val, lead (t.val) over (order by t.id), 0, 1) row_last
from tab t
)t
where not (row_first = 0 and row_last = 0)
)t
where t.row_first = 1

But such decision does not approach, since is grouped big at lines, and with nested select works very long.
The decision without them is necessary.

3

Re: Grouping of repeating lines going successively

STFF start_of_group

4

Re: Grouping of repeating lines going successively

Elic, Thanks

with tab as (
select 10 id, 1 val, 10 summa from dual union all
select 15 id, 1 val, 10 summa from dual union all
select 20 id, 22 val, 100 summa from dual union all
select 24 id, 22 val, 100 summa from dual union all
select 28 id, 22 val, 100 summa from dual union all
select 33 id, 1 val, 10 summa from dual union all
select 44 id, 22 val, 10 summa from dual union all
select 52 id, 333 val, 100 summa from dual union all
select 53 id, 333 val, 100 summa from dual union all
select 57 id, 333 val, 100 summa from dual union all
select 59 id, 333 val, 100 summa from dual
)
select val;
min (id) min_id;
max (id) max_id;
sum (summa) sum_summa;
count (*) count_row
from (
select t. *;
sum (start_of_group) over (order by t.id) gr
from (
select t. *;
decode (t.val, lag (t.val) over (order by t.id), 0, 1) start_of_group
from tab t
)t
)
group by val, gr
order by gr

5

Re: Grouping of repeating lines going successively

Elic, I join thanks.

6

Re: Grouping of repeating lines going successively

Guest_group;
Something you strongly in a roundabout way went in the decision of the simple problem.
It was possible here so:

with tab as (
select 10 id, 1 val, 10 summa from dual union all
select 15 id, 1 val, 10 summa from dual union all
select 20 id, 22 val, 100 summa from dual union all
select 24 id, 22 val, 100 summa from dual union all
select 28 id, 22 val, 100 summa from dual union all
select 33 id, 1 val, 10 summa from dual union all
select 44 id, 22 val, 10 summa from dual union all
select 52 id, 333 val, 100 summa from dual union all
select 53 id, 333 val, 100 summa from dual union all
select 57 id, 333 val, 100 summa from dual union all
select 59 id, 333 val, 100 summa from dual
)
select val, min (id) as min_id, max (id) as max_id, sum (summa) as sum_summs, count (1) as count_row
from (
select tab.*
, row_number () over (partition by val order by id) -
row_number () over (order by id) as grp_id
from tab
) v
group by grp_id, val
order by min (id);

7

Re: Grouping of repeating lines going successively

Kind  - Eh, thanks, but in your example are one nuance;
At you one grp_id can be identical for different val;
In my example it unique for all groups

with tab as (
select 10 id, 1 val, 10 summa from dual union all
select 15 id, 1 val, 10 summa from dual union all
select 20 id, 22 val, 100 summa from dual union all
select 24 id, 22 val, 100 summa from dual union all
select 28 id, 22 val, 100 summa from dual union all
select 33 id, 1 val, 10 summa from dual union all
select 44 id, 22 val, 10 summa from dual union all
select 52 id, 333 val, 100 summa from dual union all
select 53 id, 333 val, 100 summa from dual union all
select 57 id, 333 val, 100 summa from dual union all
select 59 id, 333 val, 100 summa from dual
)
select t.id, t.val;
grp_id_val;
sum (start_of_group) over (order by t.id) grp_id_global
from (
select t. *;
decode (t.val, lag (t.val) over (order by t.id), 0, 1) start_of_group;
row_number () over (order by id) - row_number () over (partition by val order by id) grp_id_val
from tab t
)t

ID, VAL, GRP_ID_VAL, GRP_ID_GLOBAL
10,1,0,1
15,1,0,1
20,22,2,2
24,22,2,2
28,22,2,2
33, 1 , 3 , 3
44, 22 , 3 , 4
52,333,7,5
53,333,7,5
57,333,7,5
59,333,7,5
In the given example it is not critical, but in some tasks it is necessary to pay attention to it.

8

Re: Grouping of repeating lines going successively

Guest_group;
In itself grp_id interest does not represent, it is necessary to look only together with VAL for GRP_ID is number of group in one VAL. For this reason GROUP BY goes on their steam.