您可以使用此解决方案:
SELECT b.Name,
AVG(b.Users) avg_users
FROM (
SELECT a.UID,
MAX(c.Datetime) last_date
FROM (SELECT DISTINCT UID FROM tbl) a
CROSS JOIN (
SELECT '14:00:00' intrvl UNION ALL
SELECT '14:15:00' UNION ALL
SELECT '14:30:00' UNION ALL
SELECT '14:45:00'
) b
JOIN tbl c ON a.UID = c.UID
AND TIME(b.intrvl) >= TIME(c.Datetime)
GROUP BY a.UID,
b.intrvl
) a
JOIN tbl b ON a.UID = b.UID
AND a.last_date = b.Datetime
GROUP BY b.UID,
b.Name
查询细分:
步骤1:
我们需要做的第一件事是将每个房间与每个时间间隔相关联。例如,在您的示例数据中,Room 4与14:15:00和14:30:00之间的间隔没有关联,但是我们仍然需要以某种方式表示这些关联。
我们通过创建每个不同房间的Cartesian product相关时间间隔来实现:
SELECT a.UID,
b.intrvl
FROM (SELECT DISTINCT UID FROM tbl) a
CROSS JOIN (
SELECT '14:00:00' intrvl UNION ALL
SELECT '14:15:00' UNION ALL
SELECT '14:30:00' UNION ALL
SELECT '14:45:00'
) b
ORDER BY b.intrvl, a.UID DESC --Ordering for display purposes
呈现:
UID | intrvl
--------------
4 | 14:00:00
3 | 14:00:00
2 | 14:00:00
1 | 14:00:00
4 | 14:15:00
3 | 14:15:00
2 | 14:15:00
1 | 14:15:00
4 | 14:30:00
3 | 14:30:00
2 | 14:30:00
1 | 14:30:00
4 | 14:45:00
3 | 14:45:00
2 | 14:45:00
1 | 14:45:00
第2步:
一旦我们有这些关联,我们将结果重新连接到主表(tbl)上,条件是它的Datetime字段的主表的时间部分小于每个UID的笛卡尔加入时间。这将是每个UID – > intrvl关联,它将显示在intrvl时间之前或之前发生的所有条目。
所以例如,由于3号房间没有入场14:30:00内幕,只有两个条目将加入这个intrvl:那些在14:15:00和14:00:00,因为他们都发生了在内部时间或之前。
你现在可以看到我们要去哪里。此步骤的结果将使我们能够访问每个intrvl的最新条目。
SELECT a.UID,
b.intrvl,
c.*
FROM (SELECT DISTINCT UID FROM tbl) a
CROSS JOIN (
SELECT '14:00:00' intrvl UNION ALL
SELECT '14:15:00' UNION ALL
SELECT '14:30:00' UNION ALL
SELECT '14:45:00'
) b
JOIN tbl c ON a.UID = c.UID
AND TIME(b.intrvl) >= TIME(c.Datetime)
ORDER BY b.intrvl, a.UID DESC, c.Datetime --Ordering for display purposes
渲染(名称栏除外):
UID | intrvl | Datetime | Users
---------------- --------------------------------
4 | 14:00:00 | 2012-08-03 14:00:00 | 3
3 | 14:00:00 | 2012-08-03 14:00:00 | 1
2 | 14:00:00 | 2012-08-03 14:00:00 | 3
1 | 14:00:00 | 2012-08-03 14:00:00 | 2
4 | 14:15:00 | 2012-08-03 14:00:00 | 3
3 | 14:15:00 | 2012-08-03 14:00:00 | 1
3 | 14:15:00 | 2012-08-03 14:15:00 | 1
2 | 14:15:00 | 2012-08-03 14:00:00 | 3
2 | 14:15:00 | 2012-08-03 14:15:00 | 4
1 | 14:15:00 | 2012-08-03 14:00:00 | 2
1 | 14:15:00 | 2012-08-03 14:15:00 | 3
4 | 14:30:00 | 2012-08-03 14:00:00 | 3
3 | 14:30:00 | 2012-08-03 14:00:00 | 1
3 | 14:30:00 | 2012-08-03 14:15:00 | 1
2 | 14:30:00 | 2012-08-03 14:00:00 | 3
2 | 14:30:00 | 2012-08-03 14:15:00 | 4
1 | 14:30:00 | 2012-08-03 14:00:00 | 2
1 | 14:30:00 | 2012-08-03 14:15:00 | 3
1 | 14:30:00 | 2012-08-03 14:30:00 | 6
4 | 14:45:00 | 2012-08-03 14:00:00 | 3
4 | 14:45:00 | 2012-08-03 14:45:00 | 4
3 | 14:45:00 | 2012-08-03 14:00:00 | 1
3 | 14:45:00 | 2012-08-03 14:15:00 | 1
3 | 14:45:00 | 2012-08-03 14:45:00 | 8
2 | 14:45:00 | 2012-08-03 14:00:00 | 3
2 | 14:45:00 | 2012-08-03 14:15:00 | 4
2 | 14:45:00 | 2012-08-03 14:45:00 | 7
1 | 14:45:00 | 2012-08-03 14:00:00 | 2
1 | 14:45:00 | 2012-08-03 14:15:00 | 3
1 | 14:45:00 | 2012-08-03 14:30:00 | 6
1 | 14:45:00 | 2012-08-03 14:45:00 | 3
步骤3:
我们的下一步是采取以上结果集,并为每个intrvl只拉取最近加入的日期时间。我们可以通过使用GROUP BY和MAX()聚合函数来实现这一点。
不幸的是,由于GROUP BY的行为如何,我们也无法正确地将用户的值与每个选定的数据时间一起提取。
SELECT a.UID,
b.intrvl,
MAX(c.Datetime) last_date
FROM (SELECT DISTINCT UID FROM tbl) a
CROSS JOIN (
SELECT '14:00:00' intrvl UNION ALL
SELECT '14:15:00' UNION ALL
SELECT '14:30:00' UNION ALL
SELECT '14:45:00'
) b
JOIN tbl c ON a.UID = c.UID
AND TIME(b.intrvl) >= TIME(c.Datetime)
GROUP BY a.UID,
b.intrvl
ORDER BY b.intrvl, a.UID DESC --Again, for display purposes
呈现:
UID | intrvl | last_date
---------------------------------------
4 | 14:00:00 | 2012-08-03 14:00:00
3 | 14:00:00 | 2012-08-03 14:00:00
2 | 14:00:00 | 2012-08-03 14:00:00
1 | 14:00:00 | 2012-08-03 14:00:00
4 | 14:15:00 | 2012-08-03 14:00:00
3 | 14:15:00 | 2012-08-03 14:15:00
2 | 14:15:00 | 2012-08-03 14:15:00
1 | 14:15:00 | 2012-08-03 14:15:00
4 | 14:30:00 | 2012-08-03 14:00:00
3 | 14:30:00 | 2012-08-03 14:15:00
2 | 14:30:00 | 2012-08-03 14:15:00
1 | 14:30:00 | 2012-08-03 14:30:00
4 | 14:45:00 | 2012-08-03 14:45:00
3 | 14:45:00 | 2012-08-03 14:45:00
2 | 14:45:00 | 2012-08-03 14:45:00
1 | 14:45:00 | 2012-08-03 14:45:00
步骤4
现在我们必须抓住每个last_date的Users的值,以便我们可以获取这些值的平均值。我们通过将最后一步中的查询作为FROM子句中的子选项来包装我们的查询,并再次返回到主表,条件是对于每个匹配的UID – > last_date关联,获取用户的值。
SELECT a.UID,
a.last_date,
b.Users
FROM (
SELECT a.UID,
MAX(c.Datetime) last_date
FROM (SELECT DISTINCT UID FROM tbl) a
CROSS JOIN (
SELECT '14:00:00' intrvl UNION ALL
SELECT '14:15:00' UNION ALL
SELECT '14:30:00' UNION ALL
SELECT '14:45:00'
) b
JOIN tbl c ON a.UID = c.UID
AND TIME(b.intrvl) >= TIME(c.Datetime)
GROUP BY a.UID,
b.intrvl
) a
JOIN tbl b ON a.UID = b.UID
AND a.last_date = b.Datetime
ORDER BY a.UID DESC --Display purposes again
呈现:
UID | last_date | Users
---------------------------------
4 | 2012-08-03 14:00:00 | 3
4 | 2012-08-03 14:00:00 | 3
4 | 2012-08-03 14:00:00 | 3
4 | 2012-08-03 14:45:00 | 4
3 | 2012-08-03 14:00:00 | 1
3 | 2012-08-03 14:15:00 | 1
3 | 2012-08-03 14:15:00 | 1
3 | 2012-08-03 14:45:00 | 8
2 | 2012-08-03 14:00:00 | 3
2 | 2012-08-03 14:15:00 | 4
2 | 2012-08-03 14:15:00 | 4
2 | 2012-08-03 14:45:00 | 7
1 | 2012-08-03 14:00:00 | 2
1 | 2012-08-03 14:15:00 | 3
1 | 2012-08-03 14:30:00 | 6
1 | 2012-08-03 14:45:00 | 3
步骤5
现在,这只是一个简单的事情,在每个房间分组,平均“用户”列:
SELECT b.Name,
AVG(b.Users) avg_users
FROM (
SELECT a.UID,
MAX(c.Datetime) last_date
FROM (SELECT DISTINCT UID FROM tbl) a
CROSS JOIN (
SELECT '14:00:00' intrvl UNION ALL
SELECT '14:15:00' UNION ALL
SELECT '14:30:00' UNION ALL
SELECT '14:45:00'
) b
JOIN tbl c ON a.UID = c.UID
AND TIME(b.intrvl) >= TIME(c.Datetime)
GROUP BY a.UID,
b.intrvl
) a
JOIN tbl b ON a.UID = b.UID
AND a.last_date = b.Datetime
GROUP BY b.UID,
b.Name
呈现:
Name | avg_users
------------------
Room 1 | 3.5
Room 2 | 4.5
Room 3 | 2.75
Room 4 | 3.25