Como obtener el registro más reciente de cada grupo de datos con un sub-query

Deja que la base lo haga…

El problema a resolver es el siguiente.

Digamos que tienes en una base de datos relacional la tabla de usuarios, por cada usuario hay cierta data que se debe almacenar y se debe persistir el histórico de esta data, lo que da como resultado que para cada usuario haya “N” cantidad de registros, algo como lo siguiente:

Ahora, lo que se necesita es obtener todos los usuarios y mandarlos a un proceso que como resultado va a escribir un nuevo registro en la tabla por usuario con la data nueva y la fecha de procesamiento; pero hay una limitante, no se pueden procesar todos los usuarios en un solo día pues son muchísimos, por lo tanto, hay que obtener los usuarios por bloques. Otra limitante es que no se tiene la lista de ids de usuarios de otra fuente que no sea esta tabla.

Simplificando para un sólo usuario, hay que obtener su id de la base, supongamos el 17501, lo mandamos a procesar y al finalizar tendremos un nuevo registro en la tabla para el usuario 17501 con la fecha de hoy.

Así que surgen varias preguntas: ¿cómo obtener los ids únicos de todos los usuarios? ¿cómo obtener los ids de usuarios que aún no han sido insertados? ¿cómo saber si un usuario ya pasó por el proceso? ¿cómo me aseguro que cada usuario pase por el proceso?

La pregunta ¿cómo obtener los ids de usuarios que aún no han sido insertados? se puede reformular de la siguiente manera: ¿cómo obtener los ids de usuarios que tienen más tiempo sin haber sido procesados? y ese es el punto clave de la solución propuesta. Se pueden ordenar los ids por fecha de procesamiento, en otras palabras, ordenar las fechas de más antigua a más reciente (ascendente) y tomar los primeros N registros, así cuando el “proceso” inserte los ids más viejos con una nueva fecha, estos quedarán al final del ordenamiento.

Pero falta resolver un detalle; si sólo ordeno las fechas ¿cómo voy a saber cuáles ids ya procesé? y más importante aún ¿que va a pasar con usuarios nuevos? ¿en qué momento se van a volver “usuarios viejos”?

Para eso hay que sacar entonces la fecha más reciente de cada usuario, y ahora sí, ordenar esas fechas, así sabremos cuál fue el usuario que ha pasado por el proceso hace más tiempo; una forma simple y elegante para obtener estos registros es el siguiente query:

SELECT 
    mi_tabla.user_id
FROM
    mi_tabla
        INNER JOIN
    (SELECT 
        id, user_id, MAX(date) AS top_date
    FROM
        mi_tabla
    GROUP BY user_id) AS EachItem 
    ON EachItem.top_date = mi_tabla.date
        AND EachItem.user_id = mi_tabla.user_id
ORDER BY date ASC LIMIT 1500

Lo que hace es hacer un join entre la tabla “mi_tabla” y un sub-query llamado “EachItem”. El sub-query selecciona la fecha más reciente y agrupa los resultados por “user_id”, luego el primer select se ve afectado obteniendo sólo los registros que coincidan con la fecha y id del sub-query (la fecha más reciente), finalmente el resultado se ordena por fecha de manera ascendente y se limita a “N” cantidad de registros por query.

Esto permite que, en una primera ejecución se seleccionen los primeros “N” resultados, luego de pasar por el “proceso” los “N” se escriben en nuevos registros con la fecha actual, en una segunda ejecución, el sub-query toma esta fecha actualizada, lo que provoca que estén al final del “ORDER BY”.

Al resolverlo con un query, se delega toda la carga de trabajo a la base de datos, lo cuál nos ahorra tiempo de procesamiento en el backend, así como el volumen de los datos transmitidos entre servicios.