Автор: Александр Уз (usalex@mail.ru)
Один из недостатков MS SQL Server (по сравнению с ORACLE) – почти полное отсутствие готовых функций для работы с множествами (здесь и далее подразумеваются уникальные множества, в которых нет повторяющихся значений). Чтобы, например, получить разность двух множеств (в ORACLE операция MUNUS), приходится писать довольно сложные SQL выражения. При изменении операции (когда, вместо (A – B) надо выполнить (B – A)) выражение приходится переписывать. А сконструировать выражение для 3 и более множеств – задача совсем не из простых.
Данная статья предлагает «унифицированный» способ операций с множествами. Становится возможным построение операций по единому шаблону, не зависящему от типа выполняемой операции и количества множеств (но не более чем с 64 множествами). Построение выражений для операции со многими множествами становятся легче. Тип операции указывается параметром, не затрагивая само выражение.
Статья состоит из 3 частей:
Части 1 и 2 рассчитаны на начинающих.
Вначале договоримся, что понимать под множеством. Можно сказать, это таблица с первичным ключом (из одного или многих столбцов). Нас сейчас интересует только этот ключ. Следовательно, строки в каждой таблице не должны повторяться.
Рассмотрим следующий пример: в Субботу и Воскресенье у Вас были гости:
|
СБ (Множество А) |
ВС (Множество В) |
|
Ольга |
Светлана |
|
Пётр |
Пётр |
(если бы в один день пришли 2 Петра, Вы записали бы, например, так: Пётр_1 и Пётр_2)
Графически некоторые очевидные операции над этими множествами можно представить следующим образом:
| Объединение | Пересечение | Разность A-B | Симметричная разность |
 |
 |
 |
 |
| Все гости, которые приходили на выходных | Гости, которые приходили в СБ и в ВС. Т.е. только те, кто был оба дня подряд | Гости, которые пришли в СБ, но не пришли в ВС | Гости, которые пришли только в один из выходных, но не оба дня подряд |
| Ольга, Пётр, Светлана | Пётр | Ольга | Ольга, Светлана |
Создадим таблицы:
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
Как показано выше, неудобство традиционных методов в том, что для каждой операции с множествами приходится заново конструировать 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. Если кто измерит, пожалуйста, сообщите.
Необходимо ещё раз отметить, что вышеописанные приёмы можно применять только с множествами, где нет повторяющихся значений.
Буду раз узнать Ваше мнение.
|
Программирование для чайников.
|
