Comment trouver des intervalles de temps minimum et maximum pour des événements consécutifs avec des espaces et des îles ?

Recherche du minimum et du maximum pour des lignes consécutives avec des espaces et des îles

Dans le contexte d'un ensemble de données ordonné contenant des séquences d'événements représentés par des lignes dans dans une table, la tâche consiste à déterminer les intervalles de temps minimum et maximum pour les lignes consécutives partageant le même type d'événement. Ce problème se pose lorsqu'il s'agit de données comportant des lacunes et des îlots, ce qui rend les méthodes d'agrégation traditionnelles insuffisantes.

Comprendre l'entrée

Le tableau d'entrée se compose des colonnes suivantes :

• nom : Identifiant unique pour chacun personne
• act : Type d'événement, soit "sommeil" ou "réveil"
• rn : Numéro de ligne dans la séquence d'événements de chaque personne
• startt : Heure de début du event
• endd : Heure de fin de l'événement

Identification des groupes consécutifs

Pour identifier des groupes de lignes consécutifs avec le même type d'événement, nous calculons la différence entre les numéros de ligne pour le type d'événement et la ligne globale numérotation :

row_number() over (partition by name, act order by rn) as seqnum_na
row_number() over (partition by name order by rn) as seqnum_n

Ces nouvelles colonnes, seqnum_na et seqnum_n, représentent respectivement le numéro de séquence du type d'événement et le numéro de séquence global. La soustraction de seqnum_na de seqnum_n crée une nouvelle colonne qui représente le numéro de groupe pour chaque sous-ensemble consécutif de lignes au sein d'un type d'événement.

Agrégation avec numéro de groupe

Avec le numéro de groupe identifiés, nous pouvons agréger les heures de début et de fin pour trouver les minimum et maximum pour chaque groupe :

select name, act, min(startt) as startt, max(endd) as endd
from (select i.*,
             row_number() over (partition by name, act order by rn) as seqnum_na,
             row_number() over (partition by name order by rn) as seqnum_n
      from input i
     ) i
group by (seqnum_n - seqnum_na), name, act;

Par en regroupant par ce numéro de groupe ainsi que le nom et le type d'événement, nous identifions et regroupons efficacement les lignes consécutives au sein de chaque sous-ensemble, fournissant les intervalles de temps minimum et maximum souhaités.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!