SQL - 7 Conceitos Avançados de SQL que Você Precisa Saber!

Prévia do material em texto

7 Conceitos Avançados de SQL que Você
Precisa Saber!
DP6 Team · Follow
Published in Blog DP6
11 min read · Jul 20, 2022
Share
Se você está entrando agora no mundo de dados ou já está por aqui há um longo
tempo, em algum momento você vai se deparar com o SQL. Essa linguagem (que já
possui quase 50 anos de história!) sobrevive ao teste do tempo e se destaca como a
linguagem mais versátil para se trabalhar com dados.
Como uma linguagem declarativa (em que você precisa se preocupar com o que
você quer consultar, não como a consulta é realizada), a curva de aprendizado tem
um início bastante íngreme, em que é possível absorver o básico da linguagem em
um tempo relativamente curto. Porém, tendo passado dessa etapa, os conceitos
Open in app Sign up Sign In
https://medium.com/@dp6blog?source=post_page-----34605e271824--------------------------------
https://blog.dp6.com.br/?source=post_page-----34605e271824--------------------------------
https://medium.com/@dp6blog?source=post_page-----34605e271824--------------------------------
https://medium.com/m/signin?actionUrl=https%3A%2F%2Fmedium.com%2F_%2Fsubscribe%2Fuser%2F3794c7b4a281&operation=register&redirect=https%3A%2F%2Fblog.dp6.com.br%2F7-conceitos-avan%C3%A7ados-de-sql-que-voc%C3%AA-precisa-saber-34605e271824&user=DP6+Team&userId=3794c7b4a281&source=post_page-3794c7b4a281----34605e271824---------------------post_header-----------
https://blog.dp6.com.br/?source=post_page-----34605e271824--------------------------------
https://rsci.app.link/?%24canonical_url=https%3A%2F%2Fmedium.com%2Fp%2F34605e271824&%7Efeature=LoOpenInAppButton&%7Echannel=ShowPostUnderCollection&source=---two_column_layout_nav----------------------------------
https://medium.com/m/signin?operation=register&redirect=https%3A%2F%2Fblog.dp6.com.br%2F7-conceitos-avan%C3%A7ados-de-sql-que-voc%C3%AA-precisa-saber-34605e271824&source=post_page---two_column_layout_nav-----------------------global_nav-----------
https://medium.com/m/signin?operation=login&redirect=https%3A%2F%2Fblog.dp6.com.br%2F7-conceitos-avan%C3%A7ados-de-sql-que-voc%C3%AA-precisa-saber-34605e271824&source=post_page---two_column_layout_nav-----------------------global_nav-----------
https://medium.com/?source=---two_column_layout_nav----------------------------------
https://medium.com/search?source=---two_column_layout_nav----------------------------------
começam a se tornar mais complexos e dependem cada vez mais de um bom
domínio do básico.
Com base nesse contexto, trazemos neste post 7 dos principais conceitos
avançados que você precisa saber para impulsionar seu conhecimento em SQL!
Observação: cada sistema possui a sua própria adaptação do SQL, adicionando ou
removendo recursos ou alterando a sintaxe. Porém, os conceitos expostos aqui são
válidos para qualquer sistema.
1. Ordem de Execução dos Comandos Select
Vamos simular uma situação em que temos uma tabela “alunos” da qual precisamos
extrair uma contagem de registros agrupados pela primeira letra da coluna “nome”.
Escrevemos a nossa query (consulta), colocando um alias “PrimeiraLetra” na coluna
da primeira letra do nome, e utilizamos esse mesmo alias no comando GROUP BY.
Executamos a consulta e obtemos este resultado:
Error: “PrimeiraLetra” invalid identifier
Num primeiro momento pode parecer estranho, já que usamos a fórmula certa,
nomeamos a coluna corretamente e utilizamos a referência correta dentro do
GROUP BY. Então, tentamos substituir o argumento do GROUP BY pela função usada
no SELECT e nos deparamos com este resultado:
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT SUBSTR(nome, 1, 1) AS PrimeiraLetra
2 ,COUNT(1) AS Contagem
3 FROM alunos
4 GROUP BY PrimeiraLetra;
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT SUBSTR(nome, 1, 1) AS PrimeiraLetra
2 ,COUNT(1) AS Contagem
3 FROM alunos
4 GROUP BY SUBSTR(nome, 1, 1);
https://gist.github.com/GianGP/b84681b3e3f2cf674aa9893d59337848/raw/58f9b897d6b306027f7a9ba0525c8999431c4f0e/gistfile1.txt
https://gist.github.com/GianGP/b84681b3e3f2cf674aa9893d59337848#file-gistfile1-txt
https://github.com/
https://gist.github.com/GianGP/362b655ecd1d7da23cdcdc712378ea5e/raw/af41aacd9dbee4dcd5ff6d86fc6d11a1c0f91143/gistfile1.txt
https://gist.github.com/GianGP/362b655ecd1d7da23cdcdc712378ea5e#file-gistfile1-txt
https://github.com/
A consulta finalizou com sucesso e trouxe a coluna da primeira letra exatamente
com o alias que passamos. A dúvida que surge é a seguinte: se ao referenciar a
função dentro do GROUP BY a execução foi perfeita, por que ao utilizar o alias
obtemos um erro?
O que acontece nesse cenário é que somos levados a crer que a ordem em que
estamos escrevendo o comando é, também, a ordem em que o comando será
processado pelo Sistema Gerenciador de Banco de Dados (SGBD). Porém, o que
acontece na prática é que o processamento da consulta segue uma ordem diferente,
conforme a lista abaixo:
1. FROM (considerando as operações de Join)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. UNION
8. ORDER BY
9. LIMIT/TOP
Como podemos ver, o primeiro passo é a preparação da fonte de dados, unindo
todas as tabelas na cláusula FROM, seguido pela aplicação do filtro WHERE, o
agrupamento via GROUP BY, o filtro do agrupamento pelo HAVING, e, somente
então, a seleção das colunas pelo SELECT. Por causa disso, tentar utilizar os aliases
na cláusula GROUP BY seria o equivalente a tentar utilizar uma variável antes de ela
ser declarada em qualquer outra linguagem de programação, já que os aliases só
serão reconhecidos após o SELECT ser processado.
Saber a ordem das cláusulas no comando Select é essencial para agilizar o
debugging dos eventuais problemas de consulta, para guiar na estruturação de
queries e, principalmente, para identificar possíveis pontos de otimização em
códigos SQL.
Observação: em alguns SGBDs, como o MySQL e o PostgreSQL, é feita uma
adaptação da linguagem SQL para permitir o alias dentro de GROUP BY, mas por
padrão os SGBD seguem rigorosamente essa ordem dos comandos.
2. Subqueries
Subqueries (ou subconsultas) são comandos SELECT executados dentro de outro
comando SQL. Esses comandos auxiliares simplificam a escrita de consultas que
normalmente seriam muito complexas ou até impossíveis de serem escritas de
outra forma.
É possível utilizar subqueries em 3 partes diferentes dentro de uma consulta:
1. Na fonte de dados de uma consulta principal
Neste caso, a subquery é utilizada como fonte de dados para a consulta externa.
Essa prática é bastante utilizada quando queremos utilizar um subconjunto (por
exemplo, uma tabela filtrada) como fonte de dados da consulta principal.
No código acima, temos uma consulta interna, que busca uma lista de sobrenomes
da tabela “alunos”, e uma consulta externa, que utiliza esta lista para a contagem de
alunos por sobrenome. Inicialmente é executado o comando interno.
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT t1.sobrenome, 
2 COUNT(1) as ContagemDeAlunos
3 FROM (
4 SELECT sobrenome
5 FROM alunos
6 WHERE nome = "Gian"
7 ) t1
8 GROUP BY t1.sobrenome;
https://gist.github.com/GianGP/a0c40520b8a7ca41582c8904a117d1c4/raw/1cc06a659cb47ed9221a5807365ade58120d7739/gistfile1.txt
https://gist.github.com/GianGP/a0c40520b8a7ca41582c8904a117d1c4#file-gistfile1-txt
https://github.com/
E o resultado é considerado uma nova tabela, que será utilizada dentro do comando
externo.
Essa aplicação das subqueries possui uma implicação importante para o SQL como
um todo: todo comando SELECT executado gera um novo conjunto de dados que,
aos olhos do SGBD, é equivalente a uma nova tabela. Por isso, é possível aninhar
comandos SELECT dentro de comandos SELECT dentro de outros comandos
SELECT e assim por diante. A única limitação nesse caso é a legibilidade do código,
que tende a ficar mais complexa a cada nível adicionado. Abaixo segue um exemplo
de consulta com 2 níveis de subqueries.
2. Dentro da lista de colunas escolhidas no SELECT
view rawgistfile1.txt hosted with ❤by GitHub
1 SELECT sobrenome
2 FROM alunos
3 WHERE nome = "Gian";
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT t1.sobrenome, 
2 COUNT(1) as ContagemDeAlunos
3 FROM ( [resultado da consulta interna] ) t1
4 GROUP BY t1.sobrenome;
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT t2.sobrenome, t2.ContagemDeAlunos
2 FROM ( 
3 -- Nível 2
4 SELECT t1.sobrenome,
5 COUNT(1) as ContagemDeAlunos
6 FROM (
7 -- Nível 1
8 SELECT sobrenome
9 FROM alunos
10 WHERE nome = 'Gian'
11 -- Fim Nível 1
12 ) t1
13 GROUP BY t1.sobrenome
14 -- Fim nível 2
15 ) t2
16 WHERE t2.sobrenome LIKE 'A%';
https://gist.github.com/GianGP/7496c6a5f4520cd4e191d69e4c1953cf/raw/399a96665cd38b470ceadb63a8dea52d6a2ff977/gistfile1.txt
https://gist.github.com/GianGP/7496c6a5f4520cd4e191d69e4c1953cf#file-gistfile1-txt
https://github.com/
https://gist.github.com/GianGP/b23d5286c0a383d23e6238c6a8393bc0/raw/450b30681647e2109b14865d9987c1cdd13ba0a8/gistfile1.txt
https://gist.github.com/GianGP/b23d5286c0a383d23e6238c6a8393bc0#file-gistfile1-txt
https://github.com/
https://gist.github.com/GianGP/12f7faffd5f122047d32661e29e2e851/raw/8a24bd9e6223c8c8f884b8ead6f084ae39c45878/gistfile1.txt
https://gist.github.com/GianGP/12f7faffd5f122047d32661e29e2e851#file-gistfile1-txt
https://github.com/
Neste segundo caso, utilizamos comandos SELECT dentro da lista de colunas de
outro comando SELECT. Esse recurso é útil quando precisamos calcular métricas
que dependam de outras tabelas, mas sem a necessidade de unir ambas as tabelas.
No código acima, utilizou-se uma subquery para fazer o cálculo da média de notas
que cada aluno obteve e outra subquery para o cálculo da frequência dos alunos.
Um detalhe importante é que, por mais que não seja feita a junção entre as tabelas,
é preciso definir a relação entre elas dentro da cláusula WHERE da subquery. Como
no caso anterior, a subquery está contida entre parênteses.
3. Como filtro da consulta externa
Por fim, é possível utilizar comandos Select como filtros de outras consultas. Nesse
caso, o resultado da consulta secundária é interpretado como um único valor ou
uma lista de valores (dependendo do que a consulta retornar). O resultado dessa
consulta pode ser utilizado dentro da cláusula Where como qualquer outra
expressão de filtro.
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT nome
2 ,sobrenome
3 ,(
4 SELECT AVG(nota)
5 FROM provas p
6 WHERE p.nome = a.nome
7 ) AS MediaNotas
8 ,(
9 SELECT COUNT(1)
10 FROM presenca p
11 WHERE p.nome = a.nome
12 AND presente = 1
13 ) AS QtdePresenca
14 FROM alunos a;
https://gist.github.com/GianGP/c6fedef5bfee646848d0e7a18f3874a0/raw/22f2599fe73d1aed9cb72c4a98fbd35577c21f3f/gistfile1.txt
https://gist.github.com/GianGP/c6fedef5bfee646848d0e7a18f3874a0#file-gistfile1-txt
https://github.com/
No código acima, por exemplo, a subquery retorna uma lista de nomes dos alunos
com falta. Essa lista é utilizada como filtro para a consulta principal, que retornará
as informações apenas dos alunos que já tiveram alguma falta.
Uma observação importante sobre as subqueries é que numa grande parte dos
casos é possível chegar ao mesmo resultado realizando junções entre as tabelas da
consulta principal e da secundária. Porém, como a consulta principal não utiliza
diretamente nenhuma coluna da consulta secundária, utilizar subqueries torna-se
vantajoso, já que operações de junção de tabelas são bastante custosas em termos
de processamento.
3. Common Table Expressions (CTEs)
O maior problema das subqueries, como já comentamos, é que elas tendem a
dificultar a leitura das consultas e, por consequência, prejudicar o entendimento
das etapas da consulta. Para solucionar esse problema, podemos utilizar o recurso
das Common Table Expressions (CTEs).
As CTEs permitem separar e encapsular cada uma das etapas de uma consulta
complexa em conjuntos, que podem ser referenciados como se fossem tabelas. A
sua sintaxe é a seguinte:
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT idAluno
2 ,nome
3 ,sobrenome
4 FROM alunos
5 WHERE nome IN (
6 SELECT nome
7 FROM presenca
8 WHERE presente = 0
9 );
view rawgistfile1.txt hosted with ❤ by GitHub
1 WITH nome_da_cte [ (coluna1 [,...] ] AS
2 (
3 -- Consulta que forma a CTE
4 )
5
6 -- Comando SQL que utiliza a CTE
https://gist.github.com/GianGP/1aaddc9d233eb6ea1f92d7f7a9f2fa82/raw/719ac182e9d5a1aeaacb18031fba89fc4684d6b7/gistfile1.txt
https://gist.github.com/GianGP/1aaddc9d233eb6ea1f92d7f7a9f2fa82#file-gistfile1-txt
https://github.com/
https://gist.github.com/GianGP/2a971905f771c728ff5980dae55deb1b/raw/9899a04c1e634a05df5aec787120dc56d1b3bce0/gistfile1.txt
https://gist.github.com/GianGP/2a971905f771c728ff5980dae55deb1b#file-gistfile1-txt
https://github.com/
Utilizando como exemplo a primeira consulta do tópico de subqueries, podemos
reescrevê-la assim:
Da mesma forma que em linguagens de programação é uma boa prática dar nomes
autoexplicativos para variáveis e funções, isso é válido também para as CTEs. No
caso acima, sabemos exatamente o que esperar da CTE, que é uma lista de alunos
cujo nome é igual a Gian. Podemos passar o nome das colunas após o nome da CTE,
mas não é obrigatório.
Numa mesma consulta é possível criar diversas CTEs, como no exemplo abaixo:
view rawgistfile1.txt hosted with ❤ by GitHub
1 WITH alunos_com_nome_gian
2 AS
3 (
4 SELECT sobrenome
5 FROM alunos
6 WHERE nome = "Gian"
7 )
8
9 SELECT sobrenome, 
10 COUNT(1) as ContagemDeAlunos
11 FROM alunos_com_nome_gian
12 GROUP BY sobrenome;
https://gist.github.com/GianGP/a9a874585905efc840add03328532d61/raw/7ff3721e480d5676110caa256eb100b8294f51e2/gistfile1.txt
https://gist.github.com/GianGP/a9a874585905efc840add03328532d61#file-gistfile1-txt
https://github.com/
O resultado dessa consulta é exatamente igual ao que seria obtido se utilizássemos
subqueries dentro de subqueries. Porém, separando em CTEs, o entendimento do
fluxo da consulta fica muito mais simples: primeiro a tabela “alunos” é filtrada, em
seguida é feita uma contagem de alunos por sobrenome e, por fim, encontram-se
todos os sobrenomes que começam com a letra A.
Uma característica importante das CTEs é que elas possuem escopo da query atual.
Isso quer dizer que no bloco em que as CTEs são definidas é possível referenciar
qualquer outra CTE já definida. Porém, após a primeira consulta que venha depois
desse bloco, não é possível consultar mais nenhuma CTE definida anteriormente.
Por exemplo, no código abaixo, todas as CTEs definidas acima da linha vermelha
podem ser referenciadas a qualquer momento. Abaixo da linha vermelha isso não é
mais possível, já que o escopo das CTEs já foi finalizado. Note que essa finalização
ocorre independente de as CTEs serem ou não usadas na consulta posterior a elas.
view rawgistfile1.txt hosted with ❤ by GitHub
1 WITH alunos_com_nome_gian AS
2 (
3 SELECT sobrenome
4 FROM alunos
5 WHERE nome = 'Gian'
6 ),
7
8 contagem_de_alunos_por_sobrenome AS
9 (
10 SELECT sobrenome
11 ,COUNT(1) as ContagemDeAlunos
12 FROM alunos_com_nome_gian
13 GROUP BY sobrenome
14 )
15
16 SELECT sobrenome
17 ,ContagemDeAlunos
18 FROM contagem_de_alunos_por_sobrenome
19 WHERE sobrenome LIKE 'A%';
https://gist.github.com/GianGP/e0826f999d8beb2a04b7d02632464398/raw/2d3c63e0f9b71c8d77ea318c5e92cb0452b5f62a/gistfile1.txt
https://gist.github.com/GianGP/e0826f999d8beb2a04b7d02632464398#file-gistfile1-txt
https://github.com/
Esse recurso é muito poderoso, então, sempre que possível, simplifique suas
queries utilizando as CTEs!
4. User Defined Functions (UDFs): Funções Escalares
Assim como nas linguagens de programação, no SQL é possível encapsular lógicas
em funções, que recebem parâmetros e retornam algumvalor ou conjunto de
valores. O primeiro tipo de função que veremos é a função escalar, que recebe
parâmetros e retorna apenas um valor. A sintaxe entre SGBDs pode variar bastante
na definição de funções, mas conceitualmente elas funcionam da mesma forma.
Abaixo, segue a sintaxe para o Google BigQuery.
As funções se tornam extremamente úteis quando temos alguma necessidade de
negócio que não está contemplada na lista de funções-padrões do SGBD em que
estamos trabalhando. Por exemplo, digamos que repetidamente calculamos a
frequência da presença e a média das notas dos alunos. Esse cálculo pode ser feito
via subqueries, como vimos no tópico de subqueries, ou, para não precisar decorar
a lógica e reproduzi-la muitas vezes, podemos criar funções como estas:
view rawgistfile1.txt hosted with ❤ by GitHub
1 CREATE FUNCTION nomeDaFuncao (p1 tipo_do_p1 [, p2 tipo_do_p2, ...])
2 RETURNS tipo_do_retorno
3 AS
4 (
5 -- Operações que são feitas dentro da função
6 );
https://gist.github.com/GianGP/750fb6e9dea9ea62399e6564b062b911/raw/3e9512f46482c9b9891832ab4b33c6a4dea6679a/gistfile1.txt
https://gist.github.com/GianGP/750fb6e9dea9ea62399e6564b062b911#file-gistfile1-txt
https://github.com/
Com as funções definidas, podemos chamá-las apenas quando precisarmos da
informação, sem repetir os mesmos cálculos dentro das consultas executadas. A
chamada das funções definidas por usuários é igual a qualquer outra função-padrão
do SQL em que passamos as colunas da tabela que vão compor os seus parâmetros.
5. User Defined Functions (UDFs): Funções de Tabela
O segundo tipo de UDFs (e um dos meus recursos preferidos em SGBDs) são as
funções de tabela. Diferentemente das funções escalares, as funções de tabela
recebem parâmetros e retornam tabelas em vez de um único valor. As funções de
tabela podem ser vistas como views parametrizáveis. Abaixo, segue a sintaxe para o
Google BigQuery.
view rawgistfile1.txt hosted with ❤ by GitHub
1 CREATE FUNCTION frequencia_alunos (nome_aluno STRING)
2 RETURNS INT64
3 AS
4 (
5 SELECT COUNT(1)
6 FROM presenca
7 WHERE nome = nome_aluno
8 AND presente = 1
9 );
10
11
12 CREATE FUNCTION media_alunos (nome_aluno STRING)
13 RETURNS FLOAT64
14 AS
15 (
16 SELECT AVG(nota)
17 FROM provas 
18 WHERE nome = nome_aluno
19 );
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT nome
2 ,sobrenome
3 ,media_alunos(nome) AS MediaNotas
4 ,frequencia_alunos(nome) AS QtdePresenca
5 FROM alunos a;
https://gist.github.com/GianGP/15ca04b971930b6df3fc0a0c73caae59/raw/8931392cf0f8669422ee3826c793e8a1755aceb1/gistfile1.txt
https://gist.github.com/GianGP/15ca04b971930b6df3fc0a0c73caae59#file-gistfile1-txt
https://github.com/
https://gist.github.com/GianGP/6b4a6e8cac9e03ca657e4ee62e2f0500/raw/29acffdd30662062e080537a7a5b97763e462abb/gistfile1.txt
https://gist.github.com/GianGP/6b4a6e8cac9e03ca657e4ee62e2f0500#file-gistfile1-txt
https://github.com/
Diferente das CTEs, as funções de tabela podem ser utilizadas no contexto do banco
de dados, não somente no contexto da consulta. Por exemplo, se precisamos
trabalhar frequentemente com a tabela de contagem de alunos por sobrenome,
como fizemos no exemplo das CTEs, mas em vez de fixar o nome do aluno e da
primeira letra do sobrenome precisamos deixar os valores dinâmicos, podemos
utilizar as funções de tabela, reaproveitando o mesmo código desenvolvido.
Com a função criada, podemos utilizá-la como se fosse uma tabela qualquer, mas
com parâmetros, como mostrado abaixo:
view rawgistfile1.txt hosted with ❤ by GitHub
1 CREATE TABLE FUNCTION nomeDaFuncao (p1 tipo_do_p1 [, p2 tipo_do_p1, ...])
2 AS
3 -- Comandos SQL que devem finalizar com um SELECT
4 ;
view rawgistfile1.txt hosted with ❤ by GitHub
1 CREATE TABLE FUNCTION cont_alunos_por_sobrenome (nome_aluno STRING, prim_letra_sobrenome STRING
2 AS
3 WITH alunos_filtrado_por_nome AS
4 (
5 SELECT sobrenome
6 FROM alunos
7 WHERE nome = nome_aluno
8 ),
9
10 contagem_de_alunos_por_sobrenome AS
11 (
12 SELECT sobrenome
13 ,COUNT(1) as ContagemDeAlunos
14 FROM alunos_filtrado_por_nome
15 GROUP BY sobrenome
16 )
17
18 SELECT sobrenome
19 ,ContagemDeAlunos
20 FROM contagem_de_alunos_por_sobrenome
21 WHERE sobrenome LIKE CONCAT(primeira_letra_sobrenome, '%');
https://gist.github.com/GianGP/2e9a7a7841afc8684025529a94467c4f/raw/5b89705a046915b3d1ff2ee8e4de8bd1a97d0682/gistfile1.txt
https://gist.github.com/GianGP/2e9a7a7841afc8684025529a94467c4f#file-gistfile1-txt
https://github.com/
https://gist.github.com/GianGP/34486ddfd736e5c4908cc85edd9e24b7/raw/3b41d3dde3e69306579b86706f17b0cbd2f8bc13/gistfile1.txt
https://gist.github.com/GianGP/34486ddfd736e5c4908cc85edd9e24b7#file-gistfile1-txt
https://github.com/
6. Window Functions (Funções de Janela)
No SQL existem vários tipos diferentes de funções. As mais comuns são as funções
escalares, que recebem um valor único e retornam um valor único (como CONCAT,
UPPER, LOWER), e as funções de agregação, que recebem um conjunto de valores e
retornam um valor único (como MAX, MIN, AVG).
Existe também um terceiro tipo de funções, menos conhecido, mas igualmente
importante e versátil: as funções de janela. Essas funções são executadas sobre um
conjunto de linhas (uma janela de valores), e os valores são calculados num
contexto de linha, acessando as linhas anteriores ou posteriores para fazer o
cálculo.
Para entender melhor esse conceito, vamos ver a utilização da função RANK.
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT sobrenome
2 ,ContagemDeAlunos
3 FROM cont_alunos_por_sobrenome('Gian', 'P');
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT nome
2 ,nota
3 ,RANK() OVER (ORDER BY nota DESC) rank_notas
4 FROM provas;
https://gist.github.com/GianGP/94363e3ad1f48d95324bc2f0d5318cc4/raw/013ce332d82b5f857652334e94b89019ad97539e/gistfile1.txt
https://gist.github.com/GianGP/94363e3ad1f48d95324bc2f0d5318cc4#file-gistfile1-txt
https://github.com/
https://gist.github.com/GianGP/cbfcf0eb672202c693b5035265d192c2/raw/78cc1bf6cc16c2173c2d2c503f3cd6038c030601/gistfile1.txt
https://gist.github.com/GianGP/cbfcf0eb672202c693b5035265d192c2#file-gistfile1-txt
https://github.com/
A função RANK percorreu todos os registros da tabela “provas” e classificou cada
uma das linhas em relação à coluna nota, com a maior nota possuindo o rank 1 e a
menor, o rank 6. Perceba que o cálculo de classificação depende não só dos dados
da linha em si, mas também da comparação entre as linhas. É exatamente dentro
desse contexto que as funções de janela atuam; elas acessam linhas anteriores e
posteriores para calcular o valor da função para a linha atual.
Como essas funções acessam as linhas anteriores e/ou posteriores da linha atual, a
ordem em que as linhas estão dispostas é de extrema importância para as funções
de janela. A regra para a avaliação das linhas é definida dentro da cláusula OVER,
através do ORDER BY.
No nosso exemplo, o ORDER BY dentro do OVER fez com que cada linha fosse
avaliada seguindo a ordem crescente de notas e retornando a posição da linha na
classificação.
Um detalhe importante é que a ordem em que a função de janela é calculada
depende exclusivamente da cláusula OVER, não sendo afetada pela ordem da
consulta em que ela é utilizada, como no exemplo abaixo:
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT nome
2 ,nota
3 ,RANK() OVER (ORDER BY nota DESC) rank_notas
4 FROM provas
5 ORDER BY nome, nota DESC;
https://gist.github.com/GianGP/dd8f7c9c0f5d8594abd4d7a2c695c37f/raw/30e5c977e5d6392422b09e72a4430621dfa8e4f7/gistfile1.txt
https://gist.github.com/GianGP/dd8f7c9c0f5d8594abd4d7a2c695c37f#file-gistfile1-txt
https://github.com/
No resultado acima, vemos que a tabela está organizada por nome e nota, mas o
RANK continua trazendo o mesmo resultado que a consulta anterior.
O segundo argumento das funções de janela é o PARTITION BY, que define grupos
de dados onde a função será avaliada.Podemos pensar que essa cláusula quebra a
tabela em grupos de linhas com alguma característica comum entre elas (essa
característica sendo os valores nas colunas definidas no PARTITION BY) e avalia
localmente o resultado da função de janela utilizada. Após todos esses grupos serem
avaliados, eles são unidos novamente num único conjunto de dados, que é o
resultado da consulta.
view rawgistfile1.txt hosted with ❤ by GitHub
1 SELECT nome
2 ,nota
3 ,RANK() OVER (PARTITION BY nome ORDER BY nota DESC) rank_notas
4 FROM provas;
https://gist.github.com/GianGP/c21282fd305dab9c2d00667ab76b200d/raw/88bf54e706bd7389a7a019145560e8c2894332c8/gistfile1.txt
https://gist.github.com/GianGP/c21282fd305dab9c2d00667ab76b200d#file-gistfile1-txt
https://github.com/
Na consulta acima, foi feita uma partição pela coluna nome, e o resultado da
consulta foi a classificação das notas em relação ao nome das pessoas.
Existem funções que são puramente de janela (como RANK, DENSE_RANK,
ROW_NUMBER) e funções de agregação que podem ser calculadas dentro de
janelas, adicionando a cláusula OVER. Se você se interessou pelas funções de janela,
recomendo procurar artigos sobre soluções do problema de “gaps and islands”
(lacunas e ilhas) utilizando SQL.
7. Tabelas Temporárias
O último conceito que trazemos é o das tabelas temporárias. Como o nome sugere,
as tabelas temporárias são tabelas que não são persistidas no banco de dados: elas
permanecem no cache e são excluídas manualmente pelo usuário ou no momento
em que a sessão é finalizada. Diferentemente das CTEs e das funções de tabela, as
tabelas temporárias possuem as mesmas características das tabelas permanentes,
sendo possível inserir, editar e deletar registros.
A principal função das tabelas temporárias é armazenar e processar resultados
intermediários de consultas, de modo a otimizar o tempo de execução/recursos
alocados de uma consulta mais complexa. Por exemplo, em vez de aplicar cálculos
em cima de tabelas de milhões de linhas para obter um subconjunto de dados,
pode-se primeiro filtrar esse subconjunto numa tabela temporária e então realizar
os cálculos. Dessa forma, trabalhamos apenas com os dados que de fato precisamos
e evitamos desperdiçar o processamento das consultas.
A sintaxe para a criação de tabelas temporárias é igual à da criação de tabelas
permanentes, precisando apenas adicionar o termo TEMPORARY após a cláusula
CREATE, como mostrado abaixo.
Perfil do Autor: Gian Panacioni | Graduado em Engenharia Elétrica pela UFPR,
atualmente atua como Engenheiro de Dados na DP6.
Tem alguma dúvida sobre marketing digital?
Conheça o #DP6Responde. Você pode trazer todas as suas dúvidas e nós responderemos
para você em formato de vídeo. Nosso time de especialistas é formado por profissionais
com trajetórias fora da curva e que estão preparados para informar tudo o que você sempre
quis saber sobre a cultura data-driven.
Clique aqui e envie sua pergunta para os nossos especialistas.
view rawgistfile1.txt hosted with ❤ by GitHub
1 CREATE TEMPORARY TABLE nome_da_tabela
2 ( 
3 col1 tipo_col1 
4 [, col2 tipo_col2 …]
5 );
6
7 CREATE TEMPORARY TABLE nome_da_tabela
8 AS
9 SELECT [colunas]
10 FROM [tabela];
https://www.linkedin.com/in/gian-panacioni/
https://info.dp6.com.br/dp6-responde-formulario?utm_campaign=campanha_ga4_02_-_dp6_responde&utm_medium=email&utm_source=RD+Station#rd-column-kky0xqjy
https://info.dp6.com.br/dp6-responde-formulario?utm_campaign=campanha_ga4_02_-_dp6_responde&utm_medium=email&utm_source=RD+Station#rd-column-kky0xqjy
https://gist.github.com/GianGP/1cd2fb6c8d99f62957714be4ee619843/raw/f5679304abcce48217760a41b7f327def74a049d/gistfile1.txt
https://gist.github.com/GianGP/1cd2fb6c8d99f62957714be4ee619843#file-gistfile1-txt
https://github.com/
Outros canais da DP6
contato@dp6.com.br
Site: https://www.dp6.com.br
DP6 News: https://info.dp6.com.br/dp6-news
LinkedIn: https://www.linkedin.com/company/dp6
Instagram: https://www.instagram.com/somosdp6/
Facebook: https://www.facebook.com/dp6.com.br
Podcast: https://anchor.fm/podcastdp6
YouTube: https://www.youtube.com/c/DP6br/videos
Projetos Open Source: https://dp6.github.io
Follow
Written by DP6 Team
872 Followers · Editor for Blog DP6
blog.dp6.com.br
More from DP6 Team and Blog DP6
Sql
https://www.dp6.com.br/?utm_medium=post&utm_source=blog-dp6&utm_campaign=post-blog
https://info.dp6.com.br/dp6-news
https://www.linkedin.com/company/dp6
https://www.instagram.com/somosdp6/
https://www.facebook.com/dp6.com.br
https://anchor.fm/podcastdp6?utm_medium=post&utm_source=blog-dp6&utm_campaign=post-blog
https://www.youtube.com/c/DP6br/videos
https://dp6.github.io/?utm_medium=post&utm_source=blog-dp6&utm_campaign=post-blog
https://medium.com/tag/sql?source=post_page-----34605e271824---------------sql-----------------
https://medium.com/@dp6blog?source=post_page-----34605e271824--------------------------------
https://blog.dp6.com.br/?source=post_page-----34605e271824--------------------------------
https://medium.com/m/signin?actionUrl=https%3A%2F%2Fmedium.com%2F_%2Fsubscribe%2Fuser%2F3794c7b4a281&operation=register&redirect=https%3A%2F%2Fblog.dp6.com.br%2F7-conceitos-avan%C3%A7ados-de-sql-que-voc%C3%AA-precisa-saber-34605e271824&user=DP6+Team&userId=3794c7b4a281&source=post_page-3794c7b4a281----34605e271824---------------------follow_profile-----------
https://medium.com/m/signin?actionUrl=%2F_%2Fapi%2Fsubscriptions%2Fnewsletters%2F130ac6adfd99&operation=register&redirect=https%3A%2F%2Fblog.dp6.com.br%2F7-conceitos-avan%C3%A7ados-de-sql-que-voc%C3%AA-precisa-saber-34605e271824&newsletterV3=3794c7b4a281&newsletterV3Id=130ac6adfd99&user=DP6+Team&userId=3794c7b4a281&source=-----34605e271824---------------------subscribe_user-----------
https://medium.com/@dp6blog?source=post_page-----34605e271824--------------------------------
https://medium.com/@dp6blog/followers?source=post_page-----34605e271824--------------------------------
https://blog.dp6.com.br/?source=post_page-----34605e271824--------------------------------
http://blog.dp6.com.br/