是否有一种更快的方法来增加时间范围，考虑到重叠?

Question

我有一个子查询，需要几分钟才能执行。如果我只取出正在加起来的初始行，它只需要半秒钟，有2400行，所以我不明白为什么主查询要花这么长时间。

我要做的是，对于一个日期范围内的所有事务，对于分配给这些事务的所有工作人员，将每个工作人员的计划工作时间加起来。

查询返回了正确的数据，只是花了很长时间。

查询

SELECT scheduled_hours = COALESCE(sum(hours), 0), worker_sysid
FROM (
SELECT DISTINCT
B.DateR1,
B.DateR2,
hours = ABS((B.DAteR1 - B.DateR2) / 3600),
B.worker_sysid
FROM Trans A
OUTER APPLY (
SELECT 
DateR1 = MIN(TRANS_START),
DateR2 = MAX(TRANS_END),
worker_sysid
FROM Trans
JOIN trans_workers ON trans_workers.trans_sysid = Trans.SYSID
LEFT JOIN Service ON Service.SYSID = Trans.SERVICESYSID
WHERE
TRANS_START <= A.TRANS_END AND TRANS_END >= A.TRANS_START
AND TRANS_START IS NOT NULL AND TRANS_END IS NOT NULL
AND TRANS_START != '' AND TRANS_END != ''
AND Trans.CHARGEBY IN ('Hours', 'Hour')
AND (
COALESCE(Service.overnight, 0) != 1
OR
COALESCE(Service.active_overnight, 0) = 1
)
AND TRANSDATE BETWEEN 80387 AND 80400 ### These are Clarion dates
AND trans_workers.deleted_at IS NULL
GROUP BY worker_sysid
) B
) A
WHERE worker_sysid IS NOT NULL
GROUP BY worker_sysid

表反式>: SYSID (int, pk)， TRANSDATE (int, clarion格式的日期)，TRANS_START/TRANS_END (UNIX时间戳)，SERVICESYSID (int, fk)， CHARGEBY (varchar)

trans_workers: trans_sysid, worker_sysid, deleted_at

: SYSID (int, pk)

将trans_workers连接移出OUTER APPLY将执行时间从1分钟减少到16秒，所以这是一个改进。

SELECT scheduled_hours = COALESCE(sum(hours), 0), worker_sysid
FROM (
SELECT DISTINCT
B.DateR1,
B.DateR2,
hours = ABS((B.DateR1 - B.DateR2) / 3600),
worker_sysid
FROM Trans A
JOIN trans_workers ON A.SYSID = trans_workers.trans_sysid
OUTER APPLY (
SELECT 
DateR1 = MIN(TRANS_START),
DateR2 = MAX(TRANS_END),
Trans.SYSID
FROM Trans
LEFT JOIN Service ON Service.SYSID = Trans.SERVICESYSID
WHERE
TRANS_START <= A.TRANS_END AND TRANS_END >= A.TRANS_START
AND TRANS_START IS NOT NULL AND TRANS_END IS NOT NULL
AND TRANS_START != '' AND TRANS_END != ''
AND Trans.CHARGEBY IN ('Hours', 'Hour')
AND COALESCE(Service.overnight, 0) != 1
AND TRANSDATE BETWEEN 80387 AND 80400
GROUP BY Trans.SYSID
) B
) A
WHERE worker_sysid IS NOT NULL
GROUP BY worker_sysid
ORDER BY worker_sysid

Answer 1

多亏了https://www.sqlservercentral.com/forums/topic/consolidate-overlapping-date-periods我有一个查询，在一秒钟内执行，并返回什么似乎是正确的小时。唯一的问题是我不明白发生了什么。

DECLARE @start INTEGER, @end INTEGER;
SET @start = 80401; --06/02/2021
SET @end = 80414; --19/02/2021
WITH cteTemp
AS (
SELECT
worker_sysid,
BeginDate =
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY worker_sysid ORDER BY theDate) - openCnt = 0 THEN theDate
END,
EndDate =
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY worker_sysid ORDER BY theDate) - closeCnt = 0 THEN theDate
END
FROM (
SELECT
worker_sysid,
theDate = DATEADD(day, 0, DATEDIFF(day, 0, (dateadd(day,[TRANSDATE]-(4),'1801-01-01')))) + DATEADD(day, 0 - DATEDIFF(day, 0, DATEADD(second, TRANS_START - DATEDIFF(S, GETDATE(), GETUTCDATE()), '1970-01-01')), DATEADD(second, TRANS_START - DATEDIFF(S, GETDATE(), GETUTCDATE()), '1970-01-01')),
closeCnt = NULL,
openCnt = (ROW_NUMBER() OVER (PARTITION BY worker_sysid ORDER BY DATEADD(day, 0, DATEDIFF(day, 0, (dateadd(day,[TRANSDATE]-(4),'1801-01-01')))) + DATEADD(day, 0 - DATEDIFF(day, 0, DATEADD(second, TRANS_START - DATEDIFF(S, GETDATE(), GETUTCDATE()), '1970-01-01')), DATEADD(second, TRANS_START - DATEDIFF(S, GETDATE(), GETUTCDATE()), '1970-01-01'))) * 2) - 1
FROM 
Trans
INNER JOIN trans_workers ON trans_workers.trans_sysid = Trans.SYSID
JOIN Service ON Service.SYSID = Trans.SERVICESYSID
WHERE 
worker_sysid IS NOT NULL
AND Trans.deleted_at IS NULL
AND trans_workers.deleted_at IS NULL
AND Trans.CHARGEBY IN ('Hour', 'Hours')
AND (transCancelled IS NULL OR transCancelled != 1)
AND (
COALESCE(Service.overnight, 0) = 0
)
AND TRANSDATE BETWEEN @start AND @end
UNION ALL
SELECT
worker_sysid,
theDate = DATEADD(day, 0, DATEDIFF(day, 0, (dateadd(day,[TRANSDATE]-(4),'1801-01-01')))) + DATEADD(day, 0 - DATEDIFF(day, 0, DATEADD(second, TRANS_END - DATEDIFF(S, GETDATE(), GETUTCDATE()), '1970-01-01')), DATEADD(second, TRANS_END - DATEDIFF(S, GETDATE(), GETUTCDATE()), '1970-01-01')),
closeCnt = ROW_NUMBER() OVER (PARTITION BY worker_sysid ORDER BY worker_sysid, DATEADD(day, 0, DATEDIFF(day, 0, (dateadd(day,[TRANSDATE]-(4),'1801-01-01')))) + DATEADD(day, 0 - DATEDIFF(day, 0, DATEADD(second, TRANS_END - DATEDIFF(S, GETDATE(), GETUTCDATE()), '1970-01-01')), DATEADD(second, TRANS_END - DATEDIFF(S, GETDATE(), GETUTCDATE()), '1970-01-01'))) * 2,
openCnt = NULL            
FROM
Trans
JOIN trans_workers ON trans_workers.trans_sysid = Trans.SYSID
JOIN Service ON Service.SYSID = Trans.SERVICESYSID
WHERE 
worker_sysid IS NOT NULL
AND Trans.deleted_at IS NULL
AND trans_workers.deleted_at IS NULL
AND Trans.CHARGEBY IN ('Hour', 'Hours')
AND (transCancelled IS NULL OR transCancelled != 1)
AND (
COALESCE(Service.overnight, 0) = 0

)
AND TRANSDATE BETWEEN @start AND @end
)
AS baseSelected
)
SELECT scheduled_hours = SUM(hours), worker_sysid 
FROM (
SELECT
dt.worker_sysid,
hours = CAST(ABS(DATEDIFF(second, MIN(dt.BeginDate), MAX(dt.EndDate))) / 3600.0 AS DECIMAL(10,2))
FROM (
SELECT
worker_sysid,
BeginDate,
EndDate,
grpID =
IIF(BeginDate IS NOT NULL, ROW_NUMBER() OVER (PARTITION BY worker_sysid ORDER BY worker_sysid, BeginDate), ROW_NUMBER() OVER (PARTITION BY worker_sysid ORDER BY worker_sysid, EndDate))
FROM
cteTemp
WHERE 
BeginDate IS NOT NULL OR EndDate IS NOT NULL
)
AS dt 
GROUP BY dt.worker_sysid,grpID
) AS final_table
GROUP BY worker_sysid ORDER BY worker_sysid

由于每个事务的DATE都是在Clarion中，而每个事务的TIME是Unix时间戳，所以转换对我自己有好处