LATERAL é seu amigo para criar variáveis de coluna locais em SQL – Java, SQL e jOOQ.

[ad_1]

O SQL padrão WITH foi extremamente útil na estruturação de consultas SQL. Em vez de aninhar tudo em tabelas derivadas ilegíveis como esta:

SELECT actor_id, name, COUNT(*)
FROM (
  SELECT actor_id, first_name || ' ' || last_name AS name
  FROM actor
) AS a
JOIN film_actor AS fa USING (actor_id)
GROUP BY actor_id, name
ORDER BY COUNT(*) DESC
LIMIT 5

As pessoas começaram a avançar a lógica, assim como em qualquer outra linguagem de programação, onde declaramos as coisas primeiro, lexicalmente, e depois as usamos:

WITH a AS (
  SELECT actor_id, first_name || ' ' || last_name AS name
  FROM actor
)
SELECT actor_id, name, COUNT(*)
FROM a
JOIN film_actor AS fa USING (actor_id)
GROUP BY actor_id, name
ORDER BY COUNT(*) DESC
LIMIT 5;

Ambas as consultas produzirão os 5 atores com mais filmes do banco de dados Sakila:

|actor_id|name          |count|
|--------|--------------|-----|
|107     |GINA DEGENERES|42   |
|102     |WALTER TORN   |41   |
|198     |MARY KEITEL   |40   |
|181     |MATTHEW CARREY|39   |
|23      |SANDRA KILMER |37   |

Mas algo sobre isso está fora. Temos que criar uma nova tabela ae use essa tabela em vez da original actor tabela. Parece que estamos usando a abstração errada aqui para obter o resultado de simplesmente criar um alias para uma expressão de coluna.

Os exemplos ainda são muito simples, mas todos nós já vimos os monstros SQL de 500 linhas onde as tabelas derivadas cascateiam para vários níveis de profundidade.

LATERAL para o resgate

O padrão SQL:1999 especifica o <lateral derived table>que é a maneira do SQL de permitir uma tabela derivada (uma subconsulta no FROM cláusula) para acessar todos os objetos lexicalmente anteriores na FROM cláusula. É um pouco estranho em termos de sintaxe, pessoalmente acho que o Microsoft SQL Server tem uma solução muito melhor para esse conceito via APPLY. Oracle suporta ambas as sintaxes (padrão e T-SQL). Db2, Firebird, MySQL, PostgreSQL só tem LATERAL.

O principal caso de uso do uso LATERAL é fazer consultas sofisticadas, como consultas top-n-per-category. Mas usando LATERALagora também podemos mover o alias da coluna para onde ele pertence, conceitualmente, ao lado (“lateralmente”) do JOIN mesas no FROM cláusula. Existem basicamente duas maneiras de fazer isso:

Como um elemento de lista de tabela

As listas de tabelas ficaram um pouco fora de moda, exceto entre os fãs hardcore da velha escola Oracle que gostam de continuar usando (+) para junções externas, festejando como se fosse 1989. Mas com LATERAL, talvez você possa apreciar listas de tabelas novamente? Veja isso:

SELECT actor_id, name, COUNT(*)
FROM 
  actor JOIN film_actor AS fa USING (actor_id),
  LATERAL (SELECT first_name || ' ' || last_name AS name) AS t
GROUP BY actor_id, name
ORDER BY COUNT(*) DESC
LIMIT 5;

Não é maravilhoso? O último elemento da FROM cláusula é um conjunto de variáveis locais derivadas do anterior FROM elementos da cláusula. Podemos fazer isso em cascata também!

SELECT actor_id, name, name_length, COUNT(*)
FROM 
  actor JOIN film_actor AS fa USING (actor_id),
  LATERAL (SELECT first_name || ' ' || last_name AS name) AS t1,
  LATERAL (SELECT length(name) AS name_length) AS t2
GROUP BY actor_id, name, name_length
ORDER BY COUNT(*) DESC
LIMIT 5;

Isso produz:

|actor_id|name          |name_length|count|
|--------|--------------|-----------|-----|
|107     |GINA DEGENERES|14         |42   |
|102     |WALTER TORN   |11         |41   |
|198     |MARY KEITEL   |11         |40   |
|181     |MATTHEW CARREY|14         |39   |
|23      |SANDRA KILMER |13         |37   |

Um elemento de árvore de junção

Se você não está prestes a ressuscitar a antiga sintaxe da lista de tabelas para este caso de uso, você sempre pode simplesmente CROSS JOIN algum <lateral derived table> exatamente onde ela pertence. Por exemplo:

SELECT actor_id, name, COUNT(*)
FROM actor
CROSS JOIN LATERAL (SELECT first_name || ' ' || last_name AS name) AS t
JOIN film_actor AS fa USING (actor_id)
GROUP BY actor_id, name
ORDER BY COUNT(*) DESC
LIMIT 5;

Novamente, se você precisar de várias etapas em cascata de variáveis locais, apenas CROSS JOIN mais tal <lateral derived table> objetos:

SELECT actor_id, name, name_length, COUNT(*)
FROM actor
CROSS JOIN LATERAL (SELECT first_name || ' ' || last_name AS name) AS t1
CROSS JOIN LATERAL (SELECT length(name) AS name_length) AS t2
JOIN film_actor AS fa USING (actor_id)
GROUP BY actor_id, name, name_length
ORDER BY COUNT(*) DESC
LIMIT 5;

A troca é simples:

WITH ajuda a declarar tudo antecipadamente, antes de usar as coisas. Mas, como tabelas derivadas comuns, elas exigem que você pense mais sobre como aninhar as coisas.
LATERAL ajuda a declarar variáveis ao lado das tabelas originais que contêm o conteúdo das variáveis, sem precisar derivar essas tabelas originais. O restante da consulta ainda pode funcionar com a tabela original não modificada e não derivada, o que facilita muito a refatoração e o raciocínio sobre as coisas, pelo menos na minha opinião.

Não posso enfatizar o suficiente como isso é útil, como você pode ver no exemplo. Porque o FROM cláusula é a primeira cláusula da sua consulta, na ordem lógica das operações. Isso significa que qualquer coisa que você declarar em FROM pode ser usado em qualquer outro lugar na consulta! Os exemplos estão usando as expressões de coluna em GROUP BYpor exemplo.

Usando T-SQL APPLY

Oracle e SQL Server têm uma sintaxe que pessoalmente acho mais intuitiva: APPLY. Porque o que estamos fazendo aqui é aplicar uma função (ou subconsulta, que é um tipo de função) a uma tabela, criando um produto cruzado entre a tabela e o resultado da função (ou subconsulta).

Veja este exemplo Oracle:

SELECT actor_id, name, name_length, COUNT(*)
FROM actor
CROSS APPLY (SELECT first_name || ' ' || last_name AS name FROM dual)
CROSS APPLY (SELECT length(name) AS name_length FROM dual)
JOIN film_actor USING (actor_id)
GROUP BY actor_id, name, name_length
ORDER BY COUNT(*) DESC
FETCH FIRST 5 ROWS ONLY;

Ele faz exatamente a mesma coisa que o anterior CROSS JOIN LATERAL exemplo.