Contagem de uma sequência em uma string

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Mensagemem Sex, 13 Dez 2013 10:38 am

Olá pessoal, preciso de uma ajuda.

Possuo uma string que tem vários caracteres e números, às vezes separados por colchetes ([ ]), por chaves ({ }) e por pipes (|) entre eles, e preciso fazer uma contagem dos valores que estão dentro deles. Em determinadas circunstâncias, preciso que sejam combinadas.

Ex.:
Código: Selecionar todos
[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524


Nesse exemplo, poderia contar uma só sequência ou duas.
- quantas vezes a sequência 4524 aparecem;
- quantas vezes as sequências 4524 e 5512 aparecem.

Já tentei utilizando LENGTH, mas são aproximadamente 20 sequências diferentes, e consulta ficaria enorme utilizando o LENGTH.

Para contar apenas uma sequência, utilizei a função REGEXP_COUNT da seguinte forma:
Código: Selecionar todos
SELECT REGEXP_COUNT('campo','\|4524\|') FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE('campo','[','|'),']','|'),'{','|'),'}','|') campo FROM 'table').


O problema está em contabilizar quando são duas sequências diferentes:
Tentei utilizar a função REGEXP_COUNT da seguinte forma:
Código: Selecionar todos
SELECT REGEXP_COUNT('campo','\|4524\||\|5512\|') FROM 'table'


Mas ele conta ou 4524 o 5512.

Como faço para que a função REGEXP_COUNT faça a contagem quando são duas sequências diferentes, ou se existir alguma outra função.

Agradeço desde já pela ajuda.
wkinoue

Mensagemem Sáb, 14 Dez 2013 5:02 pm

Olá wkinoue,

Bem vindo ao forum GLUFKE.

Para a solução do seu problema, vamos em partes:

Primeiro, teremos que remover da string tudo aquilo que não nos interessa, ou seja, todos os caracteres que não sejam "números" ou "pipe". Para isso, podemos usar a função "REGEXP_REPLACE" com o filtro "[^0-9\|]", onde só mantemos os digitos numericos e o pipe. Execute a querie abaixo:

Código: Selecionar todos
SQL> SELECT
'[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524' MINHA_STRING,
REGEXP_REPLACE('[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524', '[^0-9\|]','') MINHA_STRING_FILTRADA
FROM DUAL

SQL>

MINHA_STRING                                                  MINHA_STRING_FILTRADA
------------------------------------------------------------  ------------------------------------------
[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524  2331|1321|4524|5512|885|4242113234|5424|4524


Pronto ! Note que você tem uma linha somente com os valores numéricos, separados por pipe : "2331|1321|4524|5512|885|4242113234|5424|4524".

Agora, vamos tentar identificar quantos valores existem na string. Você disse que o separador oficial seria o "pipe". Então, vamos repetir o comando REGEXP_REPLACE para identificar quantos pipes existem na string. Neste caso, vou usar o filtro "[^|]" para só manter o pipe da string inicial:


Código: Selecionar todos
SQL> SELECT REGEXP_REPLACE('2331|1321|4524|5512|885|4242113234|5424|4524', '[^|]','') TOTAL
     FROM DUAL;

TOTAL
-------
|||||||


Mas fica muito chato ficar contando os "pauzinhos" resultandes da querie. Vamos melhorar a mesma com a ajuda dos comandos LENGTH e TRIM:

Código: Selecionar todos
SQL> SELECT LENGTH(TRIM(REGEXP_REPLACE('2331|1321|4524|5512|885|4242113234|5424|4524', '[^|]',''))) TOTAL
  2  FROM DUAL;

     TOTAL
----------
         7


OK .. então até aqui já temos a "string purificada" e quantos valores numericos se encontram na string, com base na contagens dos separadores "pipe".

Para isso, precisamos do comando "REGEXP_SUBSTR ... CONNECT BY LEVEL ...". Execute agora esta querie

Código: Selecionar todos
SQL> SELECT trim(regexp_substr('2331|1321|4524|5512|885|4242113234|5424|4524', '[^|]+', 1, LEVEL)) RESULTADO
  2    FROM dual
  3  CONNECT BY LEVEL <= 7+1;

RESULTADO
--------------------------------------------------------------------------------
2331
1321
4524
5512
885
4242113234
5424
4524


Algumas explicações:

- Note no comando REGEXP_SUBTR que eu informei o segundo parâmetro como '[^|]+'. Isso indica para ele que este é o separador de valores.
- Note no LEVEL que eu adicionei 1 ao valor (ex: 7+1). O motivo é que os separadores sempre estão um número a menos do que o total dos registros.
- Note que eu usei LENGTH e REGEXP_REPLACE para identificar quantos separadores existem. No 10g este é a forma de se fazer isso. No 11g eu passo a usar a função REGEXP_COUNT

Bem .. agora vamos verificar qual a distribuicao destas valores na string. Vamos colocar a querie anterior dentro de um SELECT .. COUNT(*) FROM .. GROUP BY.

Execute agora:

Código: Selecionar todos
SQL> SELECT RESULTADO,COUNT(*)
  2  FROM
  3  (
  4  SELECT trim(regexp_substr('2331|1321|4524|5512|885|4242113234|5424|4524', '[^|]+', 1, LEVEL)) RESULTADO
  5    FROM dual
  6  CONNECT BY LEVEL <= 7+1
  7  )
  8  GROUP BY RESULTADO;

RESULTADO    COUNT(*)
---------- ----------
4524                2
1321                1
5424                1
5512                1
2331                1
885                 1
4242113234          1


Finalizamos então a contagem que você precisava fazer. Eu vou agora então repetir esta querie agregando nela tudo o que explicamos anteriormente:

Código: Selecionar todos
SQL> SELECT RESULTADO,COUNT(*)
  2  FROM
  3  (
  4  SELECT trim(regexp_substr(REGEXP_REPLACE('[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524', '[^0-9\|]',''), '[^|]+', 1, LEVEL)) RESULTADO
  5    FROM dual
  6  CONNECT BY LEVEL <= LENGTH(TRIM(REGEXP_REPLACE('[LOG]2331|1321{VP}|4524|5512]|885|4242113234{eddx]|5424|4524', '[^|]','')))+1
  7  )
  8  GROUP BY RESULTADO
  9  /

RESULTADO    COUNT(*)
---------- ----------
4524                2
1321                1
5424                1
5512                1
2331                1
885                 1
4242113234          1


Eu desenvolvi esta explicação por meio de consultas a sites na internet, que estou postando aqui:

http://www.sqlsnippets.com/en/topic-12818.html
http://stackoverflow.com/questions/10993189/oracle-regex-expression-to-match-exactly-non-digit-then-digits-again

Espero ter resolvido o seu problema, e agradeço por ter postado este assunto, uma vez que não tinha me deparado ainda com este tipo de problema.

Abraços,

Sergio Coutinho
stcoutinho
Localização: Sao Paulo - SP

Mensagemem Seg, 16 Dez 2013 2:45 pm

Olá Sergio!

Muito obrigado pelas suas explicações. Com certeza vai me ajudar bastante em outras consultas que terei que fazer.

Entretanto, preciso de mais uma ajuda. Acho que não consegui explicar direito ou não consegui utilizar a sua explicação. Preciso contabilizar quantas vezes duas sequencias aparecerem. Por exemplo, nessa string:
[LOG]|2331{VP}5354|3215|5331|982[eddx]|3443|5234|2331{VP}|4353|3215|903]|2599

Vamos supor que as sequencias fossem "2331 e 3215". Na string acima, elas apareceriam 2x (em negrito).

Preciso contar quantas vezes a sequencia "2331 e 3215" aparecem na string.

Mais uma vez, muito obrigado!
wkinoue

Mensagemem Seg, 16 Dez 2013 6:27 pm

Acho que entendi ..

Vamos supor que [yyy] e {yyy} possam ser considerados separadores, com o mesmo poder do PIPE.

Execute então esta querie, baseada no exemplo que me passou:

Código: Selecionar todos
SELECT RESULTADO,COUNT(*)
FROM
(
SELECT trim(regexp_substr(REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('[LOG]|2331{VP}5354|3215|5331|982[eddx]|3443|5234|2331{VP}|4353|3215|903]|2599','[','|'),']','|'),'{','|'),'}','|'),  '[^0-9\|]',''), '[^|]+', 1, LEVEL)) RESULTADO
   FROM dual
CONNECT BY LEVEL <= LENGTH(TRIM(REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('[LOG]|2331{VP}5354|3215|5331|982[eddx]|3443|5234|2331{VP}|4353|3215|903]|2599','[','|'),']','|'),'{','|'),'}','|'),'[^|]','')))+1
)
GROUP BY RESULTADO
/

RESULTA   COUNT(*)
------- ----------
3443             1
                 9
3215             2
2331             2
982              1
5354             1
5331             1
2599             1
5234             1
903              1
4353             1



Não foi uma solução muuuuito bonita .. eu tive que fazer algums REPLACES para converter os "[","]","{","}" em caracteres "|".

Do resultado da querie acima, você pode desconsiderar as 9 ocorrencias de NULL.

Espero que esta adaptação resolva o seu problema.

Att

Sergio Coutinho
stcoutinho
Localização: Sao Paulo - SP



Voltar para SQL

Quem está online

Usuários navegando neste fórum: Google Adsense [Bot] e 4 visitantes