MySQL技术内幕 SQL
2.4 Session重叠问题

实验数据初始化:

CREATE TABLE sessions (
id INT AUTO_INCREMENT NOT NULL,
app VARCHAR(10) NOT NULL,
usr VARCHAR(10) NOT NULL,
starttime TIME NOT NULL,
endtime TIME NOT NULL,
KEY (id)
);
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app1', 'user1', '08:30', '10:30');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app1', 'user2', '08:30', '08:45');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app1', 'user1', '09:00', '09:30');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app1', 'user2', '09:15', '10:30');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app1', 'user1', '09:15', '09:30');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app1', 'user2', '10:30', '14:30');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app1', 'user1', '10:45', '11:30');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app1', 'user2', '11:00', '12:30');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app2', 'user1', '08:30', '08:45');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app2', 'user2', '09:00', '09:30');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app2', 'user1', '11:45', '12:00');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app2', 'user2', '12:30', '14:00');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app2', 'user1', '12:45', '13:30');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app2', 'user2', '13:00', '14:00');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app2', 'user1', '14:00', '16:30');
INSERT INTO sessions(app, usr, starttime, endtime)
VALUES('app2', 'user2', '15:30', '17:00');
CREATE UNIQUE INDEX idx_app_usr_s_e_key
ON sessions(app, usr, starttime, endtime, id);
CREATE INDEX idx_app_s_e ON sessions(app, starttime, endtime);

标记重叠的书中原文:

标示重叠是指为每个会话标示出相同应用程序和用户重叠的会话，即对于每个会话，标示出其内部的所有会话情况。
例如，app1、user1这个会话在08:30～10:30时间段内有3次重叠的会话：08:30～10:30、09:00～09:30和09:15～09:30。
对于这个问题，下面是完整的SQL解决方案：

SELECT a.app, a.usr, a.starttime, a.endtime, b.starttime
, b.endtime
FROM sessions a, sessions b
WHERE a.app = b.app
AND a.usr = b.usr
AND (b.endtime >= a.starttime
AND b.starttime <= a.endtime)

<b.starttime<b.endtime<a.endtime

分组重叠
还有一个问题是服务提供商可能允许多个session的连接，并把其计费统计为１次，这就是所谓的分组重叠。对于上面的例子，应该把app1、user1在08:30～10:30间的3次会话算为一次会话。
我们分步骤来讨论这个问题，先求出每个会话组开始时间，并用DISTINCT返回不重复的开始时间，具体过程如下：

SELECT DISTINCT app, usr, starttime AS s
FROM sessions a
WHERE NOT EXISTS (SELECT *
FROM sessions b
WHERE a.app = b.app
AND a.usr = b.usr
AND a.starttime > b.starttime
AND a.starttime <= b.endtime)

用同样的方法得到会话组结束的时间，具体过程如下：

SELECT DISTINCT app, usr, endtime AS e
FROM sessions a
WHERE NOT EXISTS (SELECT *
FROM sessions b
WHERE a.app = b.app
AND a.usr = b.usr
AND a.endtime >= b.starttime
AND a.endtime < b.endtime)

最后只需把两张表合并，并通过MIN函数取得结束的时间。完整的SQL解决方案如下面的代码所示：

SELECT DISTINCT s.app, s.usr, s.s, (
SELECT MIN(e)
FROM (SELECT DISTINCT app, usr, endtime AS e
FROM sessions a
WHERE NOT EXISTS (SELECT *
FROM sessions b
WHERE a.app = b.app
AND a.usr = b.usr
AND a.endtime >= b.starttime
AND a.endtime < b.endtime)
) s2
WHERE s2.e > s.s
AND s.app = s2.app
AND s.usr = s2.usr
) AS e
FROM (SELECT DISTINCT app, usr, starttime AS s
FROM sessions a
WHERE NOT EXISTS (SELECT *
FROM sessions b
WHERE a.app = b.app
AND a.usr = b.usr
AND a.starttime > b.starttime
AND a.starttime <= b.endtime)
) s, (SELECT DISTINCT app, usr, endtime AS e
FROM sessions a
WHERE NOT EXISTS (SELECT *
FROM sessions b
WHERE a.app = b.app
AND a.usr = b.usr
AND a.endtime >= b.starttime
AND a.endtime < b.endtime)
) e
WHERE s.app = e.app
AND s.usr = e.usr

</b.starttime<b.endtime<a.endtime

ITPub博客

Session重叠问题学习(一)

最新文章