SQL Avançado

Agregação

Já vimos, anteriormente, como funcionam as agregações em Álgebra Relacional. Como seria de esperar, estas estão também presentes no SQL.

Recapitulando algumas das funções que existem:

Relembrando a Álgebra Relacional, vamos poder ou não indicar quais as colunas pelas quais agrupamos valores. Para isso, utilizamos a cláusula GROUP BY.

-- Imaginemos que queremos saber a quantidade de compras feitas,
-- tanto no total como por cliente.

--   client |  price
-- ---------+-----------
--    Diogo |    20
--    José  |    15
--    Diogo |    18
--    Tiago |    12

-- Sem GROUP BY:
SELECT COUNT(*) as count FROM purchase;

--   count
-- --------
--     4
-- (1 row)

-- Com GROUP BY:
SELECT client, COUNT(*) as count FROM purchase
  GROUP BY client;

--   client |  count
-- ---------+---------
--    Diogo |    2
--    José  |    1
--    Tiago |    1
-- (3 rows)

Caso queiramos filtrar linhas por uma condição que contém valores agrupados, rapidamente reparamos que tal não é possível com a cláusula WHERE: esta cláusula é executada antes da agregação. Para resolver este problema, temos de usar a cláusula HAVING, que funciona de forma semelhante ao WHERE, mas é executada após a agregação.

-- Pegando no exemplo anterior,
-- vamos agora querer os clientes com mais do que 1 compra.

SELECT client, COUNT(*) FROM frigu
  GROUP BY client
  HAVING COUNT(*) > 1;

--   client |  count
-- ---------+---------
--    Diogo |    2
-- (1 row)

Nested Queries

Além de podermos fazer queries "simples", podemos também executar queries dentro de queries, utilizando o seu resultado para o FROM, o JOIN, o IN, etc.

• FROM

  Podemos utilizar o resultado de uma query no FROM, efetuando depois o SELECT dos atributos que queremos ou até mesmo operações mais avançadas (já que a utilização no exemplo abaixo é altamente redundante).

  -- Obter os nomes de todos os alunos nascidos em ou depois de 2002:
  SELECT S.student_name FROM (
    SELECT * FROM student WHERE birthday >= '2002-01-01'
  ) AS S;
  
  -- Esta query é claramente equivalente a
  SELECT student_name FROM student WHERE birthday >= '2002-01-01'

• JOIN

  Do mesmo modo, podemos utilizar o resultado de uma query no JOIN (em qualquer um deles).

  -- Obter os nomes dos alunos inscritos em 5 ou mais disciplinas:
  SELECT student.student_name FROM student
    NATURAL JOIN (
      SELECT ist_id FROM enrollment
      GROUP BY ist_id
      HAVING COUNT(*) >= 5
    );
  
  -- Esta query é equivalente a
  SELECT student_name FROM student
    NATURAL JOIN enrollment
    GROUP BY ist_id, student_name
    HAVING COUNT(*) >= 5;

• IN

  A cláusula IN pode ser usada numa condição para verificar se um valor está contido num conjunto. Em vez de especificarmos um conjunto fixo, podemos especificar uma query que retorne apenas uma coluna.

  -- Obter os delegados de LEIC-A:
  SELECT student.student_name FROM student
    WHERE student.ist_id IN (
      SELECT delegate.ist_id FROM delegate
        WHERE delegate.course = 'LEIC-A'
    );

Operações em Conjuntos

É possível efetuar comparações entre um valor e um conjunto, verificando, por exemplo, se existe um valor igual, se todos os valores são iguais, se existe um valor maior, etc.

Para isso, vamos introduzir duas novas cláusulas: ALL e ANY, com as seguintes sintaxes:

<value> <operator> ALL (<set>)
<value> <operator> ANY (<set>)

Como se pode deduzir pelos nomes, a cláusula ALL verifica se <value> <operator> <set element> para todos os valores de <set>, enquanto a cláusula ANY verifica se <value> <operator> <set element> para pelos menos um valor de <set>.

Estas duas cláusulas são úteis para calcularmos o máximo de um conjunto (ou o mínimo). Vejamos como as podemos utilizar para determinar os alunos com melhores notas e os alunos inscritos ao maior número de disciplinas:

-- Determinar o IST ID dos alunos com a melhor nota e o respetivo valor
-- (poderíamos fazer um JOIN para obter o nome):
SELECT ist_id, grade FROM grades
  WHERE grade >= ALL (
    SELECT grade FROM grades WHERE course = 'BD'
  ) AND course = 'BD';

-- Determinar o IST ID dos alunos inscritos ao maior
-- número de disciplinas (e o respetivo valor):
SELECT ist_id, COUNT(*) FROM enrollment
  GROUP BY ist_id
  HAVING COUNT(*) >= (
    SELECT COUNT(*) FROM enrollment
    GROUP BY ist_id
  );

NULL

Em SQL, tal como em algumas linguagens de programação, é possível ter valores null. Isto pode ser tanto algo bom como algo mau: por um lado, ganhamos a flexibilidade de poder omitir certos valores, mas, por outro, sujeitamo-nos a comportamentos inesperados. Tal deve-se ao facto que o comportamento do NULL em SQL é ambíguo e muda de situação para situação, como iremos ver. Geralmente, estes valores são representados como um espaço vazio, isto é, ausência de valor.

CREATE TABLE student (
  ist_id VARCHAR(15) NOT NULL,
  student_name VARCHAR(255) NOT NULL,
  PRIMARY KEY(ist_id)
);

Para percebermos o comportamento do NULL, vamos olhar para o seu comportamento em vários tipos de expressões. É preciso ter em mente que nem todas as funcionalidades em SQL seguem estas regras quando em contacto com o NULL, como iremos ver abaixo.

• Expressões Aritméticas

  Todas as expressões aritméticas que contêm NULL irão resultar em NULL.

  Expressão	Resultado
  5 + NULL	NULL
  NULL * 10	NULL
  5 * 10 + NULL	NULL

• Expressões Lógicas

  As expressões lógicas que dependem do NULL para saber o seu valor lógico irão resultar em NULL.

  Expressão	Resultado
  NULL AND TRUE	NULL
  NULL AND FALSE	FALSE
  NULL OR TRUE	TRUE
  NULL OR FALSE	NULL

  É de realçar que, nas situações em que o valor de NULL não afeta o resultado da expressão lógica, o SGBD vai-nos dar um valor de TRUE ou FALSE.

• Expressões Relacionais

  As expressões relacionais vão resultar num valor unknown se contiverem um valor NULL. A cláusula WHERE trata os valores unknown como FALSE.

  Expressão	Resultado
  NULL = NULL	unknown
  NULL = 5	unknown
  NULL <> NULL	unknown
  NULL <> 5	unknown

Este comportamento leva-nos a uma situação engraçada: se tentarmos obter os valores nulos de uma tabela com o operador =, não vamos obter qualquer resultado:

-- O operador = não funciona:
SELECT * FROM student WHERE birthday = NULL;

--  ist_id | student_name | birthday
-- --------+--------------+----------
-- (0 rows)

Para resolvermos esta situação, temos de usar um operator especial, o IS:

-- O operador IS já funciona:
SELECT * FROM student WHERE birthday IS NULL;

--    ist_id   | student_name | birthday
-- ------------+--------------+----------
--  ist1123456 | Diogo        |
-- (1 row)

Mas como é que aparecem valores NULL? Uma das formas é óbvia: são inseridos voluntariamente pelos utilizadores da base de dados.

Podem também aparecer como o resultado de outer joins, como já vimos anteriormente.

Para além disso, também podem aparecer valores NULL como resultado de funções de agregação. Caso tentemos fazer um MAX, MIN, AVG, SUM, etc. num conjunto vazio, vamos obter um valor NULL.

Por falar em funções de agregação, estas desobedecem às regras de aritmética do null: só o COUNT(*) é que se comporta como esperado, todas as outras ignoram valores NULL. Por exemplo, ao efetuar SUM(col), os valores a NULL não são somados, indo contra o princípio que x + NULL é NULL.

Substituir NULLs

Pode-nos ser útil substituir os valores NULL numa tabela por um valor predefinido. Para tal, podemos usar a cláusula COALESCE.

Esta cláusula retorna o primeiro dos seus valores que não é NULL.

SELECT ist_id, COALESCE(grade, 0) AS grade FROM grades
  WHERE course = 'BD';

--    ist_id   | grade
-- ------------+-------
--  ist1123456 |    20
--  ist1654321 |     0
--  ist1123123 |    18
-- (3 rows)

Correlation

Usando uma técnica chamada correlation, podemos efetuar nested queries que acedem aos valores da query principal, de forma a verificar se existe algum valor ou se esses valores são únicos. Para isto, utilizamos as cláusulas EXISTS e UNIQUE (que não existe em PostgreSQL), respetivamente.

-- Obter o nome dos alunos que estão inscritos a pelo menos
-- uma disciplina:
SELECT student.student_name FROM student
  WHERE EXISTS (
    SELECT * FROM enrollment
      WHERE enrollment.ist_id = student.ist_id
  );

-- A query acima é equivalente a
SELECT student.student_name FROM student
  WHERE 0 <> (
    SELECT COUNT(*) FROM enrollment
      WHERE enrollment.ist_id = student.ist_id
  );

Como é evidente pelo exemplo acima, a cláusula EXISTS vai retornar verdadeiro caso a sub query não esteja vazia.

Vejamos agora a cláusula UNIQUE:

-- Obter o nome dos alunos que estão inscritos, no máximo,
-- a uma disciplina:
SELECT student.student_name FROM student
  WHERE UNIQUE (
    SELECT student.ist_id FROM enrollment
      WHERE enrollment.ist_id = student.ist_id
  );

Esta cláusula retorna verdadeiro caso a sub query não tenha linhas repetidas. Caso a query retorne uma tabela vazia, esta cláusula retorna verdadeiro.

Cross Join

O cross join (ou produto cartesiano) permite-nos efetuar o mesmo que o produto cartesiano em álgebra relacional.

Por exemplo, se efetuarmos um cross join entre as tabelas student e enrollment, vamos obter uma nova tabela com as colunas de student e de enrollment, contendo todas as combinações possíveis entre si, tal como na álgebra relacional.

-- Efetuar um cross join entre student e enrollment
SELECT * FROM student, enrollment;

Podemos, como seria de esperar, utilizar a cláusula WHERE para filtrar os resultados. Caso igualemos as colunas correspondentes, vamos obter uma query equivalente a um inner join.

SELECT student.student_name, student.ist_id, enrollment.course
  FROM student, enrollment
  WHERE student.ist_id = enrollment.ist_id;

-- é equivalente a

SELECT student.student_name, student.ist_id, enrollment.course
  FROM student
  INNER JOIN enrollment
    ON student.ist_id = enrollment.ist_id;

Divisão

A cláusula DIVIDE não existe na maior parte dos SGBD, e é, por isso, frequentemente implementada como dupla negação.

Voltando às nossas tabelas enrollment e course, vamos querer efetuar a divisão de enrollment por course, de forma a obter os alunos que estão inscritos a todas as disciplinas.

Considerem-se os seguintes dados para os exemplos abaixo:

Para isto, podemos utilizar a seguinte query, recorrendo ao EXCEPT:

SELECT DISTINCT ist_id FROM enrollment E
WHERE NOT EXISTS (
  SELECT course_acronym
  FROM course
  EXCEPT
  SELECT course.course_acronym
  FROM course
  INNER JOIN enrollment
    ON enrollment.course_acronym = course.course_acronym
  WHERE enrollment.ist_id = E.ist_id
);

Vamos dissecar, com calma, a query acima. Dentro dos parêntesis, estamos a obter a lista de todos os cursos que um dado aluno não frequenta. De seguida, obtemos todos os alunos para os quais esse conjunto é vazio. Efetuamos, assim, a divisão.