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

Session重疊問題學習(一)

相關文章