这样做是必要的残酷的;它将使你的优化工作真正相当严肃。
根据问题之后的评论和问题,如果某一用户的身份证在邻近事件之间有固定间隔,则希望在大张桌上将事件顺序作为毗连。 例如,固定间隔为3小时。 为IBM Informix Dynamic服务器提供的I m编码(为论证,第11.70版,但还有11.50页。 这意味着,我需要解释一下的是非同义词。 具体来说,编号3 UNITS HOUR是指3小时的间隔;也可在标准Q中写成:INTERVAL(3) HOUR。
在制作SQ,特别是SQL综合体方面有两种关键技术。 其中一个步骤是把最后结果编成零敲碎打。 另一种做法是,确保你清楚地知道你之后的情况。
在下面的注解中,对同一用户(ID)的资格应当被视为始终属于该表述的一部分。
In the large table, there are three categories of range that we want to consider before joining with the small table.
- Entries where there is neither a row with an event time before the event that is close enough nor a row with an event time after the event that is close enough. This is a time range with the same start and end time.
- A pair of entries in the table that are themselves close enough but for which there is neither an event earlier than the early event of the pair that is close enough nor an event later than the late event of the pair that is close enough nor an event in between the pair.
- A sequence of three or more entries in the table for which there is:
- No event E1 earlier than the earliest that is close enough
- No event E2 later than the latest that is close enough
- An event E3 later than the earliest that is close enough to the earliest
- An event E4 earlier than the latest that is close enough to the latest (E4 might possibly be the same event as E3)
- No pair of events E5, E6 between the earliest and the latest where there is no event in between E5 and E6 but the gap between E5 and E6 is too large to count.
你们可以从描述中看到,这将是一些ary!
NB:现在对该法典进行了测试;一些(主要是小的)改动是必要的。 一项不必要的小改动是,根据关于中间询问的条款增加ER。 另一项不必要的改动是,为核查目的从小表格中挑选其他数据。 这一修订是在没有研究msh210>的修改文本的情况下进行的。
Also note that I m far from certain this is a minimal formulation; it may be feasible to classify all the ranges with a single SELECT statement instead of a UNION of three SELECT statements (and it would be good if that is the case).
Singleton ranges
-- Ranges of exactly 1 event
SELECT lt1.user_id, lt1.event_time AS min_time, lt1.event_time AS max_time
FROM Large_Table AS lt1
WHERE NOT EXISTS -- an earlier event that is close enough
(SELECT *
FROM Large_Table AS lt3
WHERE lt1.user_id = lt3.user_id
AND lt3.event_time > lt1.event_time - 3 UNITS HOUR
AND lt3.event_time < lt1.event_time
)
AND NOT EXISTS -- a later event that is close enough
(SELECT *
FROM Large_Table AS lt4
WHERE lt1.user_id = lt4.user_id
AND lt4.event_time > lt1.event_time
AND lt4.event_time < lt1.event_time + 3 UNITS HOUR
)
ORDER BY User_ID, Min_Time;
Doubleton Ranges
-- Ranges of exactly 2 events
SELECT lt1.user_id, lt1.event_time AS min_time, lt2.event_time AS max_time
FROM Large_Table AS lt1
JOIN Large_Table AS lt2
ON lt1.user_id = lt2.user_id
AND lt1.event_time < lt2.event_time
AND lt2.event_time < lt1.event_time + 3 UNITS HOUR
WHERE NOT EXISTS -- an earlier event that is close enough
(SELECT *
FROM Large_Table AS lt3
WHERE lt1.user_id = lt3.user_id
AND lt3.event_time > lt1.event_time - 3 UNITS HOUR
AND lt3.event_time < lt1.event_time
)
AND NOT EXISTS -- a later event that is close enough
(SELECT *
FROM Large_Table AS lt4
WHERE lt1.user_id = lt4.user_id
AND lt4.event_time > lt2.event_time
AND lt4.event_time < lt2.event_time + 3 UNITS HOUR
)
AND NOT EXISTS -- any event in between
(SELECT *
FROM Large_Table AS lt5
WHERE lt1.user_id = lt5.user_id
AND lt5.event_time > lt1.event_time
AND lt5.event_time < lt2.event_time
)
ORDER BY User_ID, Min_Time;
http://www.un.org/Depts/DGACM/index_french.htm
Multiple Event Ranges
-- Ranges of 3 or more events
SELECT lt1.user_id, lt1.event_time AS min_time, lt2.event_time AS max_time
FROM Large_Table AS lt1
JOIN Large_Table AS lt2
ON lt1.user_id = lt2.user_id
AND lt1.event_time < lt2.event_time
WHERE NOT EXISTS -- an earlier event that is close enough
(SELECT *
FROM Large_Table AS lt3
WHERE lt1.user_id = lt3.user_id
AND lt3.event_time > lt1.event_time - 3 UNITS HOUR
AND lt3.event_time < lt1.event_time
)
AND NOT EXISTS -- a later event that is close enough
(SELECT *
FROM Large_Table AS lt4
WHERE lt1.user_id = lt4.user_id
AND lt4.event_time > lt2.event_time
AND lt4.event_time < lt2.event_time + 3 UNITS HOUR
)
AND NOT EXISTS -- a gap that s too big in the events between first and last
(SELECT *
FROM Large_Table AS lt5 -- E5 before E6
JOIN Large_Table AS lt6
ON lt5.user_id = lt6.user_id
AND lt5.event_time < lt6.event_time
WHERE lt1.user_id = lt5.user_id
AND lt6.event_time < lt2.event_time
AND lt5.event_time > lt1.event_time
AND (lt6.event_time - lt5.event_time) > 3 UNITS HOUR
AND NOT EXISTS -- an event in between these two
(SELECT *
FROM Large_Table AS lt9
WHERE lt5.user_id = lt9.user_id
AND lt9.event_time > lt5.event_time
AND lt9.event_time < lt6.event_time
)
)
AND EXISTS -- an event close enough after the start
(SELECT *
FROM Large_Table AS lt7
WHERE lt1.user_id = lt7.user_id
AND lt1.event_time < lt7.event_time
AND lt7.event_time < lt1.event_time + 3 UNITS HOUR
AND lt7.event_time < lt2.event_time
)
AND EXISTS -- an event close enough before the end
(SELECT *
FROM Large_Table AS lt8
WHERE lt2.user_id = lt8.user_id
AND lt2.event_time > lt8.event_time
AND lt8.event_time > lt2.event_time - 3 UNITS HOUR
AND lt8.event_time > lt1.event_time
)
ORDER BY User_ID, Min_Time;
Added 漏掉了在大差距分局中设置的NOT EXISTS条款。
All Ranges in Large Table
Clearly, the complete list of ranges in the last table are the union of the three queries above.
Query deleted as not interesting enough. It is simply the 3-way UNION of the separate queries above.
Final Query
最后的询问发现,如果存在任何范围,则会由于三个部分的贪.,而这种划分接近于小表的输入。 小型表格的单一条目可能下降为1,1,1,1,3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1。 从大型桌子的两种幅度是分开的(两者之间的差距是4小时),但小桌子的条目接近两倍。 [ 测试涵盖本案:。
SELECT S.User_id, S.Event_Time, L.Min_Time, L.Max_Time, S.Other_Data
FROM Small_Table AS S
JOIN (
-- Ranges of exactly 1 event
SELECT lt1.user_id, lt1.event_time AS min_time, lt1.event_time AS max_time
FROM Large_Table AS lt1
WHERE NOT EXISTS -- an earlier event that is close enough
(SELECT *
FROM Large_Table AS lt3
WHERE lt1.user_id = lt3.user_id
AND lt3.event_time > lt1.event_time - 3 UNITS HOUR
AND lt3.event_time < lt1.event_time
)
AND NOT EXISTS -- a later event that is close enough
(SELECT *
FROM Large_Table AS lt4
WHERE lt1.user_id = lt4.user_id
AND lt4.event_time > lt1.event_time
AND lt4.event_time < lt1.event_time + 3 UNITS HOUR
)
UNION
-- Ranges of exactly 2 events
SELECT lt1.user_id, lt1.event_time AS min_time, lt2.event_time AS max_time
FROM Large_Table AS lt1
JOIN Large_Table AS lt2
ON lt1.user_id = lt2.user_id
AND lt1.event_time < lt2.event_time
AND lt2.event_time < lt1.event_time + 3 UNITS HOUR
WHERE NOT EXISTS -- an earlier event that is close enough
(SELECT *
FROM Large_Table AS lt3
WHERE lt1.user_id = lt3.user_id
AND lt3.event_time > lt1.event_time - 3 UNITS HOUR
AND lt3.event_time < lt1.event_time
)
AND NOT EXISTS -- a later event that is close enough
(SELECT *
FROM Large_Table AS lt4
WHERE lt1.user_id = lt4.user_id
AND lt4.event_time > lt2.event_time
AND lt4.event_time < lt2.event_time + 3 UNITS HOUR
)
AND NOT EXISTS -- any event in between
(SELECT *
FROM Large_Table AS lt5
WHERE lt1.user_id = lt5.user_id
AND lt5.event_time > lt1.event_time
AND lt5.event_time < lt2.event_time
)
UNION
-- Ranges of 3 or more events
SELECT lt1.user_id, lt1.event_time AS min_time, lt2.event_time AS max_time
FROM Large_Table AS lt1
JOIN Large_Table AS lt2
ON lt1.user_id = lt2.user_id
AND lt1.event_time < lt2.event_time
WHERE NOT EXISTS -- an earlier event that is close enough
(SELECT *
FROM Large_Table AS lt3
WHERE lt1.user_id = lt3.user_id
AND lt3.event_time > lt1.event_time - 3 UNITS HOUR
AND lt3.event_time < lt1.event_time
)
AND NOT EXISTS -- a later event that is close enough
(SELECT *
FROM Large_Table AS lt4
WHERE lt1.user_id = lt4.user_id
AND lt4.event_time > lt2.event_time
AND lt4.event_time < lt2.event_time + 3 UNITS HOUR
)
AND NOT EXISTS -- a gap that s too big in the events between first and last
(SELECT *
FROM Large_Table AS lt5 -- E5 before E6
JOIN Large_Table AS lt6
ON lt5.user_id = lt6.user_id
AND lt5.event_time < lt6.event_time
WHERE lt1.user_id = lt5.user_id
AND lt6.event_time < lt2.event_time
AND lt5.event_time > lt1.event_time
AND (lt6.event_time - lt5.event_time) > 3 UNITS HOUR
AND NOT EXISTS -- an event in between these two
(SELECT *
FROM Large_Table AS lt9
WHERE lt5.user_id = lt9.user_id
AND lt9.event_time > lt5.event_time
AND lt9.event_time < lt6.event_time
)
)
AND EXISTS -- an event close enough after the start
(SELECT *
FROM Large_Table AS lt7
WHERE lt1.user_id = lt7.user_id
AND lt1.event_time < lt7.event_time
AND lt7.event_time < lt1.event_time + 3 UNITS HOUR
AND lt7.event_time < lt2.event_time
)
AND EXISTS -- an event close enough before the end
(SELECT *
FROM Large_Table AS lt8
WHERE lt2.user_id = lt8.user_id
AND lt2.event_time > lt8.event_time
AND lt8.event_time > lt2.event_time - 3 UNITS HOUR
AND lt8.event_time > lt1.event_time
)
) AS L
ON S.User_ID = L.User_ID
WHERE S.Event_Time > L.Min_Time - 3 UNITS HOUR
AND S.Event_Time < L.Max_Time + 3 UNITS HOUR
ORDER BY User_ID, Event_Time, Min_Time;
OK——公平的警告;LQ没有在房舍管理处附近任何地点。
现在对该代码进行了测试。 无限机会实际上为零;存在一对一的错误,并存在一个或多个次要问题。
我在设计测试数据后进行了试验。 我使用了阿尔法数据(见下文),同时验证和确定询问,并增加了Beta数据,以确保不同用户——ID值之间没有交叉数字。
我使用了明确的<代码><和> 业务而不是。 BETWEEN 和,以排除最终点;如果你想把事情完全分开3小时,以便算得足够接近,那么你需要审查每一不平等情况,可能将其改为。 BETWEEN 和 或可酌情使用<代码>>=或<=。
answer 对于一个大致相似但较为简单的问题,即:(a) 我写了字,(b) 就上述复杂处理提供了一些有益的想法(特别是,以前没有发生但过得足够、但后来又没有结束的事件)。 足够严格的标准无疑使这一问题复杂化。
Test Data
Large Table
CREATE TABLE Large_Table
(
Event_Time DATETIME YEAR TO MINUTE NOT NULL,
User_ID CHAR(15) NOT NULL,
Other_Data INTEGER NOT NULL,
PRIMARY KEY(User_ID, Event_Time)
);
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 09:15 , Alpha , 1) { R4 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 11:15 , Alpha , 2) { R4 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 13:15 , Alpha , 3) { R4 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 15:15 , Alpha , 4) { R4 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 12:17 , Beta , 1) { R4 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-02 09:15 , Alpha , 5) { R1 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-02 10:17 , Beta , 2) { R1 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-03 09:15 , Alpha , 6) { R2 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-03 11:15 , Alpha , 7) { R2 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-03 10:17 , Beta , 3) { R1 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-04 09:15 , Alpha , 8) { R3 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-04 11:15 , Alpha , 9) { R3 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-04 13:15 , Alpha , 10) { R3 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-04 10:17 , Beta , 4) { R1 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 09:15 , Alpha , 11) { R2 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 11:15 , Alpha , 12) { R2 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 10:17 , Beta , 5) { R1 };
{ Probe here }
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 15:15 , Alpha , 13) { R2 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 17:15 , Alpha , 14) { R2 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 16:17 , Beta , 6) { R1 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 09:15 , Alpha , 15) { R6 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 11:15 , Alpha , 16) { R6 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 13:15 , Alpha , 17) { R6 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 15:15 , Alpha , 18) { R6 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 17:15 , Alpha , 19) { R6 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 19:15 , Alpha , 20) { R6 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 16:17 , Beta , 7) { R1 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-07 09:15 , Alpha , 21) { R1 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-07 11:17 , Beta , 8) { R1 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-07 12:15 , Alpha , 22) { R1 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-07 13:17 , Beta , 9) { R1 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-08 09:15 , Alpha , 23) { R5 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-08 11:15 , Alpha , 24) { R5 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-08 13:15 , Alpha , 25) { R5 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-08 15:15 , Alpha , 26) { R5 };
INSERT INTO Large_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-08 17:15 , Alpha , 27) { R5 };
Small Table
注:为测试目的,小表实际上包含的浏览量比大。 小型表格中与100多个其他元数值的行文不应出现在结果中(也不应出现)。 这里的测试是在边缘条件下进行的。
CREATE TABLE Small_Table
(
Event_Time DATETIME YEAR TO MINUTE NOT NULL,
User_ID CHAR(15) NOT NULL,
Other_Data INTEGER NOT NULL,
PRIMARY KEY(User_ID, Event_Time)
);
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 06:15 , Alpha , 131) { XX };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 06:20 , Alpha , 31) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 10:20 , Alpha , 32) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 13:20 , Alpha , 33) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 15:20 , Alpha , 34) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 18:15 , Alpha , 134) { XX };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-02 06:15 , Alpha , 135) { XX };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-02 06:16 , Alpha , 35) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-02 10:20 , Alpha , 35) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-02 12:14 , Alpha , 35) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-02 12:15 , Alpha , 135) { XX };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-03 09:20 , Alpha , 36) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-03 11:20 , Alpha , 37) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-04 09:20 , Alpha , 38) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-04 11:20 , Alpha , 39) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-04 13:20 , Alpha , 40) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 09:20 , Alpha , 41) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 11:20 , Alpha , 42) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 13:20 , Alpha , 42) { 22 };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 15:20 , Alpha , 43) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 17:20 , Alpha , 44) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 09:20 , Alpha , 45) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 11:20 , Alpha , 46) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 13:20 , Alpha , 47) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 15:20 , Alpha , 48) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 17:20 , Alpha , 49) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 19:20 , Alpha , 50) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-07 09:20 , Alpha , 51) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-07 10:20 , Alpha , 51) { 22 };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-07 12:20 , Alpha , 52) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-08 09:20 , Alpha , 53) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-08 11:20 , Alpha , 54) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-08 13:20 , Alpha , 55) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-08 15:20 , Alpha , 56) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-08 17:20 , Alpha , 57) { YY };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-07 13:27 , Beta , 9) { R1 };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-07 11:27 , Beta , 8) { R1 };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-06 16:27 , Beta , 7) { R1 };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 16:27 , Beta , 6) { R1 };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-05 10:27 , Beta , 5) { R1 };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-04 10:27 , Beta , 4) { R1 };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-03 10:27 , Beta , 3) { R1 };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-02 10:27 , Beta , 2) { R1 };
INSERT INTO Small_Table(Event_Time, User_ID, Other_Data) VALUES( 2012-01-01 12:27 , Beta , 1) { R4 };
Final Query Results
根据上述数据,取得的成果是:
Alpha 2012-01-01 06:20 2012-01-01 09:15 2012-01-01 15:15 31
Alpha 2012-01-01 10:20 2012-01-01 09:15 2012-01-01 15:15 32
Alpha 2012-01-01 13:20 2012-01-01 09:15 2012-01-01 15:15 33
Alpha 2012-01-01 15:20 2012-01-01 09:15 2012-01-01 15:15 34
Alpha 2012-01-02 06:16 2012-01-02 09:15 2012-01-02 09:15 35
Alpha 2012-01-02 10:20 2012-01-02 09:15 2012-01-02 09:15 35
Alpha 2012-01-02 12:14 2012-01-02 09:15 2012-01-02 09:15 35
Alpha 2012-01-03 09:20 2012-01-03 09:15 2012-01-03 11:15 36
Alpha 2012-01-03 11:20 2012-01-03 09:15 2012-01-03 11:15 37
Alpha 2012-01-04 09:20 2012-01-04 09:15 2012-01-04 13:15 38
Alpha 2012-01-04 11:20 2012-01-04 09:15 2012-01-04 13:15 39
Alpha 2012-01-04 13:20 2012-01-04 09:15 2012-01-04 13:15 40
Alpha 2012-01-05 09:20 2012-01-05 09:15 2012-01-05 11:15 41
Alpha 2012-01-05 11:20 2012-01-05 09:15 2012-01-05 11:15 42
Alpha 2012-01-05 13:20 2012-01-05 09:15 2012-01-05 11:15 42
Alpha 2012-01-05 13:20 2012-01-05 15:15 2012-01-05 17:15 42
Alpha 2012-01-05 15:20 2012-01-05 15:15 2012-01-05 17:15 43
Alpha 2012-01-05 17:20 2012-01-05 15:15 2012-01-05 17:15 44
Alpha 2012-01-06 09:20 2012-01-06 09:15 2012-01-06 19:15 45
Alpha 2012-01-06 11:20 2012-01-06 09:15 2012-01-06 19:15 46
Alpha 2012-01-06 13:20 2012-01-06 09:15 2012-01-06 19:15 47
Alpha 2012-01-06 15:20 2012-01-06 09:15 2012-01-06 19:15 48
Alpha 2012-01-06 17:20 2012-01-06 09:15 2012-01-06 19:15 49
Alpha 2012-01-06 19:20 2012-01-06 09:15 2012-01-06 19:15 50
Alpha 2012-01-07 09:20 2012-01-07 09:15 2012-01-07 09:15 51
Alpha 2012-01-07 09:20 2012-01-07 12:15 2012-01-07 12:15 51
Alpha 2012-01-07 10:20 2012-01-07 09:15 2012-01-07 09:15 51
Alpha 2012-01-07 10:20 2012-01-07 12:15 2012-01-07 12:15 51
Alpha 2012-01-07 12:20 2012-01-07 12:15 2012-01-07 12:15 52
Alpha 2012-01-08 09:20 2012-01-08 09:15 2012-01-08 17:15 53
Alpha 2012-01-08 11:20 2012-01-08 09:15 2012-01-08 17:15 54
Alpha 2012-01-08 13:20 2012-01-08 09:15 2012-01-08 17:15 55
Alpha 2012-01-08 15:20 2012-01-08 09:15 2012-01-08 17:15 56
Alpha 2012-01-08 17:20 2012-01-08 09:15 2012-01-08 17:15 57
Beta 2012-01-01 12:27 2012-01-01 12:17 2012-01-01 12:17 1
Beta 2012-01-02 10:27 2012-01-02 10:17 2012-01-02 10:17 2
Beta 2012-01-03 10:27 2012-01-03 10:17 2012-01-03 10:17 3
Beta 2012-01-04 10:27 2012-01-04 10:17 2012-01-04 10:17 4
Beta 2012-01-05 10:27 2012-01-05 10:17 2012-01-05 10:17 5
Beta 2012-01-05 16:27 2012-01-05 16:17 2012-01-05 16:17 6
Beta 2012-01-06 16:27 2012-01-06 16:17 2012-01-06 16:17 7
Beta 2012-01-07 11:27 2012-01-07 11:17 2012-01-07 13:17 8
Beta 2012-01-07 13:27 2012-01-07 11:17 2012-01-07 13:17 9
Intermediate results
格式差别很大。
Singleton ranges
Alpha|2012-01-02 09:15|2012-01-02 09:15
Alpha|2012-01-07 09:15|2012-01-07 09:15
Alpha|2012-01-07 12:15|2012-01-07 12:15
Beta|2012-01-01 12:17|2012-01-01 12:17
Beta|2012-01-02 10:17|2012-01-02 10:17
Beta|2012-01-03 10:17|2012-01-03 10:17
Beta|2012-01-04 10:17|2012-01-04 10:17
Beta|2012-01-05 10:17|2012-01-05 10:17
Beta|2012-01-05 16:17|2012-01-05 16:17
Beta|2012-01-06 16:17|2012-01-06 16:17
2. 双重吨位
Alpha|2012-01-03 09:15|2012-01-03 11:15
Alpha|2012-01-05 09:15|2012-01-05 11:15
Alpha|2012-01-05 15:15|2012-01-05 17:15
Beta|2012-01-07 11:17|2012-01-07 13:17
多个活动领域
Alpha|2012-01-01 09:15|2012-01-01 15:15
Alpha|2012-01-04 09:15|2012-01-04 13:15
Alpha|2012-01-06 09:15|2012-01-06 19:15
Alpha|2012-01-08 09:15|2012-01-08 17:15
