Contar valores recurrentes consecutivos.
Después de un par de horas de búsqueda, ensayo y error, no encuentro información en internet. Tenemos la siguiente estructura de tabla:
Name | EventDateTime | Mark |
---|---|---|
Dave | 2021-03-24 09:00:00 | Presente |
Dave | 2021-03-24 14:00:00 | Ausente |
Dave | 2021-03-25 09:00:00 | Ausente |
Dave | 2021-03-26 09:00:00 | Ausente |
Dave | 2021-03-27 09:00:00 | Presente |
Dave | 2021-03-27 14:00:00 | Ausente |
Dave | 2021-03-28 09:00:00 | Ausente |
Dave | 2021-03-29 10:00:00 | Ausente |
Dave | 2021-03-30 13:00:00 | Ausente |
Jane | 2021-03-30 13:00:00 | Ausente |
Básicamente, se registran personas para eventos. Necesitamos generar un informe para ver a quiénes no hemos tenido contactos durante más de x días consecutivos. Consecutivo significa que deben estar de manera consecutiva en los días en que tengan eventos en los datos, no días calendario consecutivos. Además, si hubo un día en que estuvieron presentes en el evento y otros en que estuvieron ausentes, el conteo debe comenzar nuevamente desde el siguiente día en que estuvieron ausentes.
El primer problema que tengo es obtener las fechas distintas donde solo hay ausencias, luego el segundo es obtener el número de días consecutivos de ausencia. Ya he hecho esto último en MySQL con variables, pero tengo problemas para migrar esto a PostgreSQL, donde se realiza el informe.
Un ejemplo de la salida que deseo es:
Nombre | EventDateTime | Marca | ConsecCount |
---|---|---|---|
Dave | 2021-03-24 09:00:00 | Presente | 0 |
Dave | 2021-03-24 14:00:00 | Ausente | 0 |
Dave | 2021-03-25 09:00:00 | Ausente | 1 |
Dave | 2021-03-26 09:00:00 | Ausente | 2 |
Dave | 2021-03-27 09:00:00 | Presente | 0 |
Dave | 2021-03-27 14:00:00 | Ausente | 0 |
Dave | 2021-03-28 09:00:00 | Ausente | 1 |
Dave | 2021-03-29 10:00:00 | Ausente | 2 |
Dave | 2021-03-30 13:00:00 | Ausente | 3 |
Jane | 2021-03-30 13:00:00 | Ausente | 0 |
Esta tabla actualmente tiene 639931 registros y se han generado desde el 1 de octubre y continuará creciendo a esta tasa.
Cualquier ayuda o recomendación sobre cómo empezar sería genial.
davy.ai
Solución
Para resolver este problema, debemos realizar dos pasos:
Paso 1: Obtener una lista de fechas distintas donde sólo hay ausencias para cada persona.
Para obtener una lista de fechas distintas donde sólo hay ausencias, podemos utilizar la siguiente consulta SQL:
Esta consulta nos dará una lista de fechas distintas donde cada persona tiene sólo ausencias. Primero estamos agrupando los datos por
Name
yEventDateTime::date
. Luego estamos agregando los valores deMark
en un array usandoarray_agg()
. Luego filtramos cualquier fecha donde hay unPresent
en el array usandoNOT 'Present' = ANY (marks)
y donde hay más de 1 valor en el array usandoarray_length(marks, 1) > 1
.Paso 2: Calcular el número de días consecutivos de ausencia para cada persona.
Para calcular el número de días consecutivos de ausencia, podemos utilizar la siguiente consulta SQL:
Esta consulta calculará el número de días consecutivos de ausencia para cada persona. Primero estamos utilizando una expresión de tabla común (
cte
) para obtener elName
,EventDateTime
yMark
de cada ausencia, junto con un número de fila (rn
) ordenado porEventDateTime
. Luego estamos utilizando otra expresión de tabla común (cte2
) para agrupar las ausencias en grupos consecutivos, en función de la fecha (dt
). Estamos utilizandoROW_NUMBER() OVER (PARTITION BY Name ORDER BY dt) - rn
para obtener el número de grupo consecutivo. Luego estamos utilizando otra expresión de tabla común (cte3
) para obtener la fecha mínima (start_dt
) y el recuento de ausencias (consec_count
) para cada grupo consecutivo. Luego estamos utilizando otra expresión de tabla común (cte4
) para unir la tabla original con la columnaconsec_count
, que se calcula comocount(*) OVER (PARTITION BY Name ORDER BY EventDateTime) - consec_count
. Estamos restando laconsec_count
del número de fila para obtener el número de ausencias consecutivas hasta e incluyendo esa fila.La salida final será la tabla original con una columna adicional
ConsecCount
. La columnaConsecCount
contendrá el número de ausencias consecutivas para cada fila. Si la fila no es una ausencia, la columnaConsecCount
será 0.Optimización del rendimiento
Si la tabla es muy grande, podemos optimizar el rendimiento de estas consultas creando índices en las columnas
Name
,EventDateTime
, yMark
, y particionando la tabla porName
. También podemos utilizar el comandoEXPLAIN
para analizar el plan de ejecución de la consulta y optimizar la consulta en consecuencia. También podemos considerar utilizar una solución de base de datos distribuida como Amazon Aurora para mejorar el rendimiento y la escalabilidad.