Skip to content Skip to sidebar Skip to footer
Home / Informix / Sql

Analytical Query

Post a Comment
Am looking for a single query that can convert the following information in the table name:time :state a :10:00 AM:login b :10:05 AM:login a :10:06 AM:chatting a :10:08

Solution 1:

It can be done in a single SQL statement. Here's the proof.

Setup

CREATE TEMP TABLE eventtable
(
    name CHAR(3) NOTNULL,
    time DATETIME HOURTOMINUTENOTNULL,
    state CHAR(8) NOTNULL
);

INSERTINTO eventtable(name, time, state) VALUES('a', '10:00', 'login');
INSERTINTO eventtable(name, time, state) VALUES('b', '10:05', 'login');
INSERTINTO eventtable(name, time, state) VALUES('a', '10:06', 'chatting');
INSERTINTO eventtable(name, time, state) VALUES('a', '10:08', 'Idle');
INSERTINTO eventtable(name, time, state) VALUES('b', '10:11', 'chatting');
INSERTINTO eventtable(name, time, state) VALUES('a', '10:10', 'Logout');
INSERTINTO eventtable(name, time, state) VALUES('b', '10:12', 'Logout');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:01', 'login');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:02', 'chatting');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:03', 'Idle');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:04', 'Logout');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:05', 'Idle');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:06', 'Logout');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:07', 'Idle');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:08', 'Logout');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:09', 'login');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:11', 'chatting');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:12', 'Idle');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:13', 'chatting');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:14', 'Idle');
INSERTINTO eventtable(name, time, state) VALUES('c', '10:15', 'Logout');

Correct query

Note the conditions. The result table must exclude the periods between 'login' and the first other event; further, it must exclude the period between 'Logout' and the next event (presumably a 'login'). The self-join between the table on the name column and then the asymmetric join on the time column (using '<') ensures that events are in time order. The NOT EXISTS sub-select ensures that only adjacent events are considered. Using BETWEEN AND in the sub-query is a mistake because it includes its end points and it is crucial that r1.time and r2.time are excluded from the range; it took me a few minutes to spot that bug (the query ran but returned no rows, 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) HOURTOMINUTEAND DATETIME(10:15) HOURTOMINUTEAND r2.time BETWEEN DATETIME(10:00) HOURTOMINUTEAND DATETIME(10:15) HOURTOMINUTEANDNOTEXISTS (SELECT1FROM eventtable r3
                            WHERE r3.time > r1.time AND r3.time < r2.time
                      AND r3.name = r1.name
                      AND r3.name = r2.name);

This produces the answer:

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

The 'duration' value is an INTERVAL HOUR TO MINUTE; if you want a value in just minutes, you have to convert it with a cast (using 4 for the precision to allow for intervals up to 1440 minutes, or 1 day; the data is ambiguous for longer time frames):

(r2.time - r1.time)::INTERVALMINUTE(4) TOMINUTE

Or:

CAST (r2.time - r1.time ASINTERVALMINUTE(4) TOMINUTE)

IBM Informix Dynamic Server (IDS) has very verbose notations for time constants. In Standard SQL, you could use TIME as the type and TIME '10:00:00' as a value, but the seconds would be necessary in strict standard SQL. IDS does provide exact types that people want - such as DATETIME HOUR TO MINUTE. You'd also write INTERVAL MINUTE(4) in standard SQL; the 'TO MINUTE' should be optional.

Incorrect query

In my comment to Ray Hidayat's answer, I pointed out that the EXISTS sub-query is necessary to ensure that the events under consideration are contiguous - there are no intervening events. Here's the same query with start and end times added to the output, and the EXISTS clause missing (and 'duration' renamed to 'lapse'):

SELECT r1.name, r1.state, r2.TIME - r1.TIME AS lapse,
       r1.time ASstart, r2.time ASendFROM 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) HOURTOMINUTEAND DATETIME(10:15) HOURTOMINUTEAND r2.time BETWEEN DATETIME(10:00) HOURTOMINUTEAND DATETIME(10:15) HOURTOMINUTE;

This produces the answer:

name state     lapse start end
a    chatting   0:0410:0610:10
a    chatting   0:0210:0610:08
a    Idle       0:0210:0810:10
b    chatting   0:0110:1110:12c    chatting   0:1310:0210:15c    chatting   0:1210:0210:14c    chatting   0:1110:0210:13c    chatting   0:1010:0210:12c    chatting   0:0910:0210:11c    chatting   0:0710:0210:09c    chatting   0:0610:0210:08c    chatting   0:0510:0210:07c    chatting   0:0410:0210:06c    chatting   0:0310:0210:05c    chatting   0:0210:0210:04c    chatting   0:0110:0210:03c    Idle       0:1210:0310:15c    Idle       0:1110:0310:14c    Idle       0:1010:0310:13c    Idle       0:0910:0310:12c    Idle       0:0810:0310:11c    Idle       0:0610:0310:09c    Idle       0:0510:0310:08c    Idle       0:0410:0310:07c    Idle       0:0310:0310:06c    Idle       0:0210:0310:05c    Idle       0:0110:0310:04c    Idle       0:1010:0510:15c    Idle       0:0910:0510:14c    Idle       0:0810:0510:13c    Idle       0:0710:0510:12c    Idle       0:0610:0510:11c    Idle       0:0410:0510:09c    Idle       0:0310:0510:08c    Idle       0:0210:0510:07c    Idle       0:0110:0510:06c    Idle       0:0810:0710:15c    Idle       0:0710:0710:14c    Idle       0:0610:0710:13c    Idle       0:0510:0710:12c    Idle       0:0410:0710:11c    Idle       0:0210:0710:09c    Idle       0:0110:0710:08c    chatting   0:0410:1110:15c    chatting   0:0310:1110:14c    chatting   0:0210:1110:13c    chatting   0:0110:1110:12c    Idle       0:0310:1210:15c    Idle       0:0210:1210:14c    Idle       0:0110:1210:13c    chatting   0:0210:1310:15c    chatting   0:0110:1310:14c    Idle       0:0110:1410:15

This shows how each eligible start row for user 'c' is matched with each eligible end row, giving many spurious rows of data. The NOT EXISTS sub-query is a common theme when dealing with time-based queries. You can find information about these operations in Snodgrass's "Developing Time-Oriented Applications in SQL" (PDF available online at URL), and in Date, Darwen and Lorentzos "Temporal Data and the Relational Model".

Solution 2:

I'm pretty sure it can be done using only SQL, it's going to take me quite a bit of time to come up with a query for you, I'll edit it in when I'm done. The basic steps I think would first be to calculate the amount of time each one takes (done by taking each entry and joining it to the next entry and subtracting to find the time difference) then a simple group by clause with a sum will easily get it into the form you've described.

Edit: Here 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
        ORDERBY ls.Time
    )
) t ON l.id == t.id
GROUPBY l.userid, l.state
ORDERBY l.userid

This is semi-pseudocode, I made up all the table names and things, and you won't be able to just subtract one time from another, you'll probably be using the DATEDIFF function. Besides that though, I think that's the gist of it. I think SQL is one of the most amazing languages, you can do almost anything with little code.

Post a Comment for "Analytical Query"