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

Откуда:
Сообщений: 37
 		Задача состоит в том чтобы проверить отвечает ли рабочий необходимым требованиям. Чтобы было проще объяснить, вот DDL таблиц:
    CREATE TABLE [WorkerSkills](
    	[WorkerId] [bigint] NOT NULL,
    	[SkillName] [varchar](100) NOT NULL
    ) GO
    
    CREATE TABLE [SkillCombinator](
    	[SetId] [int] NOT NULL,
    	[SkillCombinator] [varchar](5) NOT NULL
    ) GO
    
    CREATE TABLE [RequiredSkills](
    	[SetId] [int] NOT NULL,
    	[SkillName] [varchar](100) NOT NULL
    ) GO


вот тестовые данные:
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'A')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'B')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'C')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (2, 'D')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (2, 'X')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (3, 'E')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'A')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'B')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'H')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'I')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'A')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'B')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'C')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'E')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'G')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'H')
    INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'I')
    
    INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (1, 'AND')
    INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (2, 'OR')
    INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (3, 'AND')
    
    INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'A')
    INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'B')
    INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'C')
    INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'D')
    INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'E')
    INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'F')
    INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'G')
    INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'H')


Это значит что есть 3 набора (set), в каждом из которых по три навыка (skill).
    set 1: A and B and C
    set 2: D or E or F
    set 3: G and H and I

И рабочие с навыками:
    worker 1: A, B, C
    worker 2: D, X
    worker 3: E
    worker 4: A, B, H, I
    worker 5: A, B, C, E, G, H, I


Нужно написать функцию в Sql Server 2008, которая принимает параметры WorkerId and SetCombinator и определяет, отвечает ли рабочий необходимым навыкам.

Пример 1:
    WorkerId: 1
    SetCombinator: OR

Это значит что все наборы должны сравниваться с использованием OR, т.е.:
    set 1: A and B and C
    OR
    set 2: D or E or F
    OR
    set 3: G and H and I


Результат должен быть true (1), т.к. рабочий отвечает критериям 1-го набора.

Пример 2:
    WorkerId: 4
    SetCombinator: OR

Результат: false (0).

Пример 3:
    WorkerId: 1
    SetCombinator: AND

Это значит что все наборы должны сравниваться с использованием AND, т.е.:
    set 1: A and B and C
    AND
    set 2: D or E or F
    AND
    set 3: G and H and I


Результат: false, т.к. рабочий отвечает только навыкам 1-го набора, но не 2-го и 3-го.

Пример 4:
    WorkerId: 5
    SetCombinator: AND

Результат: true, т.к. рабочий соответствует критериям всех наборов.

Задачка сложная, по-крайней мере для меня, поэтому буду очень благодарен, если кто-нибудь сможет помочь!
26 янв 14, 12:34    [15470427]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь в написании запроса!  [new]
angelius
Member

Откуда:
Сообщений: 37
 		в тестовых данных не добавилась последняя строка, не нашел как можно редактировать свое сообщение, поэтому пишу здесь:
INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'I')
26 янв 14, 12:43    [15470449]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь в написании запроса!  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
 		Как-то так:
create function fnMatchedToSkillsSet
(
 @WorkerId int,
 @Condition varchar(3)
)
returns table
as
return (
 with x as
 (
  select
   sc.SetId, count(distinct ws.SkillName) as SkillCount
  from
   dbo.SkillCombinator sc join
   dbo.RequiredSkills rs on rs.SetId = sc.SetId left join
   dbo.WorkerSkills ws on ws.WorkerId = @WorkerId and ws.SkillName = rs.SkillName
  group by
   sc.SetId, sc.SkillCombinator
  having
   (sc.SkillCombinator = 'AND' and count(distinct ws.SkillName) = count(*)) or
   (sc.SkillCombinator = 'OR' /*and count(ws.SkillName) > 0*/)
 )
 select
  cast(case when count(*) > 0 then 1 else 0 end as bit) as Result
 from
  x
 where
  SkillCount > 0
 having
  (@Condition = 'AND' and count(*) = (select count(*) from dbo.SkillCombinator)) or
  (@Condition = 'OR')
);
go
26 янв 14, 14:10    [15470620]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь в написании запроса!  [new]
angelius
Member

Откуда:
Сообщений: 37
 		Супер! Красивое решение! Вроде все работает правильно, единственное при @Condition='AND', когда рабочий не соответствует требованиям, ничего не возвращается. Я не силен в CTE, но попробую разобраться и найти эту небольшую проблемку.

Спасибо большое! :)
26 янв 14, 15:04    [15470759]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь в написании запроса!  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
angelius
единственное при @Condition='AND', когда рабочий не соответствует требованиям, ничего не возвращается.
Моя недоработка. Исправленный вариант:
create function fnMatchedToSkillsSet
(
 @WorkerId int,
 @Condition varchar(3)
)
returns table
as
return (
 with x as
 (
  select
   sc.SetId,
   case
    when sc.SkillCombinator = 'AND' and count(distinct ws.SkillName) = count(*) then 1
    when sc.SkillCombinator = 'OR' then 1
    else 0
   end as MatchedToSkillsSet
  from
   dbo.SkillCombinator sc join
   dbo.RequiredSkills rs on rs.SetId = sc.SetId left join
   dbo.WorkerSkills ws on ws.WorkerId = @WorkerId and ws.SkillName = rs.SkillName
  group by
   sc.SetId, sc.SkillCombinator
 )
 select
  case
   when @Condition = 'AND' and sum(MatchedToSkillsSet) = (select count(*) from dbo.SkillCombinator) then 1
   when @Condition = 'OR' then 1
   else 0
  end as Result
 from
  x
);

count(distinct ws.SkillName) можно заменить на count(ws.SkillName) если для таблицы WorkerSkills (WorkerId, SkillName) уникально.
26 янв 14, 15:49    [15470851]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь в написании запроса!  [new]
angelius
Member

Откуда:
Сообщений: 37
 		Теперь стало возвращать "1" для параметров: 4, 'OR'.
26 янв 14, 16:12    [15470906]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь в написании запроса!  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
 
create function fnMatchedToSkillsSet
(
 @WorkerId int,
 @Condition varchar(3)
)
returns table
as
return (
 with x as
 (
  select
   sc.SetId,
   nullif(case
    when sc.SkillCombinator = 'AND' and count(distinct ws.SkillName) = count(*) then count(distinct ws.SkillName)
    when sc.SkillCombinator = 'OR' then count(distinct ws.SkillName)
   end, 0) as SkillsCount
  from
   dbo.SkillCombinator sc join
   dbo.RequiredSkills rs on rs.SetId = sc.SetId left join
   dbo.WorkerSkills ws on ws.WorkerId = @WorkerId and ws.SkillName = rs.SkillName
  group by
   sc.SetId, sc.SkillCombinator
 )
 select
  case
   when @Condition = 'AND' and count(SkillsCount) = (select count(*) from dbo.SkillCombinator) then 1
   when @Condition = 'OR' and count(SkillsCount) > 0 then 1
   else 0
  end as Result
 from
  x
);
26 янв 14, 16:22    [15470926]     Ответить | Цитировать Сообщить модератору
 Re: нужна помощь в написании запроса!  [new]
angelius
Member

Откуда:
Сообщений: 37
 		Отлично! Теперь все работает! Спасибо большое!
26 янв 14, 16:36    [15470953]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить