Предварительные требования составного курса (один или несколько из a, b, c и либо x, либо y, а также стиль z)

Спасибо всем за вклад, особенно в последние часы щедрости, это было невероятно полезно.

Это дополнительный вопрос к Выбрать курсы, которые полностью удовлетворяют заданному списку предварительных условий, и дополнительно пояснить ситуацию. Определенно рекомендуется прочитать, чтобы лучше понять этот вопрос. (Курсы и предметы — разные сущности, предметы являются только предпосылками для курсов и не обязательно должны быть предпосылками для других предметов — представьте, что школьные предметы ведут к возможным университетским курсам)

У меня есть моя база данных, выложенная как таковая.

   Prerequisite:
   +---------------+---------------+
   |      Id       |     Name      |         (Junction table)
   |---------------|---------------|         CoursePrerequisites:
   |      1        |   Maths       |         +---------------+---------------+
   |      2        |   English     |         |  Course_FK    | Prerequisite_FK
   |      3        |   Art         |         |---------------|---------------|
   |      4        |   Physics     |         |      1        |      1        |
   |      5        |   Psychology  |         |      1        |      2        |
   +-------------------------------+         |      2        |      3        |
                                             |      2        |      5        |
   Course:                                   |      5        |      4        |
   +---------------+---------------+         +---------------v---------------+
   |      Id       |     Name      |
   |---------------|---------------|
   |      1        |   Course1     |
   |      2        |   Course2     |
   |      3        |   Course3     |
   |      4        |   Course4     |
   |      5        |   Course5     |
   +---------------v---------------+

и я использовал следующий запрос:

SELECT Course.id, course.Name, GROUP_CONCAT(DISTINCT Prerequisite.Name) AS 'Prerequisite Name(s)'
FROM Course
  LEFT JOIN CoursePrerequisites ON Course.id = CoursePrerequisites.Course_FK
  LEFT JOIN Prerequisite ON Prerequisite.id = CoursePrerequisites.Prerequisite_FK 
WHERE NOT EXISTS 
  (SELECT 1
    FROM CoursePrerequisites 
    WHERE Course.id = CoursePrerequisites.Course_FK
      AND CoursePrerequisites.Prerequisite_FK NOT IN (SELECT Prerequisite.id FROM Prerequisite Where Name = 'Art' OR Name = 'English' OR Name = 'Psychology''))
GROUP BY Course.id;

Что хорошо работает, чтобы выбрать курсы, которые точно заполнены их предпосылками.

Однако я столкнулся с препятствием, пытаясь организовать базу данных таким образом, чтобы она могла представлять курсы с составными пререквизитами. Например, курс может потребовать изучения английского языка, математики и либо искусства, либо психологии. Другим примером может быть обязательный английский язык и два предмета по физике, психологии, искусству и т. д.

Что было бы подходящим способом структурировать базу данных для обработки этих типов предварительных условий (я пытался выполнить поиск, но ничего не нашел (изменить: нашел это, но бесполезно: Моделирование курсов и предварительных требований в базе данных) и как мне изменить приведенный выше запрос, чтобы он снова возвращал только курсы, для которых выполнены хотя бы предварительные условия?

Для уточнения: учитывая список предметов (из таблицы Prerequisites), я хочу вернуть список курсов, которые будут подходить для этих предметов. В текущей схеме базы данных, учитывая математику, английский язык, искусство и физику, возвращенными курсами должны быть Курс 1 и Курс 5 (а НЕ Курс 2 — у него есть предварительные условия Искусство и Психология, последний из которых не удовлетворяет данным входным данным), как это предусмотрено соединительный стол. Я хочу расширить сложность предварительных условий курса от простого «И» (Курс 1 требует математики И английского языка) до чего-то, что может обрабатывать «ИЛИ» / один из x из набора y (например, для курса 1 теперь требуется английский язык, математика И один или больше по искусству или психологии).

Прогресс Редактировать:

Я думал о расширении соединительной таблицы несколькими дополнительными столбцами для «по крайней мере одного из следующих» и «по крайней мере двух из» и т. д., а также еще одного столбца для «всех» и размещения предварительных условий в структуре туда. Является ли это разумным способом сделать это и каким будет эффективный запрос в MySQL для поиска подходящих курсов с учетом списка предметов?

Прогресс:

Куба Выростек предложил ниже перечислить все комбинации предварительных условий для каждого курса в отдельные наборы. Хотя это сработает, мне нужно сделать это для ~ 6 тыс. строк, каждая со многими перечислениями. Есть ли более эффективный способ сделать это?


mysql sql prerequisites relational-division
person lberezy    schedule 03.02.2014    source источник
comment
Пожалуйста, поясните, что именно вы имеете в виду под только курсами, для которых выполнены хотя бы предварительные требования в свете существования необязательных предварительных требований.   -  person peterm    schedule 04.02.2014
comment
Я добавлю немного больше, чтобы объяснить. Вы случайно не проверили первую ссылку в вопросе? Там уже есть дополнительная информация.   -  person lberezy    schedule 04.02.2014

Ответы (4)


arrow_upward
3
arrow_downward

На мой взгляд, моделирование конъюнкции и дизъюнкции в одной таблице всегда непросто и приводит либо к нарушению нормальной формы, либо к невозможности предсказать, сколько самосоединений необходимо. Насколько я понимаю, ваши предпосылки вообще всегда могут быть выражены как альтернативы союзов. Итак, следующее:

Math AND English AND (Physics1 OR Physics2)

может быть также выражено как:

(Math AND English AND Physics1) OR (Math AND English AND Physics2)

Это приводит к выводу, что вам, вероятно, нужна промежуточная таблица, описывающая наборы предварительных условий. Курс доступен, если успешно завершен любой набор, а набор считается успешным, когда пройдены все предметы в наборе.

Таким образом, структура может выглядеть следующим образом:

   Prerequisite:
   +---------------+---------------+
   |      Id       |     Name      |         
   |---------------|---------------|         PrerequisiteSets:
   |      1        |   Maths       |         +---------------+---------------+
   |      2        |   English     |         |  SetNumber    | Prerequisite_FK
   |      3        |   Art         |         |---------------|---------------|
   |      4        |   Physics     |         |      1        |      1        |
   |      5        |   Psychology  |         |      1        |      2        |
   +-------------------------------+         |      1        |      4        |
                                             |      2        |      1        |
                                             |      2        |      2        |
   Course:                                   |      2        |      5        |
   +---------------+---------------+         +---------------v---------------+
   |      Id       |     Name      |
   |---------------|---------------|
   |      1        |   Course1     |
   |      2        |   Course2     |
   |      3        |   Course3     |
   |      4        |   Course4     |
   |      5        |   Course5     |
   +---------------v---------------+

   CoursePrerequisite:                                
   +---------------+---------------+
   |  Course_FK    |  SetNumber    |
   |---------------|---------------|
   |      5        |       1       |
   |      5        |       2       |
   +---------------v---------------+

Пример Course5 может быть удовлетворен либо SetNumber 1 (математика, английский язык, физика), либо SetNumber 2 (математика, английский язык, психология).

К сожалению, сейчас уже слишком поздно, чтобы помочь вам с точными запросами, но если вам это нужно, я могу расширить свой ответ завтра. Однако удачи! :-)

ИЗМЕНИТЬ

Чтобы генерировать запросы, я бы начал с наблюдения, что конкретный набор соответствует, когда все предварительные условия в наборе являются подмножеством заданных предварительных условий. Это приводит к условию, что количество различных пререквизитов в наборе должно совпадать с количеством пререквизитов в этом наборе, которые также находятся в данном наборе. В основном (предполагая, что SetNumber-Prerequisite_FK является уникальной парой в таблице):

select
  SetNumber,
  count(Prerequisite_FK) as NumberOfRequired,
  sum(case when Prerequisite.Name in ('Math','English','Art') then 1 else 0 end)
    as NumberOfMatching
from PrerequisiteSets
  inner join Prerequisite on PrerequisiteSets.Prerequisite_FK = Prerequisite.ID
group by SetNumber
having
   count(Prerequisite_FK)
   =
   sum(case when Prerequisite.Name in ('Math','English','Art') then 1 else 0 end)

Теперь получение окончательных курсов сводится к получению всех курсов, хотя бы одно заданное число которых найдено в результатах запроса выше. Начиная с этого (определенно можно выразить лучше и оптимизировать с помощью объединений, но общая идея та же):

select Id, Name
from Course
where Id in
  (select Course_FK from CoursePrerequisite
   where SetNumber in
   (
      -- insert query from above (but only first column: SetNumber, skip the two latter)
   ) as MatchingSets
  ) as MatchingCourses
person Kuba Wyrostek    schedule 12.02.2014
comment
Круто, большое спасибо за ваш вклад. Буду очень признателен за любые дополнения к вашему ответу, когда у вас будет время! - person lberezy; 13.02.2014
comment
Ого, это прекрасно, спасибо за объяснение! Я посмотрю, смогу ли я заставить что-нибудь работать завтра (сейчас она опаздывает: P) - person lberezy; 13.02.2014

arrow_upward
1
arrow_downward

Куба Выростек предложил ниже перечислить все комбинации предварительных условий для каждого курса в отдельные наборы. Хотя это сработает, мне нужно сделать это для ~ 6 тыс. строк, каждая со многими перечислениями. Есть ли более эффективный способ сделать это?


Хранение наборов — очевидный выбор, я согласен с Кубой. Но я предлагаю немного другой подход:

prereqs:                     courses:
+------+------------+        +------+------------+
| p_id |   Name     |        | c_id |   Name     |
|------|------------|        |------|------------|
|   1  | Math       |        |   1  | Course1    |
|   2  | English    |        |   2  | Course2    |
|   3  | Art        |        |   3  | Course3    |
|   4  | Physics    |        |   4  | Course4    |
|   5  | Psychology |        |   5  | Course5    |
+------+------------+        +------+------------+

compound_sets:               compound_sets_prereqs:
+-------+-------+-------+    +-------+-------+
| s_id  | c_id  |  cnt  |    | s_id  | p_id  |
|-------|-------|-------|    |-------|-------|
|   1   |   1   |   1   |    |   1   |   1   |
|   2   |   1   |   2   |    |   1   |   2   |
|   3   |   2   |   1   |    |   2   |   3   |
|   4   |   2   |  null |    |   2   |   4   |
|   5   |   3   |  null |    |   2   |   5   |
+-------+-------+-------+    |   3   |   1   |
                             |   3   |   4   |
                             |   4   |   1   |
                             |   4   |   2   |
                             |   5   |   2   |
                             |   5   |   3   |
                             +-------+-------+

В приведенном выше столбце "cnt" хранится минимальное количество необходимых совпадений, значение NULL означает, что должны совпадать все предварительные условия. Итак, в моем примере у нас есть следующие требования:


Курс 1: (математика или английский язык) и (по крайней мере два из предметов искусства, физики и психологии)
Курс 2: (математика или физика) и (математика и английский язык)
Курс 3: английский язык и искусство

Вот SQL:

select t.c_id
     , c.name
  from (  select c_id
               , sets_cnt

               -- flag the set if it meets the requirements
               , case when matched >= min_cnt then 1 else 0 end  flag

            from (  select c.c_id
                         , cs.s_id

                         -- the number of matched prerequisites
                         , count(p.p_id) matched

                         -- if the cnt is null - we need
                         -- to match all prerequisites
                         , coalesce( cnt, count(csp.p_id) ) min_cnt

                          -- the total number of sets the course has
                          , (  select count(1)
                                 from compound_sets t
                                where t.c_id = c.c_id
                            ) sets_cnt

                      from courses c

                      join compound_sets cs
                        on cs.c_id = c.c_id

                      join compound_sets_prereqs csp
                        on cs.s_id = csp.s_id

                      left join (  select p_id
                                     from prereqs p
                                    -- this data comes from the outside
                                    where p.name in ( 'Physics',
                                                      'English',
                                                      'Math',
                                                      'Psychology' )
                                ) p
                        on csp.p_id = p.p_id

                     group by c.c_id, cs.s_id, cs.cnt
                 ) t
       ) t
     , courses c
 where t.c_id = c.c_id 
 group by t.c_id, c.name, sets_cnt

-- check that all sets of this course meet the requirements
having count( case when flag = 1 then 1 else null end ) = sets_cnt
person Dmitry S    schedule 17.02.2014
comment
Это правильный подход, за исключением того, что вы должны (например) свернуть s_id 1 и 4 в один s_id и ссылаться на него в составных наборах. Это уменьшит сложность и позволит запрашивать перекрестные ссылки (например, для курсов, требующих обязательного условия X). - person Kyle Hale; 17.02.2014
comment
Ага, s_id 1 и 4 случайно совпадают, данные в моем примере совершенно случайные. - person Dmitry S; 17.02.2014
comment
Спасибо за ваш вклад, это действительно полезно. Моя следующая задача состоит в том, чтобы разобрать свободно определенные текстовые строки в логическое представление, а затем преобразовать его в DNF, сохраняя каждую группу И в виде набора (я думаю?). например Английский и математика или физика -> (X && (Y || Z)) -> (X &&Y) || (Х && Z) - person lberezy; 17.02.2014

arrow_upward
1
arrow_downward

Это вырезка и вставка из одной из моих учебных лабораторий по расширенному SQL, я надеюсь, что это правильно, я не могу проверить это прямо сейчас, звучит похоже на вашу задачу.

Это просто использование пиццы и начинки, я обычно делаю это перед обедом :-)

CREATE TABLE Pizzas
(Pizza# INTEGER NOT NULL PRIMARY KEY,
 PizzaName VARCHAR(30) NOT NULL UNIQUE
);

INSERT INTO Pizzas VALUES(1, 'Margherita')
;INSERT INTO Pizzas VALUES(2, 'Salami')
;INSERT INTO Pizzas VALUES(3, 'Prosciutto')
;INSERT INTO Pizzas VALUES(4, 'Funghi')
;INSERT INTO Pizzas VALUES(5, 'Hawaii')
;INSERT INTO Pizzas VALUES(6, 'Calzone')
;INSERT INTO Pizzas VALUES(7, 'Quattro Stagioni')
;INSERT INTO Pizzas VALUES(8, 'Marinara')
;INSERT INTO Pizzas VALUES(9, 'Vegetaria')
;INSERT INTO Pizzas VALUES(10, 'Diavola')
;INSERT INTO Pizzas VALUES(11, 'Tonno')
;INSERT INTO Pizzas VALUES(12, 'Primavera')
;INSERT INTO Pizzas VALUES(13, 'Gorgonzola')
;INSERT INTO Pizzas VALUES(14, 'Fantasia')
;INSERT INTO Pizzas VALUES(15, 'Quattro Formaggi')
;INSERT INTO Pizzas VALUES(16, 'Napolitane')
;INSERT INTO Pizzas VALUES(17, 'Duplicato')
;


CREATE TABLE Toppings
(Topping# INTEGER NOT NULL PRIMARY KEY,
 Topping VARCHAR(30) NOT NULL UNIQUE
);

INSERT INTO Toppings VALUES(1, 'Tomatoes')
;INSERT INTO Toppings VALUES(2, 'Mozzarella')
;INSERT INTO Toppings VALUES(3, 'Salami')
;INSERT INTO Toppings VALUES(4, 'Mushrooms')
;INSERT INTO Toppings VALUES(5, 'Chillies')
;INSERT INTO Toppings VALUES(6, 'Pepper')
;INSERT INTO Toppings VALUES(7, 'Onions')
;INSERT INTO Toppings VALUES(8, 'Garlic')
;INSERT INTO Toppings VALUES(9, 'Olives')
;INSERT INTO Toppings VALUES(10, 'Capers')
;INSERT INTO Toppings VALUES(11, 'Tuna')
;INSERT INTO Toppings VALUES(12, 'Squid')
;INSERT INTO Toppings VALUES(13, 'Pineapple')
;INSERT INTO Toppings VALUES(14, 'Spinach')
;INSERT INTO Toppings VALUES(15, 'Scallop')
;INSERT INTO Toppings VALUES(16, 'Ham')
;INSERT INTO Toppings VALUES(17, 'Gorgonzola')
;INSERT INTO Toppings VALUES(18, 'Asparagus')
;INSERT INTO Toppings VALUES(19, 'Fried egg')
;INSERT INTO Toppings VALUES(20, 'Anchovies')
;INSERT INTO Toppings VALUES(21, 'Corn')
;INSERT INTO Toppings VALUES(22, 'Artichock')
;INSERT INTO Toppings VALUES(23, 'Seafood')
;INSERT INTO Toppings VALUES(24, 'Brokkoli')
;INSERT INTO Toppings VALUES(25, 'Anchovis')
;INSERT INTO Toppings VALUES(26, 'Parmesan')
;INSERT INTO Toppings VALUES(27, 'Goat cheese')
;


CREATE TABLE PizzaToppings
(Pizza# INTEGER NOT NULL,
 Topping# INTEGER NOT NULL,
 UNIQUE (Pizza#, Topping#)
) PRIMARY INDEX(Pizza#);

INSERT INTO PizzaToppings VALUES(1, 1)
;INSERT INTO PizzaToppings VALUES(1, 2)
;INSERT INTO PizzaToppings VALUES(2, 1)
;INSERT INTO PizzaToppings VALUES(2, 2)
;INSERT INTO PizzaToppings VALUES(2, 3)
;INSERT INTO PizzaToppings VALUES(3, 1)
;INSERT INTO PizzaToppings VALUES(3, 2)
;INSERT INTO PizzaToppings VALUES(3, 16)
;INSERT INTO PizzaToppings VALUES(4, 1)
;INSERT INTO PizzaToppings VALUES(4, 2)
;INSERT INTO PizzaToppings VALUES(4, 4)
;INSERT INTO PizzaToppings VALUES(5, 1)
;INSERT INTO PizzaToppings VALUES(5, 2)
;INSERT INTO PizzaToppings VALUES(5, 13)
;INSERT INTO PizzaToppings VALUES(5, 16)
;INSERT INTO PizzaToppings VALUES(6, 1)
;INSERT INTO PizzaToppings VALUES(6, 2)
;INSERT INTO PizzaToppings VALUES(6, 4)
;INSERT INTO PizzaToppings VALUES(6, 11)
;INSERT INTO PizzaToppings VALUES(6, 22)
;INSERT INTO PizzaToppings VALUES(7, 1)
;INSERT INTO PizzaToppings VALUES(7, 2)
;INSERT INTO PizzaToppings VALUES(7, 4)
;INSERT INTO PizzaToppings VALUES(7, 6)
;INSERT INTO PizzaToppings VALUES(7, 16)
;INSERT INTO PizzaToppings VALUES(8, 1)
;INSERT INTO PizzaToppings VALUES(8, 2)
;INSERT INTO PizzaToppings VALUES(8, 8)
;INSERT INTO PizzaToppings VALUES(8, 9)
;INSERT INTO PizzaToppings VALUES(8, 12)
;INSERT INTO PizzaToppings VALUES(8, 15)
;INSERT INTO PizzaToppings VALUES(8, 16)
;INSERT INTO PizzaToppings VALUES(8, 23)
;INSERT INTO PizzaToppings VALUES(9, 1)
;INSERT INTO PizzaToppings VALUES(9, 2)
;INSERT INTO PizzaToppings VALUES(9, 5)
;INSERT INTO PizzaToppings VALUES(9, 6)
;INSERT INTO PizzaToppings VALUES(9, 7)
;INSERT INTO PizzaToppings VALUES(9, 8)
;INSERT INTO PizzaToppings VALUES(9, 9)
;INSERT INTO PizzaToppings VALUES(9, 14)
;INSERT INTO PizzaToppings VALUES(9, 18)
;INSERT INTO PizzaToppings VALUES(10, 1)
;INSERT INTO PizzaToppings VALUES(10, 2)
;INSERT INTO PizzaToppings VALUES(10, 5)
;INSERT INTO PizzaToppings VALUES(10, 7)
;INSERT INTO PizzaToppings VALUES(10, 9)
;INSERT INTO PizzaToppings VALUES(10, 10)
;INSERT INTO PizzaToppings VALUES(11, 1)
;INSERT INTO PizzaToppings VALUES(11, 2)
;INSERT INTO PizzaToppings VALUES(11, 7)
;INSERT INTO PizzaToppings VALUES(11, 11)
;INSERT INTO PizzaToppings VALUES(12, 1)
;INSERT INTO PizzaToppings VALUES(12, 2)
;INSERT INTO PizzaToppings VALUES(12, 3)
;INSERT INTO PizzaToppings VALUES(12, 4)
;INSERT INTO PizzaToppings VALUES(13, 1)
;INSERT INTO PizzaToppings VALUES(13, 2)
;INSERT INTO PizzaToppings VALUES(13, 16)
;INSERT INTO PizzaToppings VALUES(13, 17)
;INSERT INTO PizzaToppings VALUES(13, 24)
;INSERT INTO PizzaToppings VALUES(14, 1)
;INSERT INTO PizzaToppings VALUES(14, 2)
;INSERT INTO PizzaToppings VALUES(14, 10)
;INSERT INTO PizzaToppings VALUES(14, 19)
;INSERT INTO PizzaToppings VALUES(14, 20)
;INSERT INTO PizzaToppings VALUES(14, 21)
;INSERT INTO PizzaToppings VALUES(15, 1)
;INSERT INTO PizzaToppings VALUES(15, 2)
;INSERT INTO PizzaToppings VALUES(15, 17)
;INSERT INTO PizzaToppings VALUES(15, 26)
;INSERT INTO PizzaToppings VALUES(15, 27)
;INSERT INTO PizzaToppings VALUES(16, 1)
;INSERT INTO PizzaToppings VALUES(16, 2)
;INSERT INTO PizzaToppings VALUES(16, 4)
;INSERT INTO PizzaToppings VALUES(16, 5)
;INSERT INTO PizzaToppings VALUES(16, 16)
;INSERT INTO PizzaToppings VALUES(17, 1)
;INSERT INTO PizzaToppings VALUES(17, 2)
;INSERT INTO PizzaToppings VALUES(17, 4)
;INSERT INTO PizzaToppings VALUES(17, 6)
;INSERT INTO PizzaToppings VALUES(17, 16)
;


REPLACE VIEW PizzaView AS
SELECT
  P.Pizza#
 ,P.PizzaName
 ,T.Topping
FROM
  Pizzas P
JOIN
  PizzaToppings PT
ON P.Pizza# = PT.Pizza#
JOIN
  Toppings Z ON PT.Topping# = T.Topping#
;



/***
1. Return all pizzas which are a superset of the searched toppings.

*At least* ('tomaten', 'mozzarella', 'salami') and maybe additional toppings:

Salami, Primavera
***/

/*** 1. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
FROM
  PizzaView
WHERE
  Topping IN ('tomatoes', 'mozzarella', 'salami')
GROUP BY 1,2
HAVING COUNT(*) = 3
;


/***
2. Return all pizzas which are a subset of the searched toppings.

*At most* toppings ('tomaten', 'mozzarella', 'salami'), but no other toppings:

Salami, Margherita
***/

/*** 2. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
FROM
  PizzaView
GROUP BY 1,2
HAVING
  SUM(CASE WHEN Topping IN ('tomatoes', 'mozzarella', 'salami') THEN 0 ELSE 1 END) = 0
ORDER BY #Toppings DESC
;


/***
3. Return all pizzas which are a exactly made of the searched toppings.

*All toppings* ('tomaten', 'mozzarella', 'salami'), but no other toppings

Salami
***/

/*** 3. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
FROM
  PizzaView
GROUP BY 1,2
HAVING
  SUM(CASE WHEN Topping IN ('tomatoes', 'mozzarella', 'salami') THEN 1 ELSE -1 END) = 3
ORDER BY #Toppings
;


/***
4. Return all pizzas which are a superset of the searched toppings.

*At least* toppings ('tomaten' and 'mozzarella') and ('olives' or 'capers')

Diavola, Fantasia, Marinara, Vegetaria
***/

/*** 4. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
  ,SUM(CASE WHEN Topping IN ('olives', 'capers') THEN 1 ELSE 0 END) AS #Optional
FROM PizzaView
GROUP BY 1,2
HAVING
  SUM(CASE WHEN Topping IN ('tomatoes', 'mozzarella') THEN 1 ELSE 0 END) = 2
AND
  #Optional >= 1
ORDER BY 4 DESC
;


/***
5. Return all pizzas which are a superset of the searched toppings.

*At least* toppings ('tomatoes' and 'olives') and maybe additional toppings, but no 'capers'

Marinara, Vegetaria
***/

/*** 5. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
FROM
  PizzaView
GROUP BY 1,2
HAVING
  SUM(CASE
        WHEN Topping IN ('tomatoes', 'olives') THEN 1
        WHEN Topping IN ('capers') THEN -1
        ELSE 0
      END) = 2
ORDER BY #Toppings DESC
;




/*** Instead of a list of toppings a table with searched toppings
***/
CREATE SET TABLE searched
(grp INTEGER NOT NULL,
 topping VARCHAR(30) NOT NULL
);

DELETE FROM searched;

INSERT INTO searched VALUES(1,'tomatoes');
INSERT INTO searched VALUES(1,'mozzarella');
INSERT INTO searched VALUES(1,'salami');

/*** 1. ***/
SELECT
  Pizza#
  ,PizzaName
  ,COUNT(*) AS #Toppings
FROM
  PizzaView p
JOIN searched g
ON p.Topping = g.Topping
GROUP BY 1,2
HAVING
  COUNT(*) = (SELECT COUNT(*) FROM searched)
;


/*** 2. ***/
SELECT
  Pizza#
 ,PizzaName
 ,COUNT(*) AS #Toppings
FROM
  PizzaView p
LEFT JOIN searched g
ON p.Topping = g.Topping
GROUP BY 1,2
HAVING
  COUNT(*) = COUNT(g.Topping)
;


/*** 3. ***/
SELECT
  Pizza#
 ,PizzaName
 ,COUNT(*) AS #Toppings
FROM    
  PizzaView p
LEFT JOIN searched g
ON p.Topping = g.Topping
GROUP BY 1,2
HAVING
  SUM(CASE WHEN g.Topping IS NOT NULL THEN 1 ELSE -1 END) 
  = (SELECT COUNT(*) FROM searched)
;

Мне никогда не нужно было делать #4/#5 с этой таблицей searched, но это должно быть возможно, используя приведенную выше логику.

person dnoeth    schedule 17.02.2014

arrow_upward
0
arrow_downward

Я бы смоделировал это немного иначе, чем предложили Куба и Дмитрий, хотя оба предоставили общую основу для написания этого ответа.

Заранее извиняюсь, я собираюсь сломать язык вашей существующей модели, так как я не чувствую, что «Искусство» и «Психология» являются вашими предпосылками. Это просто курсы, которые объединяются вместе и образуют объект Prerequisite. Поэтому я переименовал эту таблицу в Subjects.

Все модели данных могут быть описаны как объекты и отношения, которые можно описать без фактической физической базы данных. В этом случае у вас есть одна сложная сущность, Предпосылка. Как сущность, он представлен предметами внутри него и количеством предметов, требуемых из его набора курсов. Это хорошо сочетается с каталогом курсов, где вы можете сказать для данного курса, что каждое из его предварительных условий находится в одной строке для каждого пререквизита («Искусство, психология - 1 требуется», «Искусство и психология - все обязательные» и т. д. )

Первый вопрос:

  • Является ли количество предметов, требуемых от данного предварительного условия, достаточно уникальным и своеобразным для каждого курса, или «искусство или психология - 1 требуется» применяется к большому количеству курсов? Часто ли он меняется для каждого курса или он относительно статичен?

Если он применяется к большому количеству курсов или является относительно статичным, он должен находиться в таблице «Предварительные условия». Если каждый набор предварительных условий для курса относительно динамичен, он должен находиться в таблице PrerequisiteCourse. Пока я буду исходить из первого.

Фактические темы представляют собой отношения «многие ко многим» (каждая тема может быть частью многих предпосылок, каждая предпосылка может иметь много тем) и должны быть смоделированы в таблице перекрестных ссылок.

Отсюда очевидно, что «Искусство и Психология - выберите 1» и «Искусство и Психология - и то, и другое обязательно» являются уникальными сущностями. Поэтому я бы четко определил каждый возможный набор необходимых предметов, включая количество требуемых предметов.

Prerequisite
----------------------
PrerequisiteID
NumberOfSubjectsRequired

Subject
---------------
SubjectID
Name

PrerequisiteSubject
--------------------
PrequisiteSubjectID
PrerequisiteID
SubjectID

Course
------
CourseID

CoursePrerequisite
------------------
CoursePrerequisiteID
PrerequisiteID
CourseID

Обратите внимание, как это улучшает (если я могу быть настолько смелым) по сравнению с моделью Дмитрия, обеспечивая четкий список комбинаций обязательных предметов и позволяя повторно использовать такие предварительные условия, как «Искусство или психология - выберите 1» во всех курсах. . Это (на мой взгляд, исходя из моего понимания вашей модели данных) правильное моделирование предварительного условия. Рассмотрим сценарий, в котором «Искусство и психология — выберите 1» изменен, чтобы также включить речь для всех курсов. Здесь вы можете вставить одну строку в одно место (таблица PrerequisiteSubject), и она будет применяться ко всем курсам в ней, не нарушая никаких других предварительных условий.

Еще одно преимущество заключается в запросах: для заданного набора предметов получите предварительные требования, которым должен соответствовать данный студент (предположим, что SubjectsTaken — это предметы, которые студент изучил):

  select 
        case when count(1) >= ct then 1 else 0 end as PrerequisiteMet,
        p.PrerequisiteID
    from
        subjectstaken st
        left join [subject] s
            inner join Prerequisitesubject PS
                inner join Prerequisite P
                on PS.prerequisiteid = P.prerequisiteiD
            on S.subjectid = PS.subjectID
        on s.name = st.name
    group by
        p.PrerequisiteID, ct

И затем курсы, которые студент мог пройти:

select 
courseid
from prerequisitesmet  pm
right join 
prerequisitecourse pc
on pc.prerequisiteid = pm.PrerequisiteID
group by
courseid
having sum(prerequisitemet) >= count(1)

В любом случае, все это моделирование действительно зависит от ваших "повторно используемых" сущностей. Такое ощущение, что предварительным условием должна быть повторно используемая сущность, но я могу ошибаться.

person Kyle Hale    schedule 17.02.2014
comment
Спасибо за ваш вклад и внимание к оптимизации повторного использования предварительных требований. Мое приложение будет иметь довольно распространенный основной набор предварительных комбинаций. Однако моя модель нуждается в различии между «Курсом» и «Предметом». Предметы не обязательно должны быть предварительными условиями для других предметов, только для курсов. Подумайте о предметах, которые вы изучаете в средней школе, а затем находите все возможные университетские курсы, подходящие для этих обязательных предметов. - person lberezy; 18.02.2014