с товарами, чтооы получить частное - клиентов, которые купили каждьп! элемент каталога. И, как в случае обычного алгебраического умиожспия, мы можем умножить таблицу-делитель на таблицу-частное (с использованием CROSS JOIN), чтобы получить подмножество таблицы-делп.мого.

Это лучше исследовать на при,\1ере. Ниже приведен пример запроса, выполняющего реляционное деление. В нем используются таблицы customers, orders и items, впервые иредставлеиные в главе 1. Если у вас все еще есть эти таблицы (они должны были быть созданы в базе данных GG TS), вам остается только добавить три записи в таблицу orders, прежде че.м продолжить:

IMSERT orders

VALUES(105.19991111.3.1001.123.45; INSERT orders

VALUES(106.19991127.3.1002,678.90) INSERT orders

VALUES(107.19990101.1.1003.86753.09;

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

SELECT c.LastName.с.FirstName FROM customers с WHERE NOT EXISTS (SELECT * FROM Items i

WHERE NOT EXISTS (SELECT *

FROM items t JOIN orders 0 ON (t. itemiNumber-o, ItemNumoer) WHERE t.ItemNumber=i.ItemNumber AND 0.CustomerNumber=c.CustomerNumber))

LastName FirstName

Doe John

Citizen John

Это может выглядеть немного непонятным, но на са.мом деле не все гак плохо, как может показаться с первого взгляда. Давайте рассмотрим запрос шаг за шаго.м. Такие запросы обычно лучше всего исследовать наоборот, так что начнем с самого вложенного подзапроса. Он связан и с таблицей items, и с таблицей customers. Количество раз, которое он запускается, равно количеству записей в таблице items, умноженному на количество записей в таблице customers. Запрос к items проходит таблицу items, используя подзапрос, чтобы найти товары, которые: а) имеют такой же номер, как и текущая запись в таблице items, и б) имеются в заказах, сделанных текущим клиентом в таблице customers. Каждая запись, соответствующая этим критериям, не включается (с похющью NOT EXISTS). Таким образом, остаются только те записи, которые есть в таблице items, ио отсутствуют в таблице orders. Другими словами, это товары, которые клиент ешс не заказывал. Внешний запрос - SELECT по таблице customers - затем исключает клиентов, для которых запрос по items возвращает записи - то есть всех клиентов с не заказанными товарами.

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

SELECT с.LastName. с.FirstName FROM customers с JOIN

(SELECT CustomerNumber. COUNT(DISTINCT ItemNumber) AS NumOfltems FROM orders

GROUP BY CustomerNumber) о ON (с.CustomerNumber=o.CustomerNumber) WHERE o.NumOfItems=(SELECT COUNT(*) FROM items)

LastName FirstName

Doe John

Citizen John

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

SELECT с.LastName. с.FirstName FROM customers с

WHERE CustomerNumber IN (SELECT CustomerNumber EROM orders GROUP BY CustomerNumber HAVING COUNT(DISTINCT ItemNumber)= (SELECT COUNT(*) FROM items))

LastName FirstName

Doe John

Citizen John ,

В этом варианте подзапрос используется для формирования списка клиентов, для которых общее количество различных заказанных товаров равно количеству товаров в таблице Items - то есть тех, кто заказал по крайней мере одну единицу каждого товара. Мы разумно применяем GROUP BY, чтобы объединить но.мера клиентов для исключения дубликатов и для того, чтобы можно было использовать агрегат COUNT() в предложении HAVING. Заметьте, что хотя подзапрос задействует GROUP BY, он не вычисляет никакие агрегатные значения. Это разрешено и с точки зрения ANSI, и, очевидно, с точки зрения Transact-SQL. Основное предназначение GROUP BY - возможность применить агрегат COUNT() для фильтрации записей, возвращенных подзапросом. HAVING разрешает прямые ссылки на агрегатные функции; WHERE - нет. Вот подход, задействующий простое соединение для решения задачи:

SELECT с.LastName. с.FirstName

FROM customers с JOIN orders о ON (с.CustomerNumber=o.CustomerNumber) JOIN items 1 ON (o,ItemNumber-i.ItemNumber) GROUP BY c.LastName. c.FirstName

HAVING COUNT(DISTINCT o,ItemNumber)=(SELECT COUNT(*) FROM items)

iastNd.-ne F;rstNane

Сое Johr;

Cil-.zen John

При гаком подходе таблицы customers и orders соединяются с применением их столбцов CustomerNumber, затем результируюигее множество урезается, чтобы остались только те клиенты, для которых суммарное количество различных товаров равняется количеству записей в таблице items. И снова мы получае.м клиентов, которые заказали по Kpaiineii мере по одно.му экземпляру каждого товара из таблицы items.

Агрегатные функции

Агрегатные функцтп! cyьvпIpyют данные в столбце в едтн1ствеиное значение. Они могут сум.мировать все данные столбца, а могут подводить групповые итоги для этих данных. Агрегаты, которые су.ммируют па основании группируемых столбцов, также известны как векторные агрегаты.

SQL Server в настоящее вре.мя поддерживает восемь агрегатных фуикцн11; COUNTO, SUI4(), МАХ(), STDDEVO (среднеквадратичное отклопеиие),

STDDEVPO (среднеквадратичное отклонение совокупности), VAR() (дисперсия) и VARP() (дисперсия совокупности). Все из них, кроме COUNT(), автоматически игнорируют значения NULL. Если COUNT() передать название конкретного столбца, то она также будет игнорировать NULL-значения. Вот пример;

CREATE TABLE #testnun (cl int null) INSERT #testnull DEFAULT VALUES INSERT #testnun DEFAULT VALUES SELECT COUNT(*). COUNT(cl) FROM Itestnui)

Warning: Null value eliminated f-om aggregate.

Каждой агрегатной функции .можно иередавагь два параметра: ключевое слово ALL или DISTINCT - тем самым ,мы указываем рассматривать все значения или только уникальные (этот пара.метр опционален, по умолчанию он равен ALL) и название агрегируемого столбца. Вот несколько примеров:

SELECT COUNT(OISTINCT title i0) AS TotalTitles

FROM sales

TotalTitles

SELECT stor id. title id, SUM(qty) AS lotaiSold FROM sales

GROUP BY ston id. title id ORDER BY ston 1d. titlejd

(результаты сокращены)

ston id title id TotaiSoid

6380 BU1032 5

В первом примере используется ключевое слово DISTINCT, чтобы получить количество уникальных значений titlejd в таблице. Поскольку записи в таблице sales представляют собой индивидуальные покупки, одинаковые значения titlejd будут существовать по определению. Если включить ключевое слово DISTINCT, они будут проигнорированы при подсчете количества значений в столбце. Обратите внимание на то, что агрегаты с DISTINCT нельзя прн.менять при использовании операторов CUBE или ROLLUP.

Во втором примере вычисляется векторный агрегат с использованием столбцов storjd и titlejd. Другими словами, значение SUM() в каждой строке результирующего множества отражает сумму для конкретной комбинации storjd/titlejd. Так как в агрегате не были указаны ни ALL, пи DISTINCT, при агрегировании рассматриваются все записи в каждой группе.

Благодаря подзапроса.м, агрегатные функции могут применяться почти везде в операторе SELECT и могут также задействоваться в операторах INSERT, UPDATE и DELETE. Вот пример, показывающий использование агрегатных функций в предложении WHERE оператора SELECT, для ограничения возвращаемых записей:

SELECT t.title FROM titles t

WHERE (SELECT C0lJNT(s.titie 1d) FROM sales s WHERE s.t1tle idt,t1tlejd)>] title

Is Anger the Enemy?

The Busy Executives Database Guide

The Gourmet Microwave

Ha агрегат можно ссылаться в списке SELECT напрямую, с помощью ссылки на столбец (как было показано в предыдупп^х прн.мерах), или косвенно, с помощью подзапроса. Вот пример обоих типов ссылок: ,

SELECT stor id. COUNT (DISTINCT titlejd) AS titles sold.

100*CAST(COUNT(DISTINCT t1tle id) AS float) / (SELECT COUNT(*) EROM titles) AS percent of tota ! FROM sales GROUP BY stor id

stor id titles sold TotalSold

6380 2 11.1111111Ш11Ш

7066 2 11.111111111111111

7067 4 22,222222222222221 7131 6 33.333333333333336 7896 3 16.666666666666668 8042 5 27 777777777777779

Здесь COUNT(DISTINCT titlejd) представляет собой нря.мую ссылку, тогда как SELECT COUNT(*) - косвенную. Как и в нескольких других примерах, первый агрегат возвращает количество уникальных названий (titles) в таблице sales. Второй агрегат заключен в несвязагщый подзапрос. Он возвращает полное количество названий в таблице titles, чтобы запрос .мог вычислить процент книг, проданных

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

Агрегаты можно также задействовать в предложении HAVING запроса. Когда запрос имеет предложение HAVING, скорее всего он содержит агрегаты. Вот пример:

SELECT stor id. COUNT(DISTINCT titlejd) AS titles sold,

100*CAST(COUNT(OISTINCT title id) AS float) / (SELECT COUNT(*) FROM titles) AS percent of Jotal FROM sales GROUP BY stor id

HAVING COUNT(DISTINCT titlejd) > 2

stor id titles sold TotalSold

7067 4 22.222222222222221

7131 6 33,333333333333336

7896 3 16,666666666666668

8042 5 27.777777777777779

Это просто перефразировка предыдущего запроса, .мы добавили к нему предложение HAVING. HAVING фильтрует результирующее множество, так же как WHERE фильтрует сам оператор SELECT. Агрегат1Л обычно используются в предложении HAVING, так как их значения еще не вычислены и не доступны после обработки WHERE.

GROUP BY и HAVING

Предложения GROUP BY и HAVING тесно связаны с агрегатными функциями. GROUP BY разбивает таблтщу на группы, и каждая группа имеет свои агрегатные значения. Как я сказал ранее, HAVING офаничивает группы, возвращаемые GROUP BY. Любые столбцы, за исключением bit, text, ntext и image, могут при.меняться в предложении GROUP BY. Чтобы получить группы внутри групп, просто укажите более одного столбца. Вот простой пример, использующий GROUP BY:

SELECT st,stor name, t.type, SUM(s.qty) AS TotalSold FROM sales s JOIN titles t ON (s,title id=t,titlejd) JOIN stores St ON (s,storJd=st.stor id) GROUP BY st.stor name, t.type ORDER BY st.stor name, t,type

ветствуют критерию запроса. Агрегатные значения для групп, не соответствующих условия.м поиска запроса, возвращаются как NULL:

SELECT st.stor name. t.type. SUM(s.qty) AS TotalSold

FROM sales s JOIN titles t ON (s.title id=t.titlejd)

JOIN stores St ON (s.storJd=st.storjd)

WHERE t.type=business

GROUP BY ALL st.stor name. t.type

ORDER BY $t.stor name. t.type

GROUP BY ALL нельзя использовать совместно с операторами ROLLUP и CUBE и удаленны.ми таблицами. Он также перекрывается HAVING, как вы, наверное, и ожидали, так же как и обычный GROUP BY, HAVING фильтрует результаты GROUP BY.

Обратите внимание на применение ORDER BY в предыдущем примере. Вы больше не можете предполагать, что группы, полученные с помощью GROUP BY, будут отсортированы в соответствии с некоторым порядком. Это поведение отличается от поведения SQL Server версии 6.5 и более ранних, так что будьте осторожны. Если вам необходим конкретный порядок, используйте ORDER BY, чтобы гарантировать его. Хотя GROUP BY и HAVING обычно применяются совместно с агрегатами, их наличие не требуется. Если задействовать GROUP BY без агрегатов, из данных будут удалены дублирующиеся значения. Того же самого эффекта можно добиться, если указать DISTINCT перед групгшруе.мыми столбцами в списке SELECT, и фактически SQL Server обрабатывает запросы с GROUP BY без агрегатов так же, как обычные запросы с DISTINCT. Это означает, что для этих двух запросов будет сгенерирован одинаковый план выполнения:

SELECT s.title id

FROM sales s

GROUP BY s.titlejd

SELECT DISTINCT s.title id

FROM sales s

(Чтобы просмотреть план выполпепия в Query Analyzer, нажмите сочетание клавиш Qrl+K или выберите Show Execution Plan из меню Query перед выполнением запроса.)

Как мы обнаружили в разделе, посвящепно.м реляционно.му делению, иред-ложение GROUP BY без агрегатных функции! используется для .моделировантиг запросов SELECT DISTINCT. Если включить в запрос предложение GROUP BY, даже без агрегатов. .\южио отфтпьтровать результирующее множество, используя агрегат напрямую. В отличие от предложения WHERE, в предложении HAVING можно применять агрегаты без включения их в подзапрос. Один из ири.меров реляционного деления, приведепиыр! выше, использует этот факт для фильтрации загшсей возвраще1П1ЫХ подзапросом с по.люшью агрегата в предложении HAVING.

Сводные таблицы

Довольно часто возникает необходи.мость преобразовать вертикально расположенные данные в горизонтальные таблицы, которые мож1ю применять в отчетах и пользовательском интерфейсе. Эти таблицы нзвеспш! как сводные, или перекрестные (crosstabulations, cros.s-tabs), и они представляют собой основной элемент любого приложения OLAP (ОпИпе Analytical Processing, оперативная аналитическая обработка), EIS (Executive Information System, информационная система руководителя) или DSS (Decision Support System, система поддержки принятия решений),

SQL Server включает широкий набор инструментов для гюддержки OLAP, описание которых выходит за рамки это11 книги. Установите OLAP Services с компакт-диска SQL Server и почитайте документацию продукта для получения более подробной информации. Однако задача преобразования вертикальных данных хорошо соответствует теме этой книги и довольно просто реализуется на Transact-SQL. Предположи.м, что у нас есть таблица с квартальны.ми обьема.ми продаж:

CREATE TABLE Icrosstab iyr int, qtr int, sales money)

INSERT #crosstab VALUES (1999, 1, 44)

INSERl #crosstab VALUES (1999, 2, 50)

INSERT #crosstab VALUES (1999, 3, 52)

INSERT #crosstab VALUES (1999, 4, 49)

INSERT #crosstab VALUES (2000, 1, 50)

INSERT #crosstab VALUES (2000, 2, 51)

INSERT Icrosstab VALUES (2000, 3. 48)

INSERT #crosstab VALUES (2000. 4. 45)

INSERT #crosstab VALUES (2001. 1. 46)

INSERT #crosstab VALUES (2001. 2. 53)

INSERT #crosstab VALUES (2001. 3, 54)

INSERT #crosstab VALUES (2001, 4, 47)

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

SELECT

уг AS Year,

SUM(CASE qtr WHEN 1 THEN sales ELSE NULL END) AS 01, SUM(CASE qtr WHEN 2 THEN sales ELSE NULL END) AS 02, SUM(CASE qtr WHEN 3 THEN sales ELSE NULL END) AS 03,

Обратите внимание на го, что нет необходимости суммировать столбцы Qn, чтобы получить сумму за год. Запрос уже группирует данные по столбцу уг; все, что необходимо сделать, чтобы получить сумму за год, - добавить простой агрегат. В этом случае нет необходимости применять подзапрос, производную таблицу или другую экзотическую конструкцию, если только в таблице sales не существует записей, не попадающих в 1-4-й кварталы, чего и не должно быть. За счет наличия столбца qtr создать запрос оказалось очень просто, даже слишком просто. На практике данные очень редко содержат столбец, отражающий квартал, чаще всего временные измерения создаются, исходя из дат члетюв последовательности. Вот пример, иллюстрирующий это, в нем используется таблица Orders базы данных Northwind. Столбец OrderDate каждого заказа преобра-.зуется в соответствующую временную границу:

SELECT

OATEPARTCyy.OrderDate) AS Year.

COUNTCCASE OATEPARTCqq.OrderDate) WHEN 1 THEN 1 ELSE NULL END) AS Ql. COUNTCCASE OATEPARTCqq.OrderDate) WHEN 2 iHEN 1 ELSE NULL END) AS Q2. COUNTCCASE DATEPARTCqq.OrderDate) WHEN 3 THEN 1 ELSE NULL END) AS 03, COUNTtCASE OATEPARTCqq.OrderDate) WHEN 4 THEN 1 ELSE NULL END) AS Q4. COUNT(*) AS TotalNumberOfSales FROM Orders

GROUP BY OATEPARTCyy.OrderDate) ORDER BY 1

Этот запрос возвращает количество заказов за каждый квартал и за каждый год. Здесь используется функция DATEPART() для выделения элементов дат. Запрос проходит таблицу Orders, и функция CASE вычисляет для каждого значения OrderDate квартал, в который оно попадает, и возвращает 1 - если заказ соответствует данному кварталу, или NULL, если нет.

CUBE и ROLLUP

Операторы CUBE и ROLLUP предложения GROUP BY добавляют в результирующее множество итоговые записи. CUBE создает много.мертгый куб, из.мерения которого определяются столбцами, указанными в предложении GROUP BY. Этот куб - развертка данных таблицы, представляющая собой все возможные ко.мбинации измерений.

SUMCCASE qtr WHEN 4 THEN sales ELSE NUl L END) AS Q4. SUM(sales) AS Total FROM #crosstab GROUP BY yr

Оиерато]) ROLLUP, наоборот, осуществляет иера1)хическое сум.\н1рованне данных. Итоговые записи добавляются в результирующее хиюжество на основании иерархии группируе.мых столбцов, слева направо. Вот пример, использующий оператор ROLLUP для генерации про.межуточиых и су.\ьмарных итоговых записей:

SELECT CASE GROUPING(st.stor ndnie) wHEN С THEN st.stor nanie ELSE ALL END AS Store. CASE GROUPING(t,type) UHEN 0 THEN t.type ELSE ALL TYPES END AS Type, SUK(s,qty) AS TotalSold

FRCM sales s JOIN titles t ON (s,tit:e id=t,title id) JOIN stores st ON (s,stor id=st.sto.r id) GROUP BY 5t.stor name. t.type WITH ROLLUP

Данный запрос имеет несколько примечательных особенностей. Во-первых, обратите внимание на дополнительные записи, которые ROLLUP вставил в результирующее множество. Так как запрос производит группировку на основании столбцов stor name и type, ROLLUP создает итоговые записи сначала для каждой группы stor name (значение ALL TYPES), затем для всего результирующего множества. Функция GROUPINGO служит для создания метки для каждого группи-руе.мого столбца. Обычно сгруппированные столбцы возвращаются как NULL Используя GROUPINGO, запрос может преобразовать NULL к че.му-нибудь более осмысленному. Вот тот же самый запрос, на этот раз с применением CUBE:

SELECT CASE GROUPlNGCst.stor name) WHEN 0 THEN st.stor name ELSE ALL END AS Store. CASE GROUPING(t.type) WHEN 0 THEN t.type ELSE ALL TYPES END AS Type. -SUM(s.qty) AS TotalSold

FROM sales s JOIN titles t ON (s.title id=t.title id) JOIN stores st ON (s.stor id=st.stor id) GROUP BY st.stor name, t.type WITH CUBE

Store Type TotalSold

Barnums popular comp 50

Barnums psychology 75

Barnums ALL TYPES 125

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

Без детального знания ваших данных практически невозможно узнать, сколько записей вернет CUBE. Однако очень просто вычислить верхний предел количества возможных записей. Он равен взаимному произведению количества уникальных значений +1 для каждого столбца группировки. необходимо для записей итогов ALL, генерируемых для каждого атрибута. В нашем случае мы имеем шесть различных магазинов и шесть различных типов книг в таблице sales. Это означает, что .максимальное количество записей, которые вернет CUBE, - сорок девять ((6 -F 1) x (6 -F 1)). В нашем случае записей м'еньше, так как не каждый магазин продал каждый тип книги. Также обратите внимание, что CUBE не генерирует нулевые промежуточные итоги для типов книг, которые данный магазин не продавал. Эти данные могли бы быть полезны, чтобы мы могли увидеть, что продает магазин, а что - нет. Такой полный куб позволяет получить более однородное результирующее множество, по которому проще строить отчеты и диаграммы. Вот версия последнего запроса, создающая полный куб:

SELECT

CASE GROUPING(st.stor name) WHEN О THEN st.stor name ELSE ALL END AS Store, CASE GROUP ING(S,type) WHEN 0 THEN S,type ELSE ALL TYPES END AS Type, SUM(s,qty) AS TotalSold FROM

(SELECT DISTINCT st,stor 1d, t,type, 0 AS qty FROM stores St CROSS JOIN titles t UNION ALL

SELECT s.stor Td, t,type, s.qty FROM sales s JOIN titles t

ON s.t1t!ejd=t.title id) s JOIN stores St ON (s.stor id=st.storjd) GROUP BY st.stor name, s.type WITH CUBE