分析查询 [英] Analytical Query
问题描述
正在寻找一个可以转换表中以下信息的查询
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 AM:Idle
b :10:11 AM:chatting
a :10:10 AM:Logout
b :10:12 AM:Logout
类似这样的情况(将查询时间范围为10 AM到10:15 AM)
to something like this (given the time range 10 AM to 10:15 AM as the querying period)
name: State :Duration
a : chatting :2 Minutes
a : Idle :2 Minutes
b : chatting :1 Minute
只能使用SQL来完成此操作吗?我正在使用Informix 11.5版
Can this be done ONLY using SQL? Am using Informix version 11.5
推荐答案
可以在单个SQL语句中完成.这是证据.
It can be done in a single SQL statement. Here's the proof.
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');
正确的查询
注意条件.结果表必须排除登录"和第一个其他事件之间的时间段；此外，它必须排除注销"和下一个事件(大概是登录")之间的时间间隔. name 列上的表之间的自联接，然后是 time 列上的非对称联接(使用'<')之间的自联接确保事件按时间顺序排列. NOT EXISTS子选择可确保仅考虑相邻事件.在子查询中使用BETWEEN AND是一个错误，因为它包含其端点，并且将r1.time和r2.time从范围中排除是至关重要的；我花了几分钟才发现该错误(查询已运行但未返回任何行，但为什么?)！
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) 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天的间隔；对于较长的时间范围，数据是模棱两可的):
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)::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). 分钟"应该是可选的.
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.
在我对Ray Hidayat的回答的评论中，我指出EXISTS子查询对于确保所考虑的事件是连续的-没有介入事件是必要的.这是将开始和结束时间添加到输出中的相同查询，并且缺少EXISTS子句(并且"duration"重命名为"lapse"):
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 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的"使用SQL开发面向时间的应用程序中找到有关这些操作的信息"(可在URL上在线获取PDF)，在日期中，Darwen和Lorentzos则是"时间数据和关系模型".
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".
这篇关于分析查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
