sql语句实现同一时间范围内并发数统计计算

这是在做一个酒店系统时遇到的问题。通常情况下，一个服务生仅负责一个房间的客人用餐，但是，考虑到效益原因，酒店鼓励服务生同时为多个房间的客人提供服务。这样，酒店每天要对在同一时间段内服务房间最多的一名服务生进行奖励。表1列出了一天中酒店服务生所服务的房间号和服务时间。

 

表1 服务清单

 

room_id	waiter_name	start_time	end_time
1	张岚	2009-02-01 11:30:00.000	2009-02-01 13:30:00.000
2	张岚	2009-02-01 11:40:00.000	2009-02-01 13:15:00.000
3	孙静	2009-02-01 11:45:00.000	2009-02-01 14:20:00.000
4	孙静	2009-02-01 11:39:00.000	2009-02-01 13:20:00.000
5	孙静	2009-02-01 11:49:00.000	2009-02-01 14:16:00.000
6	孙静	2009-02-01 10:37:00.000	2009-02-01 11:00:00.000
3	孙静	2009-02-01 17:45:00.000	2009-02-01 18:20:00.000
4	孙静	2009-02-01 17:39:00.000	2009-02-01 18:25:00.000
5	孙静	2009-02-01 17:49:00.000	2009-02-01 18:36:00.000
6	孙静	2009-02-01 17:37:00.000	2009-02-01 18:40:00.000

 

下面是建立服务清单数据的SQL语句。

 

CREATE TABLE Waiters

 

(

 

room_idint,

 

waiter_name char(20),

 

start_timedatetime,

 

end_timedatetime

 

);

 

INSERT INTO Waiters VALUES

 

(1,'张岚','2009-02-01 11:30','2009-02-0113:30'),

 

(2,'张岚','2009-02-01 11:40','2009-02-0113:15'),

 

(3,'孙静','2009-02-01 11:45','2009-02-0114:20'),

 

(4,'孙静','2009-02-01 11:39','2009-02-0113:20'),

 

(5,'孙静','2009-02-01 11:49','2009-02-0114:16'),

 

(6,'孙静','2009-02-01 10:37','2009-02-0111:00'),

 

(3,'孙静','2009-02-01 17:45','2009-02-0118:20'),

 

(4,'孙静','2009-02-01 17:39','2009-02-0118:25'),

 

(5,'孙静','2009-02-01 17:49','2009-02-0118:36'),

 

(6,'孙静','2009-02-01 17:37','2009-02-0118:40');

 

为了更清楚地显示出每名服务生在同一时间内所服务房间的数量，我们将服务清单中的数据以图形的方式表示了出来。可以看出，虽然孙静在中午和晚上都是服务了4个房间，但是只有晚上4个房间的时间是重叠的。张岚是2个房间的时间重叠在一起。这样我们不难得出答案，在同一时间内张岚最多的服务房间是2个，孙静是4个。

 

 

图1 服务清单数据图形展示

 

1. 使用子查询

 

要解决这个问题，首先要找出如何判断时间重叠的方法。由图1可以看出，当一个房间的服务开始之后，只要第二个房间的服务开始时间小于或等于第一个房间，并且结束时间大于第一个房间的开始时间的，就说明这两个房间的服务时间存在重叠部分。例如，中午张岚在1号房间的服务开始时间小于2号房间的开始时间，并且结束时间大于2号房间的开始时间，说明这两个房间的服务时间重叠。而中午孙静在5号房间的服务开始之后，6号、4号、3号的服务开始时间都小于5号，但是6号的服务结束时间小于5号的开始时间，说明6号与5号服务时间并不重叠。

 

按照上述逻辑，我们首先给出下面的查询语句，以展示每个房间服务开始时所重叠的房间数量，查询结果如表2所示。表中的黑体部分数据能够更加明显地说明出这种重叠，如4号与6号重叠，3号与4、6号重叠，5号则与3、4、6号重叠。

 

SELECT W1.waiter_name,

 

W1.start_time,

 

W1.end_time,

 

MAX(W1.room_id) AS room_id,

 

COUNT(*) AS tally

 

FROM Waiters AS W1

 

INNER JOINWaiters AS W2

 

ONW1.waiter_name = W2.waiter_name

 

ANDW2.start_time <= W1.start_time

 

ANDW2.end_time > W1.start_time

 

GROUP BY W1.waiter_name, W1.start_time,W1.end_time, W1.room_id

 

ORDER BY W1.waiter_name, W1.start_time, room_id;

 

表2 房间重叠数量

 

waiter_name	start_time	end_time	room_id	tally
孙静	2009-02-01 10:37:00.000	2009-02-01 11:00:00.000	6	1
孙静	2009-02-01 11:39:00.000	2009-02-01 13:20:00.000	4	1
孙静	2009-02-01 11:45:00.000	2009-02-01 14:20:00.000	3	2
孙静	2009-02-01 11:49:00.000	2009-02-01 14:16:00.000	5	3
孙静	2009-02-01 17:37:00.000	2009-02-01 18:40:00.000	6	1
孙静	2009-02-01 17:39:00.000	2009-02-01 18:25:00.000	4	2
孙静	2009-02-01 17:45:00.000	2009-02-01 18:20:00.000	3	3
孙静	2009-02-01 17:49:00.000	2009-02-01 18:36:00.000	5	4
张岚	2009-02-01 11:30:00.000	2009-02-01 13:30:00.000	1	1
张岚	2009-02-01 11:40:00.000	2009-02-01 13:15:00.000	2	2

 

从上表可以看出，我们只要按服务生的姓名取出tally的最大值，就可以计算出每名服务生同时服务房间的最大数。下面是以子查询方式给出的最终语句，查询结果如表3所示。

 

SELECT T1.waiter_name, MAX(T1.tally) AS tally

 

FROM (SELECT W1.waiter_name,

 

W1.start_time,

 

W1.end_time,

 

COUNT(*) AS tally

 

FROMWaiters AS W1

 

INNER JOIN Waiters AS W2

 

ONW1.waiter_name = W2.waiter_name

 

AND W2.start_time <= W1.start_time

 

AND W2.end_time > W1.start_time

 

GROUPBY W1.waiter_name, W1.start_time, W1.end_time) AS T1

 

GROUP BY T1.waiter_name;

 

表3 最终查询结果

 

waiter_name	tally
孙静	4
张岚	2

 

2. 使用CTE

 

上面使用子查询的方式得出了最终结果，这里的子查询实际上起到的是一种临时结果集作用。所以，这个问题也可以使用CTE的方式进行查询。

 

WITH T1 (waiter_name, start_time, end_time,tally) -- 定义CTE表达式的名称和列

 

AS

 

(

 

SELECTW1.waiter_name,

 

W1.start_time,

 

W1.end_time,

 

COUNT(*) AS tally

 

FROMWaiters AS W1

 

INNERJOIN Waiters AS W2

 

ONW1.waiter_name = W2.waiter_name

 

ANDW2.start_time <= W1.start_time

 

ANDW2.end_time > W1.start_time

 

GROUP BYW1.waiter_name, W1.start_time, W1.end_time

 

)

 

SELECT waiter_name, MAX(tally) AS tally

 

FROM T1

 

GROUP BY T1.waiter_name;