代码之家 › 专栏 › 技术社区 › MathewT

生成动态自定义间隔

sql-server-2014

MathewT · 技术社区 · 6 年前

我正在从事一个项目,该项目要求报告能够按照非标准用户定义的时间间隔(如指定开始和结束范围内的生产班次)动态分组源数据。示例用户可能希望按班次查看过去两天的生产信息组。我创建了一个名为“IntervalConfiguration”的表,该表存储了创建分组间隔所需的配置信息,数据如下:

IntervalType                SubIntervalType     IntervalDuration     IntervalDurationUnits  IntervalStartReferenceTime IntervalRepeatDuration IntervalRepeatDurationUnits
-------------------------   ------------------- -------------------- ---------------------  -------------------------- ---------------------- ---------------------------
Production Day              ProductionDay       1                    Days                   2013-01-07 07:00:00.000    1                      Days
Production Month            ProductionMonth     1                    Months                 2013-01-01 07:00:00.000    1                      Months
Production Week             ProductionWeek      1                    Weeks                  2013-01-07 08:45:00.000    1                      Weeks
Production Year             ProductionYear      1                    Years                  2013-01-01 08:45:00.000    1                      Years
Site A - Production Shift   Day                 12                   Hours                  2013-01-06 07:00:00.000    24                     Hours
Site A - Production Shift   Night               12                   Hours                  2013-01-06 19:00:00.000    24                     Hours
Site B - Production Shift   Day                 12                   Hours                  2013-01-06 06:45:00.000    24                     Hours
Site B - Production Shift   Night               12                   Hours                  2013-01-06 18:45:00.000    24                     Hours

如果用户在报告中选择站点a-生产班次作为分组间隔,并且开始日期为“2018年1月1日”,结束日期为“2018年5月1日”,则报告必须为白班创建分组间隔,其中白班从早上7点开始,晚上7点结束,夜班从晚上7点开始,第二天早上7点结束。此外,只应返回完全包含在开始日期和结束日期内的分组间隔。下面是所述场景的预期分组间隔示例。

SubIntervalType     IntervalStart            IntervalEnd             
----------------    -----------------------  ----------------------- 
Day                 2018-01-01 07:00:00.000  2018-01-01 19:00:00.000 
Night               2018-01-01 19:00:00.000  2018-01-02 07:00:00.000 
Day                 2018-01-02 07:00:00.000  2018-01-02 19:00:00.000 
Night               2018-01-02 19:00:00.000  2018-01-03 07:00:00.000 
Day                 2018-01-03 07:00:00.000  2018-01-03 19:00:00.000 
Night               2018-01-03 19:00:00.000  2018-01-04 07:00:00.000 
Day                 2018-01-04 07:00:00.000  2018-01-04 19:00:00.000

如果用户选择生产月作为分组间隔,开始日期为“2018年1月1日”,结束日期为“2019年1月1日”,则报告应生成以下分组间隔。

SubIntervalType     IntervalStart           IntervalEnd            
-----------------   ----------------------- -----------------------
ProductionMonth     2018-01-01 07:00:00.000 2018-02-01 07:00:00.000
ProductionMonth     2018-02-01 07:00:00.000 2018-03-01 07:00:00.000
ProductionMonth     2018-03-01 07:00:00.000 2018-04-01 07:00:00.000
ProductionMonth     2018-04-01 07:00:00.000 2018-05-01 07:00:00.000
ProductionMonth     2018-05-01 07:00:00.000 2018-06-01 07:00:00.000
ProductionMonth     2018-06-01 07:00:00.000 2018-07-01 07:00:00.000
ProductionMonth     2018-07-01 07:00:00.000 2018-08-01 07:00:00.000
ProductionMonth     2018-08-01 07:00:00.000 2018-09-01 07:00:00.000
ProductionMonth     2018-09-01 07:00:00.000 2018-10-01 07:00:00.000
ProductionMonth     2018-10-01 07:00:00.000 2018-11-01 07:00:00.000
ProductionMonth     2018-11-01 07:00:00.000 2018-12-01 07:00:00.000

我已经开始构建以下表值函数来动态创建所需的分组间隔。

CREATE FUNCTION [dbo].[GetIntervals]
(
    @dateRangeStart datetime,
    @dateRangeEnd   datetime,
    @groupByInterval    NVARCHAR(200)
)
RETURNS @Intervals TABLE (
    IntervalType            NVARCHAR(100)
    ,SubIntervalType        NVARCHAR(100)
    ,IntervalStart          DATETIME
    ,IntervalEnd                DATETIME
    ,IntervalDurationSeconds    FLOAT
)
AS
BEGIN

    DECLARE @activeIntervalDateTime DATETIME = DATEADD(millisecond, 3, @dateRangeStart);
    DECLARE @intervalStartTime DATETIME = DATEADD(s, 1, @dateRangeStart);
    DECLARE @intervalEndTime DATETIME = DATEADD(s, 1, @dateRangeStart);
    DECLARE @intervalDurationSeconds FLOAT;
    DECLARE @intervalName NVARCHAR(100);
    DECLARE @subIntervalType NVARCHAR(100);

    WHILE @intervalStartTime <= @dateRangeEnd
    BEGIN

        SELECT   TOP 1
                 @intervalName = IntervalType ,@subIntervalType = SubIntervalType , @intervalStartTime = IntervalStart, @intervalEndTime = IntervalEnd
        FROM    (SELECT  IntervalType, SubIntervalType, IntervalDuration, IntervalDurationUnits,
                            IntervalRepeatDuration, IntervalRepeatDurationUnits,
                            CASE IntervalRepeatDurationUnits
                             WHEN 'Hours' THEN (DateAdd(HH, (DateDiff(HH, IntervalStartReferenceTime, ActiveTimeNormalized ) / IntervalRepeatDuration) * IntervalRepeatDuration,  IntervalStartReferenceTime))
                             WHEN 'Days' THEN (DateAdd(D, (DateDiff(D, IntervalStartReferenceTime, ActiveTimeNormalized ) / IntervalRepeatDuration) * IntervalRepeatDuration,  IntervalStartReferenceTime))
                             WHEN 'Months'THEN (DateAdd(MM, (DateDiff(MM, IntervalStartReferenceTime, ActiveTimeNormalized ) / IntervalRepeatDuration) * IntervalRepeatDuration,  IntervalStartReferenceTime))
                             END AS IntervalStart,
                            CASE IntervalRepeatDurationUnits
                             WHEN 'Hours' THEN (DateAdd(HH, IntervalDuration, DateAdd(HH, (DateDiff(HH, IntervalStartReferenceTime, ActiveTimeNormalized ) / IntervalRepeatDuration) * IntervalRepeatDuration,  IntervalStartReferenceTime)))
                             WHEN 'Days' THEN 
                                CASE IntervalDurationUnits
                                WHEN 'Hours' THEN (DateAdd(HH, IntervalDuration, DateAdd(D, (DateDiff(D, IntervalStartReferenceTime, ActiveTimeNormalized ) / IntervalRepeatDuration) * IntervalRepeatDuration,  IntervalStartReferenceTime)))
                                WHEN 'Days' THEN (DateAdd(D, IntervalDuration, DateAdd(D, (DateDiff(D, IntervalStartReferenceTime, ActiveTimeNormalized ) / IntervalRepeatDuration) * IntervalRepeatDuration,  IntervalStartReferenceTime)))
                                END
                             WHEN 'Months'THEN 
                                CASE IntervalDurationUnits
                                WHEN 'Hours' THEN (DateAdd(HH, IntervalDuration, DateAdd(MM, (DateDiff(MM, IntervalStartReferenceTime, ActiveTimeNormalized ) / IntervalRepeatDuration) * IntervalRepeatDuration,  IntervalStartReferenceTime)))
                                WHEN 'Days' THEN (DateAdd(D, IntervalDuration, DateAdd(MM, (DateDiff(MM, IntervalStartReferenceTime, ActiveTimeNormalized ) / IntervalRepeatDuration) * IntervalRepeatDuration,  IntervalStartReferenceTime)))
                                WHEN 'Months' THEN (DateAdd(MM, IntervalDuration, DateAdd(MM, (DateDiff(MM, IntervalStartReferenceTime, ActiveTimeNormalized ) / IntervalRepeatDuration) * IntervalRepeatDuration,  IntervalStartReferenceTime)))
                                END
                             END AS IntervalEnd
                            ,IntervalStartReferenceTime
                FROM    (
                            SELECT   IntervalType, SubIntervalType, IntervalDuration, IntervalDurationUnits, IntervalRepeatDuration, IntervalRepeatDurationUnits, IntervalStartReferenceTime,
                                     CASE IntervalRepeatDurationUnits
                                     WHEN 'Hours' THEN DATEADD(MILLISECOND, -1*(DATEPART(MILLISECOND, IntervalStartReferenceTime) + 3), DATEADD(SECOND, -1*DATEPART(SECOND, IntervalStartReferenceTime), DATEADD(MINUTE, -1*DATEPART(MINUTE, IntervalStartReferenceTime), @activeIntervalDateTime)))
                                     WHEN 'Days' THEN DATEADD(MILLISECOND, -1*(DATEPART(MILLISECOND, IntervalStartReferenceTime) + 3), DATEADD(SECOND, -1*DATEPART(SECOND, IntervalStartReferenceTime), DATEADD(MINUTE, -1*DATEPART(MINUTE, IntervalStartReferenceTime), DATEADD(HOUR, -1*DATEPART(HOUR, IntervalStartReferenceTime), @activeIntervalDateTime))))
                                     WHEN 'Months' THEN DATEADD(MILLISECOND, -1*(DATEPART(MILLISECOND, IntervalStartReferenceTime) + 3), DATEADD(SECOND, -1*DATEPART(SECOND, IntervalStartReferenceTime), DATEADD(MINUTE, -1*DATEPART(MINUTE, IntervalStartReferenceTime), DATEADD(HOUR, -1*DATEPART(HOUR, IntervalStartReferenceTime), DATEADD(DAY, -1*(DATEPART(DAY, IntervalStartReferenceTime) - 1), @activeIntervalDateTime)))))
                                     END AS ActiveTimeNormalized
                            FROM    dbo.IntervalConfiguration               
                        ) norm
                WHERE   IntervalType = @groupByInterval) interval
        WHERE   (@activeIntervalDateTime > IntervalStart) and (@activeIntervalDateTime <= IntervalEnd)
        ORDER BY IntervalStartReferenceTime DESC


        SET @intervalDurationSeconds = DATEDIFF(SECOND, @intervalStartTime, @intervalEndTime);

        IF @intervalStartTime >= @dateRangeStart AND @intervalEndTime <= @dateRangeEnd 
        BEGIN

            INSERT INTO @Intervals( 
                                         IntervalType
                                         ,SubIntervalType           
                                        ,IntervalStart          
                                        ,IntervalEnd            
                                        ,IntervalDurationSeconds                                    
                                      )
            VALUES(
                     @intervalName
                    ,@subIntervalType
                    ,@intervalStartTime
                    ,@intervalEndTime
                    ,@intervalDurationSeconds
                  )

        END

        SET @activeIntervalDateTime = DATEADD(MILLISECOND, 3, @intervalEndTime);

    END

    RETURN;
END

然而,这个函数越来越复杂,调试也越来越困难。我想消除在函数中使用While循环的需要。我的问题是,有没有更简单的方法来实现我的需求,有没有可能消除对while循环的需要?

1 回复 | 直到 6 年前

Edmond Quinton 6 年前

通过使用理货表,您完全可以简化查询并消除while循环,如下所示:

CREATE FUNCTION [dbo].[GetIntervals]
(   
    @startRange datetime,
    @endRange   datetime,
    @groupByPeriod  NVARCHAR(200)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Tally (N) AS
    (
        SELECT 0 UNION ALL
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)               -- 10 rows
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)         -- 100 rows
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)         -- 1,000 rows
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)         -- 10,000 rows
        --CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n)       -- 100,000 rows
        --CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f(n)       -- 1,000,000 rows
    ), 
    Intervals
    AS
    (
        SELECT       I.IntervalType
                    ,I.SubIntervalType
                    ,I.IntervalDuration
                    ,I.IntervalDurationUnits
                    ,I.IntervalStartReferenceTime
                    ,I.IntervalRepeatDuration
                    ,I.IntervalRepeatDurationUnits
                    ,(
                        CASE 
                            WHEN I.IntervalRepeatDurationUnits = 'Years' AND N < 500 THEN DATEADD(YEAR, ((DATEDIFF(YEAR, I.IntervalStartReferenceTime, @startRange) / I.IntervalRepeatDuration) + N) * I.IntervalRepeatDuration ,I.IntervalStartReferenceTime) 
                            WHEN I.IntervalRepeatDurationUnits = 'Months' AND N < 6000 THEN DATEADD(MONTH, ((DATEDIFF(MONTH, I.IntervalStartReferenceTime, @startRange) / I.IntervalRepeatDuration) + N) * I.IntervalRepeatDuration ,I.IntervalStartReferenceTime)
                            WHEN I.IntervalRepeatDurationUnits = 'Weeks' AND N < 24000 THEN DATEADD(WEEK, ((DATEDIFF(WEEK, I.IntervalStartReferenceTime, @startRange) / I.IntervalRepeatDuration) + N) * I.IntervalRepeatDuration ,I.IntervalStartReferenceTime)
                            WHEN I.IntervalRepeatDurationUnits = 'Days' THEN DATEADD(DAY, ((DATEDIFF(DAY, I.IntervalStartReferenceTime, @startRange) / I.IntervalRepeatDuration)  + N) * I.IntervalRepeatDuration ,I.IntervalStartReferenceTime)
                            WHEN I.IntervalRepeatDurationUnits = 'Hours' THEN DATEADD(HOUR, ((DATEDIFF(HOUR, I.IntervalStartReferenceTime, @startRange) / I.IntervalRepeatDuration) + N) * I.IntervalRepeatDuration ,I.IntervalStartReferenceTime)
                        END 
                     ) AS IntervalStart
                     ,Tally.N
        FROM        IntervalConfiguration I
        CROSS JOIN  Tally
        WHERE       (I.IntervalType = @groupByPeriod) 
                        AND
                     (
                        N BETWEEN 0 AND (
                            CASE I.IntervalRepeatDurationUnits
                                WHEN 'Years' THEN DATEDIFF(YEAR, @startRange, @endRange) / I.IntervalRepeatDuration 
                                WHEN 'Months' THEN DATEDIFF(MONTH, @startRange, @endRange) / I.IntervalRepeatDuration  
                                WHEN 'Weeks' THEN DATEDIFF(WEEK, @startRange, @endRange) / I.IntervalRepeatDuration 
                                WHEN 'Days' THEN DATEDIFF(DAY, @startRange, @endRange) / I.IntervalRepeatDuration 
                                WHEN 'Hours' THEN DATEDIFF(HOUR, @startRange, @endRange) / I.IntervalRepeatDuration 
                            END)
                    )
    )
    SELECT       TOP 100 PERCENT
                 I.IntervalType
                ,I.SubIntervalType
                ,I.IntervalDuration
                ,I.IntervalDurationUnits
                ,I.IntervalStartReferenceTime
                ,I.IntervalRepeatDuration
                ,I.IntervalRepeatDurationUnits
                ,I.IntervalStart
                ,(
                    CASE I.IntervalDurationUnits
                        WHEN 'Years' THEN DATEADD(YEAR, I.IntervalDuration, I.IntervalStart)
                        WHEN 'Months' THEN DATEADD(MONTH, I.IntervalDuration, I.IntervalStart)
                        WHEN 'Weeks' THEN DATEADD(WEEK,  I.IntervalDuration, I.IntervalStart)
                        WHEN 'Days' THEN DATEADD(DAY, I.IntervalDuration, I.IntervalStart)
                        WHEN 'Hours' THEN DATEADD(HOUR, I.IntervalDuration, I.IntervalStart)
                    END 
                 ) AS IntervalEnd
                 ,N
    FROM        Intervals I
    WHERE       I.IntervalStart >= @startRange 
                AND 
                (
                    CASE I.IntervalDurationUnits
                        WHEN 'Years' THEN DATEADD(YEAR, I.IntervalDuration, I.IntervalStart)
                        WHEN 'Months' THEN DATEADD(MONTH, I.IntervalDuration, I.IntervalStart)
                        WHEN 'Weeks' THEN DATEADD(WEEK,  I.IntervalDuration, I.IntervalStart)
                        WHEN 'Days' THEN DATEADD(DAY, I.IntervalDuration, I.IntervalStart)
                        WHEN 'Hours' THEN DATEADD(HOUR, I.IntervalDuration, I.IntervalStart)
                    END 
                 ) <= @endRange
    ORDER BY    I.IntervalStart
)