sql - 使用重叠事件计算工作时间 (SQL)

Question

我在 StackOverflow 上发现了类似的查询(例如 Finding simultaneous events in a database between times)，但据我所知，没有任何内容完全符合我的要求，所以我认为可以添加为一个新问题。

我有一个记录作业(或“事件”)的表，其中包含作业的开始/结束时间。我需要计算工作时间(你可以忽略非工作日、休息时间等，因为我已经涵盖了)。复杂的是一个人可以同时从事不同的工作，在不同的点重叠(假设是同时工作的同等努力)，工作时间需要反射(reflect)这一点。只需要精确到分钟，而不是秒。

根据其他建议，我将此查询实现为表值函数。它将查看该事件正在运行的每一分钟，以及是否有任何其他事件在同一时间段内为同一个人运行，并据此进行计算。它有效，但效率很低——需要一分钟的时间来执行。我有什么想法可以更有效地做到这一点吗？

运行 SQL 2005。我做了一些显而易见的事情，比如顺便在外键上添加索引。

CREATE FUNCTION [dbo].[WorkActivity_WorkTimeCalculations] (@StartDate smalldatetime, @EndDate smalldatetime)
RETURNS @retActivity TABLE
(
ActivityID bigint PRIMARY KEY NOT NULL,
WorkMins decimal NOT NULL
)

/********************************************************************
Summary:        Calculates the WORKING time on each activity running in a given date/time range
Remarks:        Takes into account staff working simultaneously on jobs
            (evenly distributes working time across simultaneous jobs)
Input Params:   @StartDate - the start of the period to calculate
            @EndDate - the end of the period to calculate
Output Params:  
Returns:        Recordset of activities and associated working time (minutes)
********************************************************************/

AS

BEGIN

-- any work activities still running use the overall end date as the activity's end date for the purpose of calculating
-- simulateneous jobs running

-- POPULATE A TEMP TABLE WITH EVERY MINUTE IN THE DATE RANGE
DECLARE @Minutes TABLE (MinuteDateTime smalldatetime NOT NULL)

;WITH cte AS (
SELECT @StartDate AS myDate
UNION ALL
SELECT DATEADD(minute,1,myDate)
FROM cte
WHERE DATEADD(minute,1,myDate) <= @EndDate
)

INSERT INTO @Minutes (MinuteDateTime)
SELECT myDate FROM cte
OPTION (MAXRECURSION 0)

-- POPULATE A TEMP TABLE WITH WORKLOAD PER EMPLOYEE PER MINUTE
DECLARE @JobsRunningByStaff TABLE (StaffID smallint NOT NULL, MinuteDateTime smalldatetime NOT NULL, JobsRunning decimal NOT NULL)

INSERT INTO @JobsRunningByStaff (StaffID, MinuteDateTime, JobsRunning)
SELECT  wka_StaffID, MinuteDateTime, COUNT(DISTINCT wka_ItemID) JobsRunning
FROM    dbo.WorkActivities
        INNER JOIN @Minutes ON (MinuteDateTime BETWEEN wka_StartTime AND DATEADD(minute,-1,ISNULL(wka_EndTime,@EndDate)))
GROUP BY wka_StaffID, MinuteDateTime

-- FINALLY MAKE THE CALCULATIONS FOR EACH ACTIVITY
INSERT INTO @retActivity
SELECT  wka_ActivityID, SUM(1/JobsRunning)WorkMins
FROM    dbo.WorkActivities
        INNER JOIN @JobsRunningByStaff ON (wka_StaffID = StaffID AND MinuteDateTime BETWEEN wka_StartTime AND DATEADD(minute,-1,ISNULL(wka_EndTime,@EndDate)))
GROUP BY wka_ActivityID

RETURN

END

一些示例数据(抱歉格式不佳!)...

来自 WorkActivities 表的源数据:

ACTIVITY ID | START TIME       | END TIME         | STAFF ID
1           | 03/03/2016 10:30 | 03/03/2016 10:50 | 1
2           | 03/03/2016 10:40 | 03/03/2016 11:00 | 1

SELECT * FROM dbo.WorkActivity_WorkTimeCalculations ('03-Mar-2016 10:30','03-Mar-2016 11:30')函数调用的预期结果:

ACTIVITY ID | WORKMINS
1           | 25
2           | 15

因此，结果考虑到在 10:40 到 10:50 之间有两个工作同时发生，因此计算在这段时间内每个工作 5 分钟的工作时间。

Answer 1

正如张贴者所建议的那样，索引产生了显着的差异 - 使用 wka_StartTime 和 wka_EndTime 创建索引对其进行排序。

(抱歉，看不到如何将其他人的评论标记为答案!)