SQL-сервер: как построить диапазон дат из таблицы с временем начала и окончания

sql-server datetime time recursive-cte

54 просмотра

2 ответа

12 Репутация автора

У меня есть временная таблица, содержащая 24 записи, по одной на каждый час определенного дня, отмеченного DayID. В таблице указывается, включено или выключено приложение в течение определенного периода времени.

Я хотел бы уменьшить количество записей в таблице, чтобы показывать только время, в течение которого приложение должно быть включено.

Вот некоторые примеры данных:

CREATE TABLE #OnOff 
(
    RowID INT identity(1, 1), 
    DayID TINYINT, 
    OnOff BIT, 
    StartTime TIME, 
    EndTime TIME
)

INSERT INTO #OnOff (DayID, OnOff, StartTime, EndTime) VALUES
(1, 0, '00:00', '00:59'),
(1, 0, '01:00', '01:59'),
(1, 0, '02:00', '02:59'),
(1, 1, '03:00', '03:59'),
(1, 1, '04:00', '04:59'),
(1, 0, '05:00', '05:59'),
(1, 1, '06:00', '06:59'),
(1, 1, '07:00', '07:59'),
(1, 0, '08:00', '08:59'),
(1, 0, '09:00', '09:59'),
(1, 0, '10:00', '10:59'),
(1, 0, '11:00', '11:59'),
(1, 0, '12:00', '12:59'),
(1, 0, '13:00', '13:59'),
(1, 1, '14:00', '14:59'),
(1, 1, '15:00', '15:59'),
(1, 1, '16:00', '16:59'),
(1, 0, '17:00', '17:59'),
(1, 0, '18:00', '18:59'),
(1, 0, '19:00', '19:59'),
(1, 0, '20:00', '20:59'),
(1, 0, '21:00', '21:59'),
(1, 0, '22:00', '22:59'),
(1, 0, '23:00', '23:59')

Желаемый результат должен быть

DayID   StartTime  EndTime
1       03:00      04:59
1       06:00      07:59
1       14:00      16:59

Таблица #OnOff всегда будет содержать значение для каждого часа дня (т. Е. Время окончания и начала всегда будет последовательным, за исключением последнего часа). Очевидно, что этого можно добиться с помощью курсора, но это кажется совершенно неэффективным. Есть ли лучший способ достичь этого результата.

Автор: Gert Источник Размещён: 18.07.2016 09:27

Ответы (2)


2 плюса

18517 Репутация автора

Вы можете достичь желаемых результатов, используя CTE над набором данных:

CREATE TABLE #OnOff
    (
      RowID INT IDENTITY(1, 1) ,
      DayID TINYINT ,
      OnOff BIT ,
      StartTime TIME ,
      EndTime TIME
    )

INSERT  INTO #OnOff
        ( DayID, OnOff, StartTime, EndTime )
VALUES  ( 1, 0, '00:00', '00:59' ),
        ( 1, 0, '01:00', '01:59' ),
        ( 1, 0, '02:00', '02:59' ),
        ( 1, 1, '03:00', '03:59' ),
        ( 1, 1, '04:00', '04:59' ),
        ( 1, 0, '05:00', '05:59' ),
        ( 1, 1, '06:00', '06:59' ),
        ( 1, 1, '07:00', '07:59' ),
        ( 1, 0, '08:00', '08:59' ),
        ( 1, 0, '09:00', '09:59' ),
        ( 1, 0, '10:00', '10:59' ),
        ( 1, 0, '11:00', '11:59' ),
        ( 1, 0, '12:00', '12:59' ),
        ( 1, 0, '13:00', '13:59' ),
        ( 1, 1, '14:00', '14:59' ),
        ( 1, 1, '15:00', '15:59' ),
        ( 1, 1, '16:00', '16:59' ),
        ( 1, 0, '17:00', '17:59' ),
        ( 1, 0, '18:00', '18:59' ),
        ( 1, 0, '19:00', '19:59' ),
        ( 1, 0, '20:00', '20:59' ),
        ( 1, 0, '21:00', '21:59' ),
        ( 1, 0, '22:00', '22:59' ),
        ( 1, 0, '23:00', '23:59' )

;WITH    cte
    AS ( -- Get the first row, seed for the cte, set the OnOffGroup to 1
        SELECT TOP 1
                RowID , DayID , OnOff , StartTime , EndTime , 1 AS OnOffGroup
        FROM     #OnOff
        WHERE    OnOff = 1
        ORDER BY RowID
        UNION ALL
        -- Join latest cte row with next row in sequence, 
        -- OnOffGroup set to previous row value if state matches, otherwise increment
        SELECT   #OnOff.RowID , #OnOff.DayID , #OnOff.OnOff , 
                 #OnOff.StartTime , #OnOff.EndTime ,
                CASE WHEN #OnOff.OnOff = 1 THEN cte.OnOffGroup
                        ELSE cte.OnOffGroup + 1
                END AS OnOffGroup
        FROM     #OnOff
                INNER JOIN cte ON cte.RowID + 1 = #OnOff.RowID
        )
    -- Output results with grouping and min/max to produce desired results
    SELECT  cte.DayID ,
            MIN(cte.StartTime) AS StartTime ,
            MAX(cte.EndTime) AS EndTime ,
            cte.OnOffGroup
    FROM    cte
    WHERE   cte.OnOff = 1
    GROUP BY cte.DayID ,
            cte.OnOffGroup

Производит:

DayID   StartTime           EndTime             OnOffGroup
1       03:00:00.0000000    04:59:00.0000000    1
1       06:00:00.0000000    07:59:00.0000000    2
1       14:00:00.0000000    16:59:00.0000000    8
Автор: Tanner Размещён: 18.07.2016 10:25

1 плюс

14079 Репутация автора

Другой способ с OUTER APPLY и DENSE_RANK:

;WITH cte AS (
SELECT  o.StartTime, 
        o.EndTime,
        DENSE_RANK() OVER (ORDER BY r.EndTime) as DR
FROM #OnOff o
OUTER APPLY (
            SELECT top 1 * 
            FROM #OnOff 
            WHERE o.EndTime < EndTime and OnOff = 0
            ) as r
WHERE o.OnOff = 1 
)

SELECT  MIN(StartTime) as StartTime,
        MAX(EndTime) as EndTime
FROM cte
GROUP BY DR

Выход:

StartTime           EndTime
03:00:00.0000000    04:59:59.0000000
06:00:00.0000000    07:59:59.0000000
14:00:00.0000000    16:59:59.0000000
Автор: gofr1 Размещён: 18.07.2016 10:42
Вопросы из категории :
32x32