Получение п первых записей

в SQL Server версии 7.0 и более поздних расширение оператора SELECT ТОР п представляет собой наиболее правильны!! способ по.тучеиия заданного ко.ш-чества записе!! из !1ача'!а или конца резу.тьгиру1още! о \!ножества. ТОР п делает как рги то, о че.м говорит ei~o название, - оно 01ранич11вает возвращаемые за-П!!С11 задан1!ы.м ко;!)гчество.м. Так как вы можете сортировать результ!1ру10шее .\!ножество в порядке убывания, ТОР п также .может возвращать зап1!си из конца результирующего .множества. Это расширение работает подобно SET ROWCOUNT, но, кроме того, .может обрабаплвать дополнительные за1И!Си (t!es) i! возвращать процент .записей от их общего 1соличества. За до!10лиителы10Й И1!фор.ма1И1ей обратитесь к разделу SELECT ТОР в !-лаве 6.

Если в!)1 т!спользуеге SQL Server 6.5 1!Л!г более ра!!июю версию или же вам Heo6xoflHN!a больша>1 птбкость, че.м может предоставить SELECT ТОР и, можно расши1)ить код из п])едыдущего при.мсра с отсечением, чтобы он допол!1ительно мог вы!10Л!1ять 1!есколько полез1!Ых функций, например возвращать первые 1!лп последн1!е записи результт!рую1!1е!-о м!!ожества. Очевидно, что надо переделагь его, чтобы О!! возвращал префт!кс или суфф1И<с 1!еобходимого размера. Вот пример кода, котор1лй как раз это i! делает:

SELECT v.cl

FROM #va1ueset v CROSS JOIN #va1ueset a GROUP BY v.ci

HAVING COUNTCCASE WE.N a.cl >=v,cl THEN 1 ELSE NULL END) > COUNTia.cl)-2 cl

Данный код возвращает две первые записи. Как и в предыдущем пр1!.мере вы можете измен!!ть -2, чтобы 3ai!poc возвращад !1еобходи.мое вам количество заш!-сей. Вот тот же са.мый при.мер с изменениям!! для возвращения трех последних saniiceii:

SELECT v.cl

FROM #valueset v CROSS JOIN #va]ueset a GROUP BY v.cl

HAVING COUNTCCASE WHEN a.cl <-=v.cl THEN I ELSE NULL END) > COUNT(a .cl)-3 cl

Эти способы работают, однако и.меют один недостаток -- они не обрабатывают дублирующиеся значения. Есть несколько способов решения данной проблемы. Вот один из них, в котором используется производ1!ая таблица и связанный подзапрос:

CREATE TABLE #valL.eset (cl wi) INSERT #valueset VALUES (2)

INSERT #valueset VALUES (2) -- Дублирующееся значение INSERT #valueset VALUES (1)

INSERT #va1ueset VALUES (3) INSERT #valueseL VALUES (4)

INSERT #vaVjeset VALUES (4) -- Дублирующееся значение INSERT #valueset VALUES (1С) INSERT #valueset VALUES (11) INSERT #va1ueset VALUES (13) SELECT l.cl

FROM (SELECT ranking=(SELECT COUNT(DISTINCT a.cl) FROM #va1ueset a WHERE v.cl >= a.cl).

v.cl

FROM #valueset v) 1 WHERE 1.ranking <=3 ORDER BY 1.ranking

1 2 2 3

Данный метод для сравнения значений из #valueset с ними са,мими ис!и)ль-зует производную таблицу и связанный подзапрос вместо перекрестного соединения. Это, в свою очередь, позволяет избавиться от предложения GROUP BY, из-за которого возникали проблемы с дублируюцигмнся значе1шя.мн. Как я уно.мн-нал ранее, GROUP BY не .может ра.зличить несколько копий одного и того же значения. Когда в группируемом столбце(ах) существуют дублирующиеся значения, GROUP BY объединяет их. Реше1Н1е, поэто.му, состоит в том, чтобы вернуть все записи из #valueset, отфильтрован их по критерию, основанно.му на их ранге среди других значений. Вышеуно.мянутый код применяет нроизгюдную таблицу, чтобы получить список рангов для значений из #valueset. Эта производная таблица использует связанный [юдзапрос, чтобы ранжировать каждое значение в соответствии с количеством других записей в таблице, поскольку он зависит (в данном случае фильтруется) от значений во внещней таблице. (Обратите внимание на использование псевдонима v в запросе SELECT COUNT(DISTINCT query).) Записи, имеющие ранг три или больше (меньше), отсекаются.

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

CREATE TABLE #va1ueset (cl int)

INSERT #valueset VALUES (2)

INSERT #valueset VALUES (2) - Дублирующееся значение

INSERT #valueset VALUES (1)

INSERT #va1ueset VALUES (3)

INSERT #valueset VALUES (4)

INSERT #va1ueset VALUES (4) -- Дублирующееся значение

INSERT #valueset VALUES (11)

INSERT #valueset VALUES (11) -- (Тублирующееся значение

INSERT #va1ueset VALUES (13)

SELECT l.cl

FROM (SELECT ranking=(SELECT COUNT(DISTINCT a.cl) FROM #va1uoset a WHERE v.cl <= a.cl). v.cl

Получение n первых записей 211

FROM Svalueset v) 1 WHERE .ranking <=4 ORDER BY 1.ranking

cl 13

Обратите внимание на то, что все эти запросы разрешают существование дополнительных записей в результирующем лнюжестве, так что вы можете получить больше записей, чем заказывали. Если это нежелательно, примените SELECT ТОР пли SET ROWCOUNT, чтобы ограничить фактическое кол]1чество воз-враи1ае.мых записей, это будет проиллюсгрировано на дальнейших примерах.

SET ROWCOUNT

Другая альтернат]1ва расширению ТОР п - ко.манда SET ROWCOUNT. Она ограничивает количество записей, возвращаемых запросом, так что вы .можете сделать что-то наподобие этого для получения первых занисей из результирующего множества:

SET ROWCOUNT 3

SELECT * FROM #va1ueset ORDER BY cl

SE ROWCOUNT 0 -- Возвоацаем обь:чное значение

Получить n последних занисей .можно также весь.ма просто. Чтобы вместо первых записей пол\чить последние, измените предложение ORDER BY, чтобы результирующее множество сортировалось в порядке убывания. Хотя это решение довольно простое, с по.мощью него нельзя гибко обрабатывать дублирующиеся значения. Вы получите точно три записи, ни больше, ни меньше. Никакой специально обработки дополнительных значений, получающихся из-за дублирующихся значений, не производится. Если вы запросите три записи, и во BTopoii П03ИЦ1И1 будет существовать дополнительное значение, вы на само.м деле не увидите запись, находя1цуюся на третьем месте, - вместо этого вы уврщите в третьей позиции дополнительную запись, которая должна быть на втором месте. Может быть, это то, что вам и требуется, но, если это не так, существует вариант данного запроса, который норматьно обрабатывает дополнительные значения. Этот вариант приии.мает во внимание тот факт, что при присвоении значения переменной запросом, возвращающим более одной записи, переменной будет присвоено значение из последней записи. Этот прием используется редко, так что ва.м лучше написать ко.м.ментарий в это.м .месте кода, который объяснял бы, чего на само.м деле вы хотите эти.м добиться. Вот ко.ч:

CREATE TABLE #valueset (cl int) INSERT Ivalueset VALUES (2)

INSERT Ivalueset VALUES (2) -- Дублирующееся значение INSERT #valueset VALUES (1) INSERT #valueset VALUES (3) INSERT Ivalueset VALUES (4)

INSERT #valueset VALUES (4) -- Дублирующееся значение INSERT #va1ueset VALUES (11)

INSERT Ivalueset VALUES (11) -- Дублирующееся значение INSERT #va1ueset VALUES (13) DECLARE laendcl int

-- Получаем третье отличное значение

SELECT DISTINCT TOP 3 laendcbcl FROM #valueset ORDER BY cl

SELECT * FROM #va1ueset WHERE cl <= (Sendcl ORDER BY cl

Этот запрос использует DISTINCT, чтобы пе дать обмануть себя дублирующим значениям. Без DISTINCT этот запрос не обработал бы дубликаты лучше своих предшественников. Здесь мы хотим присвоить значе1п1е третьего отличрюго значения нашей контролирующей переменной, чтобы затем ограничить возвращаемые записи теми, значения которых меньше или равны значению перемонгой. Так что если среди трех первых значений есть дубликаты, мы их получим. Если их там нет - ничего страпнгого, запрос все равно будет работать, как требуется.

Ранжирование

Задача, близко связанная с получением первых записей, - ранжирование множества данных. Фшстически вы увидите, что одно из решений задачи получения первых записей использует столбец-ранг для определения возвращаемых записей. Вот тот самый запрос со столбцо.м-рангом, включе1Н1ым в список столбцов оператора SELECT;

CREATE TABLE #va1ueset (cl int)

INSERT #valueset VALUES (2)

INSERT #valueset VALUES (2) -- Дублирующееся значение

INSERT #valueset VALUES (1)

INSERT #valueset VALUES (3)

INSERT #valueset VALUES (4)

INSERT #valueset VALUES (4) -- Дублирующееся значение

INSERT #valueset VALUES (11)

INSERT #valueset VALUES (11) ~- Дублирующееся значение

INSERT #va1ueset VALUES (13) SELECT 1.ranking. 1 .cl

FROM (SELECT rank1ng=(SELECT COUNT(DISTINCT a.cl) FROM #valueset a

WHERE v.cl <- a.cl).

v.cl

FROM #valueset v) 1 ORDER BY 1.ranking

ran<;ng

: 13

2 11

Этот запрос не так эффективен, как мог бы бьггь, потому что свя,заиньпг подзапрос выполняется для каждое! записи из #valueset. Вот более эффективный .запрос, которьй'г приводит к то.му же результату:

SELECT Ranking=IDENTITY(int). cl INTO #rankings EROM #valueset

WHERE 1=2 -- Создаем пустую таблицу INSERT #rankings (cl) SELECI cl FROM fvalueset ORDER BY cl DESC

SELECT * FROM #rankings ORDER BY Rarking DROP TABLE #rankings Ranking cl

1 13

3 11

5 4 6 3

Обратите внимание иа использование SELECT...INTO для создания временной рабочей таблицы. В этом операторе нрн.меияется функция IDENTITY() для создания таблицы en passant вместо явного создания с помощью CREATE TABLE. Хотя в этом случае CREATE TABLE синтаксически более компактна, я считаю поучительным от.метить, как просто можно со.здавать рабочие таблицы с помощью SELECT... INTO.

Вслед за SELECT...INTO мы используем INSERT, чтобы заполнить таблицу данны.ми. Почему бы не осуществить две onepauini за одни проход? То есть почему SELECT...INTO не помещает данные в таблицу #rankings в то же время, когда создает ее? На то есть две причины. Во-первых, SELECT...INTO - это спеттальпая нежурналируемая операция, которая блокирует системные таблицы во время выпол11ения, поэтому запускать такую операцию, которая к тб.му же может выполняться довольно долго, плохая идея. В случае базы данных tempdb вы заблокируете других пользователей, создающих временные таблицы, поэто.му вам не

еп passant (франц.) - .nui.moxcwo.nt ~ Примеч. перев.

избежать ругательств в свой адрес. Во-вторых, SQL Server не будет в этом случае работать, как ожидается, - он будет раздавать icientity-значения в соответствии с естественным порядком таблицы #valueset, а не в соответствии с порядком, определенным в предложении ORDER BY запроса. Так что, даже если не заботиться о блокировке системных таблиц, эта аномалия с упорядочиванием записей SQL Server в любом случае не позволит нам объединить эти два шага в один.

Этот запрос не обрабатывает дополнительные значения, как вы, наверное, и предполагали. Поскольку элементы в таблице #ranklngs пронумерованы последовательно, значения, которые на самом деле являются дубликатами (а значит, являются дополнительными значениями), перечислены в последовательности, как если бы дополнительных значений не было. Если вы ограничите возвращаемые записи некоторой верхней частью списка и в ней окажутся повторяющиеся значения, вы получите не те результаты, которые ожидали. Например, если вы запросите четыре записи, а для второй будет дополнительное значение, вы увидите только первую запись, сопровождаемую двумя одинаковыми записями, и еще одну - третью запись. Фактически вы не получите запись, занимающую третье место. Так как нет способа узнать, сколько дополнительных значений существует, получение первых четырех рангов множества более трудная задача, чем могла бы быть, но довольно просто можно изменить запрос, чтобы он ранжировал записи более разумно. Вот пример:

SELECT Ranking>IDENT!TY(int). cl INTO #rankings EROM #va1ueset

WHERE 1=2 -- Создаем пустую таблицу

INSERT #rankings (cl)

SELECT cl

FROM #va1ueset

ORDER BY cl DESC

SELECT a.Ranking, r.cl

FROM

(SELECT Rank1ng=M!N(n.Ranking), n.cl FROM #rankings n GROUP BY n.cl) a.

#rankings r

WHERE r.Cl=a.cl ORDER BY a.ranking DROP TABLE #rankings

Ranking cl

1 13

2 11 2 11 4 4 4 4

7 2 9 1

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

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

SELECT а.Ranking, Ties=CAST(LEFT(CASr(a.NumWithVa;ue AS varchar)--Way tie,

NULLIF(a,Num,v1thV3;Lie,l)*ll) AS CHAR(ll)), r.cl

FROM

(SELECT Ranking=MIN(n,Ranking), Kur:)WithValue=CCUNT(*), n,cl FROM

frankings n

GROUP BY n,cl) a,

#nankings r WHERE r,cl=a.cl ORDER BY a.ranking DROP TABLE #rankings

Существует три 0С1ювных способа представле1И1я срединного значения для некоторой выборки: медианы, средние значения и .моды. Мы уже поговорили о медианах и средних значениях, так что давайте посмотрим, как рассчитать .моду Nn-южества значений. Мода выборки - наиболее частое значение в выборке, независимо от того, где оно физически появляется в ней. Если у вас есть такое .чпюжество значений:

10, 10, 9, 10, 10.

.мода будет равна 10, медиана 9, а среднее - 9,8. Мода равна 10, пото.му что, очевидно, это наиболее частое значение в выборке. Вот запрос на Transact-SQL, который вычисляет моду для более сложного .множества значений:

CREATE TABLE #valueset (cl int) INSERT #valueset VALUES (2) INSERT #valueset VALUES (2) INSERT #valueset VALUES (1) INSERT #valueset VALUES (3) INSERT #valueset VALUES (4) INSERl #valueset VALUES (4) INSERT #valueset VALUES (10) INSERT #vaiueset VALUES (11) INSERT #valueset VALUES (13)

SELECT TOP 1 WITH TIES cl. COUNK*) AS Numlnstances

FROM #valueset GROUP BY cl

ORDER BY Numlnstances DESC cl Numlnstances

Так как множество может содержать более од1Юго значения с одинаковым числом экземпляров, возможно, что мода множества будет состоять из нескольких значений. Это как раз тот случай, когда удобгго использовать расширение ТОР п оператора SELECT. Его опция WITH TIES может обработать такую ситуацию без необходимости дополнительного кодирования.

Гистограммы

с помощью функции CASE очень просто вычислять некоторые тины гистограмм, особенно горизонтальные гистограммы. Используя такой же метод, как и в случае сводных таблиц (см. в этой главе), .мы можем создавать горизонтальные таблицы гистограмм с небольшим количеством Tran.sact-SQL-кода. Вот пример, использующий таблицу sales базы да1П1ых pubs: SELECT

tess than 10 =COUNT(CASE WHEN s.sales >=0 AND s.sales <10 THEN 1 ELSE NULL END). 10-19 =COUNT(CASE WHEN s,sales >-10 AND s.sales <20 THEN 1 ELSE NULL END). 20-29 =COUNT(CASE WHEN s.sales >=20 AND s.sales <30 THEN 1 ELSE NULL END). 30-39 =COUNT(CASE WHEN s.sales >--30 AND s,sales <40 THEN 1 ELSE NULL END), 40-49 =COUNT(CASE WHEN s.sales >=40 AND s.sales <50 THEN 1 ELSE NULL END). 50 or more =COUNT(CASE WHEN s.sales >-50 THEN 1 ELSE NULL END)

FROM (SELECT t.title icl. sales-lSNULL(SUM(s.qty).0) FROM titles t LEFT OUTER JOIN sales s ON (t.titlejd-s.titlejd) GROUP BY t.title id) s

Less than 10 10-19 20-29 30-39 40-49 50 or more

1 4 6 3 2 2

Этот запрос определяет книги (titles), которые попадают в соответствующую группу в зависимости от объемов их продаж. Обратите внимание на использование производной таблицы для вычисления продаж по каждой К1гиге. Это необходимо, так как выражения COUNT() в списке столбнов оператора SELECT пе могут задействовать другие агрегаты. После того как для каждого 1газвання вычислен объем продаж, это значение сравнивается с диапазоном для каждой группы, чтобы определить его .место. rncTorpaM.vibi обычно делают неясные тенденции более очевидными. Эта конкретная гистограмма показывает, что больше всего наименований книг было продано от двадцати и до триднатп экземпляров.

Многослойные гистограммы

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

ция периого примера построения THCT0Tpa.\TNn)i, к(гторая включает в сеоя столоец со значением стратификации;

SELECT

PayTenris=i snul 1 (s .payterriis, NA),

Less than 10 =COUNT(CASE WHEN s.sales >=G AhD s.saes <;0 THEN 1 ELSE NULL END), iO-19 =CGUNT(CASE WHEN s,sales >-10 AND s.sales <20 THEN 1 ELSE NULL END), 20-29 -CDUNT(CASE WHEN s,sales >20 AND s,sales <30 THEN 1 ELSE NULL END), 3C-39 =CCUNT(CASE WHEN s.sales >=3G A,NO s.sales <40 THEN 1 ELSE NULL END), 4C-49 =C0UNT(CASE WHEN s.sales >=40 AND 5.sales <50 THEN 1 ELSE NULL END), 50 or more =COUNT(CASE WHEN s,sa!es >=50 THEN 1 EiSE NULL END) FROM (SELECT t,title id, S-payte-ms, sa;eb-ISWULL(SUM(s,qty),0) FROM titles t LEFT OUTER JOIN saes s ON (t.title ic=s.title ic) GROUP BY t,titie ic:, payterms) s GROUP BY s.payterms

PayTerms Less tnan 10 10-19 20-29 30-39 40-49 50 or more

NA 1 0 0 0 0 0

Net 30 0 0 5 2 1 1

Net 60 1 4 3 0 0 0

ON invoice 0 2 0 10 1

Гистограм.мы, сводные таблицы и другие типы OLAP-конструкций можно также создавать с пспользова1И1ем модуля SQL Server OLAP Services. Рассмотрение этого набора инструментов выходит за ра.мки этой книги, так что обратитесь к Books Online за более подробной информацие!!.

Кумулятивные и скользящие агрегаты

Вычисление нарастающих итогов (running totals) в Transact-SQL - сравшгтель-но простая задача. Как и во многих других примерах в книге, данный способ использует перекрестное соединение двух Konnii исходной таблицы. Вот код:

CREATE TABLE #vaiueset (kl int identity, cl int)

INSERT #valueset (cl) VALUES (20)

INSERT #valueset (cl) VALUES (30)

INSERT #vaiueset (cl) VALUES (40)

INSERT #valueset (cl) VALUES (21)

INSERT #valueset (cl) VALUES (31)

INSERT #valueset (cl) VALUES (41)

INSERT #valueset (cl) VALUES (22j

INSERT #valueset (cl) VALUES (32)

INSERT #valueset (cl) VALUES (42)

SELECT v.cl. Runnir,gTctal=SUM(a cl)

FROM #vaiueset v CROSS JOIN #valueset a

WHERE (a.ki<:=v.kl)

GROUP BY v.kl.v.cl

ORDER BY v,kl,v,cl

cl RunningTotal

20 20

30 50 40 90

21 111

31 142

41 183 22 205 32 237

42 279

Обратите внимание на включение прелложения ORDER BY. Оно необходимо, потому что предложение GROUP BY не упорядочивает явно результирующее множество, как это было в предыдущих версиях SQL Server. Другие типы нарастающих итогов можно вычислить, заменив SUM() другой агрегатной функцией. Например, чтобы вычислить нарастаюпгее значение AVG(), воспользуйтесь этим кодом:

SELECT v.cl. RunningAverage=AVG(a.cl) FROM #valueset v CROSS JOIN #vaTueset a WHERE (a.kl<=v.kl) GROUP BY v.kl.v.cl ORDER BY v.kl.v.cl

cl RunningAverage

20 20

30 25

40 30

21 27

31 28

41 30

22 29

32 29

42 31

Для вычисления нарастающего количества значений (в результате получатся уникальные номера записей) попробуйте это:

SELECT RowNumber COUNT(*). v.cl FROM #valueset v CROSS JOIN #valueset a WHERE (a.kl<=v.kl) GROUP BY v.kl.v.cl

ORDER BY v.kl.v.cl

RowNumber cl

1 20

2 30

3 40

4 21

5 31

6 41

7 22

8 32

9 42

Скользящие (sliding) агрегаты

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