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;

所有输出:

fiddle