¿Qué es el SQL avanzado?

¿Estás confundido sobre las habilidades avanzadas de SQL? ¿Qué son? Este artículo explicará lo que puede significar el SQL avanzado, sobre todo porque lo utilizamos en LearnSQL.es.

Seguro que encuentras muy a menudo las frases "conocimientos avanzados de SQL" o "temas avanzados de SQL". Lees un artículo sobre SQL avanzado y te alegras de lo fácil que parecen ser estos temas avanzados. Luego hablas con alguien y ves que considera todo lo que sabes como conocimientos básicos de SQL. ¿Cómo te defines a ti mismo? ¿Te consideras un usuario de SQL básico, intermedio o avanzado?

SQL avanzado Está en todas partes

SQL avanzado está en todas partes. Bueno, la palabra "avanzado" lo está, al menos. Es muy utilizada por los estudiantes y usuarios de SQL. Puedes encontrarla en las descripciones de los cursos de SQL, en los anuncios de empleo y en las preguntas de las entrevistas de trabajo. Está en la literatura de SQL. Se oye cuando los colegas hablan en el trabajo. Está en numerosos artículos que intentan definir qué es el SQL avanzado.

En realidad, no estoy tratando de dar una definición de SQL avanzado. Intento decirte otra cosa: ¡no hay una sola definición de SQL avanzado! Y deberías dejar de buscarla. ¿Qué deberías hacer en su lugar?

Abrazar la inconsistencia

Así es. Debes aceptar que el término "SQL avanzado" se utiliza de forma incoherente. Significa cosas diferentes dependiendo del contexto y de quién utilice el término.

Es lógico que SQL avanzado incluya una cosa para alguien que escribe informes SQL y algo totalmente diferente para alguien que contrata a un analista de datos. Un desarrollador de software seguramente tendrá otra definición de lo que incluye el SQL avanzado.

Ya te haces una idea. SQL avanzado no puede tener una sola definición. Cuando leas sobre habilidades SQL avanzadas, siempre debes considerar el contexto, quién está hablando y su audiencia.

¿Qué podría incluir SQL avanzado?

Por ejemplo, hay una discusión muy interesante sobre SQL avanzado en Stack Overflow.

La discusión fue iniciada por alguien que buscaba un trabajo en SQL y señaló que hay muchos trabajos que requieren "conocimientos avanzados de SQL". El usuario se pregunta qué es lo que se espera de este tipo de trabajo. ¿Qué conocimientos se consideran avanzados?

La primera respuesta da un fragmento de código bastante largo como medida de los conocimientos avanzados. Aunque es bastante largo, no es tan complicado. Según esta respuesta, el SQL avanzado abarca la selección de columnas, las funciones agregadas como MIN() y MAX(), la sentencia CASE WHEN, JOINs, la cláusula WHERE, GROUP BY, la declaración de variables y las subconsultas.

Por otro lado, la siguiente respuesta considera que la mayoría de estos temas son básicos o intermedios en el mejor de los casos. Este usuario cree que los temas avanzados de SQL incluyen funciones, procedimientos almacenados, consultas jerárquicas, disparadores, índices, modelado de datos (formas normales, claves primarias y foráneas, restricciones de tablas), transacciones y mucho más. Esto se acerca mucho más a mi definición de SQL avanzado y a lo que me enseñaron en las clases de SQL. Sin embargo, éste era un programa para administradores de bases de datos; es comprensible que estos conocimientos se consideren avanzados. Es posible que algunos especialistas en informes y analistas de datos nunca necesiten utilizar estas cosas.

Es interesante observar que a veces JOINs se considera avanzado mientras que la escritura de procedimientos almacenados sigue considerándose un conocimiento básico. Puedo entender por qué, ya que un usuario insinúa el problema con JOINs. Aunque generalmente se consideran conocimientos básicos, muchos usuarios de SQL aprenden temas mucho más avanzados antes de entender realmente JOINs. Así es como lo básico se convierte fácilmente en conocimiento avanzado. No es raro encontrar a alguien usando funciones llamativas, disparadores y demás, sin saber cómo escribir un simple JOIN.

¿Qué es SQL avanzado en LearnSQL.com?

Antes de explicar lo que es el SQL avanzado, es esencial saber lo que no es. Cuando miras nuestros cursos y artículos, el SQL básico/intermedio es cualquier cosa en SQL-92. (Aquí tienes la historia y los detalles de los estándares SQL si estás interesado en saber más). Esto incluye

• Todos los tipos de JOINs
• Funciones agregadas
• GROUP BY
• HAVING
• Subconsultas
• Operaciones de conjunto (UNION, UNION ALL, INTERSECT, MINUS)

Debes estar familiarizado con estos temas si pretendes conocer SQL. Son cosas que debes entender antes de pasar a temas más avanzados.

Generalmente, consideramos tres temas como 'SQL avanzado':

• Funciones de ventana
• Expresiones de tabla comunes (CTEs)
• GROUP BY extensiones (ROLLUP, CUBE, y GROUPING SETS)

Cualquiera que quiera aprender (o practicar) los tres temas debería consultar nuestro tema SQL avanzado . Por supuesto, éste no es el único curso de SQL avanzado que existe; ya hemos revisado algunos excelentes cursos de SQL avanzado de otras plataformas. Por ahora, veamos un ejemplo de cada uno de estos temas.

Funciones de ventana

Las funciones de ventana de SQL le permiten realizar operaciones que a menudo son necesarias para crear informes, por ejemplo, clasificar datos, calcular totales y medias móviles, encontrar la diferencia entre filas, etc. No sólo eso, sino que también puede dividir los datos en ventanas, lo que le permite realizar operaciones en subconjuntos de datos en lugar de los datos en su totalidad. Puedes aprender mucho más sobre esto en nuestro curso Funciones de ventana .

Veamos un ejemplo. Este código mostrará la diferencia en el número anual de coches vendidos, según la marca (es decir, la marca del coche):

SELECT	car_make,
		cars_sold,
		year,
		cars_sold - LAG(cars_sold) OVER (PARTITION BY car_make ORDER BY year) AS sales_diff
FROM cars_sale;

Para obtener esta información, primero tienes que seleccionar las columnas que quieres en el resultado: car_make, cars_sold, year. Para obtener la diferencia anual, reste la venta del año anterior a la venta del año en curso: cars_sold - LAG(cars_sold) OVER (PARTITION BY car_make ORDER BY year) AS sales_diff. Aquí, cars_sold significa las ventas del año en curso. La función LAG() permite obtener datos de la fila anterior. La cláusula OVER significa que se trata de una función de ventana. Luego sigue la cláusula PARTITION BY, que se utiliza para definir la ventana (subconjunto de datos) que queremos utilizar. En este caso, es car_make; esto significa que la función calculará la diferencia de ventas sólo dentro de una marca de coche específica. Cuando se encuentre con otra marca de coche, la función se reiniciará y empezará a calcular la diferencia de ventas de nuevo.

Por último, la operación está ordenada por año de forma ascendente. ¿Por qué? La función LAG() nos dará los datos de la fila anterior. Por lo tanto, si esta operación se realiza para cada año en orden ascendente, el "año anterior" significará la fila anterior. Eso es exactamente lo que necesitamos.

Echa un vistazo al resultado:

¿Has visto el valor NULL en la primera fila? Eso es porque 2015 es el primer año; no hay datos anteriores que se puedan deducir. Si sigues los resultados, verás que cada fila es la diferencia entre las ventas anuales de la fila actual y las ventas anuales de la fila anterior. Cuando llegas a la fila en la que empieza Renault, vuelve a haber un NULL. Esto es lo que hacen las funciones de ventana; trabajan sobre los datos dentro de una ventana determinada., he definido la ventana según el car_make, así que la función de ventana se reinicia cuando obtenemos un nuevo valor en esta columna. Es lógico. ¿Por qué debo deducir las ventas de Renault de las de Nissan? Quiero hacerlo para cada marca de coche por separado.

Expresiones comunes de tabla (CTE)

Las CTEs le permitirán escribir consultas complejas sin utilizar subconsultas manteniendo su código simple y directo. Le ofrecen la posibilidad de elaborar informes complejos de forma rápida y eficaz. También le permiten realizar algunos cálculos que no podría hacer de otro modo.

¿Qué es una expresión de tabla común? Es un resultado temporal que puede utilizar en la sentencia SELECT. Funciona como una tabla temporal: puede unirla con otras tablas, con otros CTEs o consigo misma.

Pueden ser útiles si, por ejemplo, tiene que informar sobre el tiempo empleado en un proyecto concreto. Por un lado, hay una tabla que contiene datos sobre la fecha en que cada empleado trabajó en este proyecto. También está la hora de inicio y la hora de finalización. Por otro lado, hay una tabla que contiene los nombres de los empleados. Tiene que producir una tabla que muestre el nombre de cada empleado y su tiempo medio de trabajo en este proyecto.

Así es como el CTE puede ayudarle:

WITH time_worked AS (
	SELECT	employee_id,
			end_time - start_time AS time
FROM project_timesheet
)
SELECT	e.first_name,
		e.last_name,
		AVG (tw.time) AS avg_time_worked
FROM employee e 
LEFT JOIN time_worked tw 
ON e.id = tw.employee_id
GROUP BY e.first_name, e.last_name;

¿Cómo funciona este CTE? Todo CTE se abre con la cláusula WITH. Luego debes nombrar tu CTE; en este caso, es time_worked. Luego escribes una sentencia SELECT. Aquí, usaré la CTE para calcular cuánto tiempo trabajó cada empleado cada vez que trabajó en el proyecto. Necesito el CTE porque no tengo esta información declarada explícitamente en la tabla; sólo tengo el start_time y el end_time. Para calcular el tiempo medio trabajado, el primer paso es obtener el tiempo trabajado. Por eso este CTE deduce el start_time del end_time y muestra el resultado en la columna time. Los datos se toman de la tabla project_timesheet.

Ahora que he escrito el CTE, puedo utilizarlo en la siguiente sentencia SELECT. Primero, obtendré el nombre y el apellido de la tabla employee. Luego utilizaré la función AVG() en la columna time del CTE time_worked. Para ello, he utilizado el LEFT JOIN - y lo he utilizado exactamente como lo haría con cualquier otra tabla. Por último, los datos se agrupan por los nombres y apellidos de los empleados.

El resultado es una pequeña tabla como ésta:

Si los CTEs le han interesado, imagine lo que podrá hacer después de terminar nuestro curso Consultas recursivas y expresiones de tabla comunes . Ah, sí - no he mencionado que una CTE puede ser recursiva, lo que significa que se referencia a sí misma. Al hacerlo, devuelve el subresultado y repite el proceso hasta devolver el resultado final. Mientras que los CTEs pueden ser no recursivos, no hay consultas recursivas que no sean CTE. Si quieres aprender a hacer consultas recursivas, conocer los CTEs es imprescindible.

Extensiones GROUP BY

Las extensiones de SQL GROUP BY le proporcionan posibilidades adicionales para agrupar datos. Esto, a su vez, puede aumentar la complejidad de su análisis de datos y de los informes que cree.

Existen tres extensiones GROUP BY:

• ROLLUP
• CUBE
• GROUPING SETS

A diferencia de GROUP BY, ROLLUP le permite agrupar los datos en varios conjuntos de datos y agregar los resultados en diferentes niveles. En pocas palabras: puede utilizar ROLLUP para calcular totales y subtotales, como en las tablas dinámicas de Excel.

La extensión CUBE es similar, pero hay una diferencia crucial. CUBE generará subtotales para cada combinación de las columnas especificadas.

Por último, existe GROUPING SETs. Un conjunto de agrupación es un conjunto de columnas que se utiliza en la cláusula GROUP BY. Puede conectar diferentes consultas que contengan GROUP BY si utiliza UNION ALL. Sin embargo, cuantas más consultas tenga, más complicado será. Puedes conseguir el mismo resultado pero con consultas mucho más ordenadas utilizando GROUPING SETS.

Permítame mostrarle cómo funciona ROLLUP. Supongamos que trabajas para una tienda de guitarras que tiene varios locales. A veces necesitará crear un informe que muestre el número total de guitarras que tiene en stock. Esta es una consulta que lo hará a nivel de fabricante, modelo y tienda:

SELECT	manufacturer,
		model,
		store,
		SUM(quantity) AS quantity_sum
FROM guitars
GROUP BY ROLLUP (manufacturer, model, store)
ORDER BY manufacturer;

Esto no parece complicado. Es una simple sentencia SELECT que te dará las columnas manufacturer, model, y store de la tabla guitars. He utilizado la función agregada SUM() para obtener la cantidad. Luego he escrito GROUP BY seguido inmediatamente por ROLLUP. Los datos se agruparán según las columnas de los paréntesis. Por último, el resultado está ordenado por el fabricante.

¿Qué le devolverá esta consulta? Echa un vistazo:

Debería ser más fácil entender a qué me refiero con los diferentes niveles de agrupación. Un pequeño consejo antes de continuar: Siempre que veas un valor NULL, se trata de un subtotal. Echemos un vistazo a la tabla. En primer lugar, hay 9 Fender Jazzmasters en Amsterdam. Luego hay 32 Fender Jazzmasters en Nueva York. La cantidad total es 41, que es lo que se muestra en la fila:

El valor NULL significa que los datos están agrupados a nivel de tienda. El resultado es "hay 41 Fender Jazzmasters en total, tanto en Nueva York como en Ámsterdam". El mismo cálculo se realiza para todos los demás modelos de Fender, es decir, Stratocaster y Telecaster. Luego está esta fila:

¿Qué significa? Significa que hay en total 592 Fender de los tres modelos en ambas tiendas.

El mismo principio se aplica a Gibson. Primero se muestra la cantidad de guitarras en Ámsterdam y Nueva York para el modelo. A continuación, se suma el subtotal de las cantidades de ambas tiendas. Esto se hace para los tres modelos de Gibson: ES-335, Les Paul y SG. A continuación, hay una línea que muestra el número total de los tres modelos de guitarra Gibson en ambas tiendas (lo mismo que con Fenders):

Por último, hay una fila que muestra el número total de guitarras, sin importar la tienda, el fabricante de la guitarra o el modelo:

Seguro que ahora quieres saber cómo funcionan los conjuntos de cubos y agrupaciones. Para ello, te recomiendo que eches un vistazo al curso de extensiones GROUP BY.

Estos temas avanzados son algo que los analistas de datos utilizarán muy a menudo. Por ello, he preparado algunas construcciones SQL para mis compañeros analistas de datos. Si te dedicas a las finanzas, aquí tienes algunas consultas SQL avanzadas para el análisis financiero.

¿Te consideras un usuario de SQL avanzado?

¿Cómo te sientes ahora? ¿He aumentado tu confianza? Si ya conoces las funciones de ventana de SQL, los CTE y las extensiones GROUP BY, puedes presumir de tus conocimientos avanzados de SQL.

¿O tal vez he hecho lo contrario? Tal vez he sacudido tu confianza al darte cuenta de que no sabes nada de los temas avanzados de los que he hablado en este artículo.

No te preocupes. Sea cual sea el grupo al que pertenezcas, hay LearnSQL.es cursos que te ayudarán a aumentar tus conocimientos y tus habilidades. ¿Quieres aprender las funciones de las ventanas? No hay problema: consulte nuestro curso Funciones de ventana . ¿Te interesan los CTE? Puedes aprenderlos y practicarlos en nuestro curso Consultas recursivas y expresiones de tabla comunes. ¿Necesita sacar más provecho de GROUP BY? Nuestro curso de extensiones de GROUP BY en SQL lo tiene cubierto.