Consejos y trucos de SQL

 (github.com/ben-n93)
7 puntos por GN⁺ 2024-09-26 | 2 comentarios | Compartir por WhatsApp

Índice

Formato/legibilidad

  • Usar comas al inicio para separar campos
  • Usar un valor dummy en la cláusula WHERE
  • Indentación adecuada del código
  • Considerar CTE al escribir consultas complejas

Funciones útiles

  • Convertir tipos de datos usando el operador ::
  • Aprovechar los anti joins
  • Usar QUALIFY para filtrar funciones de ventana
  • Se puede usar GROUP BY por posición de columna

Errores que hay que evitar

  • Tener cuidado al usar NOT IN con valores NULL
  • Cambiar el nombre de campos calculados para evitar ambigüedad
  • Especificar a qué tabla pertenece cada columna
  • Entender el orden de ejecución
  • Comentar el código
  • Leer toda la documentación

Formato/legibilidad

Usar comas al inicio para separar campos

  • Al separar campos en la cláusula SELECT, usar comas al inicio puede ayudar a distinguir claramente cada nueva columna
  • Las comas al inicio dan una pista visual para detectar fácilmente si falta alguna coma
SELECT
  employee_id,
  employee_name,
  job,
  salary
FROM employees;

Usar un valor dummy en la cláusula WHERE

  • Usar un valor dummy en la cláusula WHERE permite agregar y quitar condiciones de forma dinámica
SELECT *
FROM employees
WHERE 1=1 -- valor dummy
  AND job IN ('Clerk', 'Manager')
  AND dept_no != 5;

Indentación adecuada del código

  • Indentar el código mejora la legibilidad y hace más fácil entenderlo para tus colegas y para tu yo del futuro
-- Mal ejemplo:
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;

-- Buen ejemplo:
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 CTE al escribir consultas complejas

  • En vez de anidar vistas inline, se pueden usar expresiones de tabla comunes (CTE) para mejorar la legibilidad y organización del código
-- Uso de vista 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;

-- Uso de 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;

Funciones útiles

Convertir tipos de datos usando el operador ::

  • En algunos RDBMS, se puede usar el operador :: para convertir un valor a otro tipo de dato
SELECT CAST('5' AS INTEGER); -- uso de la función CAST
SELECT '5'::INTEGER; -- uso de la sintaxis ::

Aprovechar los anti joins

  • Los anti joins son muy útiles para devolver filas que existen solo en una de las tablas
  • También se puede usar una subconsulta, pero en general el anti join suele ser más rápido
-- 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;

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

-- Subconsulta 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 funciones de ventana

  • QUALIFY permite filtrar el resultado de funciones de ventana
  • Es útil para reducir líneas 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;

-- Sin 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;

Se puede usar GROUP BY por posición de columna

  • En lugar del nombre de la columna, se puede usar la posición de la columna en GROUP BY u ORDER BY
  • Es útil para consultas temporales, pero en código de producción siempre se debería referenciar por nombre de columna
SELECT
  dept_no,
  SUM(salary) AS dept_salary
FROM employees
GROUP BY 1 -- dept_no es la primera columna de la cláusula SELECT
ORDER BY 2 DESC;

Errores que hay que evitar

Tener cuidado al usar NOT IN con valores NULL

  • NOT IN no funciona cuando hay valores NULL
  • En su lugar, se debería usar NOT EXISTS
INSERT INTO departments (id)
VALUES (1), (2), (NULL);

-- No funciona por el valor NULL
SELECT *
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT id FROM departments);

-- Solución
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.id = e.department_id
);

Cambiar el nombre de campos calculados para evitar ambigüedad

  • Si un campo calculado se renombra con el nombre de una columna existente, puede provocar comportamientos inesperados
INSERT INTO products (product, revenue)
VALUES ('Shark', 100), ('Robot', 150), ('Alien', 90);

-- La función de ventana rankea el producto 'Robot' en primer lugar
SELECT
  product,
  CASE product WHEN 'Robot' THEN 0 ELSE revenue END AS revenue,
  RANK() OVER (ORDER BY revenue DESC)
FROM products;

Especificar a qué tabla pertenece cada columna

  • En consultas complejas, indicar a qué tabla pertenece cada columna 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 el orden de ejecución

  • El consejo más importante para quien está aprendiendo SQL es entender el orden de ejecución
  • Si entiendes el orden de ejecución, tu forma de escribir consultas cambia por completo

Comentar el código

  • Al escribir código, hay que agregar comentarios que expliquen el porqué
  • Tus colegas y tu yo del futuro te lo van a agradecer
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive -- El nuevo CMS no puede procesar el formato de video archivado
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

Leer toda la documentación

  • Leer la documentación completa ayuda a evitar problemas inesperados
  • No toma más que unos minutos y puede ayudar a resolver problemas inesperados
-- Si hubieras leído más la documentación, habrías podido resolver el problema de NULL
SELECT COALESCE(GREATEST(signup_date, consumption_date), signup_date, consumption_date);

-- Se puede usar la función GREATEST_IGNORE_NULLS
SELECT GREATEST_IGNORE_NULLS(signup_date, consumption_date);

Resumen de GN⁺

  • Este artículo ofrece varios consejos y trucos para escribir SQL de forma más eficiente y legible
  • Tiene mucha información útil tanto para quienes están aprendiendo SQL por primera vez como para analistas de datos con experiencia
  • En especial, usar CTE al escribir consultas complejas, aprovechar anti joins y usar QUALIFY puede ser de gran ayuda en el trabajo diario
  • Es importante entender el orden de ejecución de SQL, comentar el código y desarrollar el hábito de leer la documentación con atención
  • Otras herramientas con funciones similares incluyen PostgreSQL, MySQL y Oracle

Lecturas relacionadas

2 comentarios

 
hiyama 2024-09-26

Parece que en esta publicación todas las comas iniciales están escritas como comas finales. En el original están puestas como comas iniciales.

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt; 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()) &gt;= 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⁺ 2024-09-26
Comentario de Hacker News

  • La necesidad de entender bien el servidor de base de datos y revisar con frecuencia el plan de ejecución de las consultas

    • A menudo EXISTS es más rápido que IN
    • NOT EXISTS y EXCEPT funcionan de forma diferente al manejar valores NULL
    • Se recomienda usar columnas de subconsultas en lugar de joins de tablas
    • Hay que evitar los table scans y agregar índices
    • Al filtrar expresiones, se pueden usar columnas calculadas e índices
    • UNION ALL puede ser más rápido que OR
    • Se puede forzar el orden de filtrado mediante joins con subconsultas

  • Consejos para trabajar con procedimientos almacenados complejos

    • Copiar tablas permanentes a tablas temporales y filtrar solo las filas necesarias
    • Manipular las tablas temporales
    • Actualizar las tablas permanentes dentro de una transacción y hacer rollback si hay errores
    • Hay que tener cuidado al trabajar con tablas remotas; se recomienda copiarlas primero a tablas temporales y luego operar sobre ellas
    • Como el plan de consulta puede volverse confuso, conviene dividir el trabajo en pasos pequeños
    • Revisar siempre el plan de consulta

  • Opiniones sobre la legibilidad del código

    • Los dos primeros ejemplos sacrifican legibilidad para facilitar la escritura
    • En el último ejemplo, la indentación no aporta un gran efecto

  • Sugerencia de usar la sintaxis FROM-first y de piping en SQL

    • La experiencia de usar Kusto query language ha sido un gran avance

  • Consejo relacionado con Anti Join

    • Se recomienda usar EXISTS, ya que es ventajoso para verificar la existencia de filas en subconsultas basadas en condiciones

  • Ventajas de usar coma inicial en sentencias SELECT

    • Permite comentar líneas individuales
    • Mejora la legibilidad mediante la indentación del código

  • En MSSQL, se recomienda usar /* */ en lugar de -- al usar comentarios

    • Esto se debe a que Query Store guarda las consultas sin saltos de línea

  • Se recomienda usar funciones de ventana

  • Debate sobre el uso de 1=1 en la cláusula WHERE

  • Introducción a AI2sql

    • Permite generar consultas SQL a partir de prompts en inglés plano
    • Es útil al escribir consultas complejas