Главная » Microsoft SQL Server, Базы данных » Упорядочение результирующего набора данных

0

Данные в таблице SQL имеют форму неупорядоченного списка. Основной задачей первичного ключа является уникальная идентификация строк, а совсем не их упорядочение. Некоторые реализации СУБД могут представлять таблицы в порядке, поддерживаемом первичным ключом. Однако лучше все же не надеяться на такое поведение. Если явно не задать предложение ORDER BY, то порядок строк в результирующем наборе данных может оставаться неопределенным.

Грубо говоря, если предложение ORDER BY отсутствует, SQL Server вернет строки в том порядке, в котором они извлекались из таблицы. Если исходная таблица имела кластеризованный индекс, то порядок результирующего набора данных будет соответствовать ему. Некоторые логические операции сортируют данные для своей поддержки. Например, некоторые объединения сортируют данные так, чтобы объединение было легче выполнить. Таким образом, даже в отсутствии предложения ORDER BY результирующий набор данных может оказаться отсортированным. И все же, если данные должны иметь конкретный порядок, лучше явно задать его в предложении ORDER BY, как показано на рис. 7.4.

.

J -

Puc. 7.4. В конструкторе запросов утилиты Management Studio вы можете задать порядок сортировки, щелкнув на кнопке Ascending или Descending панели инструментов или указав порядок на панели Grid

SQL позволяет выполнять сортировку по множеству столбцов, и ими не обязательно должны быть столбцы, извлекаемые инструкцией SELECT. Это предоставляет достаточную гибкость в определении столбцов сортировки.

Определение порядка сортировки с помощью имен столбцов

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

USE СНА2

SELECT FirstName, LastName FROM dbo.Customer ORDER BY LastName, FirstName;

Результат будет следующим:

FirstName     LastName

Joe               Adams

Missy            Anderson

Debbie        Andrews

Dave            Bettys

Предложение order by и порядок столбцов в списке отбора полностью независимы друг от друга.

Определение порядка сортировки с помощью выражений

При сортировке по выражению все оно должно быть полностью повторено в предложении ORDER BY. Это не приводит к снижению производительности, поскольку оптимизатор запросов достаточно умен, чтобы не повторять одно и то же вычисление дважды:

SELECT LastName + 1, 1 + FirstName FROM dbo.Customer

ORDER BY LastName + ‘, ‘ + FirstName;

Результат будет следующим:

FullName

Adams, Joe Anderson, Missy Andrews, Debbie Bettys, Dave

Использование выражений в предложении ORDER BY может решить множество проблем. Например, некоторые разработчики хранят заголовки в двух столбцах: в одном из них содержится полное название, а в другом — избавленное от ведущего предлога " The". С точки зрения производительности такая денормализация может оказаться полезной, но в то же время использование выражения CASE в предложении ORDER BY позволило бы выполнять корректную сортировку и без дублирования заголовка.

Дополнительная Полный синтаксис выражения case будет рассмотрен в главе 8.

информация

База данных примеров Aesop’s Fables содержит список заголовков. Если столбец Title содержит предлог "The", то выражение CASE удаляет его из него и передает в усеченном виде предложению ORDER BY:

USE Aesop;

SELECT Title, Len(FableText) AS TextLength FROM Fable ORDER BY CASE

WHEN substring(Title, 1,3) = ‘The’

THEN substring(Title, 5, Len(Title)-4)

ELSE Title END;

Результат запроса будет следующим:

FableName                                               TextLength

Androcles                                                13 7 0

The Ant and the Chrysalis                       1087

The Ants and the Grasshopper                  456

The Ass in the Lion’s Skin                         465

The Bald Knight                                         360

The Boy and the Filberts                           43 5

The Bundle of Sticks                                 551

The Crow and the Pitcher                          491

Определение порядка сортировки с помощью псевдонимов столбцов

В качестве альтернативы для явного задания столбцов в предложении ORDER BY можно использовать их псевдонимы. Этот метод предпочтительнее, так как значительно облегчает чтение программы. Обратите внимание на то, что в следующем примере сортировка выполняется по убыванию (предикат DESC), а не по возрастанию, как принято по умолчанию:

SELECT LastName + 1, 1 + FirstName as FullName FROM dbo.Customer ORDER BY FullName DESC

Результат будет следующим:

FullName

Zeniod, Kent Williams, Larry Valentino, Mary Spade, Sam

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

Определение порядка сортировки с помощью порядковых номеров столбцов

В предложении ORDER BY вместо имен столбцов могут использоваться их порядковые номера в запросе, однако я не рекомендую использовать этот метод. Если в начале инструкции SELECT изменится состав или порядок столбцов, то и предложение ORDER BY будет работать по-другому. Порядковые номера столбцов можно использовать в сложных запросах объединения, о которых мы поговорим в главе 9. В следующем примере продемонстрировано использование порядковых номеров столбцов для сортировки:

SELECT LastName + 1, ‘ + FirstName AS FullName FROM dbo.Customer ORDER BY 1

Результат будет следующим:

FullName

Adams, Joe Anderson, Missy Andrews, Debbie Bettys, Dave

Упорядочение и порядок сопоставления

Установленный порядок сопоставления, применяемый в SQL Server 2005, принципиально важен для правильной сортировки данных. Кроме определения используемого алфавита, порядок сопоставления указывает, будут ли учитываться при упорядочении регистр символов и прочие характеристики алфавита. Например, если порядок сопоставления учитывает регистр символов, строчные буквы будут упорядочены перед прописными. Следующая функция поможет вам узнать установленные на сервере параметры порядка сопоставления и используемые в настоящее время:

SELECT * FROM ::fn_helpcollations()

Результат будет следующим: name          description

Albanian_BIN            Albanian, binary sort

Albanian_CI_AI        Albanian, case-insensitive,

accent-insensitive,

kanatype-insensitive, width-nsensitive Albanian_CI_AI_WS           Albanian, case-insensitive,

accent-insens it ive,

kanatype-insensitive, width-sensitive

SQL_Latinl_General_CPl_CI_AI

Latinl-General, case-insensitive, accent-insensitive,

kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page 1252 for non-UnicodeData

Следующий запрос сообщит вам о порядке сопоставления, используемом в настоящий момент на сервере:

SELECT SERVERPROPERTY(‘Collation’) AS ServerCollation Результат может быть следующим:

ServerCollation

SQL_Latinl_General_CPl_CI_AS

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

ALTER DATABASE Family

COLLATE SQL_Latinl_General_CPl_CS_AS SELECT DATABASEPROPERTYEX(Family,’Collation’)

AS DatabaseCollation

Результат будет следующим:

DatabaseCollation

SQL_Latinl_General_CPl_CS_AS

SQL Server устанавливает порядок сопоставления не только на уровне сервера, базы данных и столбца. Порядок сопоставления может устанавливаться на уровне отдельного запроса. Следующий запрос будет использовать для упорядочения датский порядок сопоставления без учета акцентов и регистра символов:

SELECT *

FROM dbo.Product ORDER BY ProductName

COLLATE Danish_Norwegian_CI_AI

He все запросы нуждаются в сортировке, но в тех, в которых она нужна, предложение ORDER BY в сочетании со всеми возможными порядками сопоставления обеспечивают максимальную гибкость при упорядочении результирующего набора данных.

SELECT DISTINCT

Первым предикатом, используемым в сочетании с ключевым словом SELECT, является DISTINCT. Он исключает дублирование строк в результирующем наборе данных запроса. Эти дублирования оцениваются на уровне столбцов результирующего набора данных, а не исходных таблиц. Противоположную функцию выполняет предикат ALL; так как он используется по умолчанию, в запросах его обычно игнорируют.

В следующем примере демонстрируется различие между предикатами DISTINCT и ALL. Объединения будут описаны в главе 9, сейчас же отметим, что ключевое слово JOIN между именами таблиц tour и event генерирует строку каждый раз, когда тур проходит как событие. Так как инструкция SELECT возвращает только столбец tourname (название тура), это отличный пример дублирования строк, которое можно будет устранить с помощью предиката DISTINCT:

SELECT ALL TourName FROM Event JOIN Tour

ON Event.TourlD = Tour.TourlD

Результат будет следующим:

TourName

Amazon Trek Amazon Trek Appalachian Trail Appalachian Trail Appalachian Trail Bahamas Dive Bahamas Dive Bahamas Dive Gauley River Rafting Gauley River Rafting Outer Banks Lighthouses Outer Banks Lighthouses Outer Banks Lighthouses Outer Banks Lighthouses Outer Banks Lighthouses Outer Banks Lighthouses

А теперь выполним тот же запрос с предикатом DISTINCT:

SELECT DISTINCT TourName

FROM Event JOIN Tour

ON Event.TourlD = Tour.TourlD

Результат на этот раз будет другим:

TourName

Amazon Trek Appalachian Trail Bahamas Dive Gauley River Rafting Outer Banks Lighthouses

В то время как первый запрос вернул шестнадцать строк, предикат DISTINCT, использованный во втором запросе, исключил дублирование и вернул только пять строк.

Функционально предикат distinct в SQL Server отличается от предиката

На заметку distinctrow, используемого в Microsoft Access, поскольку последний исключает дублирование, основываясь на данных исходных таблиц, а не результирующего набора данных.

Инструкция SELECT DISTINCT функционирует так, будто во всех столбцах результирующего набора данных установлена группировка предложением GROUP BY (подробно

о          ней вы узнаете в главе 11). Сравнивая план выполнения двух предыдущих запросов (рис. 7.5), мы явно видим работу предиката DISTINCT в качестве потокового агрегирования. Таким образом, предикат DISTINCT создает дополнительное действие в плане выполнения запроса. Однако при этом оказывается небольшое влияние на производительность (исследования показали, что на выполнение операции потокового агрегирования затрачивается всего 0,000006% общего времени выполнения запроса). Если уникальность строк логически необходима, не избегайте предиката DISTINCT только из-за его влияния на производительность.

Puc. 7.5. Сравнение планов выполнения двух запросов выявило операцию потокового агрегирования (Stream Aggregate), которую выполняет предикат DISTINCT, чтобы не допустить дублирование строк

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

По определению инструкция SELECT работает с наборами данных, однако иногда пользователя интересуют только первые несколько строк этого набора. Для таких ситуаций в SQL Server предусмотрено несколько способов фильтрации результатов и выявления экстремальных строк.

ТОР

Как уже говорилось ранее, SQL Server по умолчанию возвращает в инструкции SELECT все строки результирующего набора данных. Необязательный предикат ТОР указывает серверу возвращать только определенное количество строк (в абсолютном или процентном выражении), основываясь на заданном параметре (рис. 7.6).

Предикат ТОР работает рука об руку с предложением ORDER BY, так как именно оно определяет, какие строки будут первыми в результирующем наборе данных. Если же в инструкции SELECT отсутствует предложение ORDER BY, то предикат ТОР все равно отработает, возвращая заданное количество строк неупорядоченного набора данных.

Рис. 7.6. Предикат ТОР устанавливается в утилите Management Studio на странице свойств запроса

Отличным полигоном для испытания предиката ТОР может стать база данных OBXKites. Следующий запрос найдет 3% самых больших цен в таблице price. В таблице price для каждого товара может быть указано несколько цен, выбор которых основан на столбце EffectiveDate:

SELECT TOP 3 PERCENT Code, ProductName, Price,

CONVERT(VARCHAR(10),EffectiveDate,1) AS PriceDate FROM Product

JOIN Price ON Product.ProductID = Price.ProductID ORDER BY Price DESC

В результате мы получим следующий список:

ProductCode ProductName                           Price                   PriceDate

1018     Competition Pro 48"                       284.9500            05/01/01

1018     Competition Pro 48"                       264.9500            05/01/02

1017     Competition 36"                              245.9500            05/20/03

1017     Competition 36м                              225.9500            05/01/01

А приведенный ниже запрос вернет три самые низкие цены в той же таблице.

SELECT ТОР 3 Code, ProductName, Price,

CONVERT(VARCHAR(10),EffectiveDate,1) AS PriceDate FROM Product

JOIN Price ON Product.ProductID = Price.ProductID ORDER BY Price

Результат будет следующим:

ProductCode ProductName                          Price                   PriceDate

1044        OBX Car Bumper Sticker .7500                                05/01/01

1045        OBX Car Window Decal .7500                                 05/20/01 1045 OBX Car Window Decal .9500 05/20/02

Вроде и запрос, и его результаты выглядят хорошо, но, к сожалению, результаты неверные. Если вы посмотрите на исходные данные, отсортированные по цене, то найдете не одну, а три строки с ценой 95 центов. Эту проблему решает параметр WITH TIES.

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

Параметр with ties

Параметр WITH TIES исключительно важен для предиката ТОР. Он позволяет дополнить строку, занявшую последнее место в ранжировке, дополнительными строками, имеющими такое же значение в столбцах, упомянутых в предложении ORDER BY, но не попадающими в количество, заданное в предикате ТОР. В следующей версии предыдущего запроса мы дополнили его параметром WITH TIES и в результате получили правильные результаты в количестве пяти строк, несмотря на использование предиката ТОР 3:

SELECT ТОР 3 WITH TIES Code, ProductName, Price,

CONVERT(VARCHAR(10),EffectiveDate,1) AS PriceDate FROM Product

JOIN Price ON Product.ProductID = Price.ProductID ORDER BY Price

Ниже приведен результат этого запроса.

ProductCode ProductName                          Price                   PriceDate

1044        OBX Car Bumper Sticker .7500                                05/01/01

1045        OBX Car Window Decal .7500                                 05/20/01

1045        OBX Car Window Decal .9500 05/20/02

1041        Kite Fabric #6                                  .9500                 05/01/01

1042        Kite Fabric #8                                  .9500                 05/01/01

Если вы переходите на SQL Server с Microsoft Access, то учтите, что во второй СУБД параметр with ties добавлялся автоматически к предикату тор. В SQL Server это не так.

Предикат тор является расширением стандарта ANSI SQL; он не переносим. Если базу данных придется переносить на другую платформу, то использование предиката тор может вызвать проблемы преобразования. В противоположность этому переменная rowcount является переносимой.

В SQL Server 2005 появилось несколько новых функций ранжирования, в том числе rownumber (), rank О, denSerank() и ntile(). Эти функции могут использоваться как дополнительные команды предиката тор (подробнее об этом речь пойдет в главе 8).

Резюме

Язык SQL создавался как инструмент манипулирования данными, а инструкция SELECT является “королевой” в этой области. Несмотря на то что в нескольких примерах этой главы использовались объединения, та же методика может быть использована и при работе с одной таблицей. В простой инструкции SELECT скрыта неимоверная мощь и гибкость. SQL является описательным языком — вы всего лишь формулируете вопрос, а тем, как именно будет получен на него ответ, занимается уже оптимизатор запросов на сервере. Так что вы вольны использовать любой стиль при формировании запроса.

Следующие девять глав посвящены раскрытию дополнительных возможностей, которые только добавляют мощь инструкции SELECT, привнося в нее выражения, множество типов объединений, подзапросы и группировку. Добро пожаловать в пакетный мир SQL!

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии