Comment regrouper les loggings en fonction de la valeur de l'logging suivant?

J'essaie de regrouper les loggings de suivi des actifs en fonction d'une date / heure et je suis perplexe, en espérant que quelqu'un a un moyen facile d'accomplir cela.

J'ai une table qui contient les colonnes suivantes:

  • asset_id
  • date_time_stamp
  • zone_name

Un exemple de jeu de données ressemblerait à ceci:

09 , 2016-02-01 18:57:23.000 , zone_0 10 , 2016-02-01 18:57:23.000 , zone_1 10 , 2016-02-01 18:57:34.000 , zone_1 10 , 2016-02-01 18:57:45.000 , zone_2 11 , 2016-02-01 18:56:17.000 , zone_3 11 , 2016-02-01 18:56:32.000 , zone_3 11 , 2016-02-01 18:56:43.000 , zone_3 11 , 2016-02-01 18:56:48.000 , zone_3 12 , 2016-02-01 18:56:43.000 , zone_1 12 , 2016-02-01 18:56:53.000 , zone_1 12 , 2016-02-01 18:57:54.000 , zone_3 12 , 2016-02-01 18:59:01.000 , zone_3 12 , 2016-02-01 18:59:14.000 , zone_1 12 , 2016-02-01 18:59:45.000 , zone_1 10 , 2016-02-01 18:58:23.000 , zone_1 10 , 2016-02-01 18:59:56.000 , zone_1 

Ce que j'aimerais savoir, c'est combien de time chaque actif a-t-il dépensé dans une zone avant de passer à la zone suivante? Je ne peux pas grouper sur nom_zone car je veux savoir qu'un actif est passé de la zone 1 à la zone 2, puis de nouveau à la zone 1.

La sortie que je cherche est:

  • asset_id
  • zone_name
  • date_time_stamp_entered_zone
  • date_time_stamp_exited_zone

Avec un jeu de données de:

 09 , zone_0 , 2016-02-01 18:57:23.000 , 2016-02-01 18:57:34.000 10 , zone_1 , 2016-02-01 18:57:23.000 , 2016-02-01 18:57:34.000 10 , zone_2 , 2016-02-01 18:57:45.000 , 2016-02-01 18:57:45.000 10 , zone_1 , 2016-02-01 18:58:23.000 , 2016-02-01 18:59:56.000 11 , zone_3 , 2016-02-01 18:56:43.000 , 2016-02-01 18:56:48.000 12 , zone_1 , 2016-02-01 18:56:43.000 , 2016-02-01 18:56:53.000 12 , zone_3 , 2016-02-01 18:57:54.000 , 2016-02-01 18:59:01.000 12 , zone_1 , 2016-02-01 18:59:14.000 , 2016-02-01 18:59:14.000 

Vous devez identifier les groupes de zones qui sont identiques. Une technique est l'approche "différence de numéros de rangées". Cela assignera un identifiant de groupement à chaque zone, qui pourra ensuite être utilisé pour l'agrégation:

 select asset_id, zone_name, min(date_time_stamp), max(date_time_stamp) from (select t.*, (row_number() over (partition by asset_id order by date_time_stamp) - row_number() over (partition by asset_id, zone_name order by date_time_stamp) ) as grp from t ) t group by asset_id, zone_name, grp; 

La "magie" dans cette approche est la différence dans les numéros de ligne. Je suggère que vous exécutez la sous-requête pour voir ce qu'elle produit. . . Vous pouvez également calculer les numéros de ligne séparément pour mieux comprendre comment cela fonctionne.