Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Получение всех перестановок  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
Добрый день.
Опишу ситуацию.

Есть таблица со списком команд
idКоманда
1Команда А


Есть справочник профессий
idПрофессия
1Слесарь
2Секретарь
3Таксист
4DBA


В каждой команде есть набор позиций. Профессии, которые нужны в этой команде.
idкомандапрофессия
111
214


Так же есть справочник специалистов
idспециалист
1Петров
2Сидоров
3Анатолий


И справочник специальностей специалистов
idспециалистпрофессия
111
224
334


А на выходе хочется получить варианты комплектации команды.
что-то типа следующего варианта.
idкомандаidкомплектапозицияспециалист
11111
21122
31211
41223

То есть нашу команду можно укомплектовать двумя способами.
1)Слесарь Петров и DBA Сидоров
2)Слесарь Петров и DBA Анатолий

+ Здесь можно посмотреть на скрипт для объявления таблиц

GO
CREATE TABLE [dbo].[Teams](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Professions](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Positions](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[teamId] [int] NOT NULL,
	[professionId] [int] NOT NULL
) ON [PRIMARY]


GO
CREATE TABLE [dbo].[Experts](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Specialties](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[expertId] [int] NOT NULL,
	[professionId] [int] NOT NULL
) ON [PRIMARY]


+ А здесь на скрипт для создания данных

INSERT INTO [Teams]([name]) VALUES ('Команда А')

INSERT INTO [Professions]([name])
	  select 'Слесарь'
union select 'Секретарь'
union select 'Таксист'
union select 'DBA'

INSERT INTO [Positions]([teamId],[professionId]) VALUES (1,1)
INSERT INTO [Positions]([teamId],[professionId]) VALUES (1,4)

INSERT INTO [Experts]([name]) VALUES ('Петров')
INSERT INTO [Experts]([name]) VALUES ('Сидоров')
INSERT INTO [Experts]([name]) VALUES ('Анатолий')

INSERT INTO [Specialties]([expertId],[professionId]) VALUES(1,1)
INSERT INTO [Specialties]([expertId],[professionId]) VALUES(2,4)
INSERT INTO [Specialties]([expertId],[professionId]) VALUES(3,4)



Нужно решить задачу, для такой ситуации, когда количество позиций в команде произвольно(не ограничено сверху).
Один человек может участвовать в команде в нескольких ролях.

PS можно не готовое решение, а хотя бы мысль в какую сторону нужно думать.
1 июн 12, 15:39    [12651831]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
*нужно
1 июн 12, 15:42    [12651860]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Ужеж было: 10712567
Возможно будет полезно: поиск великая штука.
1 июн 12, 15:47    [12651906]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
kDnZP, сорри!
Более того это же моя тема(((
1 июн 12, 16:05    [12652046]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
RubinDm
Member

Откуда:
Сообщений: 461
.Anatoly., мне непонятно .. как результат такого запроса может быть использован на практике? Вы собираетесь предлагать пользователю на выбор готовые варианты комплектов команд? Если так, то как вы собираетесь отображать список возможных комплектов команд (чисто с дизайнерской точки зрения)? Я подскажу, как конкретно можно реализовать такой запрос, если Вы сможете убедить меня в том, что в этом есть реальная необходимость.
1 июн 12, 22:47    [12654052]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
RubinDm, это только часть задачи. После построения всех перестановок, необходимо вычислить несколько функций от каждого набора. Например стоимость такой команды.
На основании результата этих функций и каких-то ключевых параметров команды строится, так называемый, однострочный кеш, который и является результатом поисковой выдачи(То что реально отображается пользователю). А уже среди понравившихся/подходящих вариантов можно посмотреть полные составы.

В данный момент, что бы решить такую задачу мне приходится делать следующее.
1)Ограничивать кол-во человек в команде сверху.
2)Разворачивать набор строк(набор позиций для команды) в строку.
3)Делаю много left join
4)Сворачиваю строку в набор строк

Решение конечно работает, но понимаю, что это говнокод все недостатки прекрасно видны.
2 июн 12, 14:02    [12655287]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
.Anatoly., ссылку я так понял не глядели? Там ведь есть скрипт на перестановки в группе без ограничений на кол-во элементов, как часть задачи.
2 июн 12, 14:39    [12655383]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
aleks2
Guest
declare @TP table(команда int, профессия int, [номер пп] int);
declare @SP table(специалист int, профессия int);

declare @T table(вариант int, команда int, [номер пп] int, профессия int, специалист int);

update T set [номер пп] = n
from (select *, row_number() over(partition by команда order by профессия) n from @TP) T;

declare @n int = 1, @rc int;

insert @T (вариант, команда, [номер пп], профессия, специалист)
select row_number() over(partition by TP.команда order by SP.специалист) n
 , TP.команда
 , TP.[номер пп]
 , TP.профессия
 , SP.специалист
from 
	(select * from @TP where [номер пп] = 1) TP 
	inner join
	@SP SP
	on SP.профессия = TP.профессия

set @rc = @@ROWCOUNT;

while @rc <> 0 begin
    
    set @n = @n+1

	insert @T (вариант, команда, [номер пп], профессия, специалист)
	select T.вариант
	 , T.команда
	 , TP.[номер пп]
	 , TP.профессия
	 , SP.специалист
	from 
		(select * from @T where [номер пп] = @n-1) T
		inner join
		(select * from @TP where [номер пп] = @n) TP 
		on T.команда = TP.команда
		inner join
		@SP SP
		on SP.профессия = TP.профессия

    set @rc = @@ROWCOUNT;
	
end;

select * from @T order by команда, вариант, [номер пп]
2 июн 12, 15:20    [12655556]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
kDnZP, я смотрел на ваше решение из поста Вычислить повторяющиеся комбинации чисел.
Безусловно очень интересное, но насколько я могу судить не совсем мой случай. Пожалуйста поправьте, если ошибаюсь.
4 июн 12, 12:33    [12660960]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
user89
Member

Откуда:
Сообщений: 2083
.Anatoly.,
вот еще интересная тема: 9918768
4 июн 12, 12:47    [12661069]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
aleks2, спасибо за ответ. Вы не могли бы дать некоторый комментарий к своем решению?

Правильно ли я понимаю, что данные в таблицу @TP попадают из таблицы Positions, а в таблицу @SP из Specialties.
Вот таким образом
declare @TP table(команда int, профессия int, [номер пп] int null);
insert into @TP(команда, профессия )
select [teamId],[professionId] from [dbo].[Positions]

update T set [номер пп] = n
from (select *, row_number() over(partition by команда order by профессия) n from @TP) T;

declare @SP table(специалист int, профессия int);
insert into @SP
select [expertId], [professionId] from [dbo].[Specialties]


Если да, то на выходе мы имеем
варианткоманда[номер пп]профессияспециалист
111 11
11242
112 43
4 июн 12, 12:49    [12661088]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
.Anatoly.
kDnZP, я смотрел на ваше решение из поста Вычислить повторяющиеся комбинации чисел.
Безусловно очень интересное, но насколько я могу судить не совсем мой случай. Пожалуйста поправьте, если ошибаюсь.

Не ваш случай. И адаптировать под ваш случай не смог.
* Все потому, что не внимательно прочитал условие задачи в этот раз.
** Пока еще думаю, может что-то и надумаю.
4 июн 12, 19:43    [12664069]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
aleks2
Guest
.Anatoly.
aleks2, спасибо за ответ. Вы не могли бы дать некоторый комментарий к своем решению?

Ну... кое-какие недоработки тама есть...
declare @TP table(команда int, профессия int, [номер пп] int);
declare @SP table(специалист int, профессия int);

insert @TP(команда, профессия )
select 1, 1
union all
select 1, 4
union all
select 1, 5
union all
select 2, 4
union all
select 2, 5

insert @SP
select 1, 1
union all
select 2, 4
union all
select 3, 4
union all
select 4, 5

declare @T table(вариант int, команда int, [номер пп] int, профессия int, специалист int);
declare @T1 table(вариант int, команда int, [номер пп] int, профессия int, специалист int, вариант0 int);

update T set [номер пп] = n
from (select *, row_number() over(partition by команда order by профессия) n from @TP) T;

declare @n int, @rc int;

set @n = 1

-- начальные варианты. Надож с чего-то начинать?
insert @T (вариант, команда, [номер пп], профессия, специалист)
select 
   row_number() over(partition by TP.команда order by SP.специалист) n
 , TP.команда
 , TP.[номер пп]
 , TP.профессия
 , SP.специалист
from 
	(select * from @TP where [номер пп] = 1) TP 
	inner join
	@SP SP
	on SP.профессия = TP.профессия


set @rc = @@ROWCOUNT;

while @rc <> 0 begin
    
    set @n = @n+1

    delete @T1

    -- варианты продолжений
	insert @T1 (вариант, команда, [номер пп], профессия, специалист, вариант0)
	select 
       row_number() over(partition by T.команда order by SP.специалист) n
	 , T.команда
	 , TP.[номер пп]
	 , TP.профессия
	 , SP.специалист
     , T.вариант
	from 
		(select * from @T where [номер пп] = @n-1) T
		inner join
		(select * from @TP where [номер пп] = @n) TP 
		on T.команда = TP.команда
		inner join
		@SP SP
		on SP.профессия = TP.профессия

   set @rc = @@ROWCOUNT;
	
   -- размножаем варианты-предшественники
   insert @T(вариант, команда, [номер пп], профессия, специалист)
   select T1.вариант, T.команда, T.[номер пп], T.профессия, T.специалист
	 from @T T inner join (select * from @T1 where вариант > вариант0) T1 on T.вариант=T1.вариант0 and T.команда=T1.команда

   -- дописываем варианты-продолжения
   insert @T(вариант, команда, [номер пп], профессия, специалист)
   select вариант, команда, [номер пп], профессия, специалист from @T1 

end;

select * from @T order by команда, вариант, [номер пп]
5 июн 12, 08:15    [12665371]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
.Anatoly., нужно уточнение. Могут ли быть ситуации:
1. Специалист имеет несколько профессий и
.Anatoly.
Один человек может участвовать в команде в нескольких ролях.

одновременно? Либо только однократно?

2. Возможно ли дублирование профессий в команде? Т.е. типа нужно 2 слесаря. При этом - это должны быть обязательно разные слесари или один человек может закрыть несколько позиций?

* Интересная задача :)
5 июн 12, 14:22    [12668322]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
kDnZP, я не совсем корректно выразился.
1)Давайте рассмотрим более простой случай, когда специалист может иметь только одну профессию. Но может принимать участие в одной команде несколько раз.
2)Одна профессия в команде может встречаться несколько раз. Ее может выполнять один и тот же человек.
5 июн 12, 15:22    [12668928]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
.Anatoly., ну вам более простой случай подходит или нет? Потому как дважды решать задачу я точно не буду. Если выяснится, что что-то не оговорено или забыто, то следующий раз будет уже без меня.

* Хотя я все еще и на счет первого раза не совсем уверен, что решу эту задачу, хоть и думаю уже второй день.
** По пункту №2 - понял.
5 июн 12, 15:34    [12669010]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
kDnZP, подходит!
5 июн 12, 16:02    [12669241]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
.Anatoly.
kDnZP, подходит!

;WITH    cte
          AS (SELECT    ROW_NUMBER() OVER (ORDER BY t.professionId) rn
                       ,t.professionId p
                       ,t.expertId s
              FROM      dbo.Specialties t),
        cnt
          AS (SELECT    COUNT(*) cnt
              FROM      dbo.Specialties),
        rcte
          AS (SELECT    rn
                       ,CAST('[' + LTRIM(STR(p)) + ']' AS VARCHAR(MAX)) p
                       ,CAST('<i>' + LTRIM(STR(s)) + '</i>' AS VARCHAR(MAX)) s
                       ,1 lv
              FROM      cte
              UNION ALL
              SELECT    t.rn
                       ,'[' + LTRIM(STR(t.p)) + ']' + r.p
                       ,'<i>' + LTRIM(STR(t.s)) + '</i>' + r.s
                       ,r.lv + 1
              FROM      rcte r
              JOIN      cte t
              ON        r.rn >= t.rn
              JOIN      cnt t2
              ON        t2.cnt > r.lv),
        cte2
          AS (SELECT    ROW_NUMBER() OVER (ORDER BY lv, p) rn
                       ,p
                       ,CAST(s AS XML) s
                       ,lv
              FROM      rcte),
        cte3
          AS (SELECT DISTINCT
                        p.teamId
                       ,'[' + REPLACE((SELECT p1.professionId AS [data()] FROM dbo.Positions p1 WHERE p1.teamId= p.teamId
                                      FOR
                                       XML PATH('')), ' ', '][') + ']' f
                       ,COUNT(*) OVER () cnt
              FROM                     dbo.Positions p),
			  cte4 AS (
    SELECT  t.teamId
           ,t.v
           ,t1.res expertId
    FROM    (SELECT t2.teamId
                   ,ROW_NUMBER() OVER (ORDER BY t2.teamId, t1.rn) v
                   ,t1.s
             FROM   cte2 t1
             JOIN   cte3 t2
             ON     t1.lv = t2.cnt
                    AND t1.p = t2.f) t
    CROSS APPLY (SELECT x.z.value ('text()[1]', 'int') res FROM s.nodes ('/i') x ( z)) t1
	)
	SELECT t.*, s.professionId FROM cte4 t
	JOIN dbo.Specialties s ON s.expertId=t.expertId

Без рекурсии, а также сворачиваний/разворачиваний строк не смог. Может кто оценит свежим взглядом и оптимизирует... А я устал, надо отдохнуть))), кофейку чтоль выпить.

* В ходе размышлений и решения вашей задачи было выяснено, что мой алгоритм из Вычислить повторяющиеся комбинации чисел. не верен, т.к. дает не все перестановки. Как получить все перестановки без ограничений сверху и без рекурсии я не придумал...
6 июн 12, 12:40    [12673722]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Исправление ошибок. Версия 0.0.0.1B
;WITH    cte
          AS (SELECT    p.teamId t
                       ,s.professionId p
                       ,s.expertId s
                       ,ROW_NUMBER() OVER (PARTITION BY p.teamId ORDER BY p.professionId) rn
              FROM      dbo.Specialties s
              JOIN      (SELECT DISTINCT
                                teamId
                               ,professionId
                         FROM   dbo.Positions) p
              ON        s.professionId = p.professionId),
        cnt
          AS (SELECT    teamId t
                       ,COUNT(*) cnt
              FROM      dbo.Positions
              GROUP BY  teamId),
        rcte
          AS (SELECT    t
                       ,rn
                       ,CAST('[' + LTRIM(STR(p)) + ']' AS VARCHAR(MAX)) p
                       ,CAST('<i>' + LTRIM(STR(s)) + '</i>' AS VARCHAR(MAX)) s
                       ,1 lv
              FROM      cte
              UNION ALL
              SELECT    t.t
                       ,t.rn
                       ,'[' + LTRIM(STR(t.p)) + ']' + r.p
                       ,'<i>' + LTRIM(STR(t.s)) + '</i>' + r.s
                       ,r.lv + 1
              FROM      rcte r
              JOIN      cte t
              ON        r.t = t.t AND r.rn >= t.rn
              JOIN      cnt t2
              ON       r.t = t2.t AND t2.cnt > r.lv),
        cte2
          AS (SELECT    t,ROW_NUMBER() OVER (ORDER BY lv, p) rn
                       ,p
                       ,CAST(s AS XML) s
                       ,lv
              FROM      rcte),
        cte3
          AS (SELECT DISTINCT
                        p.teamId t
                       ,'[' + REPLACE((SELECT   p1.professionId AS [data()]
                                       FROM     dbo.Positions p1
                                       WHERE    p1.teamId = p.teamId
                                       ORDER BY p1.professionId
                                      FOR
                                       XML PATH('')), ' ', '][') + ']' f
                       ,COUNT(*) OVER (PARTITION BY p.teamId) cnt
              FROM                     dbo.Positions p),
        cte4
          AS (SELECT    t.t
                       ,t.v
                       ,t1.res expertId
              FROM      (SELECT t2.t
                               ,ROW_NUMBER() OVER (ORDER BY t2.t, t1.rn) v
                               ,t1.s
                         FROM   cte2 t1
                         JOIN   cte3 t2
                         ON     t1.t = t2.t AND t1.lv = t2.cnt
                                AND t1.p = t2.f) t
              CROSS APPLY (SELECT x.z.value ('text()[1]', 'int') res FROM s.nodes ('/i') x ( z)) t1)
	SELECT t.*, s.professionId FROM cte4 t
	JOIN dbo.Specialties s ON s.expertId=t.expertId
	ORDER BY t.t, t.v, t.expertId, s.professionId
6 июн 12, 13:45    [12674285]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
aleks2
Guest
kDnZP
Исправление ошибок. Версия 0.0.0.1B


Не смешите.
6 июн 12, 15:13    [12675283]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
Большое всем спасибо за ответы!

Я тоже не просто сидел и ждал - нашел свой способ)
Правда еще не тестировал, чей способ лучше на больших массивах данных.
--Считаем кол-во вариантов для каждой позиции
;with [CountsOfSpesialist] as (
	select 
		 [Teams].[id] [teamId]
		,[Positions].[id] [positionId]
		,[Specialties].[professionId]
		,count(*) [countOfSpesialist]
	from Teams
	inner join Positions
	on Teams.Id = Positions.TeamId
	inner join dbo.Specialties Specialties
	on Positions.professionId = Specialties.[professionId]
	group by Teams.id, Positions.[id], [Specialties].[professionId]
)
--Считаем общее кол-во вариантов для команды. перемножая кол-во вариантов для каждой позиции
, [CountAllVariants4Team] as (
	select [teamId]
	,CAST(ROUND(
		POWER(exp(1),SUM(LOG ([countOfSpesialist]))
	),0) as int) [AllVariants4Team]
	--На всякий случай округляем и кастуем
	--Мои друзья С++ косятся на меня из-за таких конструкций, но хак работает)
	from [CountsOfSpesialist]
	group by [teamId]
)
--Вспомогательная информация.
,[AmountOfSegments] as(
	select 
		 [cos1].[teamId]
		,[cos1].[positionId]
		,[cos1].[countOfSpesialist]
		
		,CAST(ROUND(
			POWER(exp(1),SUM(LOG ([cos2].[countOfSpesialist]))
		),0) as int) [amount] 
	from [CountsOfSpesialist] [cos1]
	inner join [CountsOfSpesialist] [cos2]
	 on [cos1].[teamId] = [cos2].[teamId]
	and [cos1].[professionId]>=[cos2].[professionId]
	group by 
		 [cos1].[teamId]
		,[cos1].[positionId]
		,[cos1].[countOfSpesialist]
)
--Упорядочиваем специалистов
, [OrderExperts] as (
	select
		 [Teams].[id] [teamId]
		,Specialties.[professionId]
		,[Experts].*
		,ROW_NUMBER() OVER(PARTITION BY [Teams].[id],Specialties.[professionId] ORDER BY [Experts].[id])-1 [order]
	from Teams
	inner join Positions
	on Teams.Id = Positions.TeamId
	inner join dbo.Specialties Specialties
	on Positions.professionId = Specialties.[professionId]
	inner join [CountsOfSpesialist]
	 on [CountsOfSpesialist].[teamId] = Teams.id
	and [CountsOfSpesialist].[positionId] = Positions.id
	inner join [Experts] 
	on [Experts].[id] = Specialties.[expertId]
)

select 
	 [CountAllVariants4Team].[teamId]
	,[AllVariants4Team]
	,[number] [group]
	,[Positions].*
	,[Professions].[name]
	,[OrderExperts].[id]
	,[OrderExperts].[name]

        --Все поля идущие дальше это вспомогательные значения
	,[CountsOfSpesialist].[countOfSpesialist]

	,[AmountOfSegments].[amount]

	,[OrderExperts].[order]
	,(AllVariants4Team/amount) [Длина отрезка]
	
	
	,CEILING(([number]*1.0)/(AllVariants4Team/amount)) [отрезок]
	,(CEILING(([number]*1.0)/(AllVariants4Team/amount)) % [CountsOfSpesialist].[countOfSpesialist])  [Вспомогательные значения 1]
	,[order] [Вспомогательные значения 2]
	
 -- Пока используем эту таблицу, но можно свою соорудить, что бы в master не лазить.
 -- Так же надо понимать, что мы ограничены числом 2048
from [master].[dbo].[spt_values]

inner join [CountAllVariants4Team]
on [AllVariants4Team] >= [number]
inner join [Positions] [Positions]
on [Positions].[teamId] = [CountAllVariants4Team].[teamId]
inner join dbo.Professions Professions
on Professions.[id] = [Positions].[professionId]
inner join [CountsOfSpesialist]
on [CountAllVariants4Team].[teamId] = [CountsOfSpesialist].[teamId]
and [Positions].[id] = [CountsOfSpesialist].[positionId]

inner join [AmountOfSegments]
 on [AmountOfSegments].[teamId] = [CountAllVariants4Team].[teamId]
and [AmountOfSegments].[positionId] = [Positions].[id]

inner join [OrderExperts]
on  [OrderExperts].[teamId] = [CountAllVariants4Team].[teamId]
and [OrderExperts].[professionId] = [Positions].[professionId]
--Вот эта функция определяет какого специалиста каждый раз нужно брать
and (CEILING(([number]*1.0)/(AllVariants4Team/amount)) % [CountsOfSpesialist].[countOfSpesialist]) = [order]

 where [type] = 'P' and [number] > 0 

order by [number]  ,	[Positions].[id],[OrderExperts].[id]
6 июн 12, 20:16    [12678019]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
.Anatoly., тестировал на таком наборе данных:
TRUNCATE TABLE [dbo].[Teams]
TRUNCATE TABLE [dbo].[Professions]
TRUNCATE TABLE [dbo].[Positions]
TRUNCATE TABLE [dbo].[Experts]
TRUNCATE TABLE [dbo].[Specialties]

INSERT INTO [Teams]([name]) VALUES ('Команда А')
INSERT INTO [Teams]([name]) VALUES ('Команда Б')

INSERT INTO [Professions]([name])
	  select 'Слесарь'
union select 'Секретарь'
union select 'Таксист'
union select 'DBA'

INSERT INTO [Positions]([teamId],[professionId]) VALUES (1,1)
INSERT INTO [Positions]([teamId],[professionId]) VALUES (1,1)
INSERT INTO [Positions]([teamId],[professionId]) VALUES (1,4)
INSERT INTO [Positions]([teamId],[professionId]) VALUES (1,4)

INSERT INTO [Positions]([teamId],[professionId]) VALUES (2,1)
INSERT INTO [Positions]([teamId],[professionId]) VALUES (2,2)
INSERT INTO [Positions]([teamId],[professionId]) VALUES (2,3)
INSERT INTO [Positions]([teamId],[professionId]) VALUES (2,4)

INSERT INTO [Experts]([name]) VALUES ('Петров')
INSERT INTO [Experts]([name]) VALUES ('Сидоров')
INSERT INTO [Experts]([name]) VALUES ('Анатолий')
INSERT INTO [Experts]([name]) VALUES ('Василий')

INSERT INTO [Specialties]([expertId],[professionId]) VALUES(1,1)
INSERT INTO [Specialties]([expertId],[professionId]) VALUES(2,4)
INSERT INTO [Specialties]([expertId],[professionId]) VALUES(3,4)
INSERT INTO [Specialties]([expertId],[professionId]) VALUES(4,3)

Ну и, естественно, для больших наборов скорее всего нужно будет поставить опцию снятия ограничения уровней рекурсии. Еще как вариант оптимизации - генерировать перестановки (а может и другие вспомагательные cte) в отдельную временную таблицу один раз для всех наборов, навесить на нее нужные индексы, а затем работать уже с временной таблицей.
6 июн 12, 23:40    [12678671]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
with x as
(select f from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) t(f)),
Numbers as
(
 select
  row_number() over (order by (select 1)) as n
 from
  x x1, x x2, x x3
),
sp as
(
 select
  professionId, expertId, row_number() over (partition by professionId order by (select 1)) as n
 from
  dbo.Specialities
),
ec as
(
 select
  p.teamId, p.professionId, count(*) as ProfessionExpertsCount
 from
  dbo.Positions p join
  dbo.Specialities s on s.professionId = p.professionId
 group by
  p.teamId, p.professionId
),
tc as
(
 select
  teamId, round(exp(sum(log(ProfessionExpertsCount))), 0) as TeamCount
 from
  ec
 group by
  teamId
),
tv as
(
 select
  n.n, t.teamId, t.professionId, row_number() over (partition by t.teamID, t.professionId order by n.n) % t.ProfessionExpertsCount + 1 as en
 from
  tc join
  ec t on t.teamId = tc.teamId join
  Numbers n on n.n <= tc.TeamCount
 )
select
 tv.n, tv.teamId, tv.professionId, sp.expertId
from
 tv join
 sp on sp.professionId = tv.professionId and sp.n = tv.en
order by
 tv.n, tv.teamId, tv.professionId;
Может чего и не понял из условия задачи...
7 июн 12, 12:51    [12681271]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
invm, все правильно)
Кстати убрал из своего запроса всю воду. и получилось практически тоже самое
+ без воды
;with [CountsOfSpesialist] as (
	select 
		 [Teams].[id] [teamId]
		,[Positions].[id] [positionId]
		,[Specialties].[professionId]
		,count(*) [countOfSpesialist]
	from [Teams] [Teams]
	inner join [Positions]
	on [Teams].[Id] = [Positions].[TeamId]
	inner join [Specialties] [Specialties]
	on [Positions].[professionId] = [Specialties].[professionId]
	group by Teams.id, Positions.[id], [Specialties].[professionId]
)
, [CountAllVariants4Team] as (
	select [teamId]
	,CAST(ROUND(exp(SUM(LOG ([countOfSpesialist]))),0) as int) [AllVariants4Team]
	from [CountsOfSpesialist]
	group by [teamId]
)
,[AmountOfSegments] as(
	select 
		 [cos1].[teamId]
		,[cos1].[positionId]
		,[cos1].[countOfSpesialist]
		
		,CAST(ROUND(exp(SUM(LOG ([cos2].[countOfSpesialist]))),0) as int) [amount] 
	from [CountsOfSpesialist] [cos1]
	inner join [CountsOfSpesialist] [cos2]
	 on [cos1].[teamId] = [cos2].[teamId]
	and [cos1].[professionId]>=[cos2].[professionId]
	group by 
		 [cos1].[teamId]
		,[cos1].[positionId]
		,[cos1].[countOfSpesialist]
)
, [OrderExperts] as (
	select
		 [Teams].[id] [teamId]
		,[Specialties].[professionId]
		,[Experts].*
		,ROW_NUMBER() OVER(PARTITION BY [Teams].[id],[Specialties].[professionId] ORDER BY [Experts].[id])-1 [order]
	from Teams
	inner join Positions
	on Teams.Id = Positions.TeamId
	inner join [Specialties] [Specialties]
	on Positions.professionId = Specialties.[professionId]
	inner join [Experts]  [Experts]
	on [Experts].[id] = [Specialties].[expertId]
)

select 
	 row_number() over(
		order by
		 [CountAllVariants4Team].[teamId]
		,[number]
		,[Positions].[id]
		,[OrderExperts].[id]) [id]
	,[CountAllVariants4Team].[teamId]
	,[number] [group]
	,[Positions].[id] [position]
	,[OrderExperts].[id] [expert]
	
from [master].[dbo].[spt_values] -- Пока используем эту таблицу, но можно свою соорудить, что бы в master не лазить.
inner join [CountAllVariants4Team] [CountAllVariants4Team]
on [AllVariants4Team] >= [number]
inner join [Positions] [Positions]
on [Positions].[teamId] = [CountAllVariants4Team].[teamId]

inner join [AmountOfSegments]
 on [AmountOfSegments].[teamId] = [CountAllVariants4Team].[teamId]
and [AmountOfSegments].[positionId] = [Positions].[id]

inner join [OrderExperts] [OrderExperts]
 on  [OrderExperts].[teamId] = [CountAllVariants4Team].[teamId]
and [OrderExperts].[professionId] = [Positions].[professionId]
and (CEILING(([number]*1.0)/([CountAllVariants4Team].[AllVariants4Team]/[AmountOfSegments].[amount])) % [AmountOfSegments].[countOfSpesialist]) = [order]

 where [type] = 'P' and [number] > 0 


order by [number]
		,[Positions].[id]
		,[OrderExperts].[id]



Вот только у вас вот это условие получше записано
row_number() over (partition by t.teamID, t.professionId order by n.n) % t.ProfessionExpertsCount + 1 as en
чем мое
CEILING(([number]*1.0)/([CountAllVariants4Team].[AllVariants4Team]/[AmountOfSegments].[amount])) 
% [AmountOfSegments].[countOfSpesialist]
8 июн 12, 13:30    [12687480]     Ответить | Цитировать Сообщить модератору
 Re: Получение всех перестановок  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
.Anatoly.
invm, все правильно)
Кстати убрал из своего запроса всю воду. и получилось практически тоже самое

Ну вам, конечно, виднее... Хотя как по мне, на тестовом наборе 12678671 я могу объяснить, почему получаются именно такие данные, какие получаются)))... На счет ваших - не уверен...
8 июн 12, 14:54    [12688278]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить