How many times do two events happen at the same time

[elsenorjose]

Apologies in advance if this is convoluted, I'll do my best to explain. Given the sample data below I need to know how many times a group of timestamps and player ids have a play/stop pair. The question being asked of me is how often does a player send a play/stop event pair at the same time. My answer in this sample is 3. The last record is a standalone play event.

1188082370 8093371429477036616 play
1188082370 8093371429477036616 stop
1188082370 8093371429477036616 play
1188082386 7586439231527335752 play
1188082386 7586439231527335752 stop
1188082386 7586439231527335752 play
1188082393 8236898361920600390 play
1188082393 8236898361920600390 stop
1188082394 8236898361920600390 play

 

[hoinz]

Hello,

this problem begs for an intersect function, that may be available in a future release of Mysql. For now you have to use a workaround like this (not tested):

select count(*) from
(
select distinct time_st, player_id from my_table a
where event = 'play'
and exists
(select * from my_table b
where event = 'stop' and
a.time_st = b.time_st
and a.player_id = b.player_id)
)


hope this helps

 

[elsenorjose]

Thanks! I'll give that a try and post back my results. For now, the engineer did it in Perl...which he should have done in the first place