Если интересно также: Пример приложения MS Access, решающего аналогичную задачу
Пример выполен на базе данных AdventureWorks (см. Примеры БД MS SQL Server)
Пример равномерного (карусельного) распределения клиентов по менеджерам (/задач по исполнителям)
Тест реализации на семи клиентах:
(каждый запуск даёт стабильный равномерный результат)
Реализация равномерности с помощью запроса выдать менеджера по самой ранней дате ModifiedDate, где ModifiedDate -- дата и время назначения менеджеру очередного клиента (точность хранения времени одна сотая секунды)
SELECT TOP 1 @Result = SalesPersonId FROM Sales.SalesPerson
WHERE TerritoryID = @TerritoryID
ORDER BY ModifiedDate
[ModifiedDate] по каждому назначенному менеджеру помечается новым системным CURRENT_TIMESTAMP, при этом вызов WAITFOR DELAY обеспечивает её уникальность
Описание задачи:
USE AdventureWorks
GO
--есть список клиентов и территорий
SELECT CustomerID, TerritoryID FROM [Sales].[Customer]
--для наглядности берём одну территоррию и несколько каких-то клиентов:
WHERE TerritoryID = 4 AND CustomerID IN (3, 4, 5, 6, 21, 23, 24, 25)
--есть список менеджеров и территорий
SELECT TerritoryID, SalesPersonID FROM [Sales].[SalesPerson]
WHERE TerritoryID = 4
--Задача по каждой территории распределить клиентов между менеджерами
--на каждого CustomerID выбрать одного SalesPersonID, который работает на той же территории
--распределение должно быть равномерным
--РЕШЕНИЕ:
USE [AdventureWorks]
GO
CREATE PROCEDURE [dbo].[TestTerritorySalesPersonRoundRobin] --курсором бежит по клиентам и этому клиенту назначает менеджера
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
print 'CustomerID|TerritoryID|SalesPersonID'
declare @CustomerID int
declare @TerritoryID int
DECLARE ids_cursor CURSOR FOR
SELECT CustomerID, TerritoryID FROM Sales.Customer
OPEN ids_cursor
-- выполнить первое считывание и сохранить указанные значения (выполненного Select запроса)
-- FETCH - достать
FETCH NEXT FROM ids_cursor
-- переменные должны идти в том же порядке, в котором идут клонки Select запроса
INTO @CustomerID, @TerritoryID
-- проверить, считалась ли запись: если результат считывания успешный, обработать полученные данные
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SalesPersonID int
select @SalesPersonID = dbo.GetSalesPersonID(@TerritoryID) --GetSalesPersonID находит человека по территории
print cast(@CustomerID as varchar) + '|' + cast(@TerritoryID as varchar) + '|' + cast(@SalesPersonID as varchar)
execute dbo.TouchSalesPerson @SalesPersonID --TouchSalesPerson помечает менеджера как недавно назначенного
-- продолжить пока есть записи в рекордсете
FETCH NEXT FROM ids_cursor
INTO @CustomerID, @TerritoryID
END
-- закрыть рекордсет, освободить ресурсы на сервере
CLOSE ids_cursor
DEALLOCATE ids_cursor
END
USE [AdventureWorks]
GO
CREATE FUNCTION [dbo].[GetSalesPersonID](@TerritoryID as Int)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int
-- Add the T-SQL statements to compute the return value here
SELECT top 1 @Result = SalesPersonId from Sales.SalesPerson
where TerritoryID = @TerritoryID
order by ModifiedDate
-- Return the result of the function
RETURN @Result
END
USE [AdventureWorks]
GO
CREATE PROCEDURE [dbo].[TouchSalesPerson](@SalesPersonID as Int)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
WAITFOR DELAY '00:00:00:002' -- обеспечивает уникальность ModifiedDate (на большой скорости получались одни и те же значения, а это не то, что нужно)
update Sales.SalesPerson set ModifiedDate = CURRENT_TIMESTAMP
where SalesPersonID = @SalesPersonID
END
Запуск по всем клиентам:
USE [AdventureWorks]
GO
execute TestTerritorySalesPersonRoundRobin
Результат: 1 минута 47 секунд для 19 185 записей
Распределено супер-равномерно:
(сводка результата в Excel)
