获取持续时间作为时间戳记差异以进行重复的位置更改 [英] Get duration as timestamp difference for repeated location changes
问题描述
在SQL查询上稍作努力，希望能帮助您确定每个人在不断变化的位置所处的持续时间.
Struggling a little with a SQL query, and would appreciate help to determine the respective duration at the repeatedly changing location for each person.
一张表，三列:名称，位置和时间戳(每两秒钟新建一行).
One table, three columns: name, location and timestamp (new row every two seconds).
name location timestamp
fred home 2015-07-08 08:02:15
john home 2015-07-08 08:02:16
fred home 2015-07-08 08:02:17
john home 2015-07-08 08:02:18
fred work 2015-07-08 08:07:30
fred work 2015-07-08 08:07:32
fred work 2015-07-08 08:07:34
john work 2015-07-08 08:09:15
john work 2015-07-08 08:09:17
john work 2015-07-08 08:09:19
fred home 2015-07-08 17:17:35
fred home 2015-07-08 17:17:37
john home 2015-07-08 19:10:15
john home 2015-07-08 19:10:17
john home 2015-07-08 19:10:19
john home 2015-07-08 19:10:21
需要确定弗雷德和约翰在家里呆了多久，然后在工作中，然后又在家里呆了多久.
Need to determine how long both fred and john were at home and then at work and then at home again.
不幸的是，
TIMEDIFF(min(timestamp), max(timestamp))
确定持续时间，这是一个聚合命令.因此，您必须使用group by name或group by location-之后，它会将时间汇总为一个持续时间值(弗雷德在2015-07-08 08:02:15和2015-07-08 17:17之间在家工作: 37)，这是不正确的.他实际上是在2015-07-08 08:02:15和2015-07-08 08:02:17之间在家，然后又在2015-07-08 17:17之后(在上班一段时间之后) :35和2015-07-08 17:17:37.
Unfortunately although
TIMEDIFF(min(timestamp), max(timestamp))
determines the duration, this is an aggregate command. So you have to use group by name or group by location - after which it aggregates the time into a single duration value (fred was at home between 2015-07-08 08:02:15 and 2015-07-08 17:17:37), which is not correct. He was actually at home between 2015-07-08 08:02:15 and 2015-07-08 08:02:17, and then again later (after being at work for some time) between 2015-07-08 17:17:35 and 2015-07-08 17:17:37.
因此，我试图让查询显示持续时间(以秒为单位)和"最后一次看到":
So I am trying to get the query to show duration (in seconds) and 'last seen':
name location duration last_seen
fred home 2 2015-07-08 08:02:17
fred work 4 2015-07-08 08:07:34
fred home 2 2015-07-08 17:17:37
john home 2 2015-07-08 08:02:18
john work 4 2015-07-08 08:09:19
john home 6 2015-07-08 19:10:21
假设我需要一个查询，该查询将根据人的姓名确定持续时间，直到位置名称更改为止(然后重复下一个位置名称更改).但是，上面使用的时间差总是会汇总数据.
Assume I need a query, that determines the duration by person's name until location name changes (then repeat for the next location name change). But time difference as used above always aggregates the data.
推荐答案
创建一个名为home_to_work的视图:
Create a view called home_to_work:
CREATE VIEW `home_to_work`AS
SELECT b.name, NULL AS `home`, b.timestamp AS `work`
FROM mytable b
WHERE b.location = 'work'
AND NOT EXISTS (
SELECT 1 FROM mytable m
WHERE b.name = m.name AND m.timestamp < b.timestamp
)
UNION ALL
SELECT a.name, a.timestamp AS `home`, b.timestamp AS `work`
FROM mytable a
JOIN mytable b
ON a.name = b.name AND a.location = 'home' AND b.location = 'work'
AND a.timestamp < b.timestamp
AND NOT EXISTS (
SELECT 1 FROM mytable m
WHERE a.name = m.name AND m.timestamp > a.timestamp AND m.timestamp < b.timestamp
)
UNION ALL
SELECT a.name, a.timestamp AS `home`, NULL AS `work`
FROM mytable a
WHERE a.location = 'home'
AND NOT EXISTS (
SELECT 1 FROM mytable m
WHERE a.name = m.name AND m.timestamp > a.timestamp
);
和一个名为work_to_home的视图:
and a view called work_to_home:
CREATE VIEW `work_to_home`AS
SELECT b.name, NULL AS `work`, b.timestamp AS `home`
FROM mytable b
WHERE b.location = 'home'
AND NOT EXISTS (
SELECT 1 FROM mytable m
WHERE b.name = m.name AND m.timestamp < b.timestamp
)
UNION ALL
SELECT a.name, a.timestamp AS `work`, b.timestamp AS `home`
FROM mytable a
JOIN mytable b
ON a.name = b.name AND a.location = 'work' AND b.location = 'home'
AND a.timestamp < b.timestamp
AND NOT EXISTS (
SELECT 1 FROM mytable m
WHERE a.name = m.name AND m.timestamp > a.timestamp AND m.timestamp < b.timestamp
)
UNION ALL
SELECT a.name, a.timestamp AS `work`, NULL AS `home`
FROM mytable a
WHERE a.location = 'work'
AND NOT EXISTS (
SELECT 1 FROM mytable m
WHERE a.name = m.name AND m.timestamp > a.timestamp
);
然后使用此查询:
SELECT `name`, location, `from`, `until`, duration
FROM (
SELECT h2w.`name`, 'work' AS `location`, h2w.work AS `from`, w2h.work AS `until`, TIMESTAMPDIFF(second, h2w.`work`, w2h.`work`) AS `duration`
FROM home_to_work h2w
JOIN work_to_home w2h
ON h2w.name = w2h.name AND h2w.work < w2h.work AND NOT EXISTS (
SELECT 1 FROM mytable m
WHERE h2w.name = m.name AND m.location = 'home' AND m.timestamp > h2w.work AND m.timestamp < w2h.work
)
UNION ALL
SELECT w2h.`name`, 'home' AS `location`, w2h.home AS `from`, h2w.home AS `until`, TIMESTAMPDIFF(second, w2h.`home`, h2w.`home`) AS `duration`
FROM work_to_home w2h
JOIN home_to_work h2w
ON w2h.name = h2w.name AND w2h.home < h2w.home AND NOT EXISTS (
SELECT 1 FROM mytable m
WHERE w2h.name = m.name AND m.location = 'work' AND m.timestamp > w2h.home AND m.timestamp < h2w.home
)
) locations
ORDER BY `name`, `from`
说明:我得出从家庭到工作的过渡，以及从工作到家庭的过渡(包括最初的null到home/work以及最终的home/work到null)，然后加入这些过渡，同时检查它们是否连续.
Explanation: I derive the transitions from home to work, and work to home (including initial null to home/work and final home/work to null) and then join these transitions while checking that they're consecutive.
这是 SQL小提琴.
编辑以回复您的评论:
获取给定人员和时间的最新已知位置很容易:
Getting the last known location for a given person and time is easy:
SELECT location
FROM mytable
WHERE `name` = 'John' AND timestamp < '2015-07-08 11:07:00'
ORDER BY timestamp DESC
LIMIT 1
这篇关于获取持续时间作为时间戳记差异以进行重复的位置更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
