loading..
Русский    English
03:52
листать

Реляционное деление

Рассмотрим следующую задачу.

Определить производителей, которые выпускают модели всех типов (схема "Компьютерная фирма").

Ключевым словом здесь является "всех", т.е. производитель в таблице Product должен иметь модели каждого типа, т.е. и PC, и Laptop, и Printer.

Как раз для решения подобных задач в реляционную алгебру Коддом была введена специальная операция реляционного деления (DIVIDE BY).

С помощью этой операции наша задача решается очень просто [1]:

  1. Product[maker, type] DIVIDE BY Product[type]

Здесь квадратными скобками обозначается операция взятия проекции на соответствующие атрибуты.

Операция реляционного деления избыточна, т.е. она может быть выражена через другие операции реляционной алгебры. Возможно, поэтому ее нет в языке SQL.

На примере решения сформулированной задачи я хочу показать несколько приемов реализации операции реляционного деления на языке SQL.

Группировка

Если использовать тот факт, что, согласно описанию предметной области, типов продукции всего три, то мы можем выполнить группировку по производителю и подсчитать количество уникальных типов. Затем мы отберем только тех производителей, у которых это число равно трем.

Итак,

Консоль
Выполнить
  1. SELECT maker
  2. FROM Product
  3. GROUP BY maker
  4. HAVING COUNT(DISTINCT type) = 3;

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

Консоль
Выполнить
  1. SELECT maker
  2. FROM Product
  3. GROUP BY maker
  4. HAVING COUNT(DISTINCT type) =
  5.             (SELECT COUNT(DISTINCT type) FROM Product);

Разность

Если взять операцию разности ВСЕХ имеющихся типов моделей и типов у конкретного производителя, то результирующая выборка не должна содержать строк.

Консоль
Выполнить
  1. SELECT DISTINCT maker   
  2. FROM Product Pr1
  3. WHERE 0 = (SELECT COUNT(*) FROM
  4. (SELECT type FROM Product
  5.  EXCEPT
  6.  SELECT type FROM Product Pr2
  7.  WHERE Pr2.maker = Pr1.maker
  8. ) X );

Этот запрос можно написать короче, если воспользоваться тем свойством, что истинностное значение предиката ALL есть TRUE, если подзапрос не возвращает строк:

Консоль
Выполнить
  1. SELECT DISTINCT maker
  2. FROM Product Pr1
  3. WHERE type = ALL
  4. (SELECT type FROM Product
  5.  EXCEPT
  6.  SELECT type FROM Product Pr2
  7.  WHERE Pr2.maker = Pr1.maker
  8. );

Для искомых производителей список типов в предикате ALL будет пуст (предикат равен TRUE). В остальных случаях он будет содержать типы моделей, отсутствующие у производителя из внешнего запроса, поэтому операция сравнения (равенство "=") для всех его моделей даст FALSE.

Существование

Не должно существовать такого типа продукции, которого бы не было у искомого производителя.

Консоль
Выполнить
  1. SELECT DISTINCT maker
  2. FROM Product Pr1
  3. WHERE NOT EXISTS
  4.   (SELECT type
  5.     FROM Product
  6.     WHERE type NOT IN
  7.               (SELECT type
  8.                 FROM Product Pr2
  9.                 WHERE Pr1.maker = Pr2.maker
  10.               )
  11.   );

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

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

Рекомендуемые упражнения: 71

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100