O que é SQL Avançado?

Você está confuso sobre as habilidades avançadas em SQL? Quais são elas? Este artigo explicará o que SQL avançado pode significar, especialmente porque o usamos em LearnSQL.com.br.

Tenho certeza de que você encontra as frases "habilidades SQL avançadas" ou "tópicos SQL avançados" com muita freqüência. Você leu um artigo sobre SQL avançado e está satisfeito com a facilidade com que estes tópicos avançados parecem ser. Então você conversa com alguém e vê que ele considera tudo o que você sabe como conhecimento básico de SQL. Como você se define? Você se considera um usuário de SQL básico, intermediário ou avançado?

SQL Avançado Está em todos os lugares

SQL Avançado está em toda parte. Bem, a palavra "avançado" é, pelo menos. É usada muito comumente por aprendizes de SQL e usuários de SQL. Você pode encontrá-la nas descrições dos cursos de SQL, nos anúncios de emprego e nas perguntas das entrevistas de emprego. Está na literatura SQL. Você a ouve quando os colegas estão falando no trabalho. Está em inúmeros artigos tentando definir o que é SQL avançada.

Na verdade, não estou tentando lhe dar uma definição de SQL avançado. Estou tentando lhe dizer outra coisa: não há uma única definição de SQL avançado! E você deve parar de procurar por ela. O que você deveria fazer ao invés disso?

Abraçar a Inconsistência

Isso mesmo! Você deve aceitar que o termo 'SQL avançado' é usado de forma inconsistente. Significa coisas diferentes, dependendo do contexto e de quem está usando o termo.

É apenas lógico que SQL avançado incluiria uma coisa para alguém que escreve relatórios SQL e algo totalmente diferente para alguém que contrata um analista de dados. Um desenvolvedor de software certamente terá mais uma definição do que o SQL avançado inclui.

Você entendeu. SQL Avançado não pode ter apenas uma definição. Quando você estiver lendo sobre habilidades avançadas em SQL, você deve sempre considerar o contexto, quem está falando e seu público.

O que o SQL Avançado pode incluir?

Por exemplo, há uma discussão realmente interessante sobre SQL avançado em Stack Overflow.

A discussão foi iniciada por alguém à procura de um trabalho SQL que observou que há muitos trabalhos que requerem "conhecimento avançado de SQL". O usuário está perguntando o que se pode esperar deste tipo de trabalho. Que conhecimento é considerado avançado?

A primeira resposta dá um trecho de código bastante longo como uma medida de conhecimento avançado. Mesmo que seja bastante longo, não é tão complicado assim. De acordo com esta resposta, SQL avançado cobre a seleção de colunas, funções agregadas como MIN() e MAX(), a declaração CASE WHEN, JOINs, a cláusula WHERE, GROUP BY, declarando variáveis, e subconsultas.

Por outro lado, a resposta a seguir considera a maioria destes tópicos como básicos ou intermediários, na melhor das hipóteses. Este usuário acredita que tópicos SQL avançados incluem funções, procedimentos armazenados, consultas hierárquicas, triggers, índices, modelagem de dados (formulários normais, chaves primárias e estrangeiras, restrições de tabelas), transações, e muito mais. Isto está muito mais próximo da minha definição de SQL avançado e do que me foi ensinado em palestras SQL. Entretanto, este foi um programa para administradores de banco de dados; compreensivelmente, este conhecimento é considerado avançado. Alguns especialistas em relatórios e analistas de dados talvez nunca precisem usar tais coisas.

É interessante notar que às vezes JOINs é considerado avançado enquanto a escrita de procedimentos armazenados ainda é considerada como conhecimento básico. Posso entender porque um usuário dá dicas sobre o problema com JOINs. Mesmo que geralmente sejam considerados conhecimentos básicos, muitos usuários de SQL aprendem tópicos muito mais avançados antes de realmente entender JOINs. É assim que os conceitos básicos se tornam facilmente conhecimentos avançados. Não é incomum encontrar alguém usando funções brilhantes, gatilhos e outras coisas mais - sem saber como escrever um simples JOIN.

O que é SQL Avançado em LearnSQL.com?

Antes de explicar o que é SQL avançado, é essencial saber o que não é. Quando você olha para nossos cursos e artigos, SQL básico/intermediário é qualquer coisa em SQL-92. (Aqui está a história e os detalhes dos padrões SQL, se você estiver interessado em saber mais). Isto inclui:

• Todos os tipos de JOINs
• Funções agregadas
• GROUP BY
• HAVING
• Subconsultas
• Operações de conjunto (UNION, UNION ALL, INTERSECT, MINUS)

Você deve estar familiarizado com estes tópicos se você afirma conhecer SQL. Estas são coisas que você deve entender antes de passar para tópicos mais avançados.

Geralmente, consideramos três tópicos como 'SQL avançado':

• Funções de janela
• Expressões comuns de tabela (CTEs)
• GROUP BY extensões (ROLLUP, CUBE, e GROUPING SETS)

Qualquer pessoa que queira aprender (ou praticar) todos os três tópicos deve conferir nossa trilha SQL Avançado . É claro que este não é o único curso SQL avançado por aí; já revisamos alguns excelentes cursos SQL avançados de outras plataformas. Por enquanto, vejamos um exemplo de cada um destes tópicos.

Funções de Janela (Window Functions) em SQL

As funções de janela SQL permitem realizar operações que muitas vezes são necessárias para criar relatórios, por exemplo, classificar dados, calcular totais em execução e médias móveis, encontrar a diferença entre linhas, etc. Não só isso, mas também é possível dividir dados em janelas, o que permite realizar operações em subconjuntos de dados em vez dos dados como um todo. Você pode aprender muito mais sobre isso em nosso curso Funções de Janela (Window Functions) em SQL .

Vamos ver um exemplo. Este código mostrará a diferença no número anual de carros vendidos, de acordo com a marca (ou seja, a marca do carro):

SELECT	car_make,
		cars_sold,
		year,
		cars_sold - LAG(cars_sold) OVER (PARTITION BY car_make ORDER BY year) AS sales_diff
FROM cars_sale;

Para obter esta informação, primeiro é preciso selecionar as colunas que se deseja no resultado: car_make, cars_sold, year. Para obter a diferença anual, subtraia a venda do ano anterior da venda do ano atual: cars_sold - LAG(cars_sold) OVER (PARTITION BY car_make ORDER BY year) AS sales_diff. Aqui, cars_sold significa as vendas do ano corrente. A função LAG() permite que você obtenha dados da linha anterior. A cláusula OVER significa que esta é uma função de janela. Depois segue a cláusula PARTITION BY, que é usada para definir a janela (subconjunto de dados) que queremos usar. Neste caso, é a car_make; isto significa que a função calculará a diferença de venda somente dentro de uma marca de carro específica. Quando ela se deparar com outra marca de carro, a função será reiniciada e começará a calcular a diferença de venda novamente.

Finalmente, a operação é ordenada por ano ascendente. Por que isso acontece? A função LAG() nos dará os dados da linha anterior. Portanto, se esta operação for realizada para cada ano em ordem crescente, o "ano anterior" significará a linha anterior. Isso é exatamente o que precisamos.

Dê uma olhada no resultado:

Você viu o valor NULL na primeira fileira? Isso porque 2015 é o primeiro ano; não há dados anteriores que possam ser deduzidos dele. Se você seguir os resultados, verá que cada linha é a diferença entre as vendas anuais da linha atual e as vendas anuais da linha anterior. Quando você chega à linha onde a Renault começa, há novamente um NULL. Isto é o que as funções de janela fazem; elas trabalham com dados dentro de uma determinada janela, eu defini a janela de acordo com o car_make, então a função de janela é reiniciada quando obtemos um novo valor nesta coluna. É apenas lógico. Por que eu deduziria as vendas Renault das vendas Nissan? Eu quero fazer isso para cada marca de carro separadamente.

Expressões comuns de tabela (CTEs)

Os CTEs permitirão que você escreva consultas complexas sem utilizar subconsultas, mantendo seu código simples e direto. Eles lhe darão a possibilidade de produzir relatórios complexos de forma rápida e eficiente. Eles também permitem que você faça alguns cálculos que de outra forma não seria capaz de fazer.

O que é uma expressão de tabela comum, você pode perguntar? É um resultado temporário que você pode usar na declaração SELECT. Funciona como uma tabela temporária - você pode juntá-la com outras tabelas, outros CTEs, ou com ela mesma.

Elas podem ser úteis se você, por exemplo, tiver que informar sobre o tempo gasto em um determinado projeto. De um lado, há uma tabela contendo dados sobre a data em que cada funcionário trabalhou neste projeto. Há também a hora de início e a hora de término. Por outro lado, há uma tabela contendo os nomes dos funcionários. É preciso produzir uma tabela mostrando o nome de cada funcionário e seu tempo médio gasto neste projeto.

Veja como o CTE pode ajudá-lo:

WITH time_worked AS (
	SELECT	employee_id,
			end_time - start_time AS time
FROM project_timesheet
)
SELECT	e.first_name,
		e.last_name,
		AVG (tw.time) AS avg_time_worked
FROM employee e 
LEFT JOIN time_worked tw 
ON e.id = tw.employee_id
GROUP BY e.first_name, e.last_name;

Como funciona este CTE? Todo CTE abre com a cláusula WITH. Então você deve nomear seu CTE; neste caso, é time_worked. Então você escreve uma declaração em SELECT. Aqui, vou usar o CTE para calcular quanto tempo cada funcionário trabalhou cada vez que trabalhou no projeto. Preciso do CTE porque não tenho estas informações declaradas explicitamente na tabela; tenho apenas os endereços start_time e end_time. Para calcular o tempo médio trabalhado, o primeiro passo é fazer com que o tempo seja trabalhado. É por isso que este CTE deduz o start_time do end_time e mostra o resultado na coluna time. Os dados são retirados da tabela project_timesheet.

Agora que escrevi o CTE, posso usá-lo na próxima declaração SELECT. Primeiro, vou obter o primeiro nome e o sobrenome da tabela employee. Depois usarei a função AVG() na coluna time do CTE time_worked. Para isso, usei a LEFT JOIN - e a utilizei exatamente como faria com qualquer outra tabela. Finalmente, os dados são agrupados pelos primeiro e último nomes dos funcionários.

O resultado é uma pequena tabela como esta:

Se os CTEs lhe interessarem, imagine o que você poderá fazer depois de terminar nosso curso Consultas Recursivas . Ah, sim - Não mencionei que um CTE pode ser recursivo, o que significa que ele se refere a si mesmo. Ao fazer isso, ele retorna o subresultado e repete o processo até retornar o resultado final. Enquanto os CTEs podem ser não recursivos, não há consultas recursivas que não sejam CTE. Se você quiser aprender as consultas recursivas, conhecer os CTEs é uma obrigação.

GROUP BY Extensões

As extensões do SQL GROUP BY lhe oferecem possibilidades adicionais de agrupamento de dados. Isto, em troca, pode aumentar a complexidade de sua análise de dados e dos relatórios que você cria.

Existem três extensões GROUP BY:

• ROLLUP
• CUBE
• GROUPING SETS

Ao contrário do GROUP BY regular, ROLLUP permite agrupar os dados em múltiplos conjuntos de dados e agregar resultados em diferentes níveis. Conversa chique, mas simplesmente colocando: você pode usar ROLLUP para calcular totais e subtotais, assim como nas tabelas dinâmicas do Excel.

A extensão CUBE é semelhante, mas há uma diferença crucial. CUBE irá gerar subtotais para cada combinação das colunas especificadas.

Finalmente, há GROUPING SETs. Um conjunto de agrupamento é um conjunto de colunas que você utiliza na cláusula GROUP BY. Você pode conectar diferentes consultas contendo GROUP BY se você usar UNION ALL. Entretanto, quanto mais consultas você tiver, mais confuso ele fica. Você pode alcançar o mesmo resultado, mas com muitas consultas mais limpas, usando GROUPING SETS.

Deixe-me mostrar-lhe como funciona ROLLUP. Suponha que você esteja trabalhando para uma loja de violões que tem vários locais. Às vezes você precisará criar um relatório mostrando o número total de violões que você tem em estoque. Aqui está uma consulta que fará isso em nível de fabricante, modelo e loja:

SELECT	manufacturer,
		model,
		store,
		SUM(quantity) AS quantity_sum
FROM guitars
GROUP BY ROLLUP (manufacturer, model, store)
ORDER BY manufacturer;

Isto não parece complicado. É uma simples declaração SELECT que lhe dará as colunas manufacturer, model, e armazena a partir da tabela guitars. Usei a função agregada SUM() para obter a quantidade. Em seguida escrevi GROUP BY seguido imediatamente por ROLLUP. Os dados serão agrupados de acordo com as colunas entre parênteses. Finalmente, o resultado é encomendado pelo fabricante.

O que esta consulta retornará? Dê uma olhada:

Deve ser mais fácil de entender o que quero dizer com diferentes níveis de agrupamento. Uma pequena dica antes de continuar: Onde quer que você veja um valor NULL, este é um subtotal. Vamos dar uma olhada na tabela. Primeiro, há 9 Fender Jazzmasters em Amsterdã. Depois, há 32 Fender Jazzmasters em Nova York. A quantidade total é de 41, que é o que aparece na linha:

O valor NULL significa que os dados estão agrupados em um nível de loja. Este resultado diz "há 41 Fender Jazzmasters no total, tanto em Nova Iorque quanto em Amsterdã". O mesmo cálculo é feito para cada outro modelo Fender, ou seja, Stratocaster e Telecaster. Depois há esta linha:

O que isso significa? Significa que há no total 592 pára-lamas dos três modelos em ambas as lojas.

O mesmo princípio é aplicado à Gibson. A quantidade de violões em Amsterdã e Nova York é mostrada pela primeira vez para o modelo. Depois disso, há uma soma subtotal das quantidades de ambas as lojas. Isto é feito para os três modelos Gibson: ES-335, Les Paul, e SG. Depois há uma linha mostrando o número total dos três modelos de guitarras Gibson em ambas as lojas (o mesmo que com Fenders):

Finalmente, há uma linha mostrando o número total de violões, não importando a loja, fabricante de violões, ou modelo:

Tenho certeza que agora você quer descobrir como funcionam os conjuntos CUBE e GROUPING SETS. Para isso, eu recomendaria dar uma olhada no curso de extensão GROUP BY.

Estes tópicos avançados são algo que os analistas de dados irão usar com muita freqüência. Por isso, preparei algumas construções SQL para meus colegas analistas de dados. Se você gosta de finanças, aqui estão algumas consultas avançadas de SQL para análise financeira.

Você se considera um usuário SQL Avançado?

Como você se sente agora? Eu aumentei sua confiança? Se você já conhece as funções da janela SQL, CTEs, e as extensões GROUP BY, você pode se gabar de suas habilidades avançadas em SQL.

Ou talvez eu tenha feito exatamente o oposto? Talvez eu tenha abalado sua confiança quando você percebeu que não sabe nada sobre os tópicos avançados sobre os quais falei neste artigo.

Não se preocupe! Qualquer que seja o grupo ao qual você pertença, há LearnSQL.com.br cursos que o ajudarão a construir seus conhecimentos e suas habilidades. Deseja aprender funções de janela? Não há problema - veja nosso curso Funções de Janela (Window Functions) em SQL . Interessado em CTEs? Você pode aprendê-las e praticá-las em nosso Consultas Recursivas curso. Precisa tirar mais proveito do GROUP BY? Nosso curso GROUP BY Extensions no curso SQL tem você coberto.