Analytical inquiry

Question

We are looking for one query that can convert the following information into a table

name:time :state a :10:00 AM:login b :10:05 AM:login a :10:06 AM:chatting a :10:08 AM:Idle b :10:11 AM:chatting a :10:10 AM:Logout b :10:12 AM:Logout 

to something like this (given the time interval from 10 AM to 10:15 AM as the request period)

 name: State :Duration a : chatting :2 Minutes a : Idle :2 Minutes b : chatting :1 Minute 

Can this be done ONLY using SQL? I am using Informix version 11.5

Answer 1

This can be done in a single SQL statement. Here is the proof.

Customization

 CREATE TEMP TABLE eventtable ( name CHAR(3) NOT NULL, time DATETIME HOUR TO MINUTE NOT NULL, state CHAR(8) NOT NULL ); INSERT INTO eventtable(name, time, state) VALUES('a', '10:00', 'login'); INSERT INTO eventtable(name, time, state) VALUES('b', '10:05', 'login'); INSERT INTO eventtable(name, time, state) VALUES('a', '10:06', 'chatting'); INSERT INTO eventtable(name, time, state) VALUES('a', '10:08', 'Idle'); INSERT INTO eventtable(name, time, state) VALUES('b', '10:11', 'chatting'); INSERT INTO eventtable(name, time, state) VALUES('a', '10:10', 'Logout'); INSERT INTO eventtable(name, time, state) VALUES('b', '10:12', 'Logout'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:01', 'login'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:02', 'chatting'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:03', 'Idle'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:04', 'Logout'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:05', 'Idle'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:06', 'Logout'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:07', 'Idle'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:08', 'Logout'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:09', 'login'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:11', 'chatting'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:12', 'Idle'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:13', 'chatting'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:14', 'Idle'); INSERT INTO eventtable(name, time, state) VALUES('c', '10:15', 'Logout'); 

Correct request

Pay attention to the conditions. The results table should exclude periods between the "login" and the first other event; in addition, it should exclude the period between “Exit” and the next event (presumably “login”). Self-joining between the table in the name column and then the asymmetric join in the time column (using " < ") ensures that events are in a temporary order. The NOT EXISTS sub-selection ensures that only related events are taken into account. Using BETWEEN AND in a subquery is a mistake, because it includes its endpoints, and it is imperative that r1.time and r2.time excluded from the range; It took me a few minutes to detect this error (the request was executed, but did not return a row, but why?)!

 SELECT r1.name, r1.state, r2.TIME - r1.TIME AS duration FROM eventtable r1, eventtable r2 WHERE r1.name = r2.name AND r1.time < r2.time AND r1.state != 'login' AND r1.state != 'Logout' AND r1.time BETWEEN DATETIME(10:00) HOUR TO MINUTE AND DATETIME(10:15) HOUR TO MINUTE AND r2.time BETWEEN DATETIME(10:00) HOUR TO MINUTE AND DATETIME(10:15) HOUR TO MINUTE AND NOT EXISTS (SELECT 1 FROM eventtable r3 WHERE r3.time > r1.time AND r3.time < r2.time AND r3.name = r1.name AND r3.name = r2.name); 

This gives the answer:

 name state duration a chatting 0:02 a Idle 0:02 b chatting 0:01 c chatting 0:01 c Idle 0:01 c Idle 0:01 c Idle 0:01 c chatting 0:01 c Idle 0:01 c chatting 0:01 c Idle 0:01 

Duration value - time interval up to a minute; if you want to get the value in a matter of minutes, you need to convert it with cast (using 4 for accuracy, to allow intervals of up to 1440 minutes or 1 day, the data is ambiguous for longer time frames):

 (r2.time - r1.time)::INTERVAL MINUTE(4) TO MINUTE 

Or:

 CAST (r2.time - r1.time AS INTERVAL MINUTE(4) TO MINUTE) 

IBM Informix Dynamic Server (IDS) has very detailed notation of time constants. In standard SQL, you can use TIME as a type and TIME '10: 00: 00 'as a value, but seconds will be needed in strict standard SQL. IDS provides the exact types that people want - for example, DATETIME HOUR TO MINUTE. You must also write INTERVAL MINUTE (4) in standard SQL; "MINUTES" should be optional.

Invalid request

In my commentary on Ray Hideyat’s answer, I pointed out that the EXISTS sub-request is necessary to ensure that the events in question are contiguous - there are no intermediate events. Here is the same query with start and end time added to the output, and the missing EXISTS clause (and 'duration' is renamed to 'lapse'):

 SELECT r1.name, r1.state, r2.TIME - r1.TIME AS lapse, r1.time AS start, r2.time AS end FROM eventtable r1, eventtable r2 WHERE r1.name = r2.name AND r1.time < r2.time AND r1.state != 'login' AND r1.state != 'Logout' AND r1.time BETWEEN DATETIME(10:00) HOUR TO MINUTE AND DATETIME(10:15) HOUR TO MINUTE AND r2.time BETWEEN DATETIME(10:00) HOUR TO MINUTE AND DATETIME(10:15) HOUR TO MINUTE; 

This gives the answer:

 name state lapse start end a chatting 0:04 10:06 10:10 a chatting 0:02 10:06 10:08 a Idle 0:02 10:08 10:10 b chatting 0:01 10:11 10:12 c chatting 0:13 10:02 10:15 c chatting 0:12 10:02 10:14 c chatting 0:11 10:02 10:13 c chatting 0:10 10:02 10:12 c chatting 0:09 10:02 10:11 c chatting 0:07 10:02 10:09 c chatting 0:06 10:02 10:08 c chatting 0:05 10:02 10:07 c chatting 0:04 10:02 10:06 c chatting 0:03 10:02 10:05 c chatting 0:02 10:02 10:04 c chatting 0:01 10:02 10:03 c Idle 0:12 10:03 10:15 c Idle 0:11 10:03 10:14 c Idle 0:10 10:03 10:13 c Idle 0:09 10:03 10:12 c Idle 0:08 10:03 10:11 c Idle 0:06 10:03 10:09 c Idle 0:05 10:03 10:08 c Idle 0:04 10:03 10:07 c Idle 0:03 10:03 10:06 c Idle 0:02 10:03 10:05 c Idle 0:01 10:03 10:04 c Idle 0:10 10:05 10:15 c Idle 0:09 10:05 10:14 c Idle 0:08 10:05 10:13 c Idle 0:07 10:05 10:12 c Idle 0:06 10:05 10:11 c Idle 0:04 10:05 10:09 c Idle 0:03 10:05 10:08 c Idle 0:02 10:05 10:07 c Idle 0:01 10:05 10:06 c Idle 0:08 10:07 10:15 c Idle 0:07 10:07 10:14 c Idle 0:06 10:07 10:13 c Idle 0:05 10:07 10:12 c Idle 0:04 10:07 10:11 c Idle 0:02 10:07 10:09 c Idle 0:01 10:07 10:08 c chatting 0:04 10:11 10:15 c chatting 0:03 10:11 10:14 c chatting 0:02 10:11 10:13 c chatting 0:01 10:11 10:12 c Idle 0:03 10:12 10:15 c Idle 0:02 10:12 10:14 c Idle 0:01 10:12 10:13 c chatting 0:02 10:13 10:15 c chatting 0:01 10:13 10:14 c Idle 0:01 10:14 10:15 

This shows how each suitable start line for user 'c' is matched with each suitable end line, providing many false data lines. The NOT EXISTS sub-query is a common topic when dealing with time queries. You can find information about these operations in Snodgrass " Developing Temporary Oriented Applications in SQL" (in PDF format, available on the Internet at the URL) and in Date, Darven and Lorenzos, " Temporal Data and the Relational Model ".

Answer 2

I am sure that this can be done using only SQL, it will take me quite a while to come up with a query for you, I will edit it when done. The main steps that I think would be to first calculate the amount of time each of them takes (making each entry and attaching it to the next record and subtracting it to find the time difference), then a simple article group with an amount will easily get this into the form you described.

Edit: this is what I came up with

 SELECT l.userid, l.state, SUM(t.minutes) AS duration FROM Log l INNER JOIN ( SELECT l1.id, (l2.time - l1.time) AS minutes FROM Log l1, Log l2 WHERE l2.time == ( -- find the next entry -- SELECT TOP 1 ls.time FROM Log ls WHERE ls.Time > l1.Time && ls.userid = l1.userid ORDER BY ls.Time ) ) t ON l.id == t.id GROUP BY l.userid, l.state ORDER BY l.userid 

This is semi-pseudo code, I made up all the table names and that’s all, and you can’t just subtract one time from another, you will probably use the DATEDIFF function. Also, though, I think the essence of this. I think SQL is one of the most amazing languages, you can do almost everything with a little code.