Dificuldade em utilizar funções analíticas

[Eduardo Salles]

Olá Comunidade! Estou com dificuldades para identificar, partir de um select, uma sequencia inválida de meses, em uma série contratual.
Vamos para o exemplo:
Imaginemos a tabela da seguinte forma:
cto serie vcto
21 1 01/05/2020
21 1 01/06/2020
21 1 01/08/2020
21 2 01/01/2020
21 2 01/02/2020
25 1 01/01/2020
25 1 01/02/2020
25 1 01/04/2020
25 1 01/06/2020
25 2 01/01/2019
25 2 01/02/2019
25 2 01/04/2019
27 1 01/04/2020
28 1 01/05/2020
28 1 01/06/2020
28 1 01/12/2020

Preciso identificar os contratos/séries com sequencial inválido de meses:

cto serie
21 1 (pois entre 01/06/2020 e 01/08/2020 está faltando meses!)
25 1 (pois entre 01/04/2020 e 01/06/2020 está faltando meses!)
25 2 (pois entre 01/02/2019 e 01/04/2019 está faltando meses!)
28 1 (pois entre 01/06/2020 e 01/12/2020 está faltando meses!)

Não sou muito bom em funções analíticas, creio que deveria utilizar "partition by" e "months_between", mas não tenho idéia de como fazer isso...
Alguém poderia me ajudar? Agradeço antecipadamente!

[dr_gori]

Bom, pra conseguir fazer testes com o seu caso, eu criei uma tabela e inseri os seus dados dentro:

Selecionar tudo

create table thomas
( cto number
, serie number
, vcto date
);

begin
insert into thomas values (21, 1, to_date('01/05/2020','dd/mm/yyyy'));
insert into thomas values (21, 1, to_date('01/06/2020','dd/mm/yyyy'));
insert into thomas values (21, 1, to_date('01/08/2020','dd/mm/yyyy'));
insert into thomas values (21, 2, to_date('01/01/2020','dd/mm/yyyy'));
insert into thomas values (21, 2, to_date('01/02/2020','dd/mm/yyyy'));
insert into thomas values (25, 1, to_date('01/01/2020','dd/mm/yyyy'));
insert into thomas values (25, 1, to_date('01/02/2020','dd/mm/yyyy'));
insert into thomas values (25, 1, to_date('01/04/2020','dd/mm/yyyy'));
insert into thomas values (25, 1, to_date('01/06/2020','dd/mm/yyyy'));
insert into thomas values (25, 2, to_date('01/01/2019','dd/mm/yyyy'));
insert into thomas values (25, 2, to_date('01/02/2019','dd/mm/yyyy'));
insert into thomas values (25, 2, to_date('01/04/2019','dd/mm/yyyy'));
insert into thomas values (27, 1, to_date('01/04/2020','dd/mm/yyyy'));
insert into thomas values (28, 1, to_date('01/05/2020','dd/mm/yyyy'));
insert into thomas values (28, 1, to_date('01/06/2020','dd/mm/yyyy'));
insert into thomas values (28, 1, to_date('01/12/2020','dd/mm/yyyy'));
commit;
end;

Com base nisso, a primeira coisa que eu fiz foi usar LAG, pra deixar o mês "anterior" na mesma linha. Veja:

Selecionar tudo

select cto, serie, vcto
  , lag(vcto, 1, null) over (partition by cto, serie order by cto, serie, vcto) proximo
  from thomas

Isso gera o seguinte:

Selecionar tudo

SQL> select cto, serie, vcto
  2    , lag(vcto, 1, null) over (partition by cto, serie order by cto, serie, vcto) proximo
  3    from thomas
  4  /

       CTO      SERIE VCTO        PROXIMO
---------- ---------- ----------- -----------
        21          1 01-May-20   
        21          1 01-Jun-20   01-May-20
        21          1 01-Aug-20   01-Jun-20
        21          2 01-Jan-20   
        21          2 01-Feb-20   01-Jan-20
        25          1 01-Jan-20   
        25          1 01-Feb-20   01-Jan-20
        25          1 01-Apr-20   01-Feb-20
        25          1 01-Jun-20   01-Apr-20
        25          2 01-Jan-19   
        25          2 01-Feb-19   01-Jan-19
        25          2 01-Apr-19   01-Feb-19
        27          1 01-Apr-20   
        28          1 01-May-20   
        28          1 01-Jun-20   01-May-20
        28          1 01-Dec-20   01-Jun-20

16 rows selected

Repare que o primeiro fica NULO pois não tem "anterior".
A partir daí, ele sempre mostra o anterior.

Com base nessa query, eu coloquei ela num SUB SELECT, e mandei ver um MONTHS_BETWEEN entre as datas:

Selecionar tudo

select a.*
, months_between(a.vcto, a.proximo) dif
from ( 
  select cto, serie, vcto
  , lag(vcto, 1, null) over (partition by cto, serie order by cto, serie, vcto) proximo
  from thomas ) a

Que gera isso:

Selecionar tudo

SQL> select a.*
  2  , months_between(a.vcto, a.proximo) dif
  3  from (
  4    select cto, serie, vcto
  5    , lag(vcto, 1, null) over (partition by cto, serie order by cto, serie, vcto) proximo
  6    from thomas ) a
  7  /

       CTO      SERIE VCTO        PROXIMO            DIF
---------- ---------- ----------- ----------- ----------
        21          1 01-May-20               
        21          1 01-Jun-20   01-May-20            1
        21          1 01-Aug-20   01-Jun-20            2
        21          2 01-Jan-20               
        21          2 01-Feb-20   01-Jan-20            1
        25          1 01-Jan-20               
        25          1 01-Feb-20   01-Jan-20            1
        25          1 01-Apr-20   01-Feb-20            2
        25          1 01-Jun-20   01-Apr-20            2
        25          2 01-Jan-19               
        25          2 01-Feb-19   01-Jan-19            1
        25          2 01-Apr-19   01-Feb-19            2
        27          1 01-Apr-20               
        28          1 01-May-20               
        28          1 01-Jun-20   01-May-20            1
        28          1 01-Dec-20   01-Jun-20            6

16 rows selected

Olhando pra essa tabela, percebe-se que todo o GAP entre meses tem a coluna DIF > 1.
(se for 1 ou nulo, ta beleza)
Agora é só filtrar esses meses.

Selecionar tudo

 SQL> select distinct cto, serie
  2  from (
  3    select cto, serie, vcto
  4    , lag(vcto, 1, null) over (partition by cto, serie order by cto, serie, vcto) proximo
  5    from thomas ) a
  6  where  months_between(a.vcto, a.proximo) > 1
  7  order by 1,2
  8  ;

       CTO      SERIE
---------- ----------
        21          1
        25          1
        25          2
        28          1

SQL> 

Veja, o retorno dessa query é exatamente os citados por você na sua segunda tabela

[Eduardo Salles]

Show! Não tinha me lembrado do lag... Consegui fazer a pesquisa utilizando o último exemplo.
Agradeço a todos!!!! Vocês são f@d@! Muito obrigado!