SQL - Назначать задания работникам - Альтернатива циклу?

В SQL Server 2019 у меня есть таблица неназначенных задач и еще одна таблица работников, которые доступны для работы над этими задачами. Мое требование состоит в том, чтобы распределить рабочих равномерно по доступным задачам, обновив столбец WorkerID в таблице Tasks с помощью ID работника, который будет выполнять задачу.

  • Как количество рабочих процессов, так и количество задач могут различаться при каждом запуске SQL.
  • В любой таблице может быть больше записей, чем в другой.
  • Назначение задач начинается с первого работника в таблице Workers и заканчивается, когда все задачи назначены. (Рабочие работники не распределяются случайным образом в целях справедливости от запуска к запуску.)
  • В любой таблице может быть ноль записей.

Учитывая таблицы со следующими структурами, я хотел бы знать, как выполнять эти назначения без использования цикла. Я подозреваю, что это можно сделать с помощью номеров строк, рангов или какой-либо другой подобной магии SQL Server, но я не смог понять это. Спасибо!

Таблица Задачи:

JobID    JobName           WorkerID
------------------------------------
23       Carry Groceries   NULL
1234     Drive             NULL
6543     Dig               NULL
234567   Walk              NULL
78       Clean Room        NULL
54       Cook Dinner       NULL
2        Move Logs         NULL
34       Cut Grass         NULL
99       Milk Chickens     NULL

Таблица Рабочие:

WorkerID   WorkerName
---------------------
67         Larry
42         Sue
10         Peter
45         Steve

Ожидаемые результаты для таблицы Tasks:

JobID    JobName           WorkerID
--------------------------------------
23       Carry Groceries   67
1234     Drive             42
6543     Dig               10
234567   Walk              45
78       Clean Room        67
54       Cook Dinner       42
2        Move Logs         10
34       Cut Grass         45
99       Milk Chickens     67

sql sql-server sql-server-2019
person user1876354    schedule 29.09.2020    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете назначить номер с помощью оконных функций, а затем join. Круговой метод – это:

with toupdate as (
      select t.*, row_number() over (order by (select null)) as seqnum
      from tasks t
     )
update toupdate
     from toupdate join
          (select w.*,
                  row_number() over (order by (select null)) as seqnum,
                  count(*) over () as cnt
           from workers w
          ) w
          on w.seqnum = ( (toupdate.seqnum - 1) % w.cnt) + 1;
person Gordon Linoff    schedule 29.09.2020
comment
Спасибо, Гордон Линофф! Это прекрасно работает, хотя я обнаружил, что первый назначенный рабочий был вторым рабочим, выбранным из Workers. Поскольку они в любом случае просто циклируются, это все еще соответствует спецификации, но ради понимания будущего сопровождающего я вычел 1 из toupdate.seqnum перед тем, как подвергнуть его модулю, и это, похоже, скорректировало его так, чтобы первый назначенный рабочий был выбран первый рабочий: on w.seqnum = ((toupdate.seqnum - 1) % w.cnt) + 1; - person user1876354; 01.10.2020
comment
@user1876354 . . . Это кажется разумным, и ваше предложение - прекрасный способ решить эту проблему. - person Gordon Linoff; 01.10.2020