1.10. Consultas em SQL

Na linguagem SQL, o comando SELECT é utilizado para recuperação de dados das tabelas. A sintaxe geral deste comando é a seguinte:

    SELECT [ ALL | DISTINCT [ ON ( expressão [, ...] ) ] ]
           [ * | expressão [ [ AS ] rótulo ] [, ...] ]

[     FROM from_item [, ...] ]

[    WHERE condição ]

[ GROUP BY elemento_agrupamento [, ...] ]

[   HAVING condição [, ...] ]

[ ORDER BY expressão [ ASC | DESC ] [, ...] ]

[    LIMIT { quantidade | ALL } ]

[   OFFSET início [ ROW | ROWS ] ]

O comando SELECT é formado por várias cláusulas: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT e OFFSET. Cada uma dessas cláusulas tem um papel importante na definição dos objetivos de uma consulta. Portanto, na sintaxe mostrada acima temos que:

Tudo que está entre os pares de colchetes ([ e ]) é opcional.
A cláusula SELECT permite especificar a lista de expressões, isto é, nomes de colunas ou fórmulas matemáticas ou chamadas de função ou até mesmo sub-consultas, que farão parte das linhas de saída da consulta. A palavra-chave DISTINCT faz com que linhas com valores duplicados sejam removidas do resultado, ficando apenas uma linha do grupo de linhas repetidas. DISTINCT ON ( expressão [, ...]) tem um comportamento semelhante, mas considerando apenas a lista de expressões fornecida. A palavra-chave ALL inclui todas as linhas do resultado, que é o comportamento padrão e, portanto, pode ser omitida. A palavra-chave AS possibilita renomear uma coluna ou expressão com um novo rótulo. O caractere * é uma abreviação para a lista de todas as colunas dos conjuntos de dados presentes na cláusula FROM.
A cláusula FROM especifica uma ou mais tabelas como fonte dos dados da consulta. Caso múltiplas tabelas sejam especificadas, o resultado é um produto cartesiano (ou CROSS JOIN) de todas as tabelas envolvidas. No entanto, é muito comum o uso de uma cláusula WHERE para restringir as linhas retornadas a um subconjunto menor desse produto cartesiano. Vale ressaltar que na cláusula FROM, o from_item pode ser o nome de uma tabela do banco de dados, o nome de uma view (visão), uma sub-consulta, ou até mesmo uma chamada de função que produza valores que são tratados como um conjunto de linhas. Portanto, a cláusula FROM faz o produto cartesiano dos conjuntos de dados informados.
A cláusula WHERE permite definir uma condição, isto é, uma expressão lógica ou predicado, para filtrar o conjunto de linhas da consulta. As linhas que não satisfaçam esse predicado serão eliminadas do resultado.
A cláusula GROUP BY é utilizada para criar grupos de linhas que são condensadas em uma única linha através das operações de agregação tais como: SUM, MIN, MAX, AVG, COUNT, entre outras. O elemento_agrupamento pode ser o nome de uma coluna ou uma expressão formada a partir das colunas. Também podemos usar os nomes das colunas de saída da consulta nessa cláusula ou até mesmo a posição ordinal da coluna de saída. Quando esta cláusula está presente, apenas as colunas listadas nela ou funções de agregação podem ser usadas na lista da cláusula SELECT.
A cláusula HAVING permite definir uma condição, isto é, uma expressão lógica ou predicado, para filtrar o resutado dos grupos de linhas. Dessa maneira, as linhas resultantes de agrupamentos que não satisfaçam essa condição, são eliminadas do resultado final.
A cláusula ORDER BY ordena o resultado final de acordo com a expressão fornecida. Lembrando que a expressão pode ser uma lista de colunas, expressões matemáticas, chamadas de função, ou a posição das colunas de saída de acordo com a lista da cláusula SELECT.
As cláusulas LIMIT e OFFSET permitem, respectivamente, definir o número máximo de linhas a serem retornadas e o ponto onde esta contagem começa a valer.

Nota

A sintaxe do comando de seleção apresentada acima foi feita de maneira simplificada. Para a versão completa, consulte o seguite tópico do manual do PostgreSQL: SELECT.

1.10.1. Consultas Simples

Nesta seção veremos exemplos de consultas que envolvem uma única tabela como entrada.

1. Recuperar os dados do contrantante Leonardo:

Solução:

SELECT *
  FROM contratante
 WHERE nome = 'Leonardo';

Saída:

     cpf     |   nome   | genero |  classificacao  | data_cadastro
-------------+----------+--------+-----------------+---------------
 55555555501 | Leonardo | m      | grande produtor | 2024-10-10
(1 row)

Nessa consulta usamos três cláusulas:

SELECT: Nesta cláusula usamos o caracter especial * que é expandido para a lista de todas as colunas e, por isso, obtivemos uma linha com 05 valores como mostrado acima.
FROM: Nesta cláusula especificamos a tabela contratante como fonte da consulta.
WHERE: Nesta cláusula especificamos uma expressão lógica que realiza o filtro das linhas desejadas. No exemplo acima, temos apenas um contratante com o nome Leonardo e, portanto, somente uma linha satisfaz a condição nome = 'Leonardo'.

2. Recuperar o CPF e nome dos contratantes classificados como “grande produtor” rural:

Solução:

SELECT cpf, nome
  FROM contratante
 WHERE classificacao = 'grande produtor';

Saída:

     cpf     |   nome
-------------+----------
 22222222201 | Karine
 55555555501 | Leonardo
(2 rows)

A cláusula SELECT da consulta acima contém um subconjunto das colunas da tabela contratante: cpf e nome. Utilizamos o separador , para listar as colunas nessa cláusula. Essa cláusula nos permite, entre outra coisas, controlar a ordem de apresentação das colunas.

3. Recuperar o CPF e os três primeiros caracteres do nome dos contratantes registrados no ano de 2022 do gênero masculino (m):

Solução:

SELECT cpf, left(nome, 3) AS iniciais_nome
  FROM contratante
 WHERE (genero = 'm') AND (extract(YEAR FROM data_cadastro) = 2022);

Saída:

     cpf     | iniciais_nome
-------------+---------------
 11111111101 | Gil
 44444444401 | Tha
(2 rows)

Na consulta acima:

O segundo elemento da cláusula SELECT utilizou uma função chamada left para extrair no máximo três caracteres de um nome. Nesse elemento, também utilizamos a palavra-chave AS para criar um novo rótulo para a coluna de saída: iniciais_nome.
A clausula WHERE possui uma expressão usando o e-lógico (AND). Portanto, apenas as linhas que tenham simultaneamente o caracter m como gênero e a parte do ano da data de cadastro igual a 2022 serão selecionadas no resultado final.

1.10.2. Junção de Tabelas

Nesta seção veremos exemplos de consultas que envolvem duas ou mais tabelas como entrada.

4. Fazer o produto cartesiano entre as tabelas operacao e gleba:

Solução:

SELECT *
  FROM operacao, gleba;

Saída:

 numero |   valor   | data_inicio |  data_fim  | gid |         geom           | operacao_numero
--------+-----------+-------------+------------+-----+------------------------+-----------------
      1 | 100000.54 | 2022-05-01  | 2022-11-30 |   1 | 0106000020421200000... |               1
      1 | 100000.54 | 2022-05-01  | 2022-11-30 |   2 | 0106000020421200000... |               2
      1 | 100000.54 | 2022-05-01  | 2022-11-30 |   3 | 0106000020421200000... |               3
      1 | 100000.54 | 2022-05-01  | 2022-11-30 |   4 | 0106000020421200000... |               3
    ... |       ... |        ...  |        ... | ... |                    ... |             ...
      9 | 234000.56 | 2023-05-01  | 2023-11-30 |   8 | 0106000020421200000... |               7
      9 | 234000.56 | 2023-05-01  | 2023-11-30 |   9 | 0106000020421200000... |               7
      9 | 234000.56 | 2023-05-01  | 2023-11-30 |  10 | 0106000020421200000... |               8
      9 | 234000.56 | 2023-05-01  | 2023-11-30 |  11 | 0106000020421200000... |               9
(99 rows)

A cláusula FROM permite especificar uma lista de tabelas (ou itens de dados). Na consulta acima, a cláusula FROM realizou o produto cartesiano entre as duas tabelas. Como temos 09 operações de crédito e 11 glebas, o resultado contém 99 linhas, isto é, todas as linhas da tabela operacao pareadas com todas as linhas da tabela gleba.

Repare também que por termos usado o caractere * na cláusula SELECT, todas as colunas das duas tabelas participaram do resultado final. Na cláusula SELECT é possível controlar a lista de colunas de saída especificando o nome qualificado da coluna, isto é, o nome-tabela.nome-coluna:

SELECT operacao.*, gleba.gid
  FROM operacao, gleba;

Saída:

 numero |   valor   | data_inicio |  data_fim  | gid
--------+-----------+-------------+------------+-----
      1 | 100000.54 | 2022-05-01  | 2022-11-30 |   1
      1 | 100000.54 | 2022-05-01  | 2022-11-30 |   2
      1 | 100000.54 | 2022-05-01  | 2022-11-30 |   3
      1 | 100000.54 | 2022-05-01  | 2022-11-30 |   4
    ... |       ... |        ...  |        ... | ...
      9 | 234000.56 | 2023-05-01  | 2023-11-30 |   8
      9 | 234000.56 | 2023-05-01  | 2023-11-30 |   9
      9 | 234000.56 | 2023-05-01  | 2023-11-30 |  10
      9 | 234000.56 | 2023-05-01  | 2023-11-30 |  11
(99 rows)

Nesse último exemplo, o item operacao.* na cláusula SELECT é expandido para todas as colunas da tabela operacao, isto é, as colunas numero, valor, data_inicio e data_fim. Já a expressão gleba.gid indica que queremos apenas a coluna gid da tabela gleba no resultado.

5. Juntar as linhas correlatas das tabelas operacao e gleba:

Nota

Chamamos este tipo de consulta de junção entre tabelas.

Solução:

SELECT *
  FROM operacao, gleba
 WHERE operacao.numero = gleba.operacao_numero;

Saída:

 numero |   valor   | data_inicio |  data_fim  | gid |       geom             | operacao_numero
--------+-----------+-------------+------------+-----+------------------------+-----------------
| 100000.54 | 2022-05-01  | 2022-11-30 |   1 | 0106000020421200000... |               1
| 110000.45 | 2023-04-01  | 2023-10-30 |   2 | 0106000020421200000... |               2
| 730000.00 | 2022-05-01  | 2022-11-30 |   3 | 0106000020421200000... |               3
| 730000.00 | 2022-05-01  | 2022-11-30 |   4 | 0106000020421200000... |               3
| 200000.99 | 2021-05-01  | 2021-11-30 |   5 | 0106000020421200000... |               4
| 205000.98 | 2022-03-01  | 2022-12-31 |   6 | 0106000020421200000... |               5
| 243000.97 | 2023-06-01  | 2023-09-30 |   7 | 0106000020421200000... |               6
|  50000.12 | 2022-05-01  | 2022-11-30 |   8 | 0106000020421200000... |               7
|  50000.12 | 2022-05-01  | 2022-11-30 |   9 | 0106000020421200000... |               7
| 900000.73 | 2023-05-01  | 2023-11-30 |  10 | 0106000020421200000... |               8
| 234000.56 | 2023-05-01  | 2023-11-30 |  11 | 0106000020421200000... |               9
(11 rows)

Repare na saída acima que agora os valores nas linhas para a primeira coluna numero são iguais na coluna operacao_numero. Esse exemplo mostra como usar colunas relacionadas para busca da informação apropriada.

Outra forma de realizar a consulta acima é utilizar a palavra-chave INNER JOIN, como indicado abaixo:

SELECT *
  FROM operacao INNER JOIN gleba ON operacao.numero = gleba.operacao_numero;

Saída:

 numero |   valor   | data_inicio |  data_fim  | gid |       geom             | operacao_numero
--------+-----------+-------------+------------+-----+------------------------+-----------------
| 100000.54 | 2022-05-01  | 2022-11-30 |   1 | 0106000020421200000... |               1
| 110000.45 | 2023-04-01  | 2023-10-30 |   2 | 0106000020421200000... |               2
| 730000.00 | 2022-05-01  | 2022-11-30 |   3 | 0106000020421200000... |               3
| 730000.00 | 2022-05-01  | 2022-11-30 |   4 | 0106000020421200000... |               3
| 200000.99 | 2021-05-01  | 2021-11-30 |   5 | 0106000020421200000... |               4
| 205000.98 | 2022-03-01  | 2022-12-31 |   6 | 0106000020421200000... |               5
| 243000.97 | 2023-06-01  | 2023-09-30 |   7 | 0106000020421200000... |               6
|  50000.12 | 2022-05-01  | 2022-11-30 |   8 | 0106000020421200000... |               7
|  50000.12 | 2022-05-01  | 2022-11-30 |   9 | 0106000020421200000... |               7
| 900000.73 | 2023-05-01  | 2023-11-30 |  10 | 0106000020421200000... |               8
| 234000.56 | 2023-05-01  | 2023-11-30 |  11 | 0106000020421200000... |               9
(11 rows)

6. Quais os contratos realizados no CPF 55555555501?

Solução:

SELECT contratante.cpf, nome, operacao.numero, valor

  FROM contratante, contratante_operacao, operacao

 WHERE contratante.cpf = contratante_operacao.cpf
   AND contratante_operacao.numero = operacao.numero
   AND contratante.cpf = '55555555501';

Saída:

     cpf     |   nome   | numero |   valor
-------------+----------+--------+-----------
 55555555501 | Leonardo |      8 | 900000.73
(1 row)

Dica

Para mais detalhes das consultas com junção entre tabelas, consulte o manual do PostgreSQL nas seções 2.6. Joins Between Tables e 7.2. Table Expressions.

1.10.3. Consultas de Agregação

Temos vários operadores que trabalham com grupos de registros, sendo muito úteis para uso com a cláusula GROUP BY:

AVG: média dos valores da coluna.
SUM: soma dos valores da coluna.
COUNT: número de valores na coluna.
MAX: maior valor na coluna.
MIN: menor valor na coluna.

Nesta seção vamos explorar o uso das cláusulas GROUP BY e HAVING na construção de consultas que permitam realizar uma sumarização de valores a partir de grupos de linhas.

7. Quantos contratos existem?

Solução:

Neste caso precisamos apenas usar uma função de agregação que conte o número de linhas de uma tabela, tratando todas as linhas como um único grupo. A função de agregação COUNT pode ser usada para esta finalidade:

SELECT COUNT(*) AS num_contratos
  FROM operacao;

Saída:

 num_contratos
---------------
             9
(1 row)

8. Qual o número de operacões por ano?

Solução:

  SELECT extract(YEAR FROM data_inicio) AS ano,
         COUNT(*) AS num_operacoes
    FROM operacao
GROUP BY  extract(YEAR FROM data_inicio)
ORDER BY ano;

Saída:

 ano  | num_operacoes
------+---------------
 2021 |             1
 2022 |             4
 2023 |             4
(3 rows)

9. Quantas operações cada CPF realizou?

Solução:

  SELECT cpf, COUNT(*) AS num_operacoes

    FROM contratante INNER JOIN contratante_operacao USING (cpf)

GROUP BY cpf

ORDER BY cpf;

Saída:

     cpf     | num_operacoes
-------------+---------------
 11111111101 |             3
 22222222201 |             1
 33333333301 |             3
 44444444401 |             1
 55555555501 |             1
 66666666601 |             1
(6 rows)

10. Quais CPF’s realizaram mais do que um empréstimo?

Solução:

Esta consulta precisará contar o número de operações para cada pessoa e em seguida aplicar um filtro no resultado do grupo. Isto indica que precisaremos utilizar a cláusula HAVING como mostrado abaixo:

SELECT cpf, COUNT(*) AS num_operacoes

    FROM contratante INNER JOIN contratante_operacao USING (cpf)

GROUP BY cpf

  HAVING COUNT(*) > 1

ORDER BY cpf;

Resultado:

     cpf     | num_operacoes
-------------+---------------
 11111111101 |             3
 33333333301 |             3
(2 rows)

1.10.4. Funções de Janela (Window Functions)

Além das funções de agregação existem funções que operam em um conjunto relacionado de linhas. Essas funções são chamadas de funções de janela (window functions). Duas funções de janela muito úteis são: rank e dense_rank. Vamos explorar o uso da função dense_rank nessa seção.

11. Qual a posição (ranking) de cada CPF em relação ao valor de empréstimos realizados ao longo dos anos?

Solução:

Vamos começar construindo uma consulta que liste o ano de matricula, número de matrícula, nome e total de créditos cursados por cada estudante:

  SELECT cpf,
         extract(YEAR FROM data_inicio) AS ano,
         SUM(valor) AS total_emprestimo

    FROM operacao INNER JOIN contratante_operacao USING (numero)

GROUP BY cpf, ano

ORDER BY cpf, ano;

Resultado:

     cpf     | ano  | total_emprestimo
-------------+------+------------------
 11111111101 | 2022 |        150000.66
 11111111101 | 2023 |        110000.45
 22222222201 | 2022 |        730000.00
 33333333301 | 2021 |        200000.99
 33333333301 | 2022 |        205000.98
 33333333301 | 2023 |        243000.97
 44444444401 | 2022 |         50000.12
 55555555501 | 2023 |        900000.73
 66666666601 | 2023 |        234000.56
(9 rows)

Em seguida, vamos construir uma nova consulta que utilize a consulta acima como subconsulta para podermos utilizar a função de janela dense_rank para criar partições no resultado:

  SELECT contratos.*,
         dense_rank() OVER (PARTITION BY ano ORDER BY total_emprestimo DESC) AS rank_valor

    FROM (

          SELECT cpf,
                 extract(YEAR FROM data_inicio) AS ano,
                 SUM(valor) AS total_emprestimo

            FROM operacao INNER JOIN contratante_operacao USING (numero)

        GROUP BY cpf, ano

        ORDER BY cpf, ano

         ) AS  contratos

ORDER BY ano ASC, rank_valor ASC;

Resultado:

     cpf     | ano  | total_emprestimo | rank_valor
-------------+------+------------------+------------
 33333333301 | 2021 |        200000.99 |          1
 22222222201 | 2022 |        730000.00 |          1
 33333333301 | 2022 |        205000.98 |          2
 11111111101 | 2022 |        150000.66 |          3
 44444444401 | 2022 |         50000.12 |          4
 55555555501 | 2023 |        900000.73 |          1
 33333333301 | 2023 |        243000.97 |          2
 66666666601 | 2023 |        234000.56 |          3
 11111111101 | 2023 |        110000.45 |          4
(9 rows)

Repare na consulta que a função de janela dense_rank() particiona as linhas resultantes da subconsulta pela coluna de saída ano e cria um ranking baseado na ordem dentro de cada partição pelo valor da coluna total_emprestimo.

Dica

Para mais detalhes das consultas de janela, consulte o manual do PostgreSQL nas seções 3.5. Window Functions e 9.22. Window Functions.

1.10.5. Consultas Espaciais

12. Qual a área em hectares de cada gleba?

Solução:

SELECT gid,
       ST_Area(geom) AS area
  FROM gleba;

Resultado:

 gid |          area
-----+------------------------
| 1.0874952000010346e-05
| 1.9811417999996206e-05
|  3.292848000000658e-05
|  2.306288000001502e-05
|  8.811599999997503e-06
|   3.23872467915163e-06
| 2.5555322999988482e-05
|  0.0001896014250000293
|  1.989994500000845e-05
| 3.0835349999948616e-06
|   1.85541250005088e-05
(11 rows)

INSERT INTO spatial_ref_sys (srid, proj4text)
    VALUES (100000, '+proj=aea +lat_1=-2 +lat_2=-22 +lat_0=-12 +lon_0=-54 +x_0=5000000 +y_0=10000000 +ellps=GRS80 +units=m +no_defs ');

SELECT gid,
       ST_Area(ST_Transform(geom, 100000)) / 10000.0 AS area
  FROM gleba;

Resultado:

 gid |        area
-----+--------------------
| 12.072867606637685
| 21.993748629081985
|  36.67122515757885
| 25.759911019875354
|  9.845611033112005
|  3.616752242092826
| 28.555074776128993
| 209.41624609146064
| 21.965721476803974
|  3.436200736973672
| 20.679512542366254
(11 rows)

13. Listar o nome do município onde cada gleba encontra-se localizada.

Solução:

SELECT gleba.gid, geocodigo, nome
  FROM municipio,
       gleba
 WHERE ST_Contains(municipio.geom, gleba.geom);

 gid | geocodigo |       nome
-----+-----------+------------------
| 4105409   | Chopinzinho
| 4105409   | Chopinzinho
| 4109401   | Guarapuava
| 4109401   | Guarapuava
| 4117057   | Nova Laranjeiras
| 4117057   | Nova Laranjeiras
| 4117057   | Nova Laranjeiras
| 4117602   | Palmas
| 4117602   | Palmas
| 4127007   | Teixeira Soares
| 4127007   | Teixeira Soares
(11 rows)

14. Qual o valor do financiamento anual em cada município?

Solução:

SELECT extract(YEAR FROM data_inicio) AS ano,
       geocodigo,
       nome,
       SUM(valor) AS total,
       COUNT(*) AS num_operacoes
  FROM municipio,
       gleba,
       operacao
 WHERE operacao.numero = gleba.operacao_numero
   AND ST_Contains(municipio.geom, gleba.geom)
GROUP BY ano, geocodigo, nome
ORDER BY ano, geocodigo, nome;

 ano  | geocodigo |       nome       |   total    | num_operacoes
------+-----------+------------------+------------+---------------
| 4117057   | Nova Laranjeiras |  200000.99 |             1
| 4105409   | Chopinzinho      |  100000.54 |             1
| 4109401   | Guarapuava       | 1460000.00 |             2
| 4117057   | Nova Laranjeiras |  205000.98 |             1
| 4117602   | Palmas           |  100000.24 |             2
| 4105409   | Chopinzinho      |  110000.45 |             1
| 4117057   | Nova Laranjeiras |  243000.97 |             1
| 4127007   | Teixeira Soares  | 1134001.29 |             2
(8 rows)

1.10.6. Criando Tabelas a partir de Consultas

Podemos utilizar os comandos CREATE TABLE e SELECT em conjunto para criar tabelas a partir do resultado de consultas.

A sintaxe básica desse comando é a seguinte:

CREATE TABLE nome-tabela AS consulta;

Vamos criar uma nova tabela chamada buffer_gleba que conterá um buffer negativo de 30 metros das geometrias das glebas:

CREATE TABLE buffer_gleba AS
    SELECT gid,
           ST_Transform(ST_Buffer(ST_Transform(geom, 100000), -30), 4674)::geometry(multipolygon, 4674) AS geom
      FROM gleba;

Saída:

SELECT 11