Encuentre y sume rangos de fechas con registros superpuestos en postgresql

Solución:

demo: db <> fiddle (usa el antiguo conjunto de datos con la parte AB superpuesta)

Descargo de responsabilidad: Esto funciona para intervalos de días, no para marcas de tiempo. El requisito de ts llegó más tarde.

SELECT
    s.acts,
    s.sum,
    MIN(a.start) as start,
    MAX(a.end) as end
FROM (
    SELECT DISTINCT ON (acts)
        array_agg(name) as acts,
        SUM(count)
    FROM
        activities, generate_series(start, "end", interval '1 day') gs
    GROUP BY gs
    HAVING cardinality(array_agg(name)) > 1
) s
JOIN activities a
ON a.name = ANY(s.acts)
GROUP BY s.acts, s.sum

1. generate_series genera todas las fechas entre el inicio y el final. Entonces, cada fecha en la que existe una actividad obtiene una fila con el count
2. Agrupar todas las fechas, agregar todas las actividades existentes y la suma de sus recuentos
3. HAVING filtra las fechas en las que solo existe una actividad
4. Debido a que hay diferentes días con las mismas actividades, solo necesitamos un representante: Filtre todos los duplicados con DISTINCT ON
5. Une este resultado con la tabla original para obtener el inicio y el final. (tenga en cuenta que “fin” es una palabra reservada en Postgres, ¡será mejor que busque otro nombre de columna!). Antes era más cómodo perderlos, pero es posible obtener estos datos dentro de la subconsulta.
6. Agrupe esta unión para obtener la fecha más temprana y más reciente de cada intervalo.

---

Aquí hay una versión de las marcas de tiempo:

demo: db <> violín

WITH timeslots AS (
    SELECT * FROM (
        SELECT
            tsrange(timepoint, lead(timepoint) OVER (ORDER BY timepoint)),
            lead(timepoint) OVER (ORDER BY timepoint)     -- 2
        FROM (
            SELECT 
                unnest(ARRAY[start, "end"]) as timepoint  -- 1 
            FROM
                activities
            ORDER BY timepoint
        ) s
    )s  WHERE lead IS NOT NULL                            -- 3
)
SELECT 
    GREATEST(MAX(start), lower(tsrange)),                 -- 6
    LEAST(MIN("end"), upper(tsrange)),
    array_agg(name),                                      -- 5
    sum(count)
FROM 
    timeslots t
JOIN activities a
ON t.tsrange && tsrange(a.start, a.end)                   -- 4
GROUP BY tsrange
HAVING cardinality(array_agg(name)) > 1

La idea principal es identificar posibles franjas horarias. Así que tomo cada hora conocida (tanto de inicio como de finalización) y las pongo en una lista ordenada. Entonces puedo tomar los primeros tiempos conocidos de remolque (17:00 desde el inicio A y 18:00 desde el inicio B) y verificar qué intervalo está en él. Luego lo verifico para el 2º y 3º, luego para el 3º y 4º y así sucesivamente.

En el primer intervalo de tiempo solo cabe A. En el segundo de 18-19 también B encaja. En la siguiente ranura 19-20 también C, de 20 a 20:30 A ya no encaja, solo B y C. La siguiente es 20: 30-22 donde solo encaja B, finalmente se agrega 22-23 D a B y por último, pero no menos importante, solo D encaja en 23-23: 30.

Así que tomo esta lista de tiempo y la vuelvo a unir a la tabla de actividades donde se cruzan los intervalos. Después de eso, es solo una agrupación por intervalo de tiempo y sumar su recuento.

1. esto coloca ambos ts de una fila en una matriz cuyos elementos se expanden en una fila por elemento con unnest. Entonces obtengo todos los tiempos en una columna que se puede ordenar simplemente
2. El uso de la función de ventana principal permite llevar el valor de la siguiente fila a la actual. Entonces puedo crear un rango de marca de tiempo a partir de estos dos valores con tsrange
3. Este filtro es necesario porque la última fila no tiene “valor siguiente”. Esto crea un NULL valor que es interpretado por tsrange como infinito. Así que esto crearía una franja horaria increíblemente incorrecta. Así que tenemos que filtrar esta fila.
4. Une las franjas horarias contra la mesa original. los && El operador comprueba si se superponen dos tipos de rango.
5. Agrupación por franjas horarias únicas, agregando los nombres y el recuento. Filtre las franjas horarias con una sola actividad utilizando el HAVING cláusula
6. Un poco complicado conseguir los puntos de inicio y finalización correctos. Por lo tanto, los puntos de inicio son el máximo del inicio de la actividad o el inicio de un intervalo de tiempo (que se puede obtener usando lower). Por ejemplo, tome la franja horaria de 20-20: 30: comienza a las 20h pero ni B ni C tienen su punto de partida allí. Similar al tiempo de finalización.

---

---

Utiliza Nuestro Buscador