#sql #sql-server
#sql #sql-сервер
Вопрос:
Постановка задачи:
У меня есть таблица со следующей информацией:
----------- ----------------- --------------------- --------------------- --------------------- ------------------------
| equipment | equipment_state | equipment_state_in | equipment_state_out | shift_begin | shift_end |
----------- ----------------- --------------------- --------------------- --------------------- ------------------------
| A | X | 2020-12-05 00:00:00 | 2020-12-05 04:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00 |
| A | Y | 2020-12-05 04:00:01 | 2020-12-05 08:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00 |
| A | Z | 2020-12-05 08:00:01 | 2020-12-05 13:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00 |
----------- ----------------- --------------------- --------------------- --------------------- ------------------------
Как видно, эта таблица представляет состояние единицы оборудования во времени, при этом изменение, в котором произошло состояние, добавляется в качестве контекста. Проблема в том, что состояние оборудования может продолжаться до следующей смены, начиная с первой смены. Это видно в последней строке.
Решение:
Я ищу решение, которое будет применять логику к запросу, чтобы определить, когда состояние оборудования переходит в следующую смену, и, таким образом, создает еще одну строку для разделения информации, например:
----------- ----------------- --------------------- --------------------- --------------------- ------------------------
| equipment | equipment_state | equipment_state_in | equipment_state_out | shift_begin | shift_end |
----------- ----------------- --------------------- --------------------- --------------------- ------------------------
| A | X | 2020-12-05 00:00:00 | 2020-12-05 04:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00 |
| A | Y | 2020-12-05 04:00:01 | 2020-12-05 08:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00 |
| A | Z | 2020-12-05 08:00:01 | 2020-12-05 12:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00 |
| A | Z | 2020-12-05 12:00:01 | 2020-12-05 13:00:00 | 2020-12-05 00:12:00 | 2020-12-05 24:00:00 |
----------- ----------------- --------------------- --------------------- --------------------- ------------------------
Как видно, состояние z с 8:00 до 13:00 по-прежнему фиксируется, но теперь оно разделено так, что раздел 12:00-13:00 находится в правильном окне сдвига.
Есть идеи?
Комментарии:
1. Должны ли мы предполагать, что все сдвиги существуют и что они либо с полуночи до полудня, либо с полудня до полуночи?
2. 2020-12-05 24:00:00 должно быть 2020-12-06 00:00:00, чтобы быть допустимым datetime. Если они являются строками, логика сравнения 2020-12-05 24:00:00 с 2020-12-06 00:00:00 будет усложнена.
Ответ №1:
Попробуйте этот рекурсивный CTE. Привязка — это исходные данные с временем простоя оборудования, установленным на время окончания смены, если оно прошло. В привязку добавляются дополнительные столбцы для хранения информации за оставшееся время работы оборудования, если таковое имеется. Следующий период сдвига также вычисляется в это время, чтобы упростить рекурсивную часть запроса.
Рекурсивный запрос применяет ту же логику к оставшемуся времени работы оборудования. Оставшееся время работы с оборудованием будет сокращаться на одну смену на каждой итерации, поэтому рекурсия в конечном итоге прекратится.
WITH src as (
SELECT *
FROM (
VALUES
('A', 'X',
CAST('2020-12-05 00:00:00' as datetime), CAST('2020-12-05 04:00:00' as datetime),
CAST('2020-12-05 00:00:00' as datetime), CAST('2020-12-05 12:00:00' as datetime)),
('A', 'Y',
CAST('2020-12-05 04:00:01' as datetime), CAST('2020-12-05 08:00:00' as datetime),
CAST('2020-12-05 00:00:00' as datetime), CAST('2020-12-05 12:00:00' as datetime)),
('A', 'Z',
CAST('2020-12-05 08:00:01' as datetime), CAST('2020-12-05 13:00:00' as datetime),
CAST('2020-12-05 00:00:00' as datetime), CAST('2020-12-05 12:00:00' as datetime))
) t (equipment, equipment_state, equipment_state_in, equipment_state_out, shift_begin, shift_end)
), split as (
SELECT equipment,
equipment_state,
equipment_state_in,
CASE WHEN equipment_state_out > shift_end THEN shift_end ELSE equipment_state_out END as [equipment_state_out],
shift_begin,
shift_end,
CASE WHEN equipment_state_out > shift_end THEN shift_end END as [equipment_state_in_remainder],
CASE WHEN equipment_state_out > shift_end THEN equipment_state_out END as [equipment_state_out_remainder],
CASE WHEN equipment_state_out > shift_end THEN shift_end END as [shift_begin_remainder],
CASE WHEN equipment_state_out > shift_end THEN DATEADD(hour, 12, shift_end) END as [shift_end_remainder]
FROM src
UNION ALL
SELECT equipment,
equipment_state,
equipment_state_in_remainder as [equipment_state_in],
CASE WHEN equipment_state_out_remainder > shift_end_remainder THEN shift_end_remainder ELSE equipment_state_out_remainder END as [equipment_state_out],
shift_begin_remainder as [shift_begin],
shift_end_remainder as [shift_end],
CASE WHEN equipment_state_out_remainder > shift_end_remainder THEN shift_end_remainder END as [equipment_state_in_remainder],
CASE WHEN equipment_state_out_remainder > shift_end_remainder THEN equipment_state_out_remainder END as [equipment_state_out_remainder],
CASE WHEN equipment_state_out_remainder > shift_end_remainder THEN shift_end_remainder END as [shift_begin_remainder],
CASE WHEN equipment_state_out_remainder > shift_end_remainder THEN DATEADD(hour, 12, shift_end_remainder) END as [shift_end_remainder]
FROM split
WHERE equipment_state_in_remainder IS NOT NULL
)
select * from split
Комментарии:
1. «Строки» действительно уже являются датами (в вопросе не должно было быть 24:00:00). Но после моих предварительных попыток эти методы рекурсии, похоже, делают свое дело. Спасибо
Ответ №2:
Использование более интуитивного и программного способа перебора и сравнения —
— Одна табличная переменная с идентификатором в качестве номера строки, которая будет использоваться для цикла
— Еще одна табличная переменная для хранения конечного результата
declare @TempTable as TABLE(Id INT IDENTITY(1,1),
equipment NVARCHAR(1),
equipment_state NVARCHAR(1),
equipment_state_in DATETIME,
equipment_state_out DATETIME,
shift_begin DATETIME,
shift_end DATETIME)
declare @Output as TABLE(equipment NVARCHAR(1),
equipment_state NVARCHAR(1),
equipment_state_in DATETIME,
equipment_state_out DATETIME,
shift_begin DATETIME,
shift_end DATETIME)
INSERT INTO @TempTable(equipment, equipment_state, equipment_state_in, equipment_state_out, shift_begin, shift_end)
SELECT equipment, equipment_state, equipment_state_in, equipment_state_out, shift_begin, shift_end from OriginalTable
DECLARE @RowNum INT = 1;
DECLARE @equipment NVARCHAR(1),
@equipment_state NVARCHAR(1),
@equipment_state_in DATETIME,
@equipment_state_out DATETIME,
@shift_begin DATETIME,
@shift_end DATETIME
WHILE EXISTS(SELECT * FROM @TempTable WHERE Id = @RowNum)
BEGIN
SELECT @equipment = equipment, @equipment_state = equipment_state, @equipment_state_in = equipment_state_in, @equipment_state_out = equipment_state_out, @shift_begin = shift_begin, @shift_end = shift_end FROM @TempTable WHERE Id = @RowNum;
WHILE (@equipment_state_out > @shift_end)
BEGIN
INSERT INTO @Output(equipment, equipment_state, equipment_state_in, equipment_state_out, shift_begin, shift_end)
VALUES (@equipment, @equipment_state, @equipment_state_in, @shift_end, @shift_begin, @shift_end)
SET @equipment_state_in = @shift_end;
SET @shift_begin = @shift_end;
SET @shift_end = DATEADD(hour, 12, @shift_end);
END
INSERT INTO @Output(equipment, equipment_state, equipment_state_in, equipment_state_out, shift_begin, shift_end)
VALUES (@equipment, @equipment_state, @equipment_state_in, @equipment_state_out, @shift_begin, @shift_end)
SET @RowNum = 1;
END
SELECT * FROM @Output