Comandos SQL para cientistas de dados

Como cientista de dados ou analista de dados, os dados estão naturalmente no centro do seu trabalho. Existem muitas fontes das quais você pode coletar dados. Muitas vezes, eles estão em um banco de dados SQL — e, nesse caso, entender comandos de consulta SQL pode ser essencial para ter sucesso na função. Este artigo apresenta desde os comandos mais básicos até operações mais avançadas que vão ajudar bastante no seu dia a dia como analista ou cientista de dados.

Para entender os comandos acima, vamos usar o banco de dados open source de locadora de DVDs e mostrar como aplicar cada um deles. Você encontra o diagrama de relacionamento de entidades (ERD) aqui.

Neste artigo, configuramos o banco de dados localmente e executamos todas as consultas usando o pgAdmin4, portanto, estamos usando PostgreSQL. Muitos — se não todos — os comandos devem funcionar em outros servidores SQL. A instalação do PostgreSQL e a configuração do banco de dados em si estão fora do escopo deste artigo; recomendamos consultar outros recursos, como o guia para iniciantes em PostgreSQL da DataCamp, para orientações sobre esses processos.

Recuperação simples de dados

SELECT FROM

O exemplo mais simples de consulta é trazer todo o conteúdo de uma tabela específica do banco. Se quisermos conhecer todas as categorias de filmes na tabela "category", podemos usar:

SELECT *
FROM category
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

Aqui, especificamos o nome da tabela após FROM. Como queremos todo o conteúdo da tabela, usamos * após SELECT para indicar que queremos todas as colunas.

DISTINCT

Há casos em que só nos interessam os valores únicos. Por exemplo: e se quisermos saber todas as classificações MPAA na tabela de filmes? Usar DISTINCT permite retornar apenas valores sem repetição:

SELECT DISTINCT(rating)
FROM film
Tecnologia

O que retorna:

Recuperação de dados com condições simples

WHERE

Também há situações em que queremos recuperar apenas registros que atendem a determinadas condições. Podemos definir condições que precisam ser satisfeitas antes de retornar os dados usando a cláusula WHERE. Em essência, estamos filtrando os registros conforme o critério. Por exemplo: e se quisermos apenas os filmes classificados como PG-13?

SELECT title
FROM film
WHERE rating = 'PG-13'
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

Além disso, podemos listar os filmes com taxa de locação a partir de 4.5:

SELECT title, rental_rate
FROM film
WHERE rental_rate >= 4.5
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

ORDER BY

Ordenar o resultado ajuda bastante a entender melhor os dados. Que tal pegar a mesma lista de filmes PG-13 acima, mas ordenada por duração do maior para o menor, e trazer também a duração?

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

LIMIT

Às vezes, também nos interessam apenas um número limitado de registros. Aqui, queremos só os 10 filmes PG-13 mais longos, então usamos a cláusula LIMIT.

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
LIMIT 10
Tecnologia

O que retorna:

Agregações

GROUP BY & COUNT( )

Agregações são usadas com frequência para obter um resumo do conjunto de dados e gerar insights. Normalmente, elas são combinadas com a cláusula GROUP BY. Por exemplo, se precisamos saber quantas locações cada cliente fez até agora, podemos contar as locações e ordenar o resultado com quem mais locou no topo.

SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

SUM( )

E quanto ao total que cada cliente gastou? Podemos resolver isso usando a função agregadora SUM. Aqui, queremos o maior gastador no topo.

SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

AVG( ) & HAVING

Depois de calcular uma agregação agrupada, podemos adicionar condições com HAVING. Por exemplo: queremos saber quais categorias de classificação MPAA têm filmes com duração média acima de 115 minutos.

SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) > 115
Tecnologia

O que retorna:

MIN( ) & alias

Nem sempre é necessário agrupar para usar uma agregação. Se quisermos saber a menor duração de filme, basta usar MIN. Também queremos que a coluna apareça como 'shortest_movie_length', o que pode ser feito atribuindo um alias com 'AS'.

SELECT MIN(length) AS shortest_movie_length
FROM film
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

Você também pode usar alias para nomes de tabelas. Isso é útil quando os nomes são longos, especialmente ao fazer joins (veja abaixo). Vale a pena escolher aliases que deixem o código mais legível.

Existem outras funções de agregação úteis — você pode saber mais no curso Introduction to SQL.

Joins

INNER JOIN (JOIN)

Você provavelmente percebeu que, olhando apenas uma tabela, as possibilidades são limitadas. Muitas vezes queremos combinar dados de várias tabelas — e é aí que entram os JOINs. Podemos juntar tabelas com base em uma chave em comum. Por exemplo, se quisermos filmes que não estão em inglês:

SELECT film.title, language.name
FROM film
JOIN language
    ON film.language_id = language.language_id
WHERE language.name != 'English'
Tecnologia

(Observação: neste banco de dados não há filmes em outro idioma além do inglês. Essa consulta retorna uma tabela vazia.)

Também é possível fazer join de múltiplas tabelas. No exemplo abaixo, queremos saber as categorias e suas respectivas médias de avaliação. Também ordenamos pelos maiores valores primeiro.

SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

Existem vários tipos de join: INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN. Aqui usamos JOIN, que por padrão é um INNER JOIN — ele retorna registros apenas quando há correspondência nas duas tabelas. Para aprender mais sobre as diferenças entre esses joins, confira o curso Joining Data in SQL.

Mudando tipos de dados

CAST( )

No exemplo acima, em que calculamos valores em dinheiro, você pode ter notado que o SQL vê os valores apenas como números. Podemos formatá-los como montantes em dinheiro usando a função CAST:

SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

Dinheiro não é o único tipo para o qual podemos converter valores. Também é possível converter números para float, texto e até data e hora.

ROUND( )

De forma parecida, podemos arredondar números. No exemplo em que calculamos a média da taxa de locação, há muitas casas decimais. Podemos usar ROUND para arredondar para duas casas:

SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

Condições complexas

Instrução CASE

Combinando várias das funções e cláusulas acima, você consegue criar consultas bem complexas. Porém, há situações em que o básico não é suficiente. Por exemplo, se quisermos adicionar uma coluna categorizando a duração do filme: rotular como 'Short Film' se tiver menos de 50 minutos, 'Long Film' se tiver mais de 150, e 'Medium Film' para o restante. Isso pode ser feito com um CASE:

SELECT title, length,
CASE
    WHEN length < 50 THEN 'Short Film'
    WHEN length > 150 THEN 'Long Film'
    ELSE 'Medium Film'
END AS Movie_Length_Category
FROM film
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

Como você pode ver, o resultado é retornado quando a condição especificada é atendida. Isso é semelhante a instruções 'if-else' em linguagens de programação.

Subqueries

Agora, podemos querer saber quais são os filmes mais longos e seus títulos. Para isso, podemos usar a cláusula WHERE que vimos acima, só que com uma condição mais complexa:

SELECT title, length
FROM film
WHERE length = (SELECT max(length)
                FROM film)
Tecnologia

O que retorna:

Consultas complexas também podem ser feitas com subqueries. Neste exemplo, queremos os títulos dos filmes cuja taxa de locação é maior que a taxa média de locação de todos os filmes de comédia:

SELECT title, rental_rate
FROM film
WHERE rental_rate >
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
    ON film.film_id=cat_id.film_id
JOIN category AS cat
    ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = 'Comedy')
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

Nesse caso, primeiro encontramos a taxa média de locação dos filmes de comédia. O valor obtido nessa consulta (entre parênteses acima) é então usado como condição na cláusula WHERE para buscar os títulos dos filmes.

Common Table Expressions (CTEs)

Em um exemplo mais complexo, podemos usar uma common table expression (CTE) para salvar uma tabela temporária da qual extraímos as informações necessárias. Aqui, queremos saber o número médio de locações que cada funcionário processa por semana.

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id
Tecnologia

O que retorna:

No exemplo acima, salvamos o número de locações por semana em uma tabela temporária chamada 'weekly_rentals', sobre a qual rodamos outra consulta para obter o resultado final.

Window functions

Window functions são um conjunto importante de recursos do SQL e podem levar suas habilidades para o próximo nível. Apesar de serem consideradas de nível intermediário a avançado, muitos analistas e cientistas de dados as consideram essenciais no trabalho. Vale a pena dedicar um tempo para se familiarizar com elas.

OVER( ) & PARTITION BY. Suponha que queiramos descobrir o número semanal de locações de cada funcionário e o total geral de locações, junto com o ID do funcionário e o número da semana. Podemos reutilizar a CTE do exemplo anterior, como abaixo:

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

Usar OVER no código indica que é uma window function. Aqui, estamos aplicando a função de janela (SUM) a todas as linhas. Também podemos combinar com PARTITION BY para aplicar a função em "partições" específicas. Agora, vamos calcular o total de locações por semana em vez do total geral (além das outras colunas). Novamente, usamos a mesma CTE gerada antes.

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals
Tecnologia

O que retorna (apenas as 10 primeiras linhas):

A coluna adicional traz o total semanal de locações, em vez de um acumulado geral.

Aqui apresentamos apenas alguns exemplos simples de window functions que são importantes para atuar como analista ou cientista de dados em muitas empresas. Recomendamos aprofundar nesses comandos usando os links indicados. Você também pode conferir o curso Intermediate SQL para aprender ou revisar suas habilidades de SQL. Além disso, pratique usando um banco de dados open source e testando esses comandos enquanto pensa em perguntas relevantes para responder.