Kristian Kristian - 1 month ago 7
MySQL Question

Super complicated mysql query

The database (Mysql) witch i do query comes from an telephony system, and i need to read how many agents (event_parties.agent_id) is logged into different group (event_groups.group_id).

Each time an agent logges in to an group an new record is entered inside events table with event_id=29, if logout event_id=30 at the same time an new entry in table event_parties appears with same g_event_id and the agent_id representing the agent,
also in table events_groups an new entry appears with same g_event_id and group_id representing th egroup that the agents logges in/out to(inside the table event_groups the same g_event_id could the same for more than one entry if agent logges in/out more than one group at the same time).

So my thinking is that i could get the logged in agents in and group_id by selecting all records where there are no newer entry (event_time) with same event_groups.group_id and same event_parties.agent_id and the events.event_id is between 29 and 30.

events.event_id =29 means that agents logges in.

events.event_id =30 means that agents logges out.

I have some serious difficulties to design such an mysql select :(

Here are some example data in each table.

Table:

events

g_event_id event_id event_time
---------- -------- ----------
7816 31 2016-11-03 09:46:18
7815 30 2016-11-03 09:45:18
7814 31 2016-11-03 09:44:18
7813 29 2016-11-03 09:43:18
7812 30 2016-11-03 09:42:18
7811 29 2016-11-03 09:41:18
7810 31 2016-11-03 09:40:18
7809 29 2016-11-03 09:39:18
7808 31 2016-11-03 09:38:18
7807 7 2016-11-03 09:37:18
7806 29 2016-11-03 09:36:18
7805 30 2016-11-03 09:35:18
7804 30 2016-11-03 09:34:18
7803 29 2016-11-03 09:33:18
7802 29 2016-11-03 09:32:18


Table:
event_parties

g_event_id agent_id
---------- --------
7816 1
7815 1
7814 1
7813 1
7812 1
7811 1
7810 2
7809 2
7808 2
7807 3
7806 3
7805 3
7804 3
7803 3
7802 3


Table:
event_groups

g_event_id group_id
---------- --------
7816 1
7815 1
7814 1
7813 1
7813 2
7813 3
7813 4
7812 1
7811 1
7810 1
7809 1
7808 1
7807 1
7806 1
7806 3
7805 4
7804 1
7804 2
7803 4
7802 1
7802 2


From tables above i want my select statement result to be:

group_id agent_id
-------- --------
4 1
3 1
2 1
1 2
1 3
3 3


Is such a query possible, is there any sql genius out there :)

/ Kristian

Answer
SELECT group_id, agent_id
FROM (SELECT agent_id, eg.group_id, if(event_id = 29, 1, -1) AS transitions
      FROM event_parties      ep
           JOIN `events` e ON ep.g_event_id = e.g_event_id
           JOIN event_groups eg ON ep.g_event_id = eg.g_event_id
      WHERE e.event_id IN (29, 30)) AS t
GROUP BY agent_id, group_id
HAVING sum(transitions) > 0
ORDER BY agent_id, group_id DESC

Link to SQL Fiddle

I think that this will do what you are saying. For every agent/group combination, it sets number of transitions to 1 if they login and -1 if they log out. Looking over the whole data set, if they have logged in and then logged out, the sum will be 0 for a specific agent group, which is calculated in the outer query.

This does depend on not starting with a log out event for a specific agent/group combination. If the data set you are looking starts with a log out event, then the user will never appear to be logged out.

Alternatively, you could get the same result by looking at the last record, and determining if it's a 29 or a 30, and only displaying the ones that are 29.

SELECT group_id, agent_id
FROM (SELECT agent_id, group_id, max(e.g_event_id) AS last_event_id
      FROM event_parties      ep
           JOIN `events` e ON ep.g_event_id = e.g_event_id
           JOIN event_groups eg ON ep.g_event_id = eg.g_event_id
      WHERE e.event_id IN (29, 30)
      GROUP BY agent_id, group_id) AS last_event
     JOIN `events` e ON e.g_event_id = last_event.last_event_id
WHERE e.event_id = 29;

This is less dependent on where you are starting in the series, but the join is slightly more complex.

Link to SQL Fiddle