SQLLite 时间输入和时间从 eventtime 退出 [英] SQLLite time entry and time exit from eventime
问题描述
我有两个表，DATA 和 EVENTS，数据如下:
I have two tables, DATA and EVENTS, with the following data:
EVENTS
EventIndex ObjID LocID EventData EventTime EventType
83707365 3519434 10376 0 2013-05-19 11:32:11 137
83707849 3519434 10374 0 2013-05-19 11:35:18 137
83714233 888799 10376 0 2013-05-19 12:24:45 137
83715200 888799 10184 0 2013-05-19 12:32:18 137
DATA
EventIndex TagName TagValue
83714233 ObjName Peter
83714233 LocName H118
83715200 ObjName Peter
83715200 LocName H116
83707365 ObjName John
83707365 LocName H118
83707849 ObjName John
83707849 LocName H116
我从哪里开始 SQL SQLLite 查询?
Where do I start with a SQL SQLLite query?
我想得到结果:
Name Location Entry Exit Total
Peter H118 12:24:45 12:32:18 00:07:33
John H118 11:32:11 11:35:18 00:03:07
meewoK，可以给出以下案例:
Hi meewoK, can give the following case:
名称位置条目退出总数
彼得 H118 12:24:45 12:32:18 00:07:33
约翰 H118 11:32:11 11:35:18 00:03:07
约翰 H118 11:44:52 11:45:27 00:00:35
约翰 H118 12:31:15 12:32:46 00:01:31
Name Location Entry Exit Total
Peter H118 12:24:45 12:32:18 00:07:33
John H118 11:32:11 11:35:18 00:03:07
John H118 11:44:52 11:45:27 00:00:35
John H118 12:31:15 12:32:46 00:01:31
我正在尝试修改您的查询，但找不到解决方案.谢谢
and I'm trying to modify your query and I can not find the solution. Thanks
此代码适用于 SQL SERVER:
This code works in SQL SERVER:
SELECT E.EventIndex, N.tagvalue AS Name, L.tagvalue AS Location, E.eventtime AS Entry, NEV.[Exit]
发件人
[活动] E
INNER JOIN [DATA] N ON E.EventIndex = N.eventindex AND N.tagname = 'ObjName'
INNER JOIN [DATA] L ON E.EventIndex = L.eventindex AND L.tagname = 'LocName'
外部应用 (
SELECT TOP (1) NE.eventtime AS [退出]
发件人
[活动] NE
INNER JOIN [DATA] NL ON NE.EventIndex = NL.eventindex AND NL.tagname = 'ObjName'
哪里
NE.EventIndex > E.EventIndex
AND NL.tagvalue = N.tagvalue
按
订购NE.EventIndex
SELECT E.EventIndex, N.tagvalue AS Name, L.tagvalue AS Location, E.eventtime AS Entry, NEV.[Exit]
FROM
[EVENTS] E
INNER JOIN [DATA] N ON E.EventIndex = N.eventindex AND N.tagname = 'ObjName'
INNER JOIN [DATA] L ON E.EventIndex = L.eventindex AND L.tagname = 'LocName'
OUTER APPLY (
SELECT TOP (1) NE.eventtime AS [Exit]
FROM
[EVENTS] NE
INNER JOIN [DATA] NL ON NE.EventIndex = NL.eventindex AND NL.tagname = 'ObjName'
WHERE
NE.EventIndex > E.EventIndex
AND NL.tagvalue = N.tagvalue
ORDER BY
NE.EventIndex
) NEV
WHERE L.tagvalue = 'H118'
WHERE L.tagvalue = 'H118'
有人可以帮我把它传递给 SQLite 吗?谢谢
Someone could help me to pass it to SQLite? Thanks
推荐答案
这样的事情可以让你很好地开始:
Something like this could get you started along nicely:
SQL 小提琴:http://www.sqlfiddle.com/#!2/6e6a7/33
SELECT d.TagValue AS Name,
d2.TagValue AS LOCATION,
e.eventTime AS Entry,
( SELECT eventtime
FROM EVENTS e2
WHERE e2.objID = e.objID
AND e2.eventTime > e.eventTime LIMIT 1) AS ExitTime,
(e.eventTime -
( SELECT eventtime
FROM EVENTS e2
WHERE e2.objID = e.objID
AND e2.eventTime > e.eventTime LIMIT 1)) AS TotalTime
FROM events e,
DATA d,
DATA d2
WHERE e.eventIndex = d.eventIndex
AND e.eventIndex = d2.eventIndex
AND d.TagName = "ObjName"
AND d2.TagName = "LocName"
AND mod(
( SELECT count(*)
FROM EVENTS e2
WHERE e2.objID = e.objID
AND e2.eventTime < e.eventTime),2) = 0
ORDER BY EventTime;
结果:
NAME LOCATION ENTRY EXITTIME TOTALTIME
John H118 May, 19 2013 11:32:11+0000 May, 19 2013 11:35:18+0000 -307
Peter H118 May, 19 2013 12:24:45+0000 May, 19 2013 12:32:18+0000 -773
我添加了额外的事件，以证明一个事件的退出被视为另一个事件的进入.
I added extra events, to proof against an exit from one event being considered as an entry of another.
上面的查询可以优化，如果我们说进入和退出不能发生在不同的日子，例如用户进入 11.59PM 并离开 1.00AM...
The above query can be optimized, if we say that an entry and exit cannot take place on different days e.g. USER enters 11.59PM and leaves 1.00AM...
SELECT d.TagValue AS Name,
d2.TagValue AS LOCATION,
e.eventTime AS Entry,
( SELECT eventtime
FROM EVENTS e2
WHERE e2.objID = e.objID
AND e2.eventTime > e.eventTime LIMIT 1) AS ExitTime,
(e.eventTime -
( SELECT eventtime
FROM EVENTS e2
WHERE e2.objID = e.objID
AND e2.eventTime > e.eventTime AND
DATE(e2.eventTime) = DATE(e.eventTime) LIMIT 1)) AS TotalTime
FROM events e,
DATA d,
DATA d2
WHERE e.eventIndex = d.eventIndex
AND e.eventIndex = d2.eventIndex
AND d.TagName = "ObjName"
AND d2.TagName = "LocName"
AND mod(
( SELECT count(*)
FROM EVENTS e2
WHERE e2.objID = e.objID
AND e2.eventTime < e.eventTime AND
DATE(e2.eventTime) = DATE(e.eventTime)),2) = 0
ORDER BY EventTime;
SQL 小提琴:http://www.sqlfiddle.com/#!2/6e6a7/35
这也假设每个 entry 必须有一个 exit，这就是我使用模运算符的原因.Object 的所有 Even 事件都是一个入口，所有 Odd 事件都是一个 exit.
This also assumes that each entry must have an exit, which is why I use the modulo operator.
All Even events for a Object are an entry, all Odd events are an exit.
对于更多的条件，显然必须修改查询.
For more conditions, obviously the query must be modified.
更新:使用 SQL Lite(没有 mod 而是 %)
SQLFiddle:http://www.sqlfiddle.com/#!7/6e6a7/4
SELECT d.TagValue AS Name,
d2.TagValue AS LOCATION,
e.eventTime AS Entry,
( SELECT eventtime
FROM EVENTS e2
WHERE e2.objID = e.objID
AND e2.eventTime > e.eventTime LIMIT 1) AS ExitTime,
(e.eventTime -
( SELECT eventtime
FROM EVENTS e2
WHERE e2.objID = e.objID
AND e2.eventTime > e.eventTime LIMIT 1)) AS TotalTime
FROM events e,
DATA d,
DATA d2
WHERE e.eventIndex = d.eventIndex
AND e.eventIndex = d2.eventIndex
AND d.TagName = "ObjName"
AND d2.TagName = "LocName"
AND (
( SELECT count(*)
FROM EVENTS e2
WHERE e2.objID = e.objID
AND e2.eventTime < e.eventTime)%2) = 0
ORDER BY EventTime;
这篇关于SQLLite 时间输入和时间从 eventtime 退出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
