Top 10 Funções da Janela SQL Perguntas para Entrevistas

Muitos cargos interessantes requerem habilidades SQL - e isso inclui funções de janela, que não são comumente ensinadas em cursos on-line. Neste artigo, vou abordar as principais perguntas sobre funções de janela para cada nível de experiência.

Se você estiver indo para uma entrevista de emprego para uma posição SQL avançada ou para posições intermediárias a avançadas de analista de dados, você provavelmente será questionado sobre seu conhecimento das funções de janela SQL. Não entre em pânico! Embora estas funções não sejam comumente abordadas em cursos on-line, temos as respostas aqui mesmo.

Perguntas comuns de entrevistas de emprego sobre SQL Funções de Janela (Window Functions) em SQL

A idéia deste artigo é ajudar você a se preparar para perguntas sobre diferentes subtópicos de funções de janela. Não podemos lhe dar a pergunta exata que você receberá, mas podemos estar bastante certos sobre os tópicos para os quais as perguntas irão apontar.

Em alguns casos, a pergunta pode ser muito aberta, deixando a decisão sobre qual subtópico de funções de janela para cobrir inteiramente para você. Neste caso, você deve conhecer a importância relativa de cada subtópico. Para começar, você deve estar preparado para uma pergunta aberta como:

1. O que é uma função de janela em SQL?

Funções de janela são funções SQL que operam em um conjunto de registros chamados de "janela" ou "quadro de janela". A "janela" é um conjunto de linhas que estão de alguma forma relacionadas com a linha que está sendo processada atualmente pela consulta (por exemplo, todas as linhas antes da linha atual, 5 linhas antes da linha atual, ou 3 linhas depois da linha atual).

As funções de janela são similares às funções agregadas, pois computam estatísticas para um grupo de linhas. Entretanto, as funções de janela não colapsam as linhas; elas mantêm os detalhes de linhas individuais.

As funções de janela podem ser organizadas nas quatro categorias seguintes: funções agregadas, funções de classificação, funções analíticas e funções de distribuição.

As funções agregadas são aquelas que você utiliza com GROUP BY. Isto inclui:

• COUNT() conta o número de filas dentro da janela.
• AVG() calcula o valor médio de uma determinada coluna para todos os registros na janela.
• MAX() obtém o valor máximo de uma coluna para todos os registros na janela.
• SUM() retorna a soma de todos os valores de uma determinada coluna dentro da janela.

Na categoria de classificação:

• ROW_NUMBER() retorna a posição da linha no conjunto de resultados.
• RANK() classifica as linhas com base em um determinado valor. Quando duas linhas estão na mesma posição, atribui-lhes a mesma classificação e deixa a próxima posição vazia (por exemplo, 1, 2, 3, 3, 5...).
• DENSE_RANK() também classifica linhas por um determinado valor, mas não deixa a próxima posição vazia (por exemplo, 1, 2, 3, 3, 3, 4, 5...).

Para informações detalhadas, veja este artigo sobre as funções de classificação.

Na categoria analítica, as funções LEAD(), LAG() ou FIRST_VALUE() nos permitem obter dados de outras linhas na mesma janela. LEAD() retorna valores das linhas abaixo da linha atual; LAG() das linhas acima da linha atual. Para mais detalhes, consulte nosso artigo sobre LEAD vs LAG.

Finalmente, na categoria de distribuição há funções como PERCENT_RANK() e CUME_DIST() que podem obter rankings percentis ou distribuições cumulativas. Consulte nosso curso Funções de Janela (Window Functions) em SQL para instruções passo a passo sobre como usar estas funções.

Aqui está um exemplo de consulta com funções de janela:

SELECT
    employee_name,
    department_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary) position 
FROM employee

Nesta consulta, a função de janela RANK() é usada para classificar os funcionários por salário. Mais adiante neste artigo, discutiremos em detalhes a sintaxe da cláusula OVER() e as subcláusulas PARTITION BY e ORDER BY. Por enquanto, diremos apenas que eles são usados para definir quais registros compõem a estrutura da janela.

Perguntas sobre a função da janela de entrada

2. Qual é a Sintaxe da Cláusula OVER () ?

A cláusula OVER() é usada para definir quais linhas estarão na moldura da janela. As seguintes subcláusulas podem estar presentes na cláusula OVER():

• PARTITION BY define a partição, ou os grupos de linhas dentro da moldura da janela, que a função de janela usará para criar um resultado. (Isto será explicado abaixo).
• ORDER BY define a ordem das fileiras na moldura da janela.
• ROWS/RANGE define os limites superior e inferior da moldura da janela.

Todas as subcláusulas do OVER() são opcionais e podem ser omitidas. Nesse caso, as funções serão executadas em toda a moldura da janela.

O SQL a seguir mostra a cláusula OVER() em funcionamento:

SELECT
first_name,
last_name,
department, 
salary,
AVG(salary) OVER (PARTITION BY department) 
FROM employee

Para cada funcionário, a consulta retorna seu nome, sobrenome, salário e o salário médio em seu departamento. A cláusula OVER (PARTITION BY department) cria uma janela de linhas para cada valor na coluna do departamento. Todas as linhas com o mesmo valor na coluna do departamento pertencerão à mesma janela. A função AVG() é aplicada à janela: a consulta calcula o salário médio no departamento em questão.

O artigo Qual é a cláusula de OVER? tem uma explicação completa da cláusula OVER.

3. Descreva a diferença entre Funções de Janela (Window Functions) em SQL e as funções agregadas.

A principal diferença entre funções de janela e funções agregadas é que as funções agregadas agrupam várias linhas em uma única linha de resultado; todas as linhas individuais do grupo são colapsadas e seus dados individuais não são mostrados. Por outro lado, as funções de janela produzem um resultado para cada linha individual. Este resultado é normalmente mostrado como um novo valor de coluna em cada linha dentro da janela.

O colapso de linhas é uma característica importante das funções agregadas. Por exemplo, não podemos resolver o problema "Devolver todos os funcionários com seu salário e o salário máximo em seu departamento" com funções agregadas devido à limitação do colapso.

No lado da semelhança, tanto as funções agregadas como as de janela realizam uma operação semelhante à agregação em um conjunto de filas. Algumas funções como AVG(), MAX(), MIN(), e SUM() podem ser usadas tanto como funções agregadas quanto de janela. Entretanto, quando precisamos do resultado destas funções combinadas com dados em nível de linha, é melhor usar uma função de janela em vez de uma função agregada.

Vamos mostrar duas consultas SQL que retornam o nome do departamento e o salário máximo de cada departamento. No primeiro exemplo, usaremos MAX() como uma função agregada:

SELECT   department_name,
         MAX(salary) AS max_salary
FROM     employee
GROUP BY department_name

Abaixo, podemos ver o resultado da consulta anterior. Observe que há um registro por departamento devido ao efeito de colapso da cláusula GROUP BY:

department_name	max_salary
Accounting	93000
Sales	134000
Human Resources	78000

No próximo exemplo, obteremos um resultado semelhante, mas ligeiramente diferente, usando MAX() como uma função de janela:

SELECT employee_name, 
       salary,
       department_name,
       MAX(salary) OVER (PARTITION BY department_name) AS max_salary
FROM   employee

Como mencionamos anteriormente, as funções de janela não colapsam registros. No resultado seguinte, temos uma fila por funcionário para um total de 5 filas:

employee_name	salary	department_name	max_salary
John Doe	93000	Accounting	93000
Jeremy Smith	134000	Sales	134000
Donna Hayes	120000	Sales	134000
Mark Ron	78000	Human Resources	78000
Denis Serge	72000	Human Resources	78000

Note que adicionamos as colunas employee_name e o salário apenas adicionando seus nomes à lista de colunas no SELECT. Não pudemos adicioná-las à consulta com GROUP BY por causa da limitação de colapso.

No artigo SQL Funções de Janela (Window Functions) em SQL por Explicação, você pode encontrar uma explicação detalhada das diferenças entre as funções agregadas e de janela.

4. Qual é a diferença entre Funções de Janela (Window Functions) em SQL e o GROUP BY Clause?

As funções agregadas são freqüentemente usadas com a cláusula GROUP BY, que define os grupos de linhas onde a função agregada funcionará. A cláusula GROUP BY agrupa as linhas individuais em conjuntos de linhas, permitindo a execução de funções agregadas como SUM(), AVG() ou MAX() nestes conjuntos. Qualquer coluna das linhas individuais não pode ser parte do resultado, como podemos ver na seguinte consulta SQL:

SELECT   
   department_name, 
   AVG(salary)      -- AVG is an aggregate function
FROM  employee
GROUP BY department_name

Na consulta acima, colocamos apenas uma coluna na lista SELECT: department_name. Isto é possível porque a coluna department_name aparece na cláusula GROUP BY. Entretanto, não podemos adicionar nenhuma coluna adicional no SELECT; somente as colunas especificadas no GROUP BY são permitidas.

A seguinte consulta SQL é equivalente à anterior, mas usa funções de janela ao invés de GROUP BY:

SELECT
  department_name,
  AVG(salary) OVER(PARTITION BY department_name) -- AVG is a window function
FROM employee

A consulta anterior não tem uma cláusula GROUP BY porque a função AVG() é usada como uma função de janela. Podemos reconhecer que AVG() é uma função de janela por causa da presença da cláusula OVER.

Sugiro o artigo SQL Funções de Janela (Window Functions) em SQL vs. GROUP BY para uma comparação completa entre as funções de janela e a cláusula GROUP BY.

5. Mostre um Exemplo de SQL Funções de Janela (Window Functions) em SQL.

Esta é uma boa oportunidade para mencionar uma consulta que mostra a importância das funções de janela e, ao mesmo tempo, está conectada com as consultas que mostramos nas perguntas anteriores. A consulta que eu sugiro resolveria esta tarefa: "Obter os nomes dos funcionários, salários, nomes dos departamentos e o salário médio daquele departamento".

Esta consulta é uma maneira simples de mostrar como podemos combinar dados em nível de linha e dados agregados. (A função de janela retorna os dados agregados).

SELECT employee_name,
       salary,
       department_name,
       AVG(salary) OVER (PARTITION BY department) avg_salary 
FROM employee

Acima, podemos ver as colunas de nível de linha employee_name, salary e department com o salário médio de cada departamento, que é calculado pela função de janela AVG(). A subcláusula PARTITION BY define que as janelas de registros serão criadas com base no valor da coluna department_name. Todos os registros com o mesmo valor em department_name estarão na mesma janela. Os resultados seriam algo parecido com isto:

employee_name	salary	department_name	avg_salary
John Doe	93000	Accounting	93000
Jeremy Smith	134000	Sales	127000
Donna Hayes	120000	Sales	127000
Mark Ron	78000	Human Resources	75000
Denis Serge	72000	Human Resources	75000

Para rever mais exemplos de funções de janela, tente o artigo Exemplos de funções da Janela SQL com explicações.

6. Dê um nome a alguns registros comuns em Funções de Janela (Window Functions) em SQL.

As funções de janela podem ser organizadas em quatro categorias: funções agregadas, funções de classificação, funções analíticas e funções de distribuição.

As funções agregadas são as funções agregadas regulares que você usa com GROUP BY: MAX(), MIN(), AVG(), SUM(), e COUNT(). Estas funções, como já mostramos, podem ser usadas como funções de janela.

As funções de classificação são ROW_NUMBER(), RANK(), e DENSE_RANK(). Elas são usadas para obter diferentes posições em um ranking. Você pode encontrar uma explicação detalhada das funções de ranking no artigo seguinte.

As funções analíticas incluem LEAD(), LAG(), FIRST_VALUE(), NTH_VALUE(), LAST_VALUE(), e . Estas funções nos permitem obter dados de linhas diferentes da linha atual (por exemplo, a linha anterior, a próxima linha, a última linha dentro de uma moldura de janela, etc.). A função NTILE() divide as linhas dentro de uma partição em n grupos e retorna o número do grupo.

Finalmente as funções de distribuição PERCENT_RANK() e CUME_DIST() nos permitem obter dados sobre a distribuição percentual ou cumulativa (respectivamente) para cada linha na janela.

Prefiro as funções analíticas porque elas nos permitem comparar ou calcular as diferenças entre os diferentes registros dentro da janela (entre outras coisas). Por exemplo, se eu tiver uma série temporal com valores de estoque, eu poderia calcular o quanto o estoque aumentou a cada momento.

Aqui está outro exemplo de funções analíticas. As funções da janela analítica LEAD() e LAG() retornam uma coluna de uma linha subseqüente/anterior. Portanto, se tivermos uma tabela com moedas criptográficas, com um carimbo de tempo e um valor de cotação ...

Symbol	Timestamp	Value
BTC	2021-05-25 10:30	61400
BTC	2021-05-25 10:40	60300
BTC	2021-05-25 10:50	59800
ETH	2021-05-25 10:30	2700
ETH	2021-05-25 10:40	2750
ETH	2021-05-25 10:50	2820

Tabela de Ações

... podemos obter o seguinte relatório. Para calcular a porcentagem de variação, precisamos de dados de duas filas diferentes: O valor na linha atual, e o valor na linha anterior. A função LEAD() irá retornar o valor da linha anterior. Este é o resultado:

Symbol	Timestamp	Value	% Variation
BTC	2021-05-25 10:30	61400	--
BTC	2021-05-25 10:40	60300	-1.8%
BTC	2021-05-25 10:50	59800	-0.8%
ETH	2021-05-25 10:30	2700	--
ETH	2021-05-25 10:40	2750	1.8%
ETH	2021-05-25 10:50	2820	2.5%

A coluna % Variation foi calculada com este tipo de expressão:

(Current_value - Previous_value ) / Previous_value

Note que o valor da moeda criptográfica do carimbo da hora anterior pode ser obtido com:

LEAD(value) OVER (PARTITION BY crypto_symbol ORDER BY timestamp) 

Aqui está a consulta completa:

SELECT Symbol, 
       Timestamp, 
       Value, 
       (Value - LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) /   
       LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) AS “% variation”
FROM   Cryptocurrencies

Se você quiser aprofundar com LAG() e LEAD(), sugiro ler o artigo A Função LAG() e a Função LEAD() em SQL. Ele tem uma explicação detalhada sobre como as funções de janela funcionam em janelas ordenadas.

Perguntas sobre a Função Janela Intermediária

7. Como você define a moldura da janela?

As funções de janela calculam um resultado agregado com base em um conjunto de registros chamado "janela" ou "moldura de janela". A moldura de janela é definida pela cláusula OVER().

Uma cláusula OVER() vazia significa que a janela é o conjunto de dados completo:

SELECT employee_name,
       salary,
       department_name,
       AVG(salary) OVER () avg_salary 
FROM employee

A consulta acima calcula o salário médio e o exibe ao lado dos outros detalhes do empregado para todos os empregados da tabela.

Há várias subcláusulas que podem ser colocadas dentro da cláusula OVER() para definir com precisão uma janela.

A subcláusula PARTITION BY especifica que todos os registros com o mesmo valor em uma determinada coluna pertencem à mesma janela. Em outras palavras, PARTITION BY especifica como a janela é definida. Assim, a consulta seguinte calcula o salário médio para cada departamento; os cálculos são feitos com base nos agrupamentos dos valores na coluna department_name.

SELECT 
   employee_name,
   salary,
   department_name,
   AVG(salary) OVER (PARTITION BY department_name) avg_salary 
FROM employee

ORDER BY também pode ser usado dentro do OVER(). É usado para colocar as linhas da janela em uma ordem específica. As janelas ordenadas são muito importantes porque permitem o uso de várias funções analíticas como LAG(), LEAD(), e FIRST_VALUE().

SELECT 
   employee_name,
   salary,
   department_name,
   LAG(salary) OVER (ORDER BY salary) prev_salary 
FROM employee

Esta consulta exibe o salário do empregado imediatamente antes do empregado atual na ordem de pagamento. Note que é possível combinar as cláusulas ORDER BY e PARTITION BY em uma única consulta: o pedido é aplicado a cada divisória individualmente.

Duas sub-cláusulas similares OVER() são RANGE e ROWS. Elas definem limites para a estrutura da janela, colocando limites superiores e/ou inferiores na janela dos registros. Isto significa que as funções de janela podem ser calculadas com base em um subconjunto de linhas em vez de todas as linhas na janela. A diferença entre ROW e RANGE é explicada em detalhes em nossa folha de consultas das funções de janela SQL. Mais sobre ROWS e RANGE e as diferentes opções de limites disponíveis serão explicadas nas próximas duas perguntas.

8. Como funciona o ORDER BY OVER?

Algumas funções de janela (como LAG(), LEAD(), e FIRST_VALUE()) funcionam em uma janela ordenada de registros. Ao utilizar uma dessas funções, precisamos da subcláusula ORDER BY para definir os critérios de ordem. Um bom exemplo disso é a consulta anterior que usamos para calcular a porcentagem de variação para as moedas criptográficas:

SELECT Symbol, 
       Timestamp, 
       Value, 
       (Value - LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) /   
       LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) AS “% variation”
FROM   Cryptocurrencies

Na consulta acima, a cláusula OVER tem duas subcláusulas: PARTITION BY e ORDER BY. PARTITION BY define quais registros estão em cada janela e ORDER BY define a ordem dos registros na janela. (Neste exemplo, ordenamos os registros com base em seu carimbo de tempo.) Em seguida, a função LEAD() retorna o valor do registro anterior.

Se a cláusula OVER não incluir um ORDER BY e não tivermos ROWS/RANGE, então a moldura da janela é formada por todas as linhas de acordo com a cláusula PARTITION BY. Entretanto, quando usamos uma cláusula ORDER BY sem ROWS/RANGE, o quadro de janela inclui as linhas entre a primeira linha (baseada na cláusula ORDER BY) e a linha atual. Em outras palavras, as linhas que vão após a linha atual não serão incluídas na moldura da janela. (Explicaremos mais detalhes sobre estes limites na próxima pergunta).

As funções da janela que requerem uma subcláusula ORDER BY são:

• RANK()
• DENSE_RANK()
• LEAD()
• LAG()
• FIRST_VALUE()
• LAST_VALUE()
• NTH_VALUE()
• PERCENT_RANK()
• CUME_LIST()

Para entender mais sobre como funciona ORDER BY, consulte o artigo Como calcular a diferença entre duas linhas em SQL.

Perguntas sobre funções avançadas da janela

9. Explicar o que a PRECEDÊNCIA NÃO FUNDADADADA faz.

Um quadro de janela é um conjunto de linhas que estão de alguma forma relacionadas com a linha atual, que é avaliada separadamente dentro de cada divisória. Quando usamos a cláusula ORDER BY, podemos opcionalmente definir limites superiores e inferiores para a armação da janela. Os limites podem ser definidos como:

• UNBOUNDED PRECEDING
• n PRECEDING
• CURRENT ROW
• n FOLLOWING
• UNBOUNDED FOLLOWING

Estes limites podem ser definidos com as subcláusulas RANGE ou ROWS na cláusula OVER(). UNBOUNDED PRECEDING indica que o limite inferior da janela é o primeiro registro na janela; da mesma forma, o limite superior pode ser definido com UNBOUNDED FOLLOWING ou CURRENT ROW. Estes limites devem ser usados somente com janelas ordenadas.

Na imagem a seguir, podemos ver como funcionam os diferentes limites:

Por exemplo, se quisermos obter o valor médio de uma moeda criptográfica considerando apenas os valores que ocorrem até o valor atual, podemos usar a seguinte cláusula OVER():

AVG(value) OVER (PARTITION BY symbol_name 
                 ORDER BY timestamp 
                 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                 ) 

Nesta cláusula OVER(), definimos o CURRENT ROW como o limite superior da janela para calcular a média. Isto é exatamente o que precisamos, pois não queremos incluir os valores registrados após o timestamp atual no cálculo da média.

10. Descreva a Ordem de Operações do SQL e Funções de Janela (Window Functions) em SQL' Coloque nesta Ordem.

As subcláusulas de uma SQL SELECT são executadas na seguinte ordem:

1. FROM / JOINS
2. ONDE
3. GROUP BY
4. Funções agregadas
5. HAVING
6. Funções de Janela (Window Functions) em SQL
7. SELECIONE
8. DISTINCT
9. UNIÃO / INTERSEÇÃO / EXCETO
10. ORDEM POR
11. OFFSET
12. LIMITE / BUSCA / TOPO

Como as funções de janela são calculadas durante a etapa 6, não podemos colocá-las na cláusula WHERE (que é calculada na etapa 2). Entretanto, podemos contornar esta limitação usando um CTE (expressão comum de tabela), onde podemos chamar funções de janela e armazenar seus resultados como colunas no CTE. O CTE será tratado como uma tabela e os resultados das funções de janela serão avaliados como valores de colunas regulares pelo WHERE.

Há um artigo interessante sobre por que as funções de janela não são permitidas nas cláusulas WHERE que você deve ler se estiver procurando por alguns exemplos.

Por outro lado, podemos usar resultados de agregação/agrupamento em funções de janela, já que eles já são computados no momento em que as funções de janela são processadas.

Quer aprimorar suas habilidades em SQL Funções de Janela (Window Functions) em SQL?

Este artigo cobre várias possíveis perguntas de entrevistas de emprego sobre funções de janela SQL. Meu último conselho é sobre como conectar as perguntas deste artigo com as perguntas que você receberá durante uma entrevista. Aqui está:

Tente associar cada pergunta deste artigo com um tópico de função de janela, como "OVER clause", "name a function" ou "ORDER BY sub-clause". Então, se você for questionado sobre funções de janela durante a entrevista, identifique o tópico da pergunta e use as informações aqui para discutir o tópico.

Se você quiser ir mais fundo com as funções de janela SQL, sugiro o artigo Curso do Mês - Funções de Janela (Window Functions) em SQL, que descreve nosso curso Funções de Janela (Window Functions) em SQL. Crescer suas habilidades é um investimento que pode muito bem ajudá-lo a conseguir o emprego!