SQL持续时间计算
问题描述：
我有一个在给定时间的历史巴士位置表，每秒记录一次。模式如下所示：SQL持续时间计算
BusID int not null,
BreadcrumbID int not null identity (1, 1),
BusStopID int null,
Timestamp datetime not null
我想根据历史性旅程生成公交车站时间表。如果其车辆编号BusStopID与停车位相对应，则车辆处于“停车位”，如果BusStopID为空，则不处于“停车位”。
我需要生成公交车在每站的平均时间。所以基本上，我需要做到以下几点：
- 识别倍总线处于停止状态 - 一个简单的
where条款的伎俩 - 确定的平均时间的公交车处于停止状态。出于我的目的，我将一个离散的“停止时间”定义为正负10分钟的窗口;如果一辆公共汽车从10:04 - 10:08停靠一天，另一天在10:06 - 10:08，第三天在10:14 - 10:18停靠，那么这些停靠是相同的，但如果停止在10:45 - 10:48，这将是一个不同的停止事件。
- 筛选出来的“噪音” - 即在一个头绪来完成第二和第三颗子弹停止我完全倍，只发生过几次，但从来没有再
。请帮忙！
答
在很多场合我做了类似的事情。实质上，基于复杂排序内的分离进行分组。我使用的方法的基础知识如下：
- 构建一个所有感兴趣的时间范围表。
- 找到每组感兴趣的时间范围的开始时间。
- 找出每组感兴趣的时间范围的结束时间。
- 将开始和结束时间加入到时间范围和组列表中。
或者，更详细：（每一个步骤可能是一个大的CTE的一部分，但我已经打破它分解成易于阅读的临时表...）
第1步：找到感兴趣的所有时间范围的列表（我使用了与@Brad链接的方法类似的方法）。 注意：正如@Manfred Sorg所指出的那样，这假定公交车的数据中没有“缺失的秒数”。如果时间戳中有中断，则此代码将将单个范围解释为两个（或更多）不同的范围。
;with stopSeconds as (
select BusID, BusStopID, TimeStamp,
[date] = cast(datediff(dd,0,TimeStamp) as datetime),
[grp] = dateadd(ss, -row_number() over(partition by BusID order by TimeStamp), TimeStamp)
from #test
where BusStopID is not null
)
select BusID, BusStopID, date,
[sTime] = dateadd(ss,datediff(ss,date,min(TimeStamp)), 0),
[eTime] = dateadd(ss,datediff(ss,date,max(TimeStamp)), 0),
[secondsOfStop] = datediff(ss, min(TimeStamp), max(Timestamp)),
[sOrd] = row_number() over(partition by BusID, BusStopID order by datediff(ss,date,min(TimeStamp))),
[eOrd] = row_number() over(partition by BusID, BusStopID order by datediff(ss,date,max(TimeStamp)))
into #ranges
from stopSeconds
group by BusID, BusStopID, date, grp
第2步：找到的最早时间为每个停止
select this.BusID, this.BusStopID, this.sTime minSTime,
[stopOrder] = row_number() over(partition by this.BusID, this.BusStopID order by this.sTime)
into #starts
from #ranges this
left join #ranges prev on this.BusID = prev.BusID
and this.BusStopID = prev.BusStopID
and this.sOrd = prev.sOrd+1
and this.sTime between dateadd(mi,-10,prev.sTime) and dateadd(mi,10,prev.sTime)
where prev.BusID is null
第3步：查找每个最晚时间停止
select this.BusID, this.BusStopID, this.eTime maxETime,
[stopOrder] = row_number() over(partition by this.BusID, this.BusStopID order by this.eTime)
into #ends
from #ranges this
left join #ranges next on this.BusID = next.BusID
and this.BusStopID = next.BusStopID
and this.eOrd = next.eOrd-1
and this.eTime between dateadd(mi,-10,next.eTime) and dateadd(mi,10,next.eTime)
where next.BusID is null
第4步：一起加入一切
select r.BusID, r.BusStopID,
[avgLengthOfStop] = avg(datediff(ss,r.sTime,r.eTime)),
[earliestStop] = min(r.sTime),
[latestDepart] = max(r.eTime)
from #starts s
join #ends e on s.BusID=e.BusID
and s.BusStopID=e.BusStopID
and s.stopOrder=e.stopOrder
join #ranges r on r.BusID=s.BusID
and r.BusStopID=s.BusStopID
and r.sTime between s.minSTime and e.maxETime
and r.eTime between s.minSTime and e.maxETime
group by r.BusID, r.BusStopID, s.stopOrder
having count(distinct r.date) > 1 --filters out the "noise"
最后，是完整的，收拾：
drop table #ends
drop table #starts
drop table #ranges
答
新鲜的答案...
尝试了这一点：
DECLARE @stopWindowMinutes INT
SET @stopWindowMinutes = 10
--
;
WITH test_data
AS (SELECT 1 [BusStopId]
,'2010-01-01 10:00:04' [Timestamp]
UNION SELECT 1,'2010-01-01 10:00:05'
UNION SELECT 1,'2010-01-01 10:00:06'
UNION SELECT 1,'2010-01-01 10:00:07'
UNION SELECT 1,'2010-01-01 10:00:08'
UNION SELECT 1,'2010-01-02 10:00:06'
UNION SELECT 1,'2010-01-02 10:00:07'
UNION SELECT 1,'2010-01-02 10:00:08'
UNION SELECT 2,'2010-01-01 10:00:06'
UNION SELECT 2,'2010-01-01 10:00:07'
UNION SELECT 2,'2010-01-01 10:00:08'
UNION SELECT 2,'2010-01-01 10:00:09'
UNION SELECT 2,'2010-01-01 10:00:10'
UNION SELECT 2,'2010-01-01 10:00:09'
UNION SELECT 2,'2010-01-01 10:00:10'
UNION SELECT 2,'2010-01-01 10:00:11'
UNION SELECT 1,'2010-01-02 10:33:43'
UNION SELECT 1,'2010-01-02 10:33:44'
UNION SELECT 1,'2010-01-02 10:33:45'
UNION SELECT 1,'2010-01-02 10:33:46'
)
SELECT DISTINCT
[BusStopId]
,[AvgStop]
FROM (SELECT [a].[BusStopId]
,(SELECT MIN([b].[Timestamp])
FROM [test_data] b
WHERE [a].[BusStopId] = [b].[BusStopId]
AND CONVERT(VARCHAR(10), [a].[Timestamp], 120) = CONVERT(VARCHAR(10), [b].[Timestamp], 120)
AND [b].[Timestamp] BETWEEN DATEADD(SECOND, [email protected] * 60,
[a].[Timestamp])
AND DATEADD(SECOND, @stopWindowMinutes * 60, [a].[Timestamp]) -- w/i X minutes
) [MinStop]
,(SELECT MAX([b].[Timestamp])
FROM [test_data] b
WHERE [a].[BusStopId] = [b].[BusStopId]
AND CONVERT(VARCHAR(10), [a].[Timestamp], 120) = CONVERT(VARCHAR(10), [b].[Timestamp], 120)
AND [b].[Timestamp] BETWEEN DATEADD(SECOND, [email protected] * 60,
[a].[Timestamp])
AND DATEADD(SECOND, @stopWindowMinutes * 60, [a].[Timestamp]) -- w/i X minutes
) [MaxStop]
,(SELECT DATEADD(second,
AVG(DATEDIFF(second, CONVERT(VARCHAR(10), [b].[Timestamp], 120),
[b].[Timestamp])),
CONVERT(VARCHAR(10), MIN([b].[Timestamp]), 120))
FROM [test_data] b
WHERE [a].[BusStopId] = [b].[BusStopId]
AND CONVERT(VARCHAR(10), [a].[Timestamp], 120) = CONVERT(VARCHAR(10), [b].[Timestamp], 120)
AND [b].[Timestamp] BETWEEN DATEADD(SECOND, [email protected] * 60,
[a].[Timestamp])
AND DATEADD(SECOND, @stopWindowMinutes * 60, [a].[Timestamp]) -- w/i X minutes
) [AvgStop]
FROM [test_data] a
WHERE CONVERT(VARCHAR(10), [Timestamp], 120) = CONVERT(VARCHAR(10), [Timestamp], 120)
GROUP BY [a].[BusStopId]
,[a].[Timestamp]
) subset1
答
由于通常情况下，这些类型的问题都比较容易解决，并通过破坏它们切成一口大小的块维护：
-- Split into Date and minutes-since-midnight
WITH observed(dates,arrival,busstop,bus) AS (
SELECT
CONVERT(CHAR(8), TimeStamp, 112),
DATEPART(HOUR,TimeStamp) * 60 + DATEPART(MINUTE,TimeStamp),
busstopid,
busid
FROM
History
),
-- Identify times at stop subsequent to arrival at that stop
atstop(dates,stoptime,busstop,bus) AS (
SELECT
a.dates,
a.arrival,
a.busstop,
a.bus
FROM
observed a
WHERE
EXISTS (
SELECT
*
FROM
observed b
WHERE
a.dates = b.dates AND
a.busstop = b.busstop AND
a.bus = b.bus AND
a.arrival - b.arrival BETWEEN 1 AND 10
)
),
-- Isolate actual arrivals at stops, excluding waiting at stops
dailyhalts(dates,arrival,busstop,bus) AS (
SELECT
a.dates,a.arrival,a.busstop,a.bus
FROM
observed a
WHERE
arrival NOT IN (
SELECT
stoptime
FROM
atstop b
WHERE
a.dates = b.dates AND
a.busstop = b.busstop AND
a.bus = b.bus
)
),
-- Merge arrivals across all dates
timetable(busstop,bus,arrival) AS (
SELECT
a.busstop, a.bus, a.arrival
FROM
dailyhalts a
WHERE
NOT EXISTS (
SELECT
*
FROM
dailyhalts h
WHERE
a.busstop = h.busstop AND
a.bus = h.bus AND
a.arrival - h.arrival BETWEEN 1 AND 10
)
GROUP BY
a.busstop, a.bus, a.arrival
)
-- Print timetable for a given day
SELECT
a.busstop, a.bus, a.arrival, DATEADD(minute,AVG(b.arrival),'2010/01/01')
FROM
timetable a INNER JOIN
observed b ON
a.busstop = b.busstop AND
a.bus = b.bus AND
b.arrival BETWEEN a.arrival AND a.arrival + 10
GROUP BY
a.busstop, a.bus, a.arrival
输入：
ID BusID BusStopID TimeStamp
1 1 1 2010-01-01 10:00:00.000
2 1 1 2010-01-01 10:01:00.000
3 1 1 2010-01-01 10:02:00.000
4 1 2 2010-01-01 11:00:00.000
5 1 3 2010-01-01 12:00:00.000
6 1 3 2010-01-01 12:01:00.000
7 1 3 2010-01-01 12:02:00.000
8 1 3 2010-01-01 12:03:00.000
9 1 1 2010-01-02 11:00:00.000
10 1 1 2010-01-02 11:03:00.000
11 1 1 2010-01-02 11:07:00.000
12 1 2 2010-01-02 12:00:00.000
13 1 3 2010-01-02 13:00:00.000
14 1 3 2010-01-02 13:01:00.000
15 1 1 2010-01-03 10:03:00.000
16 1 1 2010-01-03 10:05:00.000
输出：
busstop bus arrival (No column name)
1 1 600 2010-01-01 10:02:00.000
1 1 660 2010-01-01 11:03:00.000
2 1 660 2010-01-01 11:00:00.000
2 1 720 2010-01-01 12:00:00.000
3 1 720 2010-01-01 12:01:00.000
3 1 780 2010-01-01 13:00:00.000
是'巴士ID`真的应该增加所有时间戳？另外，由于`Timestamp`实际上是SQL中的一种数据类型，我建议不要将它用作列名，但我知道您选择了一个有意义的名称（与数据类型本身的名称不同）。 – Brad 2010-12-07 13:26:20
哎呀，我在StackOverflow中输入模式时犯了一个错误。你是对的，面包屑ID增加，BusID是FK。 – 2010-12-07 13:27:20
有趣的问题，比看起来更复杂 – smirkingman 2010-12-08 13:05:20