I need an ID per "group"

Question

I need a grouping over the timeline. That means, everything should be in the same group (C, I, "A"). It is a new group, if the value of "A" has been changed.

I already tried many Oracle thinks, like analytical functions and grouping; but I did not get my required result.

No code available

Raw data:

C   I   A   FROM_DATE       TO_DATE
1   1   4   01.08.19 00:00  05.08.19 00:00
1   1   4   06.08.19 00:00  10.08.19 00:00
1   1   6   11.08.19 00:00  17.08.19 00:00
1   1   5   18.08.19 00:00  21.08.19 00:00
1   1   5   22.08.19 00:00  28.08.19 00:00
1   1   5   29.08.19 00:00  05.09.19 00:00
1   1   4   06.09.19 00:00  31.12.99 00:00

Expected (intermediate):

C   I   A   FROM_DATE       TO_DATE         GROUP_ID
1   1   4   01.08.19 00:00  05.08.19 00:00  1
1   1   4   06.08.19 00:00  10.08.19 00:00  1
1   1   6   11.08.19 00:00  17.08.19 00:00  2
1   1   5   18.08.19 00:00  21.08.19 00:00  3
1   1   5   22.08.19 00:00  28.08.19 00:00  3
1   1   5   29.08.19 00:00  05.09.19 00:00  3
1   1   4   06.09.19 00:00  31.12.99 00:00  4

Expected (final):

C   I   A   FROM_DATE       TO_DATE
1   1   4   01.08.19 00:00  10.08.19 00:00
1   1   6   11.08.19 00:00  17.08.19 00:00
1   1   5   18.08.19 00:00  05.09.19 00:00
1   1   4   06.09.19 00:00  31.12.99 00:00

Answer 1

You could use Tabibitosan:

select t.*,
  row_number() over (order by from_date)
    - row_number() over (partition by c, i, a order by from_date) as grp
from your_table t;

         C          I          A FROM_DATE  TO_DATE           GRP
---------- ---------- ---------- ---------- ---------- ----------
         1          1          4 2019-08-01 2019-08-05          0
         1          1          4 2019-08-06 2019-08-10          0
         1          1          6 2019-08-11 2019-08-17          2
         1          1          5 2019-08-18 2019-08-21          3
         1          1          5 2019-08-22 2019-08-28          3
         1          1          5 2019-08-29 2019-09-05          3
         1          1          4 2019-09-06 1999-12-31          4

The group number isn't exactly what you had, but as it's an intermediate result that isn't important.

Then use that for aggregation:

select c, i, a, min(from_date) as from_date, max(to_date) as to_date
from (
  select t.*,
    row_number() over (order by from_date)
      - row_number() over (partition by c, i, a order by from_date) as grp
  from your_table t
)
group by c, i, a, grp
order by c, i, from_date;

         C          I          A FROM_DATE  TO_DATE   
---------- ---------- ---------- ---------- ----------
         1          1          4 2019-08-01 2019-08-10
         1          1          6 2019-08-11 2019-08-17
         1          1          5 2019-08-18 2019-09-05
         1          1          4 2019-09-06 1999-12-31

db<>fiddle