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:

Função Descrição
COUNT(*) / COUNT(1) Número de linhas
COUNT([DISTINCT] col) Número de linhas com valores [distintos] não nulos de col
SUM([DISTINCT] col) Soma dos valores [distintos] não nulos de col
AVG([DISTINCT] col) Média dos valores [distintos] não nulos de col
MAX(col) O valor máximo entre os valores não nulos de col
MIN(col) O valor mínimo entre os valores não nulos de col

Valores Nulos

Como veremos mais abaixo, os valores nulos têm um comportamento à parte.

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>.

Relação com IN e NOT IN

Podemos intuitivamente reparar que IN é equivalente a = ANY e que NOT IN é equivalente a <> ALL: um elemento só pertence a um conjunto se for igual a algum elemento do mesmo, e não pertence a um conjunto se for diferente de todos os seus elementos.

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.

Comportamento predefinido

Em SQL, quando criamos uma tabela, todas as colunas são nullable, isto é, podem ter valores null. Para alterarmos este comportamento, deveremos usar a restrição NOT NULL na coluna.

Por exemplo:

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:

Enrollment:

ist_id course_acronym
ist1123456 BD
ist1123456 IA
ist1654321 BD
ist1123123 BD
ist1123123 IA

Course:

course_acronym
BD
IA

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.