Как в MSSQL разделить таблицу на отрезки времени по значению?
Есть таблица в MSSQL..
| dt_beg | dt_end | val |
|---|---|---|
| 2023-04-11 13:35:00.000 | 2023-04-11 13:36:00.000 | 0 |
| 2023-04-11 13:36:00.000 | 2023-04-11 13:37:00.000 | 0 |
| 2023-04-11 13:37:00.000 | 2023-04-11 13:38:00.000 | 1 |
| 2023-04-11 13:38:00.000 | 2023-04-11 13:39:00.000 | 1 |
| 2023-04-11 13:39:00.000 | 2023-04-11 13:40:00.000 | 1 |
| 2023-04-11 13:40:00.000 | 2023-04-11 13:41:00.000 | 0 |
| 2023-04-11 13:41:00.000 | 2023-04-11 13:42:00.000 | 0 |
| 2023-04-11 13:42:00.000 | 2023-04-11 13:43:00.000 | 0 |
Нужно разбить на интервалы времени значение..
2023-04-11 13:35:00.000 2023-04-11 13:37:00.000 0
2023-04-11 13:37:00.000 2023-04-11 13:41:00.000 1
2023-04-11 13:40:00.000 2023-04-11 13:43:00.000 0
Делаю
select dt_beg, dt_end, val,
dense_rank() over(PARTITION BY dt_end order by val)
все оконные варианты перебрал - не получается...
Ответы (1 шт):
Автор решения: Arkee
→ Ссылка
Один из вариантов решения - рекурсивная проверка на совпадение dt_end с dt_begin при изменении значения val:
-- входные данные
WITH [tempTable] AS (
SELECT CONVERT([datetime], '2023-04-11 13:35:00.000', 120) AS [dt_beg], CONVERT([datetime], '2023-04-11 13:36:00.000', 120) AS [dt_end], CAST(0 AS [int]) AS [val]
UNION ALL
SELECT CONVERT([datetime], '2023-04-11 13:36:00.000', 120) AS [dt_beg], CONVERT([datetime], '2023-04-11 13:37:00.000', 120) AS [dt_end], CAST(0 AS [int]) AS [val]
UNION ALL
SELECT CONVERT([datetime], '2023-04-11 13:37:00.000', 120) AS [dt_beg], CONVERT([datetime], '2023-04-11 13:38:00.000', 120) AS [dt_end], CAST(1 AS [int]) AS [val]
UNION ALL
SELECT CONVERT([datetime], '2023-04-11 13:38:00.000', 120) AS [dt_beg], CONVERT([datetime], '2023-04-11 13:39:00.000', 120) AS [dt_end], CAST(1 AS [int]) AS [val]
UNION ALL
SELECT CONVERT([datetime], '2023-04-11 13:39:00.000', 120) AS [dt_beg], CONVERT([datetime], '2023-04-11 13:40:00.000', 120) AS [dt_end], CAST(1 AS [int]) AS [val]
UNION ALL
SELECT CONVERT([datetime], '2023-04-11 13:40:00.000', 120) AS [dt_beg], CONVERT([datetime], '2023-04-11 13:41:00.000', 120) AS [dt_end], CAST(0 AS [int]) AS [val]
UNION ALL
SELECT CONVERT([datetime], '2023-04-11 13:41:00.000', 120) AS [dt_beg], CONVERT([datetime], '2023-04-11 13:42:00.000', 120) AS [dt_end], CAST(0 AS [int]) AS [val]
UNION ALL
SELECT CONVERT([datetime], '2023-04-11 13:42:00.000', 120) AS [dt_beg], CONVERT([datetime], '2023-04-11 13:43:00.000', 120) AS [dt_end], CAST(0 AS [int]) AS [val]
),
[recursive] AS (
-- фиксируем изменение val
SELECT
t1.[dt_beg],
t1.[dt_end],
t1.[val]
FROM
[tempTable] t1
WHERE
NOT EXISTS (
SELECT 1
FROM [tempTable] t2
WHERE
t2.[dt_end] = t1.[dt_beg]
AND t2.[val] = t1.[val]
)
UNION ALL
-- рекурсивная выборка
SELECT
r.[dt_beg],
t.[dt_end],
r.[val]
FROM
[recursive] r
INNER JOIN [tempTable] t ON t.[dt_beg] = r.[dt_end] AND r.[val] = t.[val]
)
-- финальная группировка
SELECT
r.[dt_beg],
MAX(r.[dt_end]) AS [dt_end],
r.[val]
FROM
[recursive] r
GROUP BY
r.[dt_beg],
r.[val]
ORDER BY
r.[dt_beg]