#sql #sql-server
#sql #sql-server
Вопрос:
У меня есть таблица, которую необходимо обновить значениями из той же таблицы. По сути, я хочу изменить настройку соединения в строках, где рабочие и клиент одинаковы, и чтобы измененная настройка подключения к строке началась через 5 минут после завершения другого соединения (с тем же рабочим и клиентом). Приведенный ниже код делает это. Но есть еще одна проблема. когда за короткий промежуток времени происходит несколько подключений между одним и тем же клиентом и работником, мне нужно, чтобы все они имели одинаковые настройки подключения. Я действительно не уверен, как этого добиться.
текущий запрос:
UPDATE t
SET t.[Connection setup] = t2.[Connection setup]
FROM Table1 t
INNER JOIN Table1 t2 ON t.worker = t2.worker
AND t.client = t2.client
AND t.SessionNo <> t2.SessionNo
AND t.[Connection setup] <= DATEADD(mi, 5, t2.[Connection end])
AND t.[Connection setup] >= t2.[Connection end]
Как вы можете видеть в этом примере, существует несколько подключений между одним и тем же клиентом и рабочим, а столбец «real_time» показывает, какое время у них должно быть.
SessionNo worker Tag Start Ende Dauer Connection setup Connection end client right_time
5 116590 mma 09.08.2020 00:00:00 12:44 13:01 00:17 09.08.2020 12:44:00 09.08.2020 13:01:00 OBENAT1D0137 12:44
6 106991 mma 09.08.2020 00:00:00 13:03 13:07 00:04 09.08.2020 13:03:00 09.08.2020 13:07:00 OBENAT1D0137 12:44
7 102306 mma 09.08.2020 00:00:00 13:07 13:56 00:49 09.08.2020 13:07:00 09.08.2020 13:56:00 OBENAT1D0137 12:44
8 430386 mma 09.08.2020 00:00:00 13:56 14:06 00:10 09.08.2020 13:56:00 09.08.2020 14:06:00 OBENAT1D0137 12:44
9 117264 mma 09.08.2020 00:00:00 14:06 14:17 00:10 09.08.2020 14:06:00 09.08.2020 14:17:00 OBENAT1D0137 12:44
10 434302 mma 09.08.2020 00:00:00 14:17 14:41 00:23 09.08.2020 14:17:00 09.08.2020 14:41:00 OBENAT1D0137 12:44
11 333234 mma 09.08.2020 00:00:00 14:41 14:55 00:13 09.08.2020 14:41:00 09.08.2020 14:55:00 OBENAT1D0137 12:44
12 271379 mg 09.03.2020 00:00:00 10:24 10:25 00:00 09.03.2020 10:24:00 09.03.2020 10:25:00 OBENAT1D0117 10:24
13 269650 mg 09.03.2020 00:00:00 10:25 10:47 00:21 09.03.2020 10:25:00 09.03.2020 10:47:00 OBENAT1D0117 10:24
14 290765 mg 09.03.2020 00:00:00 12:19 12:19 00:00 09.03.2020 12:19:00 09.03.2020 12:19:00 OBENAT1D0117 12:19
15 280892 mg 09.03.2020 00:00:00 12:19 12:22 00:03 09.03.2020 12:19:00 09.03.2020 12:22:00 OBENAT1D0117 12:19
с моим текущим запросом они просто занимают время из предыдущей строки.
любая помощь была бы отличной
РЕДАКТИРОВАТЬ: я добавил 4 строки в таблицу, чтобы более подробно объяснить проблему. Например, в последних 4 строках client и worker совпадают, но соединения должны быть сгруппированы в 2 разные группы. не в одной. как видно из столбца «right_time».
Комментарии:
1. Итак, вы хотите, чтобы для них было установлено минимальное значение [Настройка подключения] из группы совпадающих записей? Если нет, то какую логику необходимо использовать? Спасибо
2. @NickW да, я хочу, чтобы для них было установлено минимальное значение. но проблема в том, что в один и тот же день существует более одной группы одного и того же работника и клиента
3. Пожалуйста, не могли бы вы объяснить на английском языке, а не в коде, какой должна быть логика, учитывая конкретную запись, для выбора группы записей, из которых должно быть взято минимальное значение [Настройка подключения]. Очевидно, что у них должен быть один и тот же рабочий и клиент и день, но в течение этого дня, что включает / исключает записи из группы (я предполагаю, что это сравнение на основе времени)?
4. @NickW извините, если я плохо объяснил это… да, вы правы, это зависит от времени. Например, один и тот же работник работает с одним и тем же клиентом несколько раз за один день, нам нужно сгруппировать их по времени, где в одну группу входят все соединения, которые между ними имеют перерыв менее 5 минут. существует дополнительный столбец, который показывает, каким должно быть [right_time] для [Настройка подключения] .
5. Привет — пожалуйста, не могли бы вы точно определить, что вы подразумеваете под «перерывом менее 5 минут». Это какая-либо запись, у которой начальная дата <= 5 минут до даты начала рассматриваемой записи? Спасибо
Ответ №1:
Одним из вариантов группировки строк было бы использование рекурсии. Однако рекурсия может быть медленной для больших наборов данных…
Пример данных
Я опустил некоторые столбцы из ваших выборочных данных и использовал только нужные мне столбцы и добавил id столбец для начальных значений. Если id столбец не является частью вашего набора данных, вам придется сравнивать оба conn_start и conn_end сортировать следующую строку для каждой комбинации worker и client (потому что могут быть строки с одинаковым conn_start значением, например, строки 14 и 15 ).
create table data
(
id int,
worker nvarchar(3),
client nvarchar(15),
conn_start datetime2(0),
conn_end datetime2(0)
);
insert into data (id, worker, client, conn_start, conn_end) values
(5 , 'mma', 'OBENAT1D0137', '09.08.2020 12:44:00', '09.08.2020 13:01:00'),
(6 , 'mma', 'OBENAT1D0137', '09.08.2020 13:03:00', '09.08.2020 13:07:00'),
(7 , 'mma', 'OBENAT1D0137', '09.08.2020 13:07:00', '09.08.2020 13:56:00'),
(8 , 'mma', 'OBENAT1D0137', '09.08.2020 13:56:00', '09.08.2020 14:06:00'),
(9 , 'mma', 'OBENAT1D0137', '09.08.2020 14:06:00', '09.08.2020 14:17:00'),
(10, 'mma', 'OBENAT1D0137', '09.08.2020 14:17:00', '09.08.2020 14:41:00'),
(11, 'mma', 'OBENAT1D0137', '09.08.2020 14:41:00', '09.08.2020 14:55:00'),
(12, 'mg', 'OBENAT1D0117', '09.03.2020 10:24:00', '09.03.2020 10:25:00'),
(13, 'mg', 'OBENAT1D0117', '09.03.2020 10:25:00', '09.03.2020 10:47:00'),
(14, 'mg', 'OBENAT1D0117', '09.03.2020 12:19:00', '09.03.2020 12:19:00'),
(15, 'mg', 'OBENAT1D0117', '09.03.2020 12:19:00', '09.03.2020 12:22:00');
Решение
Если это выглядит сложно, обязательно ознакомьтесь с этой скрипкой для пошагового построения.
with cte as
(
select d.id,
d.worker,
d.client,
d.conn_start,
d.conn_end,
datediff(minute,
coalesce(lag(d.conn_end) over(partition by d.worker, d.client order by d.id), d.conn_start),
d.conn_start) as diff_minutes
from data d
),
rcte as
(
select c.id,
c.worker,
c.client,
c.conn_start,
c.conn_end,
c.diff_minutes,
c.conn_start as conn_start_group
from cte c
where not exists ( select 'x'
from data d2
where d2.worker = c.worker
and d2.client = c.client
and d2.id < c.id )
union all
-- select next row for each (worker, client), keep conn_start_group if difference < 5 min
select c.id,
c.worker,
c.client,
c.conn_start,
c.conn_end,
c.diff_minutes,
case
when c.diff_minutes <= 5
then r.conn_start_group
else c.conn_start
end
from rcte r
join cte c
on c.worker = r.worker
and c.client = r.client
and c.id > r.id
and not exists ( select 'x'
from cte c2
where c2.worker = c.worker
and c2.client = c.client
and c2.id > r.id
and c2.id < c.id )
)
select rc.id,
rc.worker,
rc.client,
rc.conn_start,
rc.conn_end,
rc.conn_start_group
from rcte rc
order by rc.id;
Результат
id worker client conn_start conn_end conn_start_group
-- ------ ------------ ------------------- ------------------- -------------------
5 mma OBENAT1D0137 2020-09-08 12:44:00 2020-09-08 13:01:00 2020-09-08 12:44:00
6 mma OBENAT1D0137 2020-09-08 13:03:00 2020-09-08 13:07:00 2020-09-08 12:44:00
7 mma OBENAT1D0137 2020-09-08 13:07:00 2020-09-08 13:56:00 2020-09-08 12:44:00
8 mma OBENAT1D0137 2020-09-08 13:56:00 2020-09-08 14:06:00 2020-09-08 12:44:00
9 mma OBENAT1D0137 2020-09-08 14:06:00 2020-09-08 14:17:00 2020-09-08 12:44:00
10 mma OBENAT1D0137 2020-09-08 14:17:00 2020-09-08 14:41:00 2020-09-08 12:44:00
11 mma OBENAT1D0137 2020-09-08 14:41:00 2020-09-08 14:55:00 2020-09-08 12:44:00
12 mg OBENAT1D0117 2020-09-03 10:24:00 2020-09-03 10:25:00 2020-09-03 10:24:00
13 mg OBENAT1D0117 2020-09-03 10:25:00 2020-09-03 10:47:00 2020-09-03 10:24:00
14 mg OBENAT1D0117 2020-09-03 12:19:00 2020-09-03 12:19:00 2020-09-03 12:19:00
15 mg OBENAT1D0117 2020-09-03 12:19:00 2020-09-03 12:22:00 2020-09-03 12:19:00
Комментарии:
1. спасибо за ваш ответ, но я должен сказать, что это не совсем правильно. Я провел дополнительные тесты и заметил, что есть ошибка. Я добавил 4 новые строки в свою таблицу, где я заметил проблему. Если у меня есть 4 или более строк с одним и тем же клиентом и работником в один и тот же день. при применении вашего кода указывается, что последняя строка имеет то же время, что и строки 12 и 13, поскольку ваш код принимает значения из ГРУППЫ, по которой возвращается самое раннее время от одного и того же клиента и работника
2. В решении нет ни одного
group by, но первая частьrcteдействительно возвращает только самую раннюю комбинацию worker / client (как и предполагалось и объяснялось в скрипке). На самом деле проблема заключалась во второй частиrcte, где первоначальное решение зависело от уникальныхconn_startзначений в комбинации worker / client. Дополнительный пример показывает, что это было неверное предположение. Вместо этого в новом решении используютсяidстолбцы (при условии, что они уникальны и всегда увеличиваются с течением времени).
Ответ №2:
Это должно сработать. Я использовал следующую таблицу, поэтому вам может потребоваться скорректировать код, чтобы заставить его работать с вашей реальной таблицей. Столбец [ConnSetupCalc] использовался для проверки вычисления, а не для обновления фактических данных во время тестирования, а затем для повторного создания правильных данных.
CREATE TABLE [dbo].[conn_data](
[SessionNo] [int] NULL,
[worker] [nvarchar](3) NULL,
[client] [nvarchar](15) NULL,
[ConnectionSetup] [datetime2](0) NULL,
[ConnectionEnd] [datetime2](0) NULL,
[RightTime] [nvarchar](15) NULL,
[ConnSetupCalc] [datetime] NULL
)
Этот сохраненный процесс должен вычислять данные в соответствии с вашим примером:
CREATE PROCEDURE UpdConn
AS
declare @MyCursor CURSOR;
declare @SessionNo int;
declare @Worker nvarchar(3);
declare @Client nvarchar(15);
declare @ConnStart datetime;
declare @ConnEnd datetime;
declare @Worker_prev nvarchar(3);
declare @Client_prev nvarchar(15);
declare @ConnStart_prev datetime;
declare @ConnEnd_prev datetime;
BEGIN
SET @MyCursor = CURSOR FOR
SELECT SessionNo
,worker
,client
,ConnectionSetup
,ConnectionEnd
FROM [dbo].[conn_data]
order by worker, client, ConnectionSetup
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @SessionNo, @Worker, @Client, @ConnStart, @ConnEnd
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Worker = @Worker_prev and @Client = @Client_prev and DATEDIFF(mi,@ConnEnd_prev,@ConnStart) between 0 and 5
BEGIN
UPDATE conn_data set ConnectionSetup = @ConnStart_prev where SessionNo = @SessionNo;
-- Test logic: UPDATE conn_data set ConnSetupCalc = @ConnStart_prev where SessionNo = @SessionNo;
END
ELSE
BEGIN
set @ConnStart_prev = @ConnStart;
END
SET @Worker_prev = @Worker;
SET @Client_prev = @Client;
SET @ConnEnd_prev = @ConnEnd;
FETCH NEXT FROM @MyCursor
INTO @SessionNo, @Worker, @Client, @ConnStart, @ConnEnd
END;
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END;