Contar valores recurrentes consecutivos.

Answer

1. 

   davy.ai

   June 17, 2023 at 9:11 am

   Solución

   Para resolver este problema, debemos realizar dos pasos:

   1. Obtener una lista de fechas distintas donde sólo hay ausencias para cada persona.
   2. Calcular el número de días consecutivos de ausencia para cada persona.

   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:

   SELECT Name, EventDateTime::date
   FROM (
       SELECT Name, EventDateTime::date, array_agg(Mark ORDER BY EventDateTime) AS marks
       FROM my_table
       GROUP BY 1, 2
       ) t
   WHERE NOT 'Present' = ANY (marks) AND array_length(marks, 1) > 1;
   

   Esta consulta nos dará una lista de fechas distintas donde cada persona tiene sólo ausencias. Primero estamos agrupando los datos por Name y EventDateTime::date. Luego estamos agregando los valores de Mark en un array usando array_agg(). Luego filtramos cualquier fecha donde hay un Present en el array usando NOT 'Present' = ANY (marks) y donde hay más de 1 valor en el array usando array_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:

   WITH cte AS (
       SELECT Name, EventDateTime::date, Mark,
              ROW_NUMBER() OVER (PARTITION BY Name ORDER BY EventDateTime) rn
       FROM my_table
       WHERE Mark = 'Absent'
   ), cte2 AS (
       SELECT Name, date_trunc('day', EventDateTime::timestamp) AS dt, rn,
              ROW_NUMBER() OVER (PARTITION BY Name ORDER BY dt) - rn AS grp
       FROM cte
   ), cte3 AS (
       SELECT Name, MIN(dt) AS start_dt, COUNT(*) AS consec_count
       FROM cte2
       WHERE Mark = 'Absent'
       GROUP BY 1, grp
   ), cte4 AS (
       SELECT Name, EventDateTime::date, Mark,
              count(*) OVER (PARTITION BY Name ORDER BY EventDateTime) - consec_count AS consec_count
       FROM my_table
       LEFT JOIN cte3 USING (Name)
       WHERE Mark = 'Absent'
   )
   SELECT *
   FROM my_table
   LEFT JOIN cte4 USING (Name, EventDateTime, Mark);
   

   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 el Name, EventDateTime y Mark de cada ausencia, junto con un número de fila (rn) ordenado por EventDateTime. 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 utilizando ROW_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 columna consec_count, que se calcula como count(*) OVER (PARTITION BY Name ORDER BY EventDateTime) - consec_count. Estamos restando la consec_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 columna ConsecCount contendrá el número de ausencias consecutivas para cada fila. Si la fila no es una ausencia, la columna ConsecCount 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, y Mark, y particionando la tabla por Name. También podemos utilizar el comando EXPLAIN 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.

Comments are closed.