Работа с множествами в Transact-SQL : Программирование для всех. Языки программирования, программирование баз данных, технологии. Статьи для програмиста скачать бесплатно учебники книги документация форум : Статьи

Автор: Александр Уз (usalex@mail.ru)

Один из недостатков MS SQL Server (по сравнению с ORACLE) – почти полное отсутствие готовых функций для работы с множествами (здесь и далее подразумеваются уникальные множества, в которых нет повторяющихся значений). Чтобы, например, получить разность двух множеств (в ORACLE операция MUNUS), приходится писать довольно сложные SQL выражения. При изменении операции (когда, вместо (A – B) надо выполнить (B – A)) выражение приходится переписывать. А сконструировать выражение для 3 и более множеств – задача совсем не из простых.

Данная статья предлагает «унифицированный» способ операций с множествами. Становится возможным построение операций по единому шаблону, не зависящему от типа выполняемой операции и количества множеств (но не более чем с 64 множествами). Построение выражений для операции со многими множествами становятся легче. Тип операции указывается параметром, не затрагивая само выражение.

Статья состоит из 3 частей:

Обзор операций с множествами
Обзор традиционных приёмов операций над множествами для T-SQL
Предлагаемый автором унифицированный метод работы с множествами

Части 1 и 2 рассчитаны на начинающих.

Часть 1. Операции над множествами.

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

Рассмотрим следующий пример: в Субботу и Воскресенье у Вас были гости:

СБ (Множество А)	ВС (Множество В)
Ольга	Светлана
Пётр	Пётр

(если бы в один день пришли 2 Петра, Вы записали бы, например, так: Пётр_1 и Пётр_2)

Графически некоторые очевидные операции над этими множествами можно представить следующим образом:

Объединение	Пересечение	Разность A-B	Симметричная разность

Все гости, которые приходили на выходных	Гости, которые приходили в СБ и в ВС. Т.е. только те, кто был оба дня подряд	Гости, которые пришли в СБ, но не пришли в ВС	Гости, которые пришли только в один из выходных, но не оба дня подряд
Ольга, Пётр, Светлана	Пётр	Ольга	Ольга, Светлана

Часть 2. Традиционные приёмы работы с множествами для Transact-SQL

Создадим таблицы:

  CREATE TABLE #A(FName varchar(10)Primary key) 
  INSERT #A(FName) values('Olga') 
  INSERT #A(FName) values('Peter') 
  CREATE TABLE #B(FName varchar(10) Primary key) 
  INSERT #B(FName) values('Svetlana')
  INSERT #B(FName) values('Peter')

Объединение:

(точнее было бы сказать слияние)

Для этого есть стандартный оператор SQL – UNION.

Если выполняем

  SELECT FName FROM #A
  UNION ALL
  SELECT FName FROM #B

то получаем элементы всех множеств «слепленные» вместе:

Olga
Peter
Peter
Svetlana

Обратите внимание, что Peter попал в результат дважды.

если выполняем

  SELECT FName FROM #A 
  UNION 
  SELECT FName FROM #B

то в результирующем множестве каждый элемент будет повторен только один раз:

Olga
Peter
Svetlana

Пересечение:

Эту задачу можно выполнить с помощью т.н. JOIN-объединения (не путать с предыдущим объединением), обычного или коррелированного подзапроса:

С помощью JOIN-объединения:

  SELECT #A.FName 
  FROM #A INNER JOIN #B on #A.FName = #B.FName

То же, но с помощью обычного подзапроса:

  SELECT FName 
  FROM #A 
  WHERE FName IN(SELECT FName FROM #B)

То же, но с помощью коррелированного подзапроса:

  SELECT FName FROM #A 
  WHERE EXISTS
  (SELECT * FROM #B WHERE #B.FName = #A.FName)

Примечание:

Коррелированный подзапрос отличается от обычного тем, что он выполняется как подпрограмма, по разу для каждой строки в таблице #A. При этом значение каждой строки #A.FName передаётся в подзапрос как параметр.

Разность

В MS SQL Server2000 нет операции MINUS, как в ORACLE. Поэтому приходится изощряться, чтобы получить тот же результат:

С помощью обычного подзапроса:

  SELECT FName FROM #A 
  WHERE 
  FName NOT IN(SELECT FName FROM #B)

Пояснение: вначале выполняется подзапрос. Затем выполняется основной запрос, который ищет и выводит все строки таблицы #A, отсутствующие в таблице #B.

С помощью коррелированного подзапроса:

  SELECT * FROM #A 
  WHERE 
  NOT EXISTS (SELECT * FROM #B WHERE #B.FName = #A.FName)

Пояснение: Каждая строка из таблицы #A по одной передаётся в подзапрос. Там ищется, есть ли такая запись в таблице #B. Если нет, то соответствующая строка из таблицы #A будет приобщена к результату.

С помощью LEFT OUTER JOIN:

  SELECT #A.FName FROM 
  #A LEFT OUTER JOIN #B ON (#A.FName = #B.FName)
  WHERE
  #B.FName IS NULL

Симметричная разность

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

разность #A минус #B объединить с разностью #B минус #A:

  SELECT FName FROM #A 
  WHERE 
  FName NOT IN(SELECT FName FROM #B)
  UNION
  SELECT FName FROM #B
  WHERE 
  FName NOT IN(SELECT FName FROM #A)

из объединения #A с #B вычесть пересечение #A с #B

Итак, пересечение #A с #B

  SELECT FName FROM #A
  UNION
  SELECT FName FROM #B

Объединение #A с #B

  SELECT #A.FName 
  FROM #A, #B
  WHERE #A.FName = #B.FName

и всё вместе:

  SELECT BIGTAB.FName 
  FROM 
  (SELECT FName FROM #A
  UNION
  SELECT FName FROM #B)BIGTAB
  WHERE 
  FName NOT IN(SELECT #A.FName 
  FROM #A, #B 
  WHERE  
  #A.FName = #B.FName)

Литература для части 2:
Ken Henderson, The Guru’s Guide to Transact-SQL ISBN 0-201-61576-2

Часть 3. Унифицированный метод работы с множествами

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

Присвоим каждому первоначальному множеству (т.е. A и B) бинарный идентификатор как степень 2: 1 для A, 2 для B. Если имеем большее количество множеств, например A,B,C,D то 4 для C и 8 для D и т.д.

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

Унифицированная конструкция приобретает вид:

  SELECT Column1, Column2, и т.д.  FROM 
  ( 
     SELECT Column1, Column2, и т.д., 1 as TAB_ID from TABLE1 
     UNION ALL 
     SELECT Column1, Column2, и т.д., 2 as TAB_ID from TABLE2 
     UNION ALL 
     SELECT Column1, Column2, и т.д., 4 as TAB_ID from TABLE3 
     и т.д.,   
  )SUPERTAB 
  group by Column1, Column2, и т.д. 
  HAVING SUM(TAB_ID) in (перечисление или выражение)

Для удобства работы с перечислением (в операторе HAVING) можно использовать следующую схему:

Для двух множеств	Для трёх множеств

Посмотрите на пример для 3 множеств: Примитивные множества 1,2,4 назовём «первичными», а множества 3,5,6,7 «вторичными», так как они состоят из пересечений «первичных» множеств. Идентификатор таблицы, собственно, не что иное, как обычная битовая маска. Каждая битовая позиция соответствует определённому примитивному первичному множеству.

Все варианты для 4 и более множеств рисунком на плоскости не представить, но схема действий – та же.

Теперь выполним несколько операций по этому способу.

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

На нашем примере (изменённая часть маркирована цветом):

Пересечение:
	SELECT FName FROM ( SELECT FName, 1 as TAB_ID from #A UNION ALL SELECT FName, 2 as TAB_ID from #B )SUPERTAB group by FName HAVING SUM(TAB_ID) in (3)

Разность А – В:
	SELECT FName FROM ( SELECT FName, 1 as TAB_ID from #A UNION ALL SELECT FName, 2 as TAB_ID from #B )SUPERTAB group by FName HAVING SUM(TAB_ID) in (1)

Разность В – А:
	SELECT FName FROM ( SELECT FName, 1 as TAB_ID from #A UNION ALL SELECT FName, 2 as TAB_ID from #B )SUPERTAB group by FName HAVING SUM(TAB_ID) in (2)

Симметричная разность:
	SELECT FName FROM ( SELECT FName, 1 as TAB_ID from #A UNION ALL SELECT FName, 2 as TAB_ID from #B )SUPERTAB group by FName HAVING SUM(TAB_ID) in (1,2)

Теперь несколько операций с 3 множествами:

  CREATE TABLE #T1(Col1 varchar(1), Col2 varchar(1), primary key(col1,Col2)) 
  insert #T1 values('a','a') 
  insert #T1 values('b','b') 
  insert #T1 values('c','c') 
  CREATE TABLE #T2(Col1 varchar(1), Col2 varchar(1), primary key(col1,Col2)) 
  insert #T2 values('b','b') 
  insert #T2 values('c','c') 
  insert #T2 values('d','d') 
  CREATE TABLE #T3(Col1 varchar(1), Col2 varchar(1), primary key(col1,Col2)) 
  insert #T3 values('c','c') 
  insert #T3 values('d','d') 
  insert #T3 values('e','e')

Пересечение A, B и C:

	  SELECT Col1, Col2 FROM 
        ( 
           SELECT Col1, Col2, 1 as TAB_ID from #T1 
           UNION ALL 
           SELECT Col1, Col2, 2 as TAB_ID from #T2 
           UNION ALL 
           SELECT Col1, Col2, 4 as TAB_ID from #T3 
        )SUPERTAB 
        group by Col1,Col2 
        HAVING SUM(TAB_ID) in (7)

Разность Т1 – Т2 – Т3

	  SELECT Col1, Col2 FROM 
        ( 
           SELECT Col1, Col2, 1 as TAB_ID from #T1 
           UNION ALL 
           SELECT Col1, Col2, 2 as TAB_ID from #T2 
           UNION ALL 
           SELECT Col1, Col2, 4 as TAB_ID from #T3 
        )SUPERTAB 
        group by Col1,Col2 
        HAVING SUM(TAB_ID) in (1)

Симметричная разность для T1, T2 и T3

	  SELECT Col1, Col2 FROM 
        ( 
           SELECT Col1, Col2, 1 as TAB_ID from #T1 
           UNION ALL 
           SELECT Col1, Col2, 2 as TAB_ID from #T2 
           UNION ALL 
           SELECT Col1, Col2, 4 as TAB_ID from #T3 
        )SUPERTAB 
        group by Col1,Col2 
        HAVING SUM(TAB_ID) in (1,2,4)

Симметричная разность T1 с T2 объединяется с T3

	  SELECT Col1, Col2 FROM 
        ( 
           SELECT Col1, Col2, 1 as TAB_ID from #T1 
           UNION ALL 
           SELECT Col1, Col2, 2 as TAB_ID from #T2 
           UNION ALL 
           SELECT Col1, Col2, 4 as TAB_ID from #T3 
        )SUPERTAB 
        group by Col1,Col2 
        HAVING SUM(TAB_ID) in (1,2,4,5,6,7)

Заключение

Как Вы заметили, существуют некоторые закономерности.

Пересечению множеств всегда соответствует сумма идентификаторов. Разности - идентификатор вычитаемого. Симметричной разности – перечисление идентификаторов первичных множеств. И т.д. Пользуясь этими закономерностями, можно было бы даже написать некую процедуру, что-то типа калькулятора множеств. Как параметр она принимала бы имена таблиц и выражение, которое надо вычислить.

К достоинствам предлагаемого способа можно отнести его лёгкую «программируемость» в динамическом SQL. Особенно в случаях, когда заранее не известно, какую операцию придётся выполнять. Удобно работать с таблицами, содержащими много столбцов, так как не надо связывать соответствующие столбцы из разных таблиц, как при использовании JOIN.

Производительность способа пока не измерялась. Возможно, она ниже, чем с JOIN. Если кто измерит, пожалуйста, сообщите.

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

Буду раз узнать Ваше мнение.