Calcul du time de trajet dans les bases de données relationnelles?

Voici une méthodologie Oracle utilisant une fonction analytique.

with data as ( SELECT 1 sortingp_id, to_date('20080801 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Initial' step from dual UNION ALL SELECT 1 sortingp_id, to_date('20080802 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Work' step from dual UNION ALL SELECT 1 sortingp_id, to_date('20080803 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Review' step from dual UNION ALL SELECT 1 sortingp_id, to_date('20080804 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Work' step from dual UNION ALL SELECT 1 sortingp_id, to_date('20080805 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Review' step from dual UNION ALL SELECT 1 sortingp_id, to_date('20080806 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Accepted' step from dual UNION ALL SELECT 1 sortingp_id, to_date('20080807 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Done' step from dual ) select sortingp_id, step, dt - lag(dt) over (partition by sortingp_id order by dt) sortingp_time from data / 1 Initial 1 Work 1 1 Review 1 1 Work 1 1 Review 1 1 Accepted 1 1 Done 1 

Ceux-ci sont très couramment utilisés dans des situations où, traditionnellement, nous pourrions utiliser une auto-jointure.

Syntaxe PostgreSQL:

 DROP TABLE ObjectState; CREATE TABLE ObjectState ( object_id integer not null,--foreign key event_time timestamp NOT NULL, state varchar(10) NOT NULL, --Other fields CONSTRAINT pk_ObjectState PRIMARY KEY (object_id,event_time) ); 

Pour un état donné, find le premier état suivant du type donné

 select parent.object_id,parent.event_time,parent.state,min(child.event_time) as ch_event_time,min(child.event_time)-parent.event_time as step_time from ObjectState parent join ObjectState child on (parent.object_id=child.object_id and parent.event_time<child.event_time) where --Starting state parent.object_id=1 and parent.event_time=to_timestamp('01-Aug-2008 13:30:00','dd-Mon-yyyy hh24:mi:ss') --needed state and child.state='Review' group by parent.object_id,parent.event_time,parent.state; 

Cette requête n'est pas la plus courte mais elle devrait être facile à comprendre et à utiliser dans le cadre d'autres requêtes:

Liste les events et leur durée pour un object donné

 select parent.object_id,parent.event_time,parent.state,min(child.event_time) as ch_event_time, CASE WHEN parent.state<>'Done' and min(child.event_time) is null THEN (select localtimestamp)-parent.event_time ELSE min(child.event_time)-parent.event_time END as step_time from ObjectState parent left outer join ObjectState child on (parent.object_id=child.object_id and parent.event_time<child.event_time) where parent.object_id=4 group by parent.object_id,parent.event_time,parent.state order by parent.object_id,parent.event_time,parent.state; 

Lister les états actuels des objects qui ne sont pas "terminés"

 select states.object_id,states.event_time,states.state,(select localtimestamp)-states.event_time as step_time from (select parent.object_id,parent.event_time,parent.state,min(child.event_time) as ch_event_time,min(child.event_time)-parent.event_time as step_time from ObjectState parent left outer join ObjectState child on (parent.object_id=child.object_id and parent.event_time<child.event_time) group by parent.object_id,parent.event_time,parent.state) states where states.object_id not in (select object_id from ObjectState where state='Done') and ch_event_time is null; 

Données de test

 insert into ObjectState (object_id,event_time,state) select 1,to_timestamp('01-Aug-2008 13:30:00','dd-Mon-yyyy hh24:mi:ss'),'Initial' union all select 1,to_timestamp('02-Aug-2008 13:40:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all select 1,to_timestamp('03-Aug-2008 13:50:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all select 1,to_timestamp('04-Aug-2008 14:30:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all select 1,to_timestamp('04-Aug-2008 16:20:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all select 1,to_timestamp('06-Aug-2008 18:00:00','dd-Mon-yyyy hh24:mi:ss'),'Accepted' union all select 1,to_timestamp('07-Aug-2008 21:30:00','dd-Mon-yyyy hh24:mi:ss'),'Done'; insert into ObjectState (object_id,event_time,state) select 2,to_timestamp('01-Aug-2008 13:30:00','dd-Mon-yyyy hh24:mi:ss'),'Initial' union all select 2,to_timestamp('02-Aug-2008 13:40:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all select 2,to_timestamp('07-Aug-2008 13:50:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all select 2,to_timestamp('14-Aug-2008 14:30:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all select 2,to_timestamp('15-Aug-2008 16:20:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all select 2,to_timestamp('16-Aug-2008 18:02:00','dd-Mon-yyyy hh24:mi:ss'),'Accepted' union all select 2,to_timestamp('17-Aug-2008 22:10:00','dd-Mon-yyyy hh24:mi:ss'),'Done'; insert into ObjectState (object_id,event_time,state) select 3,to_timestamp('12-Sep-2008 13:30:00','dd-Mon-yyyy hh24:mi:ss'),'Initial' union all select 3,to_timestamp('13-Sep-2008 13:40:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all select 3,to_timestamp('14-Sep-2008 13:50:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all select 3,to_timestamp('15-Sep-2008 14:30:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all select 3,to_timestamp('16-Sep-2008 16:20:00','dd-Mon-yyyy hh24:mi:ss'),'Review'; insert into ObjectState (object_id,event_time,state) select 4,to_timestamp('21-Aug-2008 03:10:00','dd-Mon-yyyy hh24:mi:ss'),'Initial' union all select 4,to_timestamp('22-Aug-2008 03:40:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all select 4,to_timestamp('23-Aug-2008 03:20:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all select 4,to_timestamp('24-Aug-2008 04:30:00','dd-Mon-yyyy hh24:mi:ss'),'Work'; 

Je ne pense pas que vous pouvez get cette réponse avec une instruction SQL que vous essayez d'get un résultat de nombreux loggings. La seule façon d'get cela dans SQL est d'get le champ d'horodatage pour deux loggings différents et de calculer la différence (datediff). Par conséquent, UNIONS ou jointures intérieures sont nécessaires.

Je ne suis pas sûr de comprendre exactement la question, mais vous pouvez faire quelque chose comme ce qui suit qui lit la table en une passe puis utilise une table dérivée pour le calculer. Code SQL Server:

 CREATE TABLE #testing ( eventdatetime datetime NOT NULL, state varchar(10) NOT NULL ) INSERT INTO #testing ( eventdatetime, state ) SELECT '20080801 13:30:00', 'Initial' UNION ALL SELECT '20080802 13:30:00', 'Work' UNION ALL SELECT '20080803 13:30:00', 'Review' UNION ALL SELECT '20080804 13:30:00', 'Work' UNION ALL SELECT '20080805 13:30:00', 'Review' UNION ALL SELECT '20080806 13:30:00', 'Accepted' UNION ALL SELECT '20080807 13:30:00', 'Done' SELECT DATEDIFF(dd, Initial, Review) FROM ( SELECT MIN(CASE WHEN state='Initial' THEN eventdatetime END) AS Initial, MIN(CASE WHEN state='Review' THEN eventdatetime END) AS Review FROM #testing ) AS A DROP TABLE #testing 

Il est probablement plus facile si vous avez un numéro de séquence ainsi que l'horodatage: dans la plupart des SGBDR, vous pouvez créer une colonne d'auto-incrémentation et ne modifier aucune des instructions INSERT . Ensuite, vous rejoignez la table avec une copy de lui-même pour get les deltas

 select after.moment - before.moment, before.state, after.state from object_states before, object_states after where after.sequence + 1 = before.sequence 

(où les détails de la syntaxe SQL varient en fonction du système de database).

  -- Oracle SQl CREATE TABLE ObjectState ( startdate date NOT NULL, state varchar2(10) NOT NULL ); insert into ObjectState select to_date('01-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Initial' union all select to_date('02-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Work' union all select to_date('03-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Review' union all select to_date('04-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Work' union all select to_date('05-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Review' union all select to_date('06-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Accepted' union all select to_date('07-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Done'; -- Days in between two states select o2.startdate - o1.startdate as days from ObjectState o1, ObjectState o2 where o1.state = 'Initial' and o2.state = 'Review'; 

 create table A ( At datetime not null, State varchar(20) not null ) go insert into A(At,State) select '2008-08-01T13:30:00','Initial' union all select '2008-08-02T13:30:00','Work' union all select '2008-08-03T13:30:00','Review' union all select '2008-08-04T13:30:00','Work' union all select '2008-08-05T13:30:00','Review' union all select '2008-08-06T13:30:00','Accepted' union all select '2008-08-07T13:30:00','Done' go --Find sortingp time from Initial to Done select DATEDIFF(day,t1.At,t2.At) from A t1 inner join A t2 on t1.State = 'Initial' and t2.State = 'Review' and t1.At < t2.At left join A t3 on t3.State = 'Initial' and t3.At > t1.At and t4.At < t2.At left join A t4 on t4.State = 'Review' and t4.At < t2.At and t4.At > t1.At where t3.At is null and t4.At is null 

N'a pas dit si les jointures étaient autorisées ou non. Les jointures à t3 et t4 (et leurs comparaisons) vous permettent de dire si vous voulez l'occurrence la plus récente ou la plus récente des états de début et de fin (dans ce cas, je request la dernière "Révision" initiale et la plus récente)

En code réel, mes états de début et de fin seraient des parameters

Edit: Oups, vous devez inclure "t3.At <t2.At" et "t4.At> t1.At", pour corriger certaines séquences d'états (par exemple, si nous avons supprimé le second "Review", puis interrogé à partir de "Work "à" Revoir ", la requête originale échouera)

Je pense que vos pas (chaque logging de votre voyage peut être vu comme une étape) peuvent être quelque part regroupés dans le cadre de la même activité. Il est alors possible de regrouper vos données, comme par exemple:

 SELECT Min(Tbl_Step.dateTimeStep) as sortingpBegin, _ Max(Tbl_Step.dateTimeStep) as sortingpEnd _ FROM Tbl_Step WHERE id_Activity = 'AAAAAAA' 

En utilisant ce principe, vous pouvez ensuite calculer d'autres agrégats comme le nombre d'étapes dans l'activité et ainsi de suite. Mais vous ne findez pas de méthode SQL pour calculer des valeurs comme l'écart entre deux étapes, car une telle donnée n'appartient ni à la première ni à la deuxième étape. Certains outils de reporting utilisent ce qu'ils appellent des "sums en cours" pour calculer ces données intermédiaires. Selon vos objectives, cela pourrait être une solution pour vous.

J'ai essayé de le faire en MySQL. Vous auriez besoin d'utiliser une variable car il n'y a pas de fonction rank dans MySQL, donc ça irait comme ceci:

 set @sortingp1 = 0; set @sortingp2 = 0; SELECT sortingp1.`date` as startdate, datediff(sortingp2.`date`, sortingp1.`date`) length_of_sortingp FROM (SELECT @sortingp1 := @sortingp1 + 1 as rank1, `date` from sortingp where state='Initial') as sortingp1 INNER JOIN (SELECT @sortingp2 := @sortingp2 + 1 as rank2, `date` from sortingp where state='Done') as sortingp2 ON rank1 = rank2; 

Je suppose que vous voulez calculer le time entre les états 'Initial' et 'Terminé'.

 +---------------------+----------------+ | startdate | length_of_sortingp | +---------------------+----------------+ | 2008-08-01 13:30:00 | 6 | +---------------------+----------------+ 

Ok, c'est un peu geek, mais j'ai construit une application web pour suivre les contractions de ma femme juste avant que nous ayons un bébé afin que je puisse voir du travail quand il devenait proche d'aller à l'hôpital. En tout cas, j'ai construit cette chose de base assez facilement comme deux vues.

 create table contractions time_date timestamp primary key; create view contraction_time as SELECT a.time_date, max(b.prev_time) AS prev_time FROM contractions a, ( SELECT contractions.time_date AS prev_time FROM contractions) b WHERE b.prev_time < a.time_date GROUP BY a.time_date; create view time_between as SELECT contraction_time.time_date, contraction_time.prev_time, contraction_time.time_date - contraction_time.prev_time FROM contraction_time; 

Cela pourrait également être fait en tant que sous-sélection, mais j'ai aussi utilisé les vues intermédiaires pour d'autres choses, et cela a bien fonctionné.