Distribution du temps dans des seaux d'intervalle

C'est un problème intéressant. J'ai légèrement simplifié la dénomination des colonnes à faire:

with t as (
      select 20024 as agent, '2019-03-18 12:01:00.0'::timestamp as conn, '2019-03-18 13:01:30.0'::timestamp as disconn, 3630 duration union all
      select 20011, '2019-03-11 08:47:40.0', '2019-03-11 09:30:10.0', 2550
     )
select gs.t, t.*,
       extract(epoch from least(gs.t + interval '15 minute', disconn) - greatest(gs.t, conn))
from t cross join lateral
     generate_series(date_trunc('hour', t.conn), date_trunc('hour', t.disconn) + interval '1 hour', interval '15 minute') gs(t)
where conn <= gs.t + interval '15 minute' and disconn >= gs.t ;

Ici a> est un violon db .

Ce que je réalise, c'est que la colonne de durée est inutile. Vous essayez de capturer les secondes de chevauchement avec des intervalles de 15 minutes.

Cela crée des intervalles sur les limites des heures - c'est juste plus facile. Cela signifie que certains des chevauchements ne sont pas corrects, ce que la clause where filtre.

Dans Postgres, vous pouvez utiliser generate_series () pour générer des séries de données. Je commencerais par générer une série de nombres, puis JOIN avec les données d'origine, pour générer des créneaux de 15 minutes. Une requête interne peut être utilisée pour précalculer les limites de début et de fin.

Considérez la requête suivante, qui montre la logique d'arrondir les horodatages à 15 minutes et JOIN la table avec la série: p>

| agent | interval_start_15min     | duration |
| ----- | ------------------------ | -------- |
| 20011 | 2019-03-11T08:45:00.000Z | 740      |
| 20011 | 2019-03-11T09:00:00.000Z | 900      |
| 20011 | 2019-03-11T09:15:00.000Z | 900      |
| 20011 | 2019-03-11T09:30:00.000Z | 10       |
| 20024 | 2019-03-18T12:00:00.000Z | 840      |
| 20024 | 2019-03-18T12:15:00.000Z | 900      |
| 20024 | 2019-03-18T12:30:00.000Z | 900      |
| 20024 | 2019-03-18T12:45:00.000Z | 900      |
| 20024 | 2019-03-18T13:00:00.000Z | 90       |

Par exemple, pour agent = 2011 , cela renverrait:

SELECT 
    agent,
    c.connect_15min + ( t.x * 15 || ' minute' )::interval interval_start_15min,

    EXTRACT(EPOCH FROM (
        LEAST(datetimedisconnect, c.connect_15min + ( (t.x + 1) * 15 || ' minute' )::interval) 
        - GREATEST(datetimeconnect, c.connect_15min + ( t.x * 15 || ' minute' )::interval )
    )) duration
FROM generate_series(0, 99, 1) t(x)
INNER JOIN (
    SELECT 
        f.*,
        DATE_TRUNC('hour', datetimeconnect)    
            + DATE_PART('minute', datetimeconnect   )::int / 15 * interval '15 min' connect_15min,
        DATE_TRUNC('hour', datetimedisconnect) 
            + DATE_PART('minute', datetimedisconnect)::int / 15 * interval '15 min' disconnect_15min
    FROM f_contact f
) c 
    ON c.disconnect_15min >= c.connect_15min + ((t.x * 15) || ' minute')::interval 
ORDER BY agent, interval_start_15min;

Maintenant nous pouvons faire le calcul de la durée dans la clause FROM . L'astuce consiste à gérer correctement le premier et le dernier intervalle, en utilisant LEAST () et GREATEST () (notez que duration n'est pas utilisé pour faire le calcul):

| x   | agent | datetimeconnect          | datetimedisconnect       | duration | connect_15min            | disconnect_15min         |
| --- | ----- | ------------------------ | ------------------------ | -------- | ------------------------ | ------------------------ |
| 0   | 20011 | 2019-03-11T08:47:40.000Z | 2019-03-11T09:30:10.000Z | 2550     | 2019-03-11T08:45:00.000Z | 2019-03-11T09:30:00.000Z |
| 1   | 20011 | 2019-03-11T08:47:40.000Z | 2019-03-11T09:30:10.000Z | 2550     | 2019-03-11T08:45:00.000Z | 2019-03-11T09:30:00.000Z |
| 2   | 20011 | 2019-03-11T08:47:40.000Z | 2019-03-11T09:30:10.000Z | 2550     | 2019-03-11T08:45:00.000Z | 2019-03-11T09:30:00.000Z |
| 3   | 20011 | 2019-03-11T08:47:40.000Z | 2019-03-11T09:30:10.000Z | 2550     | 2019-03-11T08:45:00.000Z | 2019-03-11T09:30:00.000Z |

Ce démo sur DB Fiddle renvoie:

    SELECT *
    FROM generate_series(0, 99, 1) t(x)
    INNER JOIN (
        SELECT 
            f.*,
            DATE_TRUNC('hour', datetimeconnect)    
                + DATE_PART('minute', datetimeconnect   )::int / 15 * interval '15 min' connect_15min,
            DATE_TRUNC('hour', datetimedisconnect) 
                + DATE_PART('minute', datetimedisconnect)::int / 15 * interval '15 min' disconnect_15min
        FROM f_contact f
    ) c 
        ON c.disconnect_15min >= c.connect_15min + ((t.x * 15) || ' minute')::interval 
    ORDER BY c.datetimeconnect, t.x;

Il devrait être plus rapide de convertir immédiatement en secondes et de calculer avec des nombres entiers:

SELECT agent
     , to_char(to_timestamp(q) AT TIME ZONE 'UTC', 'HH24:MI') AS quarter_hour
     , least(q + 900, b) - greatest(a, q) AS seconds
FROM   (
   SELECT agent
        , extract(epoch FROM datetimeconnect)::int    AS a
        , extract(epoch FROM datetimedisconnect)::int AS b
   FROM   f_contact
   )  f, generate_series(a / 900 * 900, b, 900) q
ORDER  BY agent DESC, q;

Produit le résultat souhaité.

db fiddle ici (avec les cas d'angle ajoutés au scénario de test)

Points clés

• extrait (epoch FROM datetimeconnect) extraits (en citant le manuel ) ...

  le nombre de secondes depuis le 01/01/1970 à 00:00:00 UTC

• generate_series () (la variante entière) génère exactement le nombre de lignes nécessaires, pas de surplus. La limite inférieure de chaque quart d'heure pertinent, pour être précis - la valeur que vous affichez dans le résultat.

• a / 900 * 900 utilise division entière pour arrondir à un quart d'heure complet (multiples de 900). Utilisation depuis date_trunc () code > n'a pas la possibilité de tronquer aux quarts d'heure

• moins (q + 900, b) - plus grand (a, q) s'assure que le début et la fin sont correctement comptés, même si les deux se trouvent dans le même quart d'heure (comme démontré dans le cas de test étendu dans le violon).

• to_timestamp (q) AT TIME ZONE 'UTC' car to_timestamp () renvoie timestamptz , nous voulons le horodatage à UTC à partir de celui-ci.

A part: duration est une valeur fonctionnellement dépendante. Il n'est pas nécessaire pour la tâche et ne doit pas être stocké de manière redondante dans la table. C'est juste du ballast qui ralentit tout. Il peut être calculé à la volée à moindre coût.