Нарастающие итоги в Oracle SQL

В Oracle 12 вы можете использовать MATCH_RECOGNIZE для сопоставления шаблонов построчно:

SELECT cp,
       rok,
       dopis_c,
       CASE dopis_c WHEN 'C' THEN posilani END AS posilani
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY cp
         ORDER BY rok
         MEASURES
           COUNT(dopis_c) AS posilani
         ALL ROWS PER MATCH
         PATTERN ( C+ | NOT_C )
         DEFINE C AS dopis_c = 'C'
       );

Или в более ранних версиях вы можете использовать несколько аналитических функций:

SELECT cp,
       rok,
       dopis_c,
       rn,
       CASE dopis_c
       WHEN 'C'
       THEN rn - COALESCE(
              LAST_VALUE(CASE WHEN dopis_c IS NULL THEN rn END) IGNORE NULLS
                OVER (PARTITION BY cp ORDER BY rok),
              0
            )
       END AS posilani
FROM (
  SELECT cp,
         rok,
         dopis_c,
         ROW_NUMBER() OVER (PARTITION BY cp ORDER BY rok) AS rn
  FROM   table_name
);

или:

SELECT cp,
       rok,
       dopis_c,
       CASE dopis_c
       WHEN 'C'
       THEN ROW_NUMBER() OVER (PARTITION BY cp, dopis_c, c_grp ORDER BY rok)
       END AS posilani
FROM (
  SELECT cp,
         rok,
         dopis_c,
         COUNT(1) OVER (PARTITION BY cp ORDER BY rok)
           - COUNT(dopis_c) OVER (PARTITION BY cp ORDER BY rok) AS c_grp
  FROM   table_name
)
ORDER BY
       cp,
       rok

Что для примера данных:

CREATE TABLE table_name (CP, ROK, DOPIS_C) AS
SELECT 6059150790, 2014, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2015, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2016, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2017, NULL FROM DUAL UNION ALL
SELECT 6059150790, 2018, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2019, NULL FROM DUAL UNION ALL
SELECT 6059150790, 2020, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2021, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2022, 'C'  FROM DUAL UNION ALL
SELECT 6059150790, 2023, NULL FROM DUAL UNION ALL
SELECT 6059150790, 2024, NULL FROM DUAL;

Все выходы:

рабочий пример

Вы также можете сделать это без MATCH_RECOGNIZE, но с помощью подзапроса:

select cp, rok_dopis, c, 
    nvl2(c,row_number() over(partition by cp,  c || n order by rok_dopis),null) as rn_c1
from (
    select d.*, 
        sum(case when c is null then 1 else 0 end) over(partition by cp order by rok_dopis) as n    
    from table_name d 
)
order by rok_dopis
;