Transponer datos de times secuenciales para derivar la duración

Tengo una tabla de acciones dentro de una session y duración (milisegundos) entre cada paso:

+-----------------------------------------------------------------------+ | | userid | sessionid | action sequence | action | milliseconds | | | +--------+-----------+-----------------+-------------+--------------+ | | | 1 | 1 | 1 | event start | 0 | | | | 1 | 1 | 2 | other | 188114 | | | | 1 | 1 | 3 | event end | 248641 | | | | 1 | 1 | 4 | other | 398215 | | | | 1 | 1 | 5 | event start | 488284 | | | | 1 | 1 | 6 | other | 528445 | | | | 1 | 1 | 7 | other | 572711 | | | | 1 | 1 | 8 | event end | 598123 | | | | 1 | 2 | 1 | event start | 0 | | | | 1 | 2 | 2 | event end | 54363 | | | | 2 | 1 | 1 | other | 0 | | | | 2 | 1 | 2 | other | 2345 | | | | 2 | 1 | 1 | other | 75647 | | | | 3 | 1 | 2 | other | 0 | | | | 3 | 1 | 3 | event start | 34678 | | | | 3 | 1 | 4 | other | 46784 | | | | 3 | 1 | 5 | other | 78905 | | | | 4 | 1 | 1 | event start | 0 | | | | 4 | 1 | 2 | other | 7454 | | | | 4 | 1 | 3 | other | 11245 | | | | 4 | 1 | 4 | event end | 24567 | | | | 4 | 1 | 5 | other | 29562 | | | | 4 | 1 | 6 | other | 43015 | | | +--------+-----------+-----------------+-------------+--------------+ | 

Me gustaría capturar events completos: sesiones que contienen un inicio y un final de un evento (algunos pueden tener un inicio pero no un final, un final pero no un inicio, o ninguno (no los quiero), y su inicio y finalización veces. En última instancia, quiero hacer un seguimiento de la duración mediante la transposition de las filas secuenciales de veces en columnas para que pueda calcular una diferencia. La tabla de datos anterior idealmente se transpondrá a:

 +--------+-----------+---------------+--------+--------+ | userid | sessionid | full event id | start | end | +--------+-----------+---------------+--------+--------+ | 1 | 1 | 1 | 0 | 248641 | | 1 | 1 | 2 | 488284 | 598123 | | 1 | 2 | 1 | 0 | 54363 | | 4 | 1 | 1 | 0 | 24567 | +--------+-----------+---------------+--------+--------+ 

Intenté algo como:

 select a.userid, a.sessionid, a.milliseconds as start, b.milliseconds as end from #table a inner join #table b on a.userid=b.userid and a.sessionid=b.sessionid and a.action='event start' and b.action='event end' 

Sin embargo, eso no funciona, ya que algunos usuarios pueden tener múltiples events de inicio y finalización en la session (como ID de usuario 1). Estoy atascado en la mejor forma de transponer los datos de times para cada evento. ¡Gracias por tu ayuda!

Entonces, dados sus datos anteriores:

 CREATE TABLE test_table ( `userid` int, `sessionid` int, `actionSequence` int, `action` varchar(11), `milliseconds` int ); INSERT INTO test_table (`userid`, `sessionid`, `actionSequence`, `action`, `milliseconds`) VALUES (1, 1, 1, 'event start', 0), (1, 1, 2, 'other', 188114), (1, 1, 3, 'event end', 248641), (1, 1, 4, 'other', 398215), (1, 1, 5, 'event start', 488284), (1, 1, 6, 'other', 528445), (1, 1, 7, 'other', 572711), (1, 1, 8, 'event end', 598123), (1, 2, 1, 'event start', 0), (1, 2, 2, 'event end', 54363), (2, 1, 1, 'other', 0), (2, 1, 2, 'other', 2345), (2, 1, 1, 'other', 75647), (3, 1, 2, 'other', 0), (3, 1, 3, 'event start', 34678), (3, 1, 4, 'other', 46784), (3, 1, 5, 'other', 78905), (4, 1, 1, 'event start', 0), (4, 1, 2, 'other', 7454), (4, 1, 3, 'other', 11245), (4, 1, 4, 'event end', 24567), (4, 1, 5, 'other', 29562), (4, 1, 6, 'other', 43015); 

La siguiente consulta debería llevarlo a donde quiere (estaba en el path correcto):

 SELECT tt1.userid, tt1.sessionid, tt1.actionSequence, tt1.milliseconds AS startMS, MIN(tt2.milliseconds) AS endMS, MIN(tt2.milliseconds) - tt1.milliseconds AS totalMS FROM test_table tt1 INNER JOIN test_table tt2 ON tt2.userid = tt1.userid AND tt2.sessionid = tt1.sessionid AND tt2.actionSequence > tt1.actionSequence AND tt2.action = 'event end' WHERE tt1.action = 'event start' GROUP BY tt1.userid, tt1.sessionid, tt1.actionSequence, startMS 

Te da este set de resultados:

 userid sessionid actionSequence startMS endMS totalMS 1 1 1 0 248641 248641 1 1 5 488284 598123 109839 1 2 1 0 54363 54363 4 1 1 0 24567 24567 

El GROUP BY es importante, porque hay dos filas con action = 'event end' y sequence > 1 para sessionid = 1 y userid = 1 , entonces (supongo) queremos el más cercano a la secuencia actual, es decir, el MIN(milliseconds) . Como puede ver, también le permite seguir adelante y tomar la diferencia de las dos columnas en este set de resultados, ahorrándole el paso adicional que estaba planeando:]

Aquí hay un SQLFiddle de esta consulta en acción en MySQL 5.6. No especificó un RDBMS, pero creo que el lenguaje utilizado por esta consulta debería ser lo suficientemente simple para funcionar en cualquier motor SQL.