Oracle 12c: Conheça a cláusula pattern matching

Conhecendo a versão Oracle 12c

Estamos vivendo em uma época em que a informação está sendo cada vez mais valorizada e necessária. Podemos constatar isso com o aumento de ofertas de emprego na área de BI, onde são analisados os dados da empresa para apoiar as atividades de tomada de decisão. Encontrar padrões no consumo dos clientes, nas ofertas de certo produto etc., está cada vez mais comum e quase que mandatório em uma empresa que deseja sobreviver nesse mundo cada vez mais competitivo.

Encontrar esses padrões já era possível em versões anteriores a 12c do Oracle, mas essas soluções eram difíceis de se escrever, de entender e muitas vezes ineficientes ao executar, consumindo recursos consideráveis no processo. Com a ajuda das novas cláusulas e sintaxe do 12c, o mesmo processo ficou agora nativo ao SQL e podemos, assim, alcançar resultados melhores de forma bem mais eficiente.

Podem existir diferentes tipos de padrões, tudo depende daquilo que estamos procurando. Alguns exemplos seriam: padrão de preços, em que diferentes épocas do ano o preço sobe e desce de acordo com o mercado, ou apenas o número bruto de vendas, quantidade de visitas em um site, comportamento em aplicações financeiras para detecção de fraude, etc. Neste artigo iremos abordar como definir esses padrões, agora com a sintaxe nativa do SQL, e como pesquisar por isso nas linhas que estão armazenadas no banco de dados. Iremos aprender a utilizar a nova cláusula introduzida na versão 12c do Oracle, MATCH_RECOGNIZE, que irá nos ajudar a identificar padrões através de uma sequência de linhas armazenadas no banco. Com essa nova sintaxe, podemos fazer o mesmo trabalho de antes, mas de uma maneira muito mais simples, ganhando em performance, escalabilidade e manutenção. Vamos aprender o que são esses padrões, como defini-los e encontrá-los nas linhas correspondentes, e o que podemos ganhar com isso.

DEFINE

Entre todas as novas cláusulas da sintaxe do Oracle relacionadas a Pattern Matching, a primeira que vamos falar é a DEFINE. Com essa cláusula, que é obrigatória, podemos definir variáveis de padrão primárias que serão então utilizadas para criar o padrão final que será utilizado para realizar a busca nas linhas. A pergunta que devemos fazer é: que características definem o meu padrão?

Vamos utilizar um exemplo clássico, que é o histórico de preços de um produto, algo bem comum em sites que buscam o melhor preço para um determinado produto, guardando assim o seu histórico para saber se o preço realmente está bom ou não. Vamos analisar a Figura 1.

Olhando rapidamente para esse gráfico, podemos encontrar o preço do produto em um determinado dia. Mas podemos ver mais nesse gráfico do que apenas isso. Podemos encontrar alguns padrões, como as variações de preços em forma de “V”, onde o preço começa a baixar em um determinado momento e depois começa a subir até certo ponto. Ou até mesmo um “W” em que esse mesmo processo ocorre duas vezes. Antes de definir esse padrão, precisamos das variáveis que serão utilizadas nesse padrão, que nada mais é que uma condição que deverá ser cumprida para que uma linha seja mapeada para essa variável.

É isso o que a cláusula DEFINE faz. Vamos ver agora como definir essas variáveis utilizando o exemplo do gráfico. Quais são as características do padrão em “V” que está compreendido entre os dias 5 e 10? O dia 5 é o ponto de partida, onde o preço estava alto e a próxima mudança de preço é quando possivelmente existiu uma promoção, no dia 6. Essa foi caracterizada por uma decida no preço, a primeira perna do nosso “v”. A parti do dia 7 até o dia 10, o preço começa a subir até atingir o preço mais alto, antes de começar a cair novamente. Então devemos ter três variáveis, o início, a descida e a subida. A cláusula ficaria como nos mostra o código da Listagem 1.

Listagem 1. Criando um padrão com duas variáveis.

Definimos então duas variáveis de padrão. A primeira identificada com SUB, faz a comparação com a linha anterior, PREV, para saber se tem um preço maior ou não. Se tiver, essa linha será mapeada como SUB. A variável DES é exatamente o contrário, comparando se tem um valor menor que a linha anterior, assim essa linha será mapeada como DES. Agora faltou uma variável para o início do padrão. Acontece que nem toda variável precisa de uma definição, significando que qualquer linha pode então ser mapeada para esse padrão, que é o caso do que veremos mais adiante.

PATTERN

Aqui definimos qual é o padrão que será pesquisado em todas as linhas. Nessa cláusula, utilizamos as variáveis definidas em DEFINE para criar esse padrão. Descrevemos de forma simples, a sequência em que as linhas serão mapeadas entre as variáveis e a quantidade de linhas. Podemos utilizar expressões regulares para tornar a nossa busca ainda mais poderosa. Veja um exemplo na linha a seguir:

PATTERN (INI, DES+, SUB+)

Nesse exemplo utilizamos uma variável INI, que não foi definida na Listagem 1, ou seja, qualquer linha pode ser mapeada como INI, o nosso ponto de partida. Logo após temos uma a variável DESC, que significa que a próxima linha deverá ter um preço menor que a linha mapeada como INI. O símbolo de “+” significa que pelo menos uma linha deverá ser mapeada ou mais como essa variável. E logo após temos a última variável, SUB, que também tem que ter pelo menos uma ou mais linhas mapeadas, indicando um aumento no preço. Formando assim um padrão de “V”.

Para que esse padrão seja encontrado, um conjunto de linhas contínuas deverão ser mapeadas conforme a sequência definida em PATTERN e todas as condições em DEFINE deverão ser verdadeiras. A definição desse padrão poderá ser algo bem mais avançada do que esse exemplo. Para isso, é necessário ter um bom conhecimento sobre expressões regulares.

MEASURES

Aqui definimos as medidas, que serão apresentadas em forma de uma lista de colunas como resultado final para cada combinação de padrão encontrada. Aqui podemos usar funções junto com as variáveis de padrão, além de operadores de navegação, como o PREV que já vimos anteriormente. Vejamos um exemplo na Listagem 2.

Listagem 2. Criando measures.

Nessa listagem definimos qual será o retorno da nossa query em formato de colunas ao encontrar uma combinação de padrão. As duas primeiras colunas, definidas nas linhas 01 e 02, estão referenciando a variável INI, que não tem definição, ou seja, poderá ser qualquer linha (como o início do nosso padrão). Irá então exibir tanto o preço inicial como a data inicial do nosso ponto de partida do padrão. Logo após isso, nas linhas 03 a 06 temos a referência às variáveis DES e SUB, que foram definidas na Listagem 1, que é uma linha que tem um preço menor que a sua anterior (DES) e uma linha com um preço maior que a anterior (SUB). Também irá exibir a data e o preço da linha. Note o uso da função de navegação LAST. Com o uso dessa função, garantimos que o valor retornado será o último para cada padrão, tanto o de descida quanto o de subida, sendo então o menor e o maior preço, respectivamente.

AFTER MATCH SKIP

Essa cláusula se refere a determinar o ponto para retomar a busca por um padrão após ter encontrado uma correspondência. Ou seja, a partir de qual linha poderá ser contado como início para uma próxima combinação de padrão. Temos algumas opções disponíveis, que são:

• AFTER MATCH SKIP TO NEXT ROW: será determinado como ponto de partida a linha após a primeira linha do padrão atual encontrado;
• AFTER MATCH SKIP PAST LAST ROW: será determinado como ponto de partida a linha após a última linha do padrão atual encontrado. Esse é o valor default;
• AFTER MATCH SKIP TO FIRST variavel_padrao: será determinado como ponto de partida a primeira linha mapeada para uma determinada variável de padrão;
• AFTER MATCH SKIP TO [LAST] variavel_padrao: será determinado como ponto de partida a última linha mapeada para uma determinada variável de padrão.

Deve-se ter alguns cuidados ao escolher o ponto de retorno para a busca de uma nova combinação de padrão, pois poderá não ser encontrado nada ou até mesmo ficar em um estado de loop infinito, gerando assim exceções. Um exemplo seria retomar para uma variável padrão, mas se no DEFINE essa variável for opcional e não tiver nenhuma linha mapeada para ela (não existindo assim um ponto de retomada), será gerada uma exceção em tempo de execução. Outro exemplo seria utilizar o ponto de retorno para uma determinada variável sendo que ela também foi o início da combinação de padrão atual, formando assim um loop infinito, gerando também uma exceção em tempo de execução.

Algumas outras cláusulas importantes

Temos algumas outras cláusulas importantes a serem mencionadas antes de termos um exemplo prático. Veja alguma delas:

• (ONE ROW | ALL ROWS) PER MATCH: aqui escolhemos se para cada variável de padrão encontrada, serão exibidas todas as linhas mapeadas ou apenas uma como um resumo;
• PARTITION BY coluna: dividimos as linhas em grupos de acordo com os valores em comum na coluna especificada. Algo similar ao GROUP BY;
• ORDER BY: ordena as linhas, com os seus grupos, para serem localizados os padrões.

Essas últimas duas cláusulas são já bem conhecidas por quem usa funções analíticas. Temos também algumas funções importantes para mencionar, que são:

• CLASSIFIER(): retorna qual foi a variável de padrão na qual a linha foi mapeada;
• MATCH_NUMBER(): atribui um número em sequência para cada padrão encontrado, retornando assim em qual padrão, da sequência, aquela linha pertence.

Como os dados são processados

Após ter conhecido as cláusulas e funções mais importantes, vamos ver como é o processamento de uma query com a cláusula MATCH_RECOGNIZE em alguns passos simples:

1. A tabela será particionada em grupos de acordo com a cláusula PARTITION BY, onde cada grupo tem o mesmo valor em uma determinada coluna;
2. Cada partição será ordenada pelo ORDER BY;
3. Se inicia então em cada partição a busca pelo padrão definido em PATTERN;
4. A busca se inicia na primeira linha e as seguintes para encontrar uma combinação como definida em PATTERN. Se não for encontrado nada, a busca irá se iniciar na linha seguinte e assim por diante. Caso seja encontrada uma combinação positiva, são calculadas as expressões presentes na cláusula MEASURES;
5. São retornadas as quantidades de linhas de acordo com a cláusula ONE ROW PER MATCH ou ALL ROWS PER MATCH;
6. E para finalizar, após uma combinação de padrão, a cláusula AFTER MATCH SKIP irá informar aonde irá se retomar o processo de pesquisa por mais uma combinação de padrão.

Criando o ambiente de testes

Para iniciar a demonstração do uso do MATCH_RECOGNIZE, devemos criar uma tabela onde irão ficar os registros de testes, que nada mais são que um histórico de preços de determinados produtos. Após isso, serão inseridas algumas linhas para popular a tabela e realizar uma query. É nessa tabela que iremos fazer a busca pelos padrões. Na estrutura dessa tabela temos o ID, produto, preço e data da venda. Confira a Listagem 3.

Listagem 3. Criando o ambiente de testes.

Vamos analisar um pouco o conteúdo da tabela teste_pattern, que possui apenas quatro colunas. Além do ID da venda, temos o produto, que são apenas dois, JAVA e ORACLEDB. Nas próximas duas colunas temos a data da venda e o preço que o produto foi vendido. Nas instruções em sequência, temos uma série de INSERTs, que cobre um período de 18 dias e tem uma variação de preço dos dois produtos já mencionados. Apenas com essas informações, podemos criar e pesquisar por padrões. Iremos utilizar tudo o que foi visto anteriormente na explicação de cada uma das cláusulas mais comuns. Veja um primeiro exemplo na Listagem 4.

Listagem 4. MATCH_RECOGNIZE.

Na linha 2 foi utilizada a cláusula MATCH_RECOGNIZE, que possibilita criar e pesquisar por padrões. Logo nas linhas 3 e 4, foi definido que a partição será feita com base nos valores da coluna produto, que no caso será duas partições, e que cada partição será ordenada com base na coluna data_venda. As variáveis de padrão foram definidas nas linhas 14 e 15 e utilizadas para criar o padrão na linha 12. Na linha 10 foi definido que para cada padrão encontrado só será exibida uma linha e não todas as linhas que foram mapeadas para as variáveis de padrão. Já na linha 11 indicamos onde recomeçar pela busca de um novo padrão após já ter encontrado um, no caso foi escolhida a última linha mapeada como SUB, ou seja, a última linha de um padrão poderá ser o início de outro padrão. E, para finalizar, nas linhas 5 até 9 foram definidas as medidas que serão apresentadas como resultado final em forma de colunas. Veja esse resultado na Listagem 5.

Listagem 5. Resultado gerado.

Aqui podemos verificar o resultado da nossa primeira query. Além das colunas que definimos na cláusula MEASURES, foi retornada a coluna produto, que foi a condição da nossa cláusula PARTITION BY. Os resultados estão ordenados de acordo com a cláusula ORDER BY da linha 17 da Listagem 4. Foram encontrados dois padrões para o produto JAVA, conforme as linhas 2 e 3, e três padrões para o produto ORACLEDB, que estão representados nas linhas 4 a 6. Para cada um dos padrões encontrados é mostrada a data de início, do menor preço e do maior preço, juntamente com os respectivos preços nessas datas. Para cada padrão é exibida apenas uma linha, como uma espécie de resumo. Podemos constatar esse resultado verificando a Figura 2.

Compare os resultados da Listagem 5 com a Figura 2. Vamos pegar um exemplo de padrão e comparar com o nosso gráfico. Veja a linha 5 da Listagem 5. Esse padrão indica que o ponto de partida foi no dia 03/06/2016, se olhar no gráfico, verá que o valor do produto ORACLEDB, linha em laranja, era de 210, após isso o valor do produto sofreu uma queda chegando a ser cotado com o menor valor em 150, no dia 04/06/2016. Após isso, o preço começou a subir tendo o valor de 180 e depois 300 como o valor mais alto no dia 07/06/2016. Isso foi um exemplo de padrão em forma de “V”, que foi definido como sendo o PATTERN INI, DES+, SUB+.

Se for necessária mais informação no retorno do padrão, como cada preço e cada data que foi mapeado, deve ser utilizada a cláusula ALL ROWS PER MATCH. Mas talvez fique difícil de identificar cada linha com os seus respectivos padrões e o que significa cada uma dessas linhas. Para auxiliar nisto, existem duas funções que podem nos ajudar a identificar cada linha.

Classifier e Match_number

Existem duas funções que podem nos ajudar a identificar cada linha retornada quando é utilizado ALL ROWS PER MATCH, visto que o número de linhas retornado pode ser muito grande para cada padrão. A primeira função, a CLASSIFIER, retorna a variável padrão na qual aquela linha foi mapeada. Já a função MATCH_NUMBER retorna um número inteiro positivo em sequência, para cada padrão encontrado, representando a qual padrão aquela linha pertence. Para visualizar melhor como essas funções funcionam, iremos analisar mais um exemplo.

Contudo, antes disso, devemos entender que ao mudar de ONE ROW para ALL ROWS, o comportamento das medidas em MEASURES pode ser alterado. Isso ocorre por que, por default, é utilizado o modificador ou função de navegação RUNNING, que significa que o valor calculado naquela linha não será considerado o padrão inteiro, mas será considerada a linha corrente e as anteriores da mesma, mesmo que existam muitas linhas depois dela que serão incluídas no mesmo padrão. Um exemplo disso seria a medida do menor preço, que talvez não exiba o menor preço que foi encontrado no padrão inteiro, mas sim o menor preço até aquela linha. Para corrigir isso, caso não seja o desejado, deve-se utilizar o modificador FINAL, que irá então considerar todas as linhas do mesmo padrão. Observe agora a Listagem 6.

Listagem 6. Utilização do CLASSIFIER, MATCH_NUMBER e FINAL.

Vamos entender as alterações feitas nessa listagem. A primeira modificação foi feita na cláusula MEASURES. Nela alteramos algumas medidas, removemos algumas listagens e adicionamos outras. Na linha 7 adicionamos o modificador FINAL, conforme já foi discutido, e assim podemos comparar com o retorno da medida na linha 6, que é a mesma medida, mas sem modificador nenhum explícito, ou seja, utilizou o modificador default, que é o RUNNING no caso. Nas linhas 8 e 9 adicionamos o uso das funções CLASSIFIER e MATCH_NUMBER para identificar melhor o que cada linha representa, já que na linha 11 foi informado para retornar todas as linhas por padrão, e não apenas uma como no exemplo anterior. Observe o resultado agora na Listagem 7.

Listagem 7. Uso das funções CLASSIFIER e MATCH_NUMBER.

Agora podemos analisar todas as linhas em cada padrão. A coluna PADRAO poderá nos ajudar nessa tarefa, já que com a ajuda da função MATCH_NUMBER, podemos identificar a qual padrão aquela linha pertence. Já para a coluna VAR_PADRAO foi utilizada a função CLASSIFIER, que nos retorna para qual variável de padrão aquela linha foi mapeada. Agora podemos saber quem é o início, a descida de preço e depois a subida de preço.

Agora vamos analisar em conjunto duas colunas, a MAIOR_PRECO e MAIOR_FINAL. As medidas que essas colunas fazem são as mesmas, a única diferença é que uma utiliza o modificador FINAL e a outra, sem o modificador, utiliza o RUNNING. Vamos pegar o primeiro padrão do produto JAVA como exemplo. Repare que desde a primeira linha a coluna MAIOR_FINAL já mostra o maior preço encontrado em todo o padrão. Já a linha MAIOR_PRECO retorna NULL no início e na descida. Já na subida, conforme ele encontra um preço maior que o anterior ele vai retornando esse valor, até encontrar o maior preço em todo o padrão, apenas na última linha do padrão. Isso ocorre por que essa coluna usa o modificar default RUNNING e ele verifica apenas a linha atual e as anteriores que já foram analisadas.

Note também que algumas linhas foram mapeadas duas vezes, uma em cada padrão diferente. Um exemplo foi a linha com o PATTERN_ID 15. Perceba que ela foi mapeada como SUB, última linha, no segundo padrão do produto ORACLEDB e como INI no terceiro padrão do mesmo produto. Esse comportamento ocorrer a depender de como foi utilizada a cláusula AFTER MATCH SKIP. No nosso exemplo foi para SKIP TO LAST SUB, indicando que o ponto de partida para a procura de um novo padrão será a última linha mapeada como SUB. Ou seja, essa linha poderá ser contada como início de outro padrão.

Diferentes tipos de padrão

Até aqui, em todos os nossos testes e exemplos, foi considerado um único tipo de padrão em forma de “V”. Vejamos agora como definir outros tipos de padrão, como um em forma de “W”, onde o padrão anterior que já utilizamos, em forma de “V”, ocorre duas vezes seguidas. Confira como ficaria essa query na Listagem 8.

Listagem 8. Padrão em Forma de “W”.

Para melhorar a compressão do retorno da query, a cláusula MEASURES, nas linhas 5 e 6, foi modificada para retornar pouca informação. Na linha 9 foi criado o nosso padrão, utilizando as variáveis de padrão nas linhas 11 e 12. Lá foi informado que após o início do padrão, o preço iria descer, depois subir e refazer o processo de descer e subir, formando assim uma forma de “W” no gráfico da Figura 2. Vamos analisar o retorno dessa query na Listagem 9.

Listagem 9. Padrão em Forma de “W”.

Essa listagem retorna dois padrões, um para cada produto, onde foi encontrada uma combinação positiva do padrão em forma de “W”. Pode-se constatar isso observando o gráfico na Figura 2. Podemos verificar isso também pela ordem em que as variáveis de padrão retornadas pela função CLASSIFIER na coluna VARIAVEL_PADRAO é retornada, sendo exatamente a mesma ordem que aparece na cláusula PATTERN. Isso evidência que as possibilidades de criar padrões diferentes são muitas.

Até agora, todos os nossos exemplos foram baseados no valor de cada produto em uma determinada data. Vejamos agora, na Listagem 10, um exemplo de padrão um pouco diferente, referente à quantidade de determinados produtos vendidos em uma certa sequência.

Listagem 10. Padrão de quantidade de determinados produtos vendidos.

O primeiro item a se notar é que não criamos partição nenhuma. Nas linhas 11 e 12 foram definidas duas variáveis de padrão onde verificamos qual foi o produto vendido. Após isso, o padrão foi criado na linha 8 utilizando essas mesmas variáveis já citadas. Aqui utilizamos o poder das expressões regulares, onde definimos que primeiro teria que ter sido vendido entre 2 e 4 produtos JAVA e, após isso, entre 1 e 2 produtos ORACLEDB. Toda essa verificação é feita respeitando a ordem das linhas que foi imposta na linha 3, no ORDER BY. A última diferença desse exemplo para os anteriores se encontra na linha 7, onde foi definido que a busca de uma nova combinação de padrão será feita na linha seguinte à última linha do padrão atual encontrado. Sendo assim, uma mesma linha não poderá ser mapeada em dois padrões diferentes.

Todos os exemplos utilizados aqui foram apenas para ajudar no entendimento dessa nova cláusula, que em um primeiro momento pode parecer um pouco complicada, mas que na verdade não é. A versão 12c do Oracle trouxe grandes avanços na sintaxe do SQL, um bom exemplo disso é a nova cláusula MATCH_RECOGNIZE. Com ela podemos fazer tudo nativamente, a criação e a pesquisa de padrões, ganhando assim bastante em performance, escalabilidade e manutenção. Caso seja necessário, sempre utilize essas novas cláusulas e, para formas mais avançadas do que as apresentadas aqui, consulte a documentação do Oracle.

Relacionados