Cómo encontrar el siguiente valor no nulo en SQL

Necesita encontrar el siguiente valor noNULL en una serie temporal, pero no sabe cómo hacerlo. ¿Puede hacerlo en SQL? Sí, se puede. Este artículo le mostrará cómo hacerlo.

Si trabajas con SQL, tarde o temprano te enfrentarás a los valores de NULL. Tener NULLs en una base de datos es casi inevitable. Sin embargo, a veces quieres evitarlos en tus informes. Esto es bastante frecuente cuando se analizan datos de series temporales; los valores de NULL significan que no hay datos disponibles. Que no haya datos disponibles en la serie de datos suele significar que algún evento ocurrió o no.

Entonces, ¿cómo encontrar el siguiente valor noNULL en una serie temporal? Antes de responder al "cómo", vamos a centrarnos en el "cuándo", es decir, en el "cuándo" en el que necesito encontrar el siguiente valor que no seaNULL. Aquí hay un escenario que proporcionará una respuesta.

Escenario

Supongamos que estás analizando datos para una plataforma de autónomos llamada NoBoss que conecta a las empresas con los autónomos. Las empresas buscan buenos autónomos; los autónomos buscan buenos trabajos (o gigs, como se dice en el sector). Se trabaja con la tabla log que contiene los datos de los autónomos, sus datos de acceso y algunas actividades. Estas son las columnas de la tabla:

• id - El ID del registro y la clave primaria de la tabla (PK).
• user_id - El ID del usuario.
• first_name - El nombre del usuario.
• last_name - El apellido del usuario.
• login_start - La hora de inicio de sesión del usuario.
• login_end - La hora de finalización de la sesión del usuario.
• job_id - El ID del puesto de trabajo al que se presentó el usuario.
• job_name - El nombre del trabajo al que se presentó el usuario.
• category_id - El ID de la categoría del trabajo.
• category_name - El nombre de la categoría del trabajo.

Su tarea aquí es encontrar los IDs y nombres de los usuarios. Además, necesita obtener las horas de inicio y fin de la sesión de los usuarios, junto con el puesto de trabajo al que el usuario se presentó. Por último, también necesita una nueva columna llamada profile_category. La categoría del perfil de un usuario está determinada por la categoría del primer trabajo que el usuario solicita (por ejemplo, si el autónomo solicita un trabajo de "Asistente Virtual", su valor en category_name es "Asistente Virtual"). De esta manera, la plataforma NoBoss puede proporcionar a sus usuarios informes y estadísticas que los comparen con otros usuarios dentro de la misma categoría.

¿Suena complicado? Tal vez no al principio. Pero eche un vistazo a los datos de la tabla log tabla y verá por qué su tarea no es sencilla. Presta especial atención a la columna resaltada.

¿Ves lo que hace esto tan difícil? Esta tabla registra cada inicio de sesión. En otras palabras, esta tabla contiene una serie temporal, lo que significa que puede haber registros de entrada en los que el usuario no haya solicitado ningún trabajo. Tiene que excluir todos los registros con valores NULL en la columna job_id. Esta es la parte más fácil.

También necesita mostrar sólo la categoría del primer empleo. El usuario puede estar conectado y no solicitar su primer empleo durante meses. Esto significa que tendrá que omitir quién sabe cuántos registros con valores de NULL antes de llegar al registro noNULL que equivale al primer empleo.

Recuerde que para completar esta tarea, tendrá que encontrar todos los valores que no seanNULL en la columna job_id y sólo el primer valor que no seaNULL de la misma columna. Para resolver este problema, necesitarás las funciones de ventana de SQL; si no estás familiarizado con ellas, consulta nuestro cursoFunciones de ventana .

Ejemplo de solución: SQL Funciones de ventana

El uso de funciones de ventana no es la única manera de resolver este problema en SQL. Por eso el siguiente código es sólo un ejemplo. Sin embargo, el uso de funciones de ventana es la forma más elegante de encontrar valores que no seanNULL. Así que, aquí está el código:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		FIRST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id ASC) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

¿Qué hace este código? Primero selecciona varias columnas de la tabla lognada del otro mundo. La parte emocionante viene del uso de la función de ventana FIRST_VALUE(). Esta función devuelve el primer valor de un conjunto de datos definido. En este caso, devolverá el primer valor de la columna category_name.

Sin embargo, no quiero que la primera categoría de trabajo elegida sea la categoría para todos los usuarios; el primer trabajo de los usuarios se utiliza sólo para ese usuario en particular. Para conseguirlo, he utilizado el PARTITION BY. Esto define la ventana sobre la que trabajará la función FIRST_VALUE(). Esto significa que encontrará el primer trabajo del primer usuario; luego pasará al siguiente usuario y encontrará su primer trabajo, y así sucesivamente.

Observe que esta operación se realiza sobre la columna job_id en orden ascendente. ¿Por qué? Los ID de trabajo se asignan secuencialmente a los trabajos. Si ordenas los IDs de los trabajos de forma ascendente, significa que el ID más bajo estará en la parte superior y los valores de NULL estarán en la parte inferior. De esta manera, se evita obtener un NULL como primer valor en el resultado.

Por último, la cláusula WHERE excluye todos los inicios de sesión en los que el usuario no solicitó ningún empleo.

Comprobemos el código para ver si devuelve el resultado deseado:

¡El código parece funcionar perfectamente! Este es sólo un aspecto del análisis de series temporales en SQL. Puedes aprender más leyendo este artículo sobre el análisis de series temporales. No es necesario que te sepas de memoria todas las funciones de ventana y su sintaxis, sobre todo si no las utilizas muy a menudo. En tales situaciones, nuestra hoja de trucos de SQL Funciones de ventana puede ser útil. Allí encontrará todas las funciones de ventana, su sintaxis y ejemplos que muestran cómo funcionan.

Preste atención al adaptar esta consulta

La consulta anterior le da el marco general para la solución y logra encontrar el siguiente valor noNULL. Puede adaptar fácilmente este código a la serie de tiempo que esté analizando. Sin embargo, ¡tenga cuidado y preste atención cuando lo haga!

Lo más importante es conocer tus datos. Entienda si su conjunto de datos contiene los valores de NULL y qué significan. En el ejemplo anterior, era necesario saber que el usuario puede estar registrado y nunca solicitar ningún trabajo. Para estos datos, tener los valores de NULL no es un error; es una información que te lleva a ciertas conclusiones.

Hay otro ejemplo de la importancia de conocer los datos. Sabía que los valores de job_id se asignan de forma secuencial, no aleatoria. He utilizado este conocimiento para ordenar los datos y así eliminar NULLs del resultado. También es útil saber cómo se tratan los valores de NULL al ordenar los datos. Dependiendo del orden, aparecerán como los primeros o los últimos valores de la tabla.

Es fácil pensar que se puede utilizar la función LAST_VALUE() en lugar de FIRST_VALUE() y obtener los mismos resultados simplemente ordenando los datos de forma diferente. Veamos si funciona.

La lógica común dice que sólo tengo que tomar nuestra consulta anterior y escribirla de esta manera:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

La única diferencia está en esta línea: LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ) AS profile_category. En lugar de FIRST_VALUE(), he utilizado la función LAST_VALUE(). La otra diferencia es que la función se realizará sobre job_id en orden descendente.

Vamos a ejecutar el código y analizar el resultado para el usuario Suzy Cinque:

Sólo debería haber "Análisis de Datos" en el profile_category. ¿Por qué? Porque Suzy Cinque se presentó primero al puesto "Analista de datos para depurar los datos". Sin embargo, en esta columna aparece ahora también la categoría "Redacción". Fíjate bien. Los otros dos puestos a los que se presentó Suzy Cinque son "Corrector de pruebas" y "Escritor fantasma para biografía". Ambos pertenecen a la categoría de trabajo "Escritura".

Lo que este código te da es sólo la categoría del trabajo actual. Un corrector de pruebas es un trabajo de escritura. Un escritor fantasma también es un trabajo de escritura. Un analista de datos es, bueno, un trabajo de análisis de datos. ¿Por qué ocurre esto? Es porque el marco de la ventana por defecto es RANGE UNBOUNDED PRECEDING cuando se utiliza ORDER BY. Significa que el LAST_VALUE() sólo considerará los valores entre la primera fila y la fila actual.

No desesperes, ¡hay una forma de hacer que esto funcione! El truco consiste en definir correctamente el marco de la ventana:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ROWS BETWEEN UNBOUNDED PRECEDING
      AND UNBOUNDED FOLLOWING ) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

Este código modificado considerará ahora todos los valores entre la primera y la última fila. Lo consigue mediante la siguiente cláusula: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Compruebe el nuevo resultado de Suzy Cinque:

Ahora el resultado vuelve a ser correcto. El primer trabajo al que se presentó Suzy Cinque es "Analista de datos para depurar los datos"; por eso la categoría de su perfil siempre será "Análisis de datos".

Encontrar el siguiente valor noNULL es sólo un aspecto del análisis de una serie temporal. Para familiarizarse con las series temporales y las funciones de ventana, intente practicar con datos reales de COVID-19 como en este artículo.

Hablando de los datos de tiempo, es posible que tenga que calcular la longitud de una serie de tiempo. No se preocupe, aquí hay un artículo que le enseña cómo usar las funciones de ventana para calcular la longitud de una serie.

¿Quiere más ayuda para trabajar con valores no nulos?

Este escenario con la plataforma NoBoss es sólo un ejemplo de cómo encontrar los siguientes valores noNULL. Utiliza este escenario y el código que he explicado como base para seguir aprendiendo y practicando. Nuestro curso Funciones de ventana le dará más estructura y le ayudará a aprender sobre todas las demás funciones de la ventana. Si estás interesado, aquí tienes toda la información sobre el curso Funciones de ventana que necesitarás, proporcionada por nuestro Director de Contenidos.

Practica lo que has aprendido aquí, y ¡buena suerte para encontrar tu camino a través de los valores noNULL en los datos de las series temporales!