代码之家 › 专栏 › 技术社区 › calvinkrishy

分析查询

informix sql

4

calvinkrishy · 技术社区 · 16 年前

我正在寻找一个可以转换表中以下信息的单个查询

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

类似于这样(给定上午10点到上午10:15的时间范围作为查询时段)

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

这只能通过SQL来实现吗?我使用Informix 11.5版本

2 回复 | 直到 16 年前

1

5

Jonathan Leffler 16 年前

它可以在单个SQL语句中完成。这是证据。

安装

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');

正确的查询

注意条件。结果表必须排除“登录”和第一个其他事件之间的时间段;此外,它必须排除“注销”和下一个事件(可能是“登录”)之间的时间段。桌子上的自我连接名称列,然后是非对称连接时间列(使用' < “)确保事件按时间顺序进行。NOT EXISTS子选项可确保只考虑相邻事件。在子查询中使用BETWEEN AND是一个错误,因为它包含了端点,这一点至关重要 r1.time 和 r2.time 被排除在范围之外;我花了几分钟才发现这个bug(查询运行了,但没有返回任何行,但是 为什么? ?)!

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);

这就产生了答案:

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

“持续时间”值是一个小时到分钟的间隔;如果你想在几分钟内得到一个值,你必须用强制转换来转换它(使用4作为精度,允许间隔长达1440分钟或1天;对于较长的时间段,数据是模糊的):

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

或者:

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

IBM Informix Dynamic Server(IDS)对时间常数有非常详细的表示法。在标准SQL中,您可以使用TIME作为类型,TIME“10:00:00”作为值,但在严格的标准SQL中需要秒。IDS确实提供了人们想要的确切类型,例如DATETIME HOUR TO MINUTE。您还可以用标准SQL编写INTERVAL MINUTE(4);“TO MINUTE”应该是可选的。

查询不正确

在我对Ray Hidayat的回答的评论中,我指出EXISTS子查询对于确保所考虑的事件是连续的是必要的——没有中间的事件。这是同一个查询,输出中添加了开始和结束时间,缺少EXISTS子句(并且“duration”重命名为“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;

这就产生了答案:

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

这显示了用户“c”的每个符合条件的开始行是如何与每个符合条件地结束行匹配的,从而产生了许多虚假的数据行。在处理基于时间的查询时,NOT EXISTS子查询是一个常见的主题。您可以在Snodgrass的“ Developing Time-Oriented Applications in SQL “(PDF可在URL上在线获取),以及Date、Darwen和Lorentzos” Temporal Data and the Relational Model ".

2

3

Ray Hidayat 16 年前

我很确定它可以只使用SQL来完成,这将花费我相当多的时间来为你提出一个查询,我会在完成后对其进行编辑。我认为的基本步骤是首先计算每个条目所花费的时间(通过将每个条目与下一个条目连接起来并减去时间差来完成),然后用一个简单的带总和的子句将其轻松地转换为您所描述的形式。

编辑:这是我的想法

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

这是半伪代码,我编写了所有的表名和内容,你不能只从一个表中减去另一个表,你可能会使用DATEDIFF函数。除此之外,我认为这就是它的要点。我认为SQL是最神奇的语言之一,你几乎可以用很少的代码做任何事情。