Postgresql - Função split_part

 A função split_part divide uma string em duas ou mais partes (pedaços) e retorna um determinado pedaço de acordo com a posição escohida. 

SINTAXE

split_part(string, 'delimitador', posição)

Onde:

• delimitador: um caractere ou um conjunto de caracteres que indica onde a string será quebrada em pedaços. Este delimitador deve ser usado entre aspas simples;
• posição: qual o pedaço da string será retornado pela função. Preencha com 1 para o 1º pedaço, 2 para o 2º pedaço, 3 para o 3º pedaço, e assim por diante

Veja os dois exemplos abaixo, caso tenha interesse veja os scripts no GitHub ou faça o download no Google Drive.

1º Exemplo

Em uma coluna chamada "e_mail" de uma tabela "clientes", temos os dados de e-mails dos clientes. Vamos separar o usuário do e-mail do domínio do e-mail, vejamos alguns casos na prática:

Aqui explico o conceito de usuário e domínio de um e-mail, caso você já conheça, pule esta explicação e veja a execução do comando sql logo abaixo.

• no e-mail maria@gmail.com: o usuário (é o que vem antes do "@" neste caso maria), já o domínio (é o que vem depois do "@" neste caso "gmail.com");
• no e-mail joao123@outlook.com: o usuário (é o que vem antes do "@" neste caso joao123) já o domínio (é o que vem depois do "@" neste caso "outlook.com");
• no e-mail carolina.alves@empresax.com.br: o usuário (é o que vem antes do "@" neste caso carolina.alves) já o domínio (é o que vem depois do "@" neste caso "empresax.com.br");

Neste caso, o "@" será utilizado para fazer a quebra do e-mail em usuário e domínio, logo o "@" será o delimitador.

Veja abaixo a query (comando sql) para fazer a quebra da string:

SELECT 

e_mail,

split_part(e_mail, '@', 1),

split_part(e_mail, '@', 2)

FROM clientes;

Após a execução da query, temos o resultado exibido na imagem abaixo:

Perceba que após a execução do comando acima, a coluna chamada "split_part" armazena o usuário do e-mail e a coluna chamada "split_part_2" armazena o domínio do e-mail. Podemos dar alias (apelidos) para os nomes das colunas para facilitar o entendimento utilizando o comando "AS"

SELECT 

e_mail,

split_part(e_mail, '@', 1) AS usuario,

split_part(e_mail, '@', 2) AS dominio

FROM clientes;

Após a execução da query, temos o resultado exibido na imagem abaixo:

Antes de usar "SPLIT_PART", verifique 

• se o delimitador existe na string: para evitar resultados inesperados. Neste exemplo foi o "@".
• se o delimitador está bem posicionado: no caso deste exemplo, em específico, verificar se o "@" não está no início ou no fim do e-mail

Se o delimitador NÃO for encontrado ou estiver mal posicionado, algum pedaço de string será vazia.

No exemplo anterior, consideramos que todos os e-mails, tem o "@" que é o delimitador e que ele está bem posicionado. 

No segundo exemplo, vamos simular o uso do "split_part" com e-mails sem "@" ou mal posicionado.

2º Exemplo

Vamos executar a mesma query do e-mail anterior mas com e-mails inválidos.

SELECT 

e_mail,

split_part(e_mail, '@', 1) AS usuario,

split_part(e_mail, '@', 2) AS dominio

FROM clientes;

Após a execução da query, temos o resultado exibido na imagem abaixo:

Percebemos que houve retorno de string vazia tanto na coluna usuário quanto na coluna domínio.

Logo, antes de utilizarmos está função, é importante verificarmos se os dados são validos.

Observação

O uso de SPLIT_PART não é adequado para colunas com grandes volumes de texto devido a possíveis problemas com desempenho.

Sua opinião é muito importante, criticas ou sugestões serão bem-vindas.

Postgresql - Função rpad

Neste artigo, vamos mostrar 2 exemplos, da utilização da função RPAD.
 
Utilizamos a função RPAD para completar uma string do lado direito com determinado(s) caractere(s).

Veja o script dos exemplos no GitHub ou faça o download no Google Drive.

SINTAXE

RPAD (string, posicao_de_caracteres, 'caracter_para_preechimento')

• string: sequëncia de caracteres;  
• posicao_de_caracteres: indica até que posição a string será preenchida;
• caracter_para_preechimento: caractere(s) utilizado(s) para o preenchimento, estes caracteres devem estar entre aspas;

1º Exemplo

Completar com o asterisco (*) a direita dos nomes dos produtos até a 10ª posição.  
Neste exemplo, vamos utilizar a coluna "nome_produto" tabela "tb_produto", exibida na imagem abaixo.

tb_produto

Solução

SELECT
nome_produto,
RPAD (nome_produto,10, '*') AS nome_produto_com_asterisco
FROM tb_produto;

Após a execução da sentença, teremos o seguinte resultado:

Observações

• lápis:        tem 5  caracteres e foi preenchida com 5 caracteres para completar as 10 posições;
• caderno:  tem 7  caracteres e foi preenchida com 3 caracteres para completar as 10 posições;
• borracha: tem 8 caracteres e foi preenchida com 2 caracteres para completar as 10 posições;
• cartolina: tem 9 caracteres e foi preenchida com 1 caractere  para completar as  10 posições;

No lugar do asterisco (*), poderíamos utilizar qualquer outro caractere alfanumérico ou caractere especial.
Por exemplo: uma ou mais letras, dígitos, espaços, hífen entre outros.
  
2º Exemplo

Preencher com asterisco (*) a direita, até a 5ª posição, os códigos dos produtos.
Neste exemplo, vamos utilizar a coluna "codigo_produto" tabela "tb_produto", exibida na imagem abaixo.

tb_produto

Solução

Como a função RPAD completa uma string, antes de utilizarmos a função RPAD, neste exemplo, devemos utilizar a função CAST para converter a coluna codigo_produto que é do tipo integer (número inteiro) para string (CHARACTER VARYING ou VARCHAR como é mais conhecido).

SELECT
RPAD (CAST(codigo_produto AS VARCHAR), 5, '*') AS codigo_produto, 
nome_produto
FROM tb_produto;

Após a execução da sentença, teremos o seguinte resultado:

Observações

• O número   8426 tem   4 dígitos, e foi preenchido com 1 * para completar 5 dígitos;
• O número     438 tem   3 dígitos, e foi preenchido com 2 * para completar 5 dígitos;  
• O número       22 tem   2 dígitos, e foi preenchido com 3 * para completar 5 dígitos;
• O número 16547 tem   5 dígitos, logo não foi preenchido com *, pois já contém 5 dígitos;

Aprenda também a utiliza a função lpad:

http://jquerydicas.blogspot.com.br/2014/09/postgresql-funcao-lpad.html

Deixe o seu comentário ou sugestão.
Gostou?  Siga no Google +  ou Facebook. 

Postgresql - Formatar CNPJ com REGEXP_REPLACE

Neste artigo, vamos mostrar um exemplo de como utilizar a função REGEXP_REPLACE para formatar o CNPJ.

Caso tenha interesse, veja o script no github ou faça o download 

1º Exemplo

Para formatar o CNPJ vamos utilizar a tabela "tb_cadastro", exibida na imagem a seguir:

 Solução

Observe que na função REGEXP_REPLACE:

• Utiliza-se parenteses "( )" para separar cada parte da string, neste caso o CNPJ;
• Utiliza-se "\d" para representar os digitos de "0" até "9";
• Dentro das chaves "{}" deve ser colocado a quantidade de dígitos que vamos utilizar em cada parte; 
• Utiliza-se contra-barra "\" antes de cada parte criada;

Após a execução da sentença, teremos o seguinte resultado:

Veja também:

PostgreSql - Formatar CEP com REGEXP_REPLACE

PostgreSql - Formatar CPF com REGEXP_REPLACE

Deixe o seu comentário ou sugestão.

Gostou?  Siga no Google +  ou Facebook.

Postgresql - Formatar CPF com REGEXP_REPLACE

Neste artigo, vamos mostrar 3 exemplos de como utilizar a função REGEXP_REPLACE para formatar o CPF.

Veja qual o exemplo é mais fácil para você, eu considero o 3º exemplo o mais fácil de ser utilizado.

Caso tenha interesse, veja o script no github, faça o download ou execute no Sqlfiddle: 

1º Exemplo

Para formatar o CPF vamos utilizar a tabela "tb_alunos", exibida na imagem a seguir:

 Solução

Observe que na função REGEXP_REPLACE:

• Utilizamos parenteses "( )" para separar cada parte da string, neste caso o CPF;
• Utilizamos colchetes   "[ ]" para indicar quais os caracteres que iremos utilizar em cada parte, neste caso utilizamos caracteres numéricos de "0" até "9", representados pela expressão 0-9;
• Indicamos dentro das chaves "{}" a quantidade de dígitos que vamos utilizar; 
• Utilizamos contra-barra "\" antes de cada parte criada;

Após a execução da sentença, teremos o seguinte resultado:

 2º Exemplo

Também podemos substituir a expressão "0-9" que indica a utilização de caracteres numéricos de 0 até 9, pela expressão [:digit:]. O resultado será o mesmo. 

Solução

Após a execução da sentença, teremos o seguinte resultado:

3º Exemplo

Para facilitar, podemos substituir a expressão "[[:digit:]]" que indica a utilização de caracteres numéricos de 0 até 9, pela expressão abreviada "\d". O resultado será o mesmo.

Solução

Após a execução da sentença, teremos o seguinte resultado:

Deixe o seu comentário ou sugestão.
Gostou?  Siga no Google +  ou Facebook.

PostgreSql - Calcular subtotal / total - equivalente ao WITH ROLLUP no mysql

Neste artigo, vamos mostrar 2 exemplos de como utilizar a função "SUM" para calcular o subtotal / total em uma consulta.

Para quem usa o Mysql, a consulta que vamos fazer é semelhante ao "WITH ROLLUP".

Caso ainda não conheça a função "SUM", veja o artigo "PostgreSql SUM - Soma "

Caso tenha interesse, faça o download ou veja o script deste artigo no github.
O script está disponível para execução através do SQL Fiddle e foi testado no PostgreSql 9.1.9.

1º Exemplo

Vamos calcular as despesas com fornecedor para cada segmento de uma empresa.
Iremos exibir o subtotal por segmento: "Papelaria e informática", "Marcenaria", "Serralheria" e "Limpeza e higiêne".
Também vamos exibir no final da consulta o total geral para todos os segmentos.
Neste exemplo, utilizaremos a tabela "tb_fornecedor". Veja a imagem da tabela a seguir:

Solução

Temos duas formas de fazer esta consulta, podemos utilizar subquery ou o comando WITH, vamos mostrar as duas formas, use a que for mais fácil para você.

Primeiro vamos mostrar a solução com subquery. Veja a sentença abaixo:

SELECT
segmento,
produto,
valor
FROM
(
SELECT segmento, produto, SUM(valor) valor
FROM tb_fornecedor
GROUP BY segmento, produto
UNION ALL
SELECT segmento, NULL AS  produto, SUM(valor) valor
FROM tb_fornecedor
GROUP BY segmento
UNION ALL
SELECT NULL AS segmento, NULL AS produto, SUM(valor) AS valor
FROM tb_fornecedor
) CALCULO
ORDER BY segmento, produto;

Nesta consulta, criamos uma subquery chamada CALCULO, o conteúdo da subquery está entre parenteses. A subquery contém três comandos "SELECT" com a seguinte função:

• o primeiro "SELECT" agrupa a soma por segmento e produto;
• o segundo "SELECT" agrupa a soma por segmento;
• o terceiro  "SELECT" calcula a soma de todos os produtos;

Repare que os registros retornados pelos comandos "SELECT" serão unidos através do comando "UNION ALL";

Observe também que os comandos "SELECT" da subquery foram intercalados com valores nulos "NULL", para que a quantidade de colunas fossem as mesmas em cada consulta.

Após a execução da sentença teremos o resultado exibido a seguir:

Agora vamos mostrar a solução utilizando o comando "WITH". Veja a sentença abaixo:

WITH CALCULO AS
(
SELECT segmento, produto, SUM(valor) valor
FROM tb_fornecedor
GROUP BY segmento, produto
UNION
SELECT segmento, NULL AS  produto, SUM(valor) valor
FROM tb_fornecedor
GROUP BY segmento
UNION
SELECT NULL AS segmento, NULL AS produto, SUM(valor) AS valor
FROM tb_fornecedor
)
SELECT
segmento,
produto,
valor
FROM calculo
ORDER BY segmento, produto;

2º Exemplo

Para facilitarmos a interpretação do resultado da consulta vamos indicar o "Total Geral" e o "Subtotal" na consulta.

Solução

• Total Geral: colocaremos a indicação de "Total Geral" na primeira coluna, onde o valor do registro da primeira coluna apresentar valor nulo. Para fazer isso, vamos utilizar o comando "COALESCE";
• Subtotal: colocaremos a indicação de "Subtotal" na segunda coluna, onde o valor do registro da primeira coluna for diferente de nulo e o valor da segunda coluna for nulo. Para fazer isso, utilizamos o comando "CASE WHEN";

Temos duas formas de fazer esta consulta, podemos utilizar "subquery" ou o comando "WITH", vamos mostrar as duas formas, use a que for mais fácil para você.

Primeiro vamos mostrar a solução com subquery. Veja a sentença abaixo:

SELECT
COALESCE(segmento, 'TOTAL GERAL')  AS segmento,  
CASE 
WHEN(segmento IS NOT NULL AND produto IS NOT NULL) THEN produto 
WHEN(segmento IS NOT NULL AND produto IS NULL) THEN 'SUBTOTAL' || ' - '|| segmento
END AS produto,
valor
FROM
(
 SELECT segmento, produto, SUM(valor) valor
 FROM tb_fornecedor
 GROUP BY segmento, produto
 UNION
 SELECT segmento, NULL AS  produto, SUM(valor) valor
 FROM tb_fornecedor
 GROUP BY segmento
 UNION
 SELECT NULL AS segmento, NULL AS produto, SUM(valor) AS valor
 FROM tb_fornecedor
) CALCULO
ORDER BY
CASE
  WHEN(segmento IS NOT NULL) THEN CAST(0 AS INTEGER)
  ELSE CAST(1 AS INTEGER)
END
, segmento,
CASE
  WHEN(produto IS NOT NULL) THEN CAST(0 AS INTEGER)
  ELSE CAST(1 AS INTEGER)
END,
produto;

Após a execução da sentença teremos o resultado exibido a seguir:

Observação: perceba que no "ORDER BY" utilizamos duas vezes o "CASE WHEN":

• o primeiro para que o "TOTAL GERAL" fosse exibido no último registo da coluna "segmento" independente da ordem alfabética;
• o segundo para que o "Subtotal" fosse exibido no final de cada segmento da coluna "produto" independente da ordem alfabética;

Agora vamos mostrar a solução utilizando o comando "WITH". Veja a sentença abaixo:

WITH CALCULO AS
(
 SELECT segmento, produto, SUM(valor) valor
 FROM tb_fornecedor
 GROUP BY segmento, produto
 UNION
 SELECT segmento, NULL AS  produto, SUM(valor) valor
 FROM tb_fornecedor
 GROUP BY segmento
 UNION
 SELECT NULL AS segmento, NULL AS produto, SUM(valor) AS valor
 FROM tb_fornecedor
)
SELECT
COALESCE(segmento, 'TOTAL GERAL') AS segmento,  
CASE 
WHEN(segmento IS NOT NULL AND produto IS NOT NULL) THEN produto 
WHEN(segmento IS NOT NULL AND produto IS NULL) THEN 'SUBTOTAL' || ' - '|| segmento
END AS produto,
valor
FROM calculo
ORDER BY
CASE
  WHEN(segmento IS NOT NULL) THEN CAST(0 AS INTEGER)
  ELSE CAST(1 AS INTEGER)
END,
segmento,
CASE
  WHEN(produto IS NOT NULL) THEN CAST(0 AS INTEGER)
  ELSE CAST(1 AS INTEGER)
END,
produto;

Deixe o seu comentário ou sugestão.
Gostou?  Siga no Google +  ou Facebook.

Postgresql - Função MIN

A função MIN retorna o valor mínimo (menor valor) de uma coluna. 
Serão descritos 3 exemplos de utilização desta função. 

Caso tenha interesse, faça o download ou veja os scripts no github.

SINTAXE

SELECT MIN(nome_da_coluna) FROM nome_da_tabela;

1º Exemplo
 
Neste exemplo, utilizaremos a tabela "tb_imoveis". Veja a imagem abaixo:

tb_imoveis

  

Cenário: qual o imóvel que possui o valor mais baixo?

Solução: o valor dos imóveis estão armazenados na coluna valor, logo devemos calcular o valor mais baixo a partir desta coluna.

Para calcularmos o valor mínimo, executamos a sentença abaixo:

SELECT MIN(valor) FROM tb_imoveis;

Após a execução, teremos o imóvel com o valor mais baixo. Conforme podemos visualizar na imagem a seguir:

O nome da coluna aparece como "min", mas vamos supor que precisamos que seja exibido "imovel_valor_minimo".

Podemos modificar o nome desta coluna, ou seja, criar um alias (apelido). Colocamos o alias depois do "AS" 

SELECT MIN(valor) AS imovel_valor_minimo FROM tb_imoveis;

Após a execução, o nome da coluna será exibido como "imovel_valor_minimo".

2º Exemplo

Cenário: Uma equipe de três atletas treina uma série de corridas. Queremos saber qual foi o melhor treino para cada atleta, ou seja qual corrida levou menos tempo.
Neste exemplo, utilizaremos a tabela "tb_treino".

tb_treino

Solução: já que procuramos o menor tempo por atleta, vamos agrupar os atletas que estão armazenados na coluna "atleta_id". Para agrupar, utilizaremos o " GROUP BY".

Para calcularmos o menor tempo por atleta, executamos a sentença abaixo:

SELECT atleta_id, MIN(tempo) AS melhor_tempo
FROM tb_treino
GROUP BY atleta_id;

Após a execução, teremos o melhor tempo  para cada atleta. Conforme podemos visualizar na imagem abaixo:

Para ordenar a identificação dos atleta em ordem crescente, ou seja, do valor menor para o maior, deve-se usar "ORDER BY" seguido do nome da coluna, neste caso, a coluna 'atleta_id' será ordenada .

Para ordenar as identificações, utilizamos a sentença abaixo:

SELECT atleta_id, 
MIN(tempo) AS pior_tempo
FROM tb_treino
GROUP BY atleta_id
ORDER BY atleta_id;

Após a execução, teremos as identificações dos atletas em ordem crescente. Conforme podemos visualizar na imagem abaixo:

3º Exemplo

Cenário: Qual o atleta que obteve o melhor treino? Qual a data que o treino ocorreu?
Neste exemplo, utilizaremos a tabela "tb_treino".

tb_treino

Solução: também podemos utilizar a função "MIN" para filtrar um registro, desde que a função "MIN" pertença a uma subquery.

Veja a subquery destacada, em azul, na sentença abaixo:

SELECT 
atleta_id,   
data, 
tempo AS melhor_tempo
FROM tb_treino 
WHERE tempo =
(
    SELECT MIN(tempo)
    FROM tb_treino
)

Após a execução da sentença, teremos o resultado exibido na imagem abaixo:

Observação:

A função MIN não funciona no WHERE sem a subquery.

WHERE tempo = MIN(tempo)

Deixe o seu comentário ou sugestão.
Gostou?  Siga no Google +  ou Facebook.

Postgresql - Funcão MAX

A função MAX retorna o valor máximo (maior valor) de uma coluna. 
Serão descritos 3 exemplos de utilização desta função. 

Caso tenha interesse, faça o download ou veja os scripts no github.

SINTAXE

SELECT MAX(nome_da_coluna) FROM nome_da_tabela;

1º Exemplo
 
Neste exemplo, utilizaremos a tabela "tb_imoveis". Veja a imagem abaixo:

tb_imoveis

  

Cenário: qual o imóvel que possui o valor mais alto?

Solução: o valor dos imóveis estão armazenados na coluna valor, logo devemos calcular o valor mais alto a partir desta coluna.

Para calcularmos o valor máximo, executamos a sentença abaixo:

SELECT MAX(valor) FROM tb_imoveis;

Após a execução, teremos o imóvel com o valor mais alto. Conforme podemos visualizar na imagem abaixo:

O nome da coluna aparece como "max", mas vamos supor que precisamos que seja exibido "imovel_valor_maximo".

Podemos modificar o nome desta coluna, ou seja, criar um alias (apelido). Colocamos o alias depois do "AS" 

SELECT MAX(valor) AS imovel_valor_maximo FROM tb_imoveis;

Após a execução, o nome da coluna será exibido como "imovel_valor_maximo".

2º Exemplo

Cenário: Uma equipe de três atletas treina uma série de corridas. Queremos saber qual foi o pior treino para cada atleta, ou seja qual corrida levou maior tempo.
Neste exemplo, utilizaremos a tabela "tb_treino".

tb_treino

Solução: já que procuramos o maior tempo por atleta, vamos agrupar os atletas que estão armazenados na coluna "atleta_id". Para agrupar, utilizaremos o " GROUP BY".

Para calcularmos o maior tempo por atleta, executamos a sentença abaixo:

SELECT atleta_id, MAX(tempo) AS pior_tempo
FROM tb_treino
GROUP BY atleta_id;

Após a execução, teremos o pior tempo  para cada atleta. Conforme podemos visualizar na imagem abaixo:

Para ordenar a identificação dos atleta em ordem crescente, ou seja, do valor menor para o maior, deve-se usar "ORDER BY" seguido do nome da coluna, neste caso, a coluna 'atleta_id' será ordenada .

Para ordenar as identificações, utilizamos a sentença abaixo:

SELECT atleta_id, 
MAX(tempo) AS pior_tempo
FROM tb_treino
GROUP BY atleta_id
ORDER BY atleta_id;

Após a execução, teremos as identificações dos atletas em ordem crescente. Conforme podemos visualizar na imagem abaixo:

3º Exemplo

Cenário: Qual o atleta que obteve o pior treino? Qual a data que o treino ocorreu?
Neste exemplo, utilizaremos a tabela "tb_treino".

Solução: também podemos utilizar a função "MAX" para filtrar um registro, desde que a função "MAX" pertença a uma subquery.

Veja a subquery destacada, em azul, na sentença abaixo:

SELECT 
atleta_id,   
data, 
tempo AS pior_tempo
FROM tb_treino 
WHERE tempo =
(
    SELECT MAX(tempo)
    FROM tb_treino
)

Após a execução da sentença, teremos o resultado exibido na imagem abaixo:

Observação:

A função MAX não funciona no WHERE sem a  subquery.

WHERE tempo = MAX(tempo)

Deixe o seu comentário ou sugestão.
Gostou?  Siga no Google +  ou Facebook.