sql-server - 考虑到重叠，有没有更快的方法来增加时间范围？

Question

我有一个需要几分钟才能执行的子查询。如果我只取出要加起来的初始行，那么 2,400 行只需要半秒钟，所以我不明白为什么做总和的主查询需要这么长时间。

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

查询返回正确的数据，它只需要 FOREVER 就可以了。

询问

 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

表格

Trans : SYSID (int, pk), TRANSDATE (int, clarion-formatted date), TRANS_START / TRANS_END (UNIX timestamp), 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

标签： sql-server

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 时间戳，所以奖励指向我自己的转换