7th Jul 2022 Lectura de 16 minutos Las 10 mejores preguntas de la entrevista sobre las funciones de ventana de SQL Ignacio L. Bisso SQL aprender SQL funciones de ventana trabajos y carrera Muchos puestos de trabajo interesantes requieren conocimientos de SQL, y eso incluye las funciones de ventana, que no se suelen enseñar en los cursos online. En este artículo, cubriré las principales preguntas sobre funciones de ventana para cada nivel de experiencia. Si vas a una entrevista de trabajo para un puesto avanzado de SQL o para puestos de analista de datos de nivel intermedio o avanzado, es probable que te pregunten sobre tus conocimientos de las funciones ventana de SQL. Que no cunda el pánico. Aunque estas funciones no se suelen tratar en los cursos online, aquí tenemos las respuestas. Preguntas comunes de las entrevistas de trabajo sobre SQL Funciones de ventana La idea de este artículo es ayudarte a prepararte para las preguntas sobre los diferentes subtemas de las funciones de ventana. No podemos darte la pregunta exacta que recibirás, pero podemos estar bastante seguros de los temas a los que apuntarán las preguntas. En algunos casos, la pregunta puede ser muy abierta, dejando la decisión sobre qué subtema de las funciones de las ventanas cubrir completamente a usted. En este caso, debes conocer la importancia relativa de cada subtema. Para empezar, debe estar preparado para una pregunta abierta como 1. ¿Qué es una función de ventana en SQL? Las funciones de ventana son funciones SQL que operan sobre un conjunto de registros llamado "ventana" o "marco de ventana". La "ventana" es un conjunto de filas que están relacionadas de alguna manera con la fila que está siendo procesada por la consulta (por ejemplo, todas las filas antes de la fila actual, 5 filas antes de la fila actual, o 3 filas después de la fila actual). Las funciones de ventana son similares a las funciones de agregación en el sentido de que calculan las estadísticas de un grupo de filas. Sin embargo, las funciones de ventana no contraen las filas; mantienen los detalles de las filas individuales. Las funciones de ventana pueden organizarse en las siguientes cuatro categorías: funciones de agregación, funciones de clasificación, funciones analíticas y funciones de distribución. Las funciones de agregación son las que se utilizan con GROUP BY. Esto incluye: COUNT() cuenta el número de filas dentro de la ventana. AVG() calcula el valor medio de una columna determinada para todos los registros de la ventana. MAX() obtiene el valor máximo de una columna para todos los registros de la ventana. SUM() devuelve la suma de todos los valores de una columna determinada dentro de la ventana. En la categoría de clasificación: ROW_NUMBER() devuelve la posición de la fila en el conjunto de resultados. RANK() clasifica las filas en función de un valor determinado. Cuando dos filas están en la misma posición, les otorga el mismo rango y deja la siguiente posición vacía (por ejemplo, 1, 2, 3, 3, 5...). DENSE_RANK() también ordena las filas según un valor determinado, pero no deja vacía la siguiente posición (por ejemplo, 1, 2, 3, 3, 4, 5...). Para obtener información detallada, consulte este artículo sobre las funciones de clasificación. En la categoría analítica, las funciones LEAD(), LAG() o FIRST_VALUE() permiten obtener datos de otras filas de la misma ventana. LEAD() devuelve valores de las filas inferiores a la fila actual; LAG() de las filas superiores a la fila actual. Para más detalles, consulte nuestro artículo sobre LEAD vs LAG. Finalmente, en la categoría de distribución hay funciones como PERCENT_RANK() y CUME_DIST() que pueden obtener rankings percentiles o distribuciones acumulativas. Consulte nuestro curso Funciones de ventana para obtener instrucciones paso a paso sobre cómo utilizar estas funciones. A continuación se muestra un ejemplo de consulta con funciones de ventana: SELECT employee_name, department_name, salary, RANK() OVER (PARTITION BY department ORDER BY salary) position FROM employee En esta consulta, la función de ventana RANK() se utiliza para clasificar a los empleados por su salario. Más adelante en este artículo, discutiremos en detalle la sintaxis de la cláusula OVER() y las subcláusulas PARTITION BY y ORDER BY. Por ahora, sólo diremos que se utilizan para definir qué registros componen el marco de la ventana. Preguntas sobre la función de ventana de nivel de entrada 2. ¿Cuál es la sintaxis de la cláusula OVER ()? La cláusula OVER() se utiliza para definir qué filas estarán en el marco de la ventana. Las siguientes subcláusulas pueden estar presentes en la cláusula OVER(): PARTITION BY define la partición, o los grupos de filas dentro del marco de la ventana, que la función ventana utilizará para crear un resultado. (Esto se explicará más adelante). ORDER BY define el orden de las filas en el marco de la ventana. ROWS/RANGE define los límites superior e inferior del marco de la ventana. Todas las subcláusulas de OVER() son opcionales y pueden ser omitidas. En ese caso, las funciones se realizarán en todo el marco de la ventana. El siguiente SQL muestra la cláusula OVER() en funcionamiento: SELECT first_name, last_name, department, salary, AVG(salary) OVER (PARTITION BY department) FROM employee Para cada empleado, la consulta devuelve su nombre, su apellido, su salario y el salario medio de su departamento. La cláusula OVER (PARTITION BY department) crea una ventana de filas para cada valor de la columna departamento. Todas las filas con el mismo valor en la columna departamento pertenecerán a la misma ventana. La función AVG() se aplica a la ventana: la consulta calcula el salario medio en el departamento dado. El artículo ¿Qué es la cláusula OVER? tiene una explicación completa de la cláusula OVER. 3. Describa la diferencia entre Funciones de ventana y las funciones agregadas. La principal diferencia entre las funciones ventana y las funciones agregadas es que las funciones agregadas agrupan varias filas en una única fila de resultado; todas las filas individuales del grupo se contraen y no se muestran sus datos individuales. Por otro lado, las funciones de ventana producen un resultado para cada fila individual. Este resultado suele mostrarse como un nuevo valor de columna en cada fila de la ventana. El colapso de las filas es una característica importante de las funciones agregadas. Por ejemplo, no podemos resolver el problema "Devolver todos los empleados con su salario y el salario máximo en su departamento" con funciones agregadas debido a la limitación de colapso. En cuanto a la similitud, tanto las funciones de agregación como las de ventana realizan una operación similar a la de agregación en un conjunto de filas. Algunas funciones como AVG(), MAX(), MIN() y SUM() pueden utilizarse como funciones agregadas y de ventana. Sin embargo, cuando necesitemos el resultado de estas funciones combinado con datos a nivel de fila, es mejor utilizar una función ventana en lugar de una función agregada. Mostraremos dos consultas SQL que devuelven el nombre del departamento y el salario máximo de cada departamento. En el primer ejemplo, utilizaremos MAX() como función agregada: SELECT department_name, MAX(salary) AS max_salary FROM employee GROUP BY department_name A continuación, podemos ver el resultado de la consulta anterior. Observe que hay un registro por departamento debido al efecto de colapso de la cláusula GROUP BY: department_namemax_salary Accounting93000 Sales134000 Human Resources78000 En el siguiente ejemplo, obtendremos un resultado similar pero ligeramente diferente utilizando MAX() como función ventana: SELECT employee_name, salary, department_name, MAX(salary) OVER (PARTITION BY department_name) AS max_salary FROM employee Como hemos mencionado anteriormente, las funciones de ventana no colapsan los registros. En el siguiente resultado, tenemos una fila por empleado para un total de 5 filas: employee_namesalarydepartment_namemax_salary John Doe93000Accounting93000 Jeremy Smith134000Sales134000 Donna Hayes120000Sales134000 Mark Ron78000Human Resources78000 Denis Serge72000Human Resources78000 Observe que hemos añadido las columnas employee_name y salario simplemente añadiendo sus nombres a la lista de columnas en SELECT. No podíamos añadirlas a la consulta con GROUP BY debido a la limitación de colapso. En el artículo Explicación de las funciones de la ventana SQL, puedes encontrar una explicación detallada de las diferencias entre las funciones de agregación y de ventana. 4. ¿Cuál es la diferencia entre Funciones de ventana y la cláusula GROUP BY? Las funciones agregadas se utilizan frecuentemente con la cláusula GROUP BY, que define los grupos de filas en los que actuará la función agregada. La cláusula GROUP BY agrupa las filas individuales en conjuntos de filas, permitiendo la ejecución de funciones de agregación como SUM(), AVG() o MAX() en estos conjuntos. Ninguna columna de las filas individuales puede formar parte del resultado, como podemos ver en la siguiente consulta SQL: SELECT department_name, AVG(salary) -- AVG is an aggregate function FROM employee GROUP BY department_name En la consulta anterior, sólo ponemos una columna en la lista SELECT: department_name. Esto es posible porque la columna nombre_departamento aparece en la cláusula GROUP BY. Sin embargo, no podemos añadir ninguna columna adicional en SELECT; sólo se permiten las columnas especificadas en GROUP BY. La siguiente consulta SQL es equivalente a la anterior, pero utiliza funciones de ventana en lugar de GROUP BY: SELECT department_name, AVG(salary) OVER(PARTITION BY department_name) -- AVG is a window function FROM employee La consulta anterior no tiene una cláusula GROUP BY porque la función AVG() se utiliza como función ventana. Podemos reconocer que AVG() es una función ventana por la presencia de la cláusula OVER. Sugiero el artículo SQL Funciones de ventana vs. GROUP BY para una comparación completa entre las funciones ventana y la cláusula GROUP BY. 5. Muestre un ejemplo de SQL Funciones de ventana. Esta es una buena oportunidad para mencionar una consulta que muestre la importancia de las funciones ventana y que al mismo tiempo esté conectada con las consultas que mostramos en las preguntas anteriores. La consulta que sugiero resolvería esta tarea: "Obtener los nombres de los empleados, los salarios, los nombres de los departamentos y el salario medio de ese departamento". Esta consulta es una forma sencilla de mostrar cómo podemos combinar datos a nivel de fila y datos agregados. (La función ventana devuelve los datos agregados). SELECT employee_name, salary, department_name, AVG(salary) OVER (PARTITION BY department) avg_salary FROM employee Arriba, podemos ver las columnas a nivel de fila employee_name, salary, y department con el salario medio de cada departamento, que se calcula mediante la función de ventana AVG(). La subcláusula PARTITION BY define que las ventanas de registros se crearán en base al valor de la columna department_name. Todos los registros con el mismo valor en department_name estarán en la misma ventana. El resultado sería algo así: employee_namesalarydepartment_nameavg_salary John Doe93000Accounting93000 Jeremy Smith134000Sales127000 Donna Hayes120000Sales127000 Mark Ron78000Human Resources75000 Denis Serge72000Human Resources75000 Para revisar más ejemplos de funciones de ventana, prueba el artículo Ejemplos de funciones de ventana SQL con explicaciones. 6. Nombrar algunas funciones comunes Funciones de ventana. Las funciones de ventana pueden organizarse en cuatro categorías: funciones de agregado, funciones de clasificación, funciones analíticas y funciones de distribución. Las funciones de agregación son las funciones regulares de agregación que se utilizan con GROUP BY: MAX() MIN() , AVG(), SUM(), y COUNT(). Estas funciones, como ya hemos demostrado, pueden utilizarse como funciones de ventana. Las funciones de clasificación son ROW_NUMBER(), RANK(), y DENSE_RANK(). Se utilizan para obtener diferentes posiciones en un ranking. Puede encontrar una explicación detallada de las funciones de clasificación en el siguiente artículo. Las funciones analíticas son LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE(), y NTH_VALUE(). Estas funciones permiten obtener datos de filas distintas de la actual (por ejemplo, la fila anterior, la siguiente, la última fila dentro de un marco de ventana, etc.). La función NTILE() divide las filas de una partición en n grupos y devuelve el número de grupo. Por último, las funciones de distribución PERCENT_RANK() y CUME_DIST() permiten obtener datos sobre el percentil o la distribución acumulativa (respectivamente) de cada fila de la ventana. Prefiero las funciones analíticas porque nos permiten comparar o calcular las diferencias entre distintos registros dentro de la ventana (entre otras cosas). Por ejemplo, si tengo una serie temporal con valores de acciones, podría calcular cuánto aumentaron las acciones en cada momento. Aquí hay otro ejemplo de funciones analíticas. Las funciones de ventana analítica LEAD() y LAG() devuelven una columna de una fila posterior/anterior. Así, si tenemos una tabla con criptomonedas, con una marca de tiempo y un valor de cotización ... SymbolTimestampValue BTC2021-05-25 10:3061400 BTC2021-05-25 10:4060300 BTC2021-05-25 10:5059800 ETH2021-05-25 10:302700 ETH2021-05-25 10:402750 ETH2021-05-25 10:502820 Tabla Acciones ... podemos obtener el siguiente informe. Para calcular el porcentaje de variación, necesitamos datos de dos filas diferentes: El valor de la fila actual, y el valor de la fila anterior. La función LEAD() devolverá el valor de la fila anterior. Este es el resultado: SymbolTimestampValue% Variation BTC2021-05-25 10:3061400-- BTC2021-05-25 10:4060300-1.8% BTC2021-05-25 10:5059800-0.8% ETH2021-05-25 10:302700-- ETH2021-05-25 10:4027501.8% ETH2021-05-25 10:5028202.5% La columna % Variation fue calculada con este tipo de expresión: (Current_value - Previous_value ) / Previous_value Tenga en cuenta que el valor de la criptomoneda de la marca de tiempo anterior se puede obtener con: LEAD(value) OVER (PARTITION BY crypto_symbol ORDER BY timestamp) Esta es la 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 Si quieres profundizar en LAG() y LEAD(), te sugiero que leas el artículo La función LAG() y la función LEAD() en SQL. Tiene una explicación detallada sobre cómo funcionan las funciones de ventana en las ventanas ordenadas. Preguntas intermedias sobre la función de ventana 7. ¿Cómo se define el marco de la ventana? Las funciones ventana calculan un resultado agregado basado en un conjunto de registros llamado "ventana" o "marco de ventana". Los marcos de ventana se definen mediante la cláusula OVER(). Una cláusula OVER() vacía significa que la ventana es todo el conjunto de datos: SELECT employee_name, salary, department_name, AVG(salary) OVER () avg_salary FROM employee La consulta anterior calcula el salario medio y lo muestra junto a los demás datos de los empleados de la tabla. Hay varias subcláusulas que pueden colocarse dentro de la cláusula OVER() para definir con precisión una ventana. La subcláusula PARTITION BY especifica que todos los registros que tienen el mismo valor en una columna determinada pertenecen a la misma ventana. En otras palabras, PARTITION BY especifica cómo se define la ventana. Así, la siguiente consulta calcula el salario medio de cada departamento; los cálculos se realizan a partir de la agrupación de los valores de la columna department_name. SELECT employee_name, salary, department_name, AVG(salary) OVER (PARTITION BY department_name) avg_salary FROM employee ORDER BY también puede utilizarse dentro de OVER(). Se utiliza para poner las filas de la ventana en un orden específico. Las ventanas ordenadas son muy importantes porque permiten el uso de varias funciones analíticas como LAG(), LEAD(), y FIRST_VALUE(). SELECT employee_name, salary, department_name, LAG(salary) OVER (ORDER BY salary) prev_salary FROM employee Esta consulta muestra el salario del empleado inmediatamente anterior al empleado actual en el orden de los salarios. Tenga en cuenta que puede combinar las cláusulas ORDER BY y PARTITION BY en una consulta: el ordenamiento se aplica a cada partición individualmente. Dos subcláusulas similares de OVER() son RANGE y ROWS. Definen los límites del marco de la ventana poniendo límites superiores y/o inferiores a la ventana de registros. Esto significa que las funciones de ventana pueden ser calculadas en base a un subconjunto de filas en lugar de todas las filas de la ventana. La diferencia entre ROW y RANGE se explica en detalle en nuestra hoja de trucos de funciones de ventana de SQL. En las próximas dos preguntas se explicará más sobre ROWS y RANGE y las diferentes opciones de límite disponibles. 8. ¿Cómo funciona ORDER BY con OVER? Algunas funciones de ventana (como LAG(), LEAD(), y FIRST_VALUE()) trabajan en una ventana ordenada de registros. Al utilizar una de estas funciones, necesitamos la subcláusula ORDER BY para definir los criterios de orden. Un buen ejemplo de ello es la consulta anterior que utilizamos para calcular el porcentaje de variación de las criptomonedas: 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 En la consulta anterior, la cláusula OVER tiene dos subcláusulas: PARTITION BY y ORDER BY. PARTITION BY define qué registros hay en cada ventana y ORDER BY define el orden de los registros en la ventana. (En este ejemplo, ordenamos los registros en función de su fecha y hora). A continuación, la función LEAD() devuelve el valor del registro anterior. Si la cláusula OVER no incluye un ORDER BY y no tenemos ROWS/RANGE, entonces el marco de la ventana está formado por todas las filas que cumplen con la cláusula PARTITION BY. Sin embargo, cuando utilizamos una cláusula ORDER BY sin ROWS/RANGE, el marco de la ventana incluye las filas entre la primera fila (basada en la cláusula ORDER BY) y la fila actual. En otras palabras, las filas que van después de la fila actual no se incluirán en el marco de la ventana. (Explicaremos más detalles sobre estos límites en la siguiente pregunta). Las funciones de ventana que requieren una subcláusula ORDER BY son: RANK() DENSE_RANK() LEAD() LAG() FIRST_VALUE() LAST_VALUE() NTH_VALUE() PERCENT_RANK() CUME_LIST() Para entender mejor cómo funciona ORDER BY, consulte el artículo Cómo calcular la diferencia entre dos filas en SQL. Preguntas sobre la función de ventana avanzada 9. Explique lo que hace UNBOUNDED PRECEDING. Un marco de ventana es un conjunto de filas que están relacionadas de alguna manera con la fila actual, que se evalúa por separado dentro de cada partición. Cuando utilizamos la cláusula ORDER BY, podemos definir opcionalmente límites superiores e inferiores para el marco de ventana. Los límites se pueden definir como UNBOUNDED PRECEDING n PRECEDING CURRENT ROW n FOLLOWING UNBOUNDED FOLLOWING Estos límites pueden definirse con las subcláusulas RANGE o ROWS de la cláusula OVER(). UNBOUNDED PRECEDING indica que el límite inferior de la ventana es el primer registro de la ventana; del mismo modo, el límite superior puede definirse con UNBOUNDED FOLLOWING o CURRENT ROW. Estos límites deben utilizarse sólo con ventanas ordenadas. En la siguiente imagen podemos ver cómo funcionan los diferentes límites: Por ejemplo, si queremos obtener el valor medio de una criptomoneda considerando sólo los valores ocurridos hasta el valor actual, podemos utilizar la siguiente cláusula OVER(): AVG(value) OVER (PARTITION BY symbol_name ORDER BY timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) En esta cláusula OVER(), definimos el CURRENT ROW como el límite superior de la ventana para calcular el promedio. Esto es exactamente lo que necesitamos, ya que no queremos incluir valores registrados después de la marca de tiempo actual en el cálculo del promedio. 10. Describa el orden de las operaciones de SQL y Funciones de ventana' Lugar en este orden. Las subcláusulas de un SQL SELECT se ejecutan en el siguiente orden: FROM / JOINS WHERE GROUP BY Funciones Agregadas HAVING Funciones de ventana SELECT DISTINTO UNIÓN / INTERSECCIÓN / EXCEPCIÓN ORDENAR POR OFFSET LIMIT / FETCH / TOP Como las funciones de ventana se calculan en el paso 6, no podemos ponerlas en la cláusula WHERE (que se calcula en el paso 2). Sin embargo, podemos eludir esta limitación utilizando una CTE (expresión de tabla común), donde podemos llamar a las funciones de ventana y almacenar sus resultados como columnas en la CTE. La CTE será tratada como una tabla y los resultados de la función ventana serán evaluados como valores de columna regulares por el WHERE. Hay un artículo interesante sobre por qué las funciones ventana no están permitidas en las cláusulas WHERE que deberías leer si buscas algunos ejemplos. Por otro lado, podemos utilizar los resultados de agregación/agrupación en las funciones ventana, ya que estos ya están calculados en el momento en que se procesan las funciones ventana. ¿Quieres mejorar tus habilidades en SQL Funciones de ventana? Este artículo cubre varias posibles preguntas de la entrevista de trabajo sobre las funciones de ventana de SQL. Mi consejo final es que conectes las preguntas de este artículo con las preguntas que te harán durante una entrevista. Aquí está: Intenta asociar cada pregunta de este artículo con un tema de función ventana, como "OVER clause", "name a function" o "ORDER BY sub-clause". Entonces, si te preguntan sobre las funciones ventana durante la entrevista, identifica el tema de la pregunta y utiliza la información aquí para discutir el tema. Si quieres profundizar en las funciones de ventana de SQL, te sugiero el artículo Curso del mes - Funciones de ventana, que describe nuestro curso Funciones de ventana. ¡Ampliar tus conocimientos es una inversión que bien podría ayudarte a conseguir el trabajo! Tags: SQL aprender SQL funciones de ventana trabajos y carrera