Впорядкування результуючого набору даних

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

Грубо кажучи, якщо пропозиція ORDER BY відсутня, SQL Server поверне рядки в тому порядку, в якому вони витягали з таблиці Якщо вихідна таблиця мала кластерізованний індекс, то порядок результуючого набору даних буде відповідати йому Деякі логічні операції сортують дані для своєї підтримки Наприклад, деякі обєднання сортують дані так, щоб обєднання було легше виконати Таким чином, навіть у відсутності пропозиції ORDER BY результуючий набір даних може виявитися відсортованим І все ж, якщо дані повинні мати конкретний порядок, краще явно задати його в реченні ORDER BY, як показано на рис 74

.

J –

Puc 74 У конструкторі запитів утиліти Management Studio ви можете задати порядок сортування, клацнувши на кнопці Ascending або Descending панелі інструментів або вказавши порядок на панелі Grid

SQL дозволяє виконувати сортування по безлічі стовпців, і ними не обовязково повинні бути стовпці, які добуваються інструкцією SELECT Це надає достатню гнучкість у визначенні стовпців сортування

Визначення порядку сортування за допомогою імен стовпців

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

USE СНА2

SELECT FirstName, LastName FROM dboCustomer ORDER BY LastName, FirstName

Результат буде наступним:

FirstName     LastName

Joe               Adams

Missy            Anderson

Debbie        Andrews

Dave            Bettys

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

Визначення порядку сортування за допомогою виразів

При сортуванні за висловом все воно має бути повністю повторено в реченні ORDER BY Це не призводить до зниження продуктивності, оскільки оптимізатор запитів досить розумний, щоб не повторювати одне і те ж обчислення двічі:

SELECT LastName + 1, 1 + FirstName FROM dboCustomer

ORDER BY LastName + , + FirstName

Результат буде наступним:

FullName

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

Використання виразів в реченні ORDER BY може вирішити безліч проблем Наприклад, деякі розробники зберігають заголовки у двох стовпцях: в одному з них міститься повна назва, а в іншому – позбавлене від провідного прийменника The . З точки зору продуктивності така денормализация може виявитися корисною, але в той же час використання виразу CASE в реченні ORDER BY дозволило б виконувати коректну сортування і без дублювання заголовка

Додаткова Повний синтаксис вираження case буде розглянуто в розділі 8

інформація

База даних прикладів Aesops 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 Lions 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 dboCustomer 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 dboCustomer 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 dboProduct 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 EventTourlD = TourTourlD

Результат буде наступним:

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 EventTourlD = TourTourlD

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

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) Порівнюючи план виконання двох попередніх запитів (рис 75), ми явно бачимо роботу предиката DISTINCT в якості потокового агрегування Таким чином, предикат DISTINCT створює додаткову дію в плані виконання запиту Однак при цьому виявляється невеликий вплив на продуктивність (дослідження показали, що на виконання операції потокового агрегування витрачається всього 0,000006% загального часу виконання запиту) Якщо унікальність рядків логічно необхідна, що не уникайте предиката DISTINCT тільки через його впливу на продуктивність

Puc 75 Порівняння планів виконання двох запитів виявило операцію потокового агрегування (Stream Aggregate), яку виконує предикат DISTINCT, щоб не допустити дублювання рядків

Ранжування

За визначенням інструкція SELECT працює з наборами даних, однак іноді користувача цікавлять тільки перші кілька рядків цього набору Для таких ситуацій в SQL Server передбачено кілька способів фільтрації результатів і виявлення екстремальних рядків

ТОР

Як вже говорилося раніше, SQL Server за замовчуванням повертає в інструкції SELECT всі рядки результуючого набору даних Необовязковий предикат ТОР вказує серверу повертати тільки певна кількість рядків (в абсолютному або процентному вираженні), грунтуючись на заданому параметрі (рис 76)

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

Рис 76 Предикат ТОР встановлюється в утиліті 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 ProductProductID = PriceProductID ORDER BY Price DESC

У результаті ми отримаємо наступний список:

ProductCode ProductName                           Price                   PriceDate

1018     Competition Pro 48&quot                       2849500            05/01/01

1018     Competition Pro 48&quot                       2649500            05/01/02

1017     Competition 36&quot                              2459500            05/20/03

1017     Competition 36м                              2259500            05/01/01

А наведений нижче запит поверне три найнижчі ціни в тій же таблиці

SELECT ТОР 3 Code, ProductName, Price,

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

JOIN Price ON ProductProductID = PriceProductID ORDER BY Price

Результат буде наступним:

ProductCode ProductName                          Price                   PriceDate

1044&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp OBX Car Bumper Sticker 7500                                05/01/01

1045&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 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 ProductProductID = PriceProductID ORDER BY Price

Нижче наведено результат цього запиту

ProductCode ProductName                          Price                   PriceDate

1044&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp OBX Car Bumper Sticker 7500                                05/01/01

1045&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp OBX Car Window Decal 7500                                 05/20/01

1045&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp OBX Car Window Decal 9500 05/20/02

1041&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Kite Fabric #6                                  9500                 05/01/01

1042&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 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 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*