关系数据库中的行程时间计算？
时间：2020-03-05 18:58:50 来源:igfitidea点击:
我想到了这个问题，因为我刚刚发现了这个网站，所以决定将其发布在这里。
假设我有一个带有时间戳和表的表，用于给定的"对象"(一般含义，而不是OOP对象)；有没有一种最佳方法，可以使用一条SQL语句(不计算内部SELECT和UNION)来计算一个状态与下一次出现另一个(或者相同)状态(我称为"行程")之间的时间？
例如：对于以下情况，"初始"和"完成"之间的旅行时间为6天，但是"初始"和"审阅"之间的旅行时间为2天。
2008-08-01 13:30:00 - Initial 2008-08-02 13:30:00 - Work 2008-08-03 13:30:00 - Review 2008-08-04 13:30:00 - Work 2008-08-05 13:30:00 - Review 2008-08-06 13:30:00 - Accepted 2008-08-07 13:30:00 - Done
无需通用，只需说说解决方案针对的SGBD(如果不是通用的话)。
解决方案
回答
我认为我们无法通过一条SQL语句来获得该答案，因为我们正试图从许多记录中获得一种结果。在SQL中实现此目标的唯一方法是获取两个不同记录的timestamp字段并计算差异(datediff)。因此，需要UNIONS或者内部联接。
回答
我不确定我是否完全理解这个问题，但是我们可以执行类似以下的操作：一次读取表，然后使用派生表进行计算。 SQL Server代码：
CREATE TABLE #testing
(
eventdatetime datetime NOT NULL,
state varchar(10) NOT NULL
)
INSERT INTO #testing (
eventdatetime,
state
)
SELECT '20080801 13:30:00', 'Initial' UNION ALL
SELECT '20080802 13:30:00', 'Work' UNION ALL
SELECT '20080803 13:30:00', 'Review' UNION ALL
SELECT '20080804 13:30:00', 'Work' UNION ALL
SELECT '20080805 13:30:00', 'Review' UNION ALL
SELECT '20080806 13:30:00', 'Accepted' UNION ALL
SELECT '20080807 13:30:00', 'Done'
SELECT DATEDIFF(dd, Initial, Review)
FROM (
SELECT MIN(CASE WHEN state='Initial' THEN eventdatetime END) AS Initial,
MIN(CASE WHEN state='Review' THEN eventdatetime END) AS Review
FROM #testing
) AS A
DROP TABLE #testing
回答
create table A (
At datetime not null,
State varchar(20) not null
)
go
insert into A(At,State)
select '2008-08-01T13:30:00','Initial' union all
select '2008-08-02T13:30:00','Work' union all
select '2008-08-03T13:30:00','Review' union all
select '2008-08-04T13:30:00','Work' union all
select '2008-08-05T13:30:00','Review' union all
select '2008-08-06T13:30:00','Accepted' union all
select '2008-08-07T13:30:00','Done'
go
--Find trip time from Initial to Done
select DATEDIFF(day,t1.At,t2.At)
from
A t1
inner join
A t2
on
t1.State = 'Initial' and
t2.State = 'Review' and
t1.At < t2.At
left join
A t3
on
t3.State = 'Initial' and
t3.At > t1.At and
t4.At < t2.At
left join
A t4
on
t4.State = 'Review' and
t4.At < t2.At and
t4.At > t1.At
where
t3.At is null and
t4.At is null
没有说是否允许加入。加入t3和t4(及其比较)后，我们可以说是要最早还是最晚出现开始和结束状态(在这种情况下，我要的是最新的"初始"和最早的"查看")
在实际代码中，我的开始和结束状态将是参数
编辑：糟糕，需要包括" t3.At <t2.At"和" t4.At> t1.At"，以解决一些奇怪的状态序列(例如，如果我们删除了第二个" Review"，然后从" Work"中查询"到"审阅"，原始查询将失败)
回答
如果我们有序列号和时间戳，可能会更容易：在大多数RDBMS中，我们可以创建一个自动增量列，而不更改任何INSERT语句。然后，将表及其自身的副本加入表中以获取增量
select after.moment - before.moment, before.state, after.state from object_states before, object_states after where after.sequence + 1 = before.sequence
(其中SQL语法的详细信息将根据所使用的数据库系统而有所不同)。
回答
-- Oracle SQl
CREATE TABLE ObjectState
(
startdate date NOT NULL,
state varchar2(10) NOT NULL
);
insert into ObjectState
select to_date('01-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Initial' union all
select to_date('02-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Work' union all
select to_date('03-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Review' union all
select to_date('04-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Work' union all
select to_date('05-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Review' union all
select to_date('06-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Accepted' union all
select to_date('07-Aug-2008 13:30:00','dd-Mon-rrrr hh24:mi:ss'),'Done';
-- Days in between two states
select o2.startdate - o1.startdate as days
from ObjectState o1, ObjectState o2
where o1.state = 'Initial'
and o2.state = 'Review';
回答
我认为步骤(每次旅行的记录都可以看作一个步骤)可以作为同一活动的一部分组合在一起。然后可以将数据分组，例如：
SELECT Min(Tbl_Step.dateTimeStep) as tripBegin, _
Max(Tbl_Step.dateTimeStep) as tripEnd _
FROM
Tbl_Step
WHERE
id_Activity = 'AAAAAAA'
使用此原理，我们可以计算其他聚合，例如活动中的步骤数等等。但是我们不会找到一种SQL方法来计算诸如两步之间的间隙之类的值，因为这样的数据既不属于第一步也不属于第二步。一些报告工具使用所谓的"运行总和"来计算此类中间数据。根据目标，这可能是解决方案。
回答
这是使用解析函数的Oracle方法论。
with data as (
SELECT 1 trip_id, to_date('20080801 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Initial' step from dual UNION ALL
SELECT 1 trip_id, to_date('20080802 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Work' step from dual UNION ALL
SELECT 1 trip_id, to_date('20080803 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Review' step from dual UNION ALL
SELECT 1 trip_id, to_date('20080804 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Work' step from dual UNION ALL
SELECT 1 trip_id, to_date('20080805 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Review' step from dual UNION ALL
SELECT 1 trip_id, to_date('20080806 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Accepted' step from dual UNION ALL
SELECT 1 trip_id, to_date('20080807 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Done' step from dual )
select trip_id,
step,
dt - lag(dt) over (partition by trip_id order by dt) trip_time
from data
/
1 Initial
1 Work 1
1 Review 1
1 Work 1
1 Review 1
1 Accepted 1
1 Done 1
这些通常在传统上我们可能使用自联接的情况下使用。
回答
PostgreSQL语法：
DROP TABLE ObjectState;
CREATE TABLE ObjectState (
object_id integer not null,--foreign key
event_time timestamp NOT NULL,
state varchar(10) NOT NULL,
--Other fields
CONSTRAINT pk_ObjectState PRIMARY KEY (object_id,event_time)
);
对于给定状态，找到给定类型的第一跟随状态
select parent.object_id,parent.event_time,parent.state,min(child.event_time) as ch_event_time,min(child.event_time)-parent.event_time as step_time
from
ObjectState parent
join ObjectState child on (parent.object_id=child.object_id and parent.event_time<child.event_time)
where
--Starting state
parent.object_id=1 and parent.event_time=to_timestamp('01-Aug-2008 13:30:00','dd-Mon-yyyy hh24:mi:ss')
--needed state
and child.state='Review'
group by parent.object_id,parent.event_time,parent.state;
这个查询不是最短的可能，但应该易于理解，并可以用作其他查询的一部分：
列出给定对象的事件及其持续时间
select parent.object_id,parent.event_time,parent.state,min(child.event_time) as ch_event_time,
CASE WHEN parent.state<>'Done' and min(child.event_time) is null THEN (select localtimestamp)-parent.event_time ELSE min(child.event_time)-parent.event_time END as step_time
from
ObjectState parent
left outer join ObjectState child on (parent.object_id=child.object_id and parent.event_time<child.event_time)
where parent.object_id=4
group by parent.object_id,parent.event_time,parent.state
order by parent.object_id,parent.event_time,parent.state;
列出未"完成"的对象的当前状态
select states.object_id,states.event_time,states.state,(select localtimestamp)-states.event_time as step_time
from
(select parent.object_id,parent.event_time,parent.state,min(child.event_time) as ch_event_time,min(child.event_time)-parent.event_time as step_time
from
ObjectState parent
left outer join ObjectState child on (parent.object_id=child.object_id and parent.event_time<child.event_time)
group by parent.object_id,parent.event_time,parent.state) states
where
states.object_id not in (select object_id from ObjectState where state='Done')
and ch_event_time is null;
测试数据
insert into ObjectState (object_id,event_time,state)
select 1,to_timestamp('01-Aug-2008 13:30:00','dd-Mon-yyyy hh24:mi:ss'),'Initial' union all
select 1,to_timestamp('02-Aug-2008 13:40:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all
select 1,to_timestamp('03-Aug-2008 13:50:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all
select 1,to_timestamp('04-Aug-2008 14:30:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all
select 1,to_timestamp('04-Aug-2008 16:20:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all
select 1,to_timestamp('06-Aug-2008 18:00:00','dd-Mon-yyyy hh24:mi:ss'),'Accepted' union all
select 1,to_timestamp('07-Aug-2008 21:30:00','dd-Mon-yyyy hh24:mi:ss'),'Done';
insert into ObjectState (object_id,event_time,state)
select 2,to_timestamp('01-Aug-2008 13:30:00','dd-Mon-yyyy hh24:mi:ss'),'Initial' union all
select 2,to_timestamp('02-Aug-2008 13:40:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all
select 2,to_timestamp('07-Aug-2008 13:50:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all
select 2,to_timestamp('14-Aug-2008 14:30:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all
select 2,to_timestamp('15-Aug-2008 16:20:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all
select 2,to_timestamp('16-Aug-2008 18:02:00','dd-Mon-yyyy hh24:mi:ss'),'Accepted' union all
select 2,to_timestamp('17-Aug-2008 22:10:00','dd-Mon-yyyy hh24:mi:ss'),'Done';
insert into ObjectState (object_id,event_time,state)
select 3,to_timestamp('12-Sep-2008 13:30:00','dd-Mon-yyyy hh24:mi:ss'),'Initial' union all
select 3,to_timestamp('13-Sep-2008 13:40:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all
select 3,to_timestamp('14-Sep-2008 13:50:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all
select 3,to_timestamp('15-Sep-2008 14:30:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all
select 3,to_timestamp('16-Sep-2008 16:20:00','dd-Mon-yyyy hh24:mi:ss'),'Review';
insert into ObjectState (object_id,event_time,state)
select 4,to_timestamp('21-Aug-2008 03:10:00','dd-Mon-yyyy hh24:mi:ss'),'Initial' union all
select 4,to_timestamp('22-Aug-2008 03:40:00','dd-Mon-yyyy hh24:mi:ss'),'Work' union all
select 4,to_timestamp('23-Aug-2008 03:20:00','dd-Mon-yyyy hh24:mi:ss'),'Review' union all
select 4,to_timestamp('24-Aug-2008 04:30:00','dd-Mon-yyyy hh24:mi:ss'),'Work';
回答
我试图在MySQL中做到这一点。由于MySQL中没有等级函数，因此我们将需要使用一个变量，因此它将如下所示：
set @trip1 = 0; set @trip2 = 0; SELECT trip1.`date` as startdate, datediff(trip2.`date`, trip1.`date`) length_of_trip FROM (SELECT @trip1 := @trip1 + 1 as rank1, `date` from trip where state='Initial') as trip1 INNER JOIN (SELECT @trip2 := @trip2 + 1 as rank2, `date` from trip where state='Done') as trip2 ON rank1 = rank2;
我假设我们要计算"初始"和"完成"状态之间的时间。
+---------------------+----------------+ | startdate | length_of_trip | +---------------------+----------------+ | 2008-08-01 13:30:00 | 6 | +---------------------+----------------+
回答
好的，这有点令人讨厌，但是我在生孩子之前就构建了一个Web应用程序来跟踪妻子的宫缩，这样我就可以在下班时间到医院就可以下班。无论如何，我很容易就将这基本的东西构建成了两个视图。
create table contractions time_date timestamp primary key;
create view contraction_time as
SELECT a.time_date, max(b.prev_time) AS prev_time
FROM contractions a, ( SELECT contractions.time_date AS prev_time
FROM contractions) b
WHERE b.prev_time < a.time_date
GROUP BY a.time_date;
create view time_between as
SELECT contraction_time.time_date, contraction_time.prev_time, contraction_time.time_date - contraction_time.prev_time
FROM contraction_time;
这显然也可以作为子选择来完成，但是我也将中间视图用于其他事情，因此效果很好。
