Dicas e truques de SQL

Índice

Formatação/legibilidade

• Usar vírgulas no início para separar campos
• Usar um valor fictício na cláusula WHERE
• Indentação adequada do código
• Considerar CTEs ao escrever consultas complexas

Recursos úteis

• Converter tipos de dados com o operador ::
• Aproveitar anti joins
• Usar QUALIFY para filtrar funções de janela
• É possível usar GROUP BY pela posição da coluna

Armadilhas a evitar

• Cuidado ao usar NOT IN com valores NULL
• Renomear campos calculados para evitar ambiguidade
• Especificar a qual tabela cada coluna pertence
• Entender a ordem de execução
• Comentar o código
• Ler a documentação inteira

Formatação/legibilidade

Usar vírgulas no início para separar campos

• Ao separar campos na cláusula SELECT, usar vírgulas no início ajuda a distinguir claramente uma nova coluna
• Vírgulas no início fornecem uma pista visual para verificar facilmente se alguma vírgula foi esquecida

SELECT
  employee_id,
  employee_name,
  job,
  salary
FROM employees;

Usar um valor fictício na cláusula WHERE

• Você pode usar um valor fictício na cláusula WHERE para adicionar e remover condições dinamicamente

SELECT *
FROM employees
WHERE 1=1 -- valor fictício
  AND job IN ('Clerk', 'Manager')
  AND dept_no != 5;

Indentação adequada do código

• Indentar o código melhora a legibilidade e facilita o entendimento para colegas e para você mesmo no futuro

-- Exemplo ruim:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

-- Exemplo bom:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

Considerar CTEs ao escrever consultas complexas

• Em vez de aninhar views inline, você pode usar expressões de tabela comuns (CTEs) para melhorar a legibilidade e a organização do código

-- Usando views inline:
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM
  (SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id) AS cs
INNER JOIN
  (SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id) AS vhs
ON cs.movie_id = vhs.movie_id;

-- Usando CTE:
WITH cinema_sales AS (
  SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id
),
vhs_sales AS (
  SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id
)
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM cinema_sales AS cs
INNER JOIN vhs_sales AS vhs
ON cs.movie_id = vhs.movie_id;

Recursos úteis

Converter tipos de dados com o operador ::

• Em alguns RDBMS, você pode usar o operador :: para converter um valor para outro tipo de dado

SELECT CAST('5' AS INTEGER); -- usando a função CAST
SELECT '5'::INTEGER; -- usando a sintaxe ::

Aproveitar anti joins

• Anti joins são muito úteis quando você quer retornar linhas que existem em apenas uma tabela
• Também é possível usar subqueries, mas em geral anti joins são mais rápidos

-- Anti join:
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

-- Subquery:
SELECT
  *
FROM video_content
WHERE series_id NOT IN (SELECT DISTINCT series_id FROM archive);

-- Subquery correlacionada:
SELECT
  *
FROM video_content
WHERE NOT EXISTS (
  SELECT 1
  FROM archive a
  WHERE a.series_id = vc.series_id
);

-- EXCEPT:
SELECT series_id
FROM video_content
EXCEPT
SELECT series_id
FROM archive;

Usar QUALIFY para filtrar funções de janela

• Você pode usar QUALIFY para filtrar o resultado de funções de janela
• Isso é útil para reduzir o número de linhas de código

-- Usando QUALIFY:
SELECT
  product,
  market,
  SUM(revenue) AS market_revenue
FROM sales
GROUP BY product, market
QUALIFY DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) <= 10
ORDER BY product, market_revenue;

-- Sem QUALIFY:
SELECT
  product,
  market,
  market_revenue
FROM (
  SELECT
    product,
    market,
    SUM(revenue) AS market_revenue,
    DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) AS market_rank
  FROM sales
  GROUP BY product, market
)
WHERE market_rank <= 10
ORDER BY product, market_revenue;

É possível usar GROUP BY pela posição da coluna

• Em vez do nome da coluna, você pode usar a posição da coluna em GROUP BY ou ORDER BY
• Isso é útil em consultas temporárias, mas em código de produção você deve sempre referenciar os nomes das colunas

SELECT
  dept_no,
  SUM(salary) AS dept_salary
FROM employees
GROUP BY 1 -- dept_no é a primeira coluna da cláusula SELECT
ORDER BY 2 DESC;

Armadilhas a evitar

Cuidado ao usar NOT IN com valores NULL

• NOT IN não funciona quando há valores NULL
• Em vez disso, use NOT EXISTS

INSERT INTO departments (id)
VALUES (1), (2), (NULL);

-- Não funciona por causa do valor NULL
SELECT *
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT id FROM departments);

-- Solução
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.id = e.department_id
);

Renomear campos calculados para evitar ambiguidade

• Renomear um campo calculado com o nome de uma coluna existente pode causar comportamentos inesperados

INSERT INTO products (product, revenue)
VALUES ('Shark', 100), ('Robot', 150), ('Alien', 90);

-- A função de janela classifica o produto 'Robot' em 1º lugar
SELECT
  product,
  CASE product WHEN 'Robot' THEN 0 ELSE revenue END AS revenue,
  RANK() OVER (ORDER BY revenue DESC)
FROM products;

Especificar a qual tabela cada coluna pertence

• Em consultas complexas, especificar a qual tabela cada coluna pertence facilita rastrear problemas

SELECT
  vc.video_id,
  vc.series_name,
  metadata.season,
  metadata.episode_number
FROM video_content AS vc
INNER JOIN video_metadata AS metadata
ON vc.video_id = metadata.video_id;

Entender a ordem de execução

• O conselho mais importante para quem está aprendendo SQL é entender a ordem de execução
• Quando você entende a ordem de execução, a forma como escreve consultas muda completamente

Comentar o código

• Ao escrever código, você deve adicionar comentários explicando o motivo
• Seus colegas e seu eu do futuro vão agradecer

SELECT
  video_content.*
FROM video_content
LEFT JOIN archive -- o novo CMS não consegue processar o formato de vídeo arquivado
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

Ler a documentação inteira

• Ler a documentação de ponta a ponta ajuda a evitar problemas inesperados
• Leva só alguns minutos e pode ajudar a resolver problemas que você não esperava

-- Se você tivesse lido mais a documentação, poderia ter resolvido o problema com NULL
SELECT COALESCE(GREATEST(signup_date, consumption_date), signup_date, consumption_date);

-- É possível usar a função GREATEST_IGNORE_NULLS
SELECT GREATEST_IGNORE_NULLS(signup_date, consumption_date);

Resumo do GN⁺

• Este texto traz várias dicas e truques para escrever SQL de forma mais eficiente e legível
• Há muita informação útil tanto para quem está começando em SQL quanto para analistas de dados mais experientes
• Em especial, usar CTEs em consultas complexas, aproveitar anti joins e usar QUALIFY tende a ajudar bastante no trabalho do dia a dia
• É importante criar o hábito de entender a ordem de execução do SQL, comentar o código e ler a documentação com atenção
• Outras ferramentas com funcionalidades parecidas incluem PostgreSQL, MySQL e Oracle

[hiyama]

Parece que todas as vírgulas iniciais neste post foram escritas como vírgulas finais. No original, elas estão como iniciais.

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7, -- First argument of IFF.  
	LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity), -- Second argument of IFF.  
		NULL) AS C7_fta_share -- Third argument of IFF.  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,   
		LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),   
			NULL) AS C28_fta_share  
FROM timeslot_data  
;  

[GN⁺]

Comentários do Hacker News

• Necessidade de entender bem o servidor de banco de dados e verificar com frequência o plano de execução das consultas

  • EXISTS muitas vezes é mais rápido que IN
  • NOT EXISTS e EXCEPT se comportam de forma diferente no tratamento de valores NULL
  • Recomenda-se usar colunas de subconsulta em vez de junções de tabelas
  • É preciso evitar varreduras de tabela e adicionar índices
  • Ao filtrar expressões, é possível usar colunas calculadas e índices
  • UNION ALL pode ser mais rápido que OR
  • É possível forçar a ordem de filtragem por meio de JOIN com subconsultas

• Dicas para trabalhar com procedures armazenadas complexas

  • Copiar tabelas permanentes para tabelas temporárias e filtrar apenas as linhas necessárias
  • Manipular as tabelas temporárias
  • Atualizar as tabelas permanentes dentro de uma transação e fazer rollback em caso de erro
  • É preciso ter cuidado ao trabalhar com tabelas remotas; recomenda-se copiar para tabelas temporárias antes de operar
  • Como o plano de execução pode ficar confuso, é melhor dividir o trabalho em etapas menores
  • Sempre verificar o plano de execução

• Opiniões sobre legibilidade de código

  • Os dois primeiros exemplos sacrificam a legibilidade e aumentam a facilidade de escrita
  • No último exemplo, a indentação não traz um efeito tão grande

• Sugestão de usar a sintaxe FROM-first e de piping no SQL

  • A experiência de uso da Kusto query language foi um grande avanço

• Dica sobre Anti Join

  • Recomenda-se usar EXISTS, pois é vantajoso para verificar a existência de linhas em subconsultas baseadas em condições

• Vantagens de usar vírgula no início da linha em instruções SELECT

  • Permite comentar linhas individuais
  • Melhora a legibilidade com a indentação do código

• No MSSQL, ao usar comentários, recomenda-se /* */ em vez de --

  • Porque o query store salva as consultas sem quebras de linha

• Recomenda-se usar funções de janela

• Polêmica sobre o uso de 1=1 na cláusula WHERE

• Apresentação do AI2sql

  • Permite gerar consultas SQL a partir de prompts em inglês simples
  • Útil ao escrever consultas complexas