能否将SQL标量函数CheckHours转换为内联函数？

问题背景 #

需要将SQL标量函数CheckHours转换为内联表值函数，原函数在处理dbo.session表10万条记录时性能受限，自行尝试转换未成功。

原函数逻辑 #

该函数用于计算每个会话的有效时长（小时数），逻辑如下：

• 仅统计**非周末（周六、周日除外）**的时间
• 若起始日期与结束日期间隔超过1天且不超过1年：
  • 遍历完整天数，每遇到非周末则累加24小时
  • 处理最后不足1天的剩余时长，若当天为非周末则累加该时长
• 最后根据dbo.datetable中的数据，从总时长中扣除每条记录对应的24小时（每存在一条符合条件的记录，总时长减24）

原标量函数代码 #

CREATE FUNCTION [dbo].[CheckHours](@startdate datetime,@enddate datetime)
RETURNS int
AS
BEGIN
    DECLARE @hours_count int, @latestdate datetime, @count int;
    SELECT @latestdate = @startdate, @hours_count = 0
    IF (DATEDIFF(hh, @latestdate, @enddate) > 0)
    BEGIN
        WHILE (DATEDIFF(hh, @latestdate, @enddate) > 24 AND @hours_count < 8760)
        BEGIN     
            IF (DATEPART(dw, @latestdate) <> 6 AND DATEPART(dw, @latestdate) <> 7) 
            BEGIN
                SET @hours_count = @hours_count + 24 
            END
            SET @latestdate = DATEADD(hh, 24, @latestdate) 
        END
        IF (DATEPART(dw, @latestdate) <> 6 AND DATEPART(dw, @latestdate) <> 7)  
        BEGIN
            SET @hours_count = @hours_count + DATEDIFF(hh, @latestdate, @enddate)
        END
    END
    SELECT 
        @count = COUNT(*) 
    FROM dbo.datetable dt
    WHERE dt.tdate BETWEEN @startdate AND @enddate AND dt.tdate <> @startdate
    SET @hours_count = @hours_count - (@count * 24)
    RETURN @hours_count
END

转换后的内联表值函数 #

CREATE FUNCTION [dbo].[InlineCheckHours](@startdate datetime, @enddate datetime)
RETURNS TABLE
AS
RETURN
(
    WITH DateRange AS (
        -- 生成起止日期之间的所有完整日期（忽略时间部分）
        SELECT 
            DATEADD(day, number, CAST(@startdate AS date)) AS DateOnly
        FROM master.dbo.spt_values
        WHERE type = 'P'
            AND DATEADD(day, number, CAST(@startdate AS date)) < CAST(@enddate AS date)
    ),
    FullDayHours AS (
        -- 统计完整非周末天数对应的小时数
        SELECT 
            COUNT(*) * 24 AS FullHours
        FROM DateRange
        WHERE DATEPART(dw, DateOnly) NOT IN (6,7) -- 排除周六、周日
    ),
    PartialDayHours AS (
        -- 计算最后不足一天的剩余时长（仅当天为非周末时统计）
        SELECT 
            CASE 
                WHEN DATEPART(dw, CAST(@enddate AS date)) NOT IN (6,7) 
                     AND DATEDIFF(hh, @startdate, @enddate) > 0
                THEN DATEDIFF(hh, 
                              CASE WHEN CAST(@startdate AS date) = CAST(@enddate AS date) 
                                   THEN @startdate 
                                   ELSE CAST(@enddate AS date) 
                              END, 
                              @enddate)
                ELSE 0
            END AS PartialHours
    ),
    AdjustmentHours AS (
        -- 计算需要扣除的时长（datetable中符合条件的记录数*24）
        SELECT 
            ISNULL(COUNT(*),0) * 24 AS Adjustment
        FROM dbo.datetable dt
        WHERE dt.tdate BETWEEN @startdate AND @enddate 
              AND dt.tdate <> @startdate
    )
    SELECT 
        ISNULL(fd.FullHours, 0) + ISNULL(pd.PartialHours, 0) - ISNULL(adj.Adjustment, 0) AS CheckHours
    FROM FullDayHours fd
    CROSS JOIN PartialDayHours pd
    CROSS JOIN AdjustmentHours adj
    WHERE DATEDIFF(hh, @startdate, @enddate) > 0
    UNION ALL
    SELECT 0 AS CheckHours
    WHERE DATEDIFF(hh, @startdate, @enddate) <= 0
)

查询语句替换 #

原标量函数调用替换为内联函数后，使用CROSS APPLY可获得更好的性能：

SELECT ich.CheckHours, s.lastsessiondate
FROM dbo.session s
CROSS APPLY dbo.InlineCheckHours(s.lastsessiondate, GETDATE()) ich
WHERE s.manucat BETWEEN 4 AND 9
    AND s.soloid IS NOT NULL

验证说明 #

可使用以下语句验证转换后函数与原函数结果一致：

-- 原函数测试
SELECT dbo.CheckHours('2022-07-24 20:33:36.710', '2022-07-26 19:38:52.630') AS OriginalCheckHours

-- 内联函数测试
SELECT CheckHours AS InlineCheckHours
FROM dbo.InlineCheckHours('2022-07-24 20:33:36.710', '2022-07-26 19:38:52.630')

注：若环境中master.dbo.spt_values不可用，可替换为自定义日期表或递归CTE生成日期序列。

内容的提问来源于stack exchange，提问作者Goran