T-SQL, Функции за класиране
И четирите функции за класиране поддържат клауза за разделяне по избор и задължителна клауза за подреждане на прозорци. Ако няма клауза за разделяне на прозорци, целият набор от резултати на основната заявка (не забравяйте входовете към етапа SELECT) се брои като един дял. Що се отнася до клаузата за подреждане на прозорци, тя осигурява подреждане в изчисленията. Разбираемо е, че класирането на редове без дефиниране на подреждането едва ли има смисъл. При класирането на прозоречните функции подреждането служи за различни цели от функции, които поддържат рамкиране, като например обобщени прозоречни функции. В първия случай подреждането има логичен смисъл за самите изчисления, а във втория подреждането е свързано с кадриране, тоест служи за целите на филтрирането.
ROW_NUMBER функция
Тази функция изчислява номерата на последователните редове, започвайки от 1, в съответния раздел на прозореца и в съответствие с дадената подредба на прозорци. Разгледайте примерна заявка:
Ето съкратения изход от тази заявка:
Това искане изглежда тривиално, но тук има няколко неща, които си струва да се споменат.
Тъй като заявката няма клауза ORDER BY view, подреждането на изгледа не е гарантирано. Следователно, подреждането на презентацията тук трябва да се счита за произволно. На практика SQL Server оптимизира заявката за отсъствие на клауза ORDER BY, така че редовете могат да бъдат върнати във всякакъв ред. Ако искате да гарантирате подреждането на изгледа, трябва да запомните да добавите клаузата ORDER BY на изгледа. Ако искате подреждането на изгледа да се извършва въз основа на номера на реда, можете да използвате псевдонима, присвоен по време на оценката на клаузата ORDER BY на изгледа, нещо подобно на това:
Помислете за изчисляването на номера на редове като за генериране на друг атрибут в набора от резултати от заявката. Естествено, ако искате, можете да получите подреждането на изгледа, което е различно от подреждането на прозореца, както в следната заявка:
Можете да използвате агрегата на прозореца COUNT, за да създадете операция, която е логически еквивалентна на функцията ROW_NUMBER. Да приемем, че WPO е дефиниция за разделяне и подреждане на прозорци, приложена във функцията ROW_NUMBER. Тогава ROW_NUMBER OVER WPO е еквивалентно на COUNT (*) OVER (WPO ROWS UNBONDED PRECEDING). Например, следното е еквивалентно на заявката от предишния примерен списък:
Както казах, добра практика е да се опитвате да създавате алтернативи, вместо да използвате функции за прозорци, без значение дали тези алтернативи са по-сложни и по-малко ефективни. Ако говорим за функцията ROW_NUMBER, ето стандартна алтернатива на заявка, която не използва прозоречни функции:
Това решение използва агрегата COUNT в подзаявката, за да определи колко реда имат стойност за подреждане (в нашия случай orderid) по-малка или равна на текущата. Лесно е, ако имате уникална поръчка въз основа на един атрибут. Но нещата стават много по-сложни, ако подреждането не е уникално, което ще покажа в дискусията си относно детерминизма.
Детерминизъм
Ако подреждането на прозореца е уникално, изчисляването на ROW_NUMBER е детерминирано. Това означава, че заявката има само един правилен резултат, т.е. ако не промените входните данни, гарантирано ще получите дублирани резултати. Но ако подреждането на прозореца не е уникално, изчислението става недетерминирано. Функцията ROW_NUMBER генерира уникални номера на редове в секция, дори за редове със същите стойности в атрибутите за подреждане на прозорци. Обърнете внимание на следната заявка като пример:
Тъй като атрибутът orderdate не е уникален, подреждането на редове със същата стойност на orderdate трябва да се счита за произволно. По принцип за тази заявка има повече от един валиден резултат. Да вземем за пример четири реда с датата на поръчката 2008-05-06. Всеки ред на редове, номерирани от 1 до 4, се счита за правилен. Следователно, ако стартирате заявката отново, тогава по принцип можете да получите различна поръчка - сега няма да оценяваме вероятността за подобно събитие поради особеностите на внедряването на SQL Server.
Ако искате да гарантирате повторяеми резултати, трябва да направите заявката детерминирана. Това може да се направи чрез добавяне на допълнителен параметър към дефиницията за подреждане на прозорци, за да се осигури уникалност в даден раздел. Например в следващата заявка уникалността на подреждането в прозореца се постига чрез добавяне на подредения DESC към списъка:
Използването на прозоречни функции улеснява определянето на номера на страниците. Да направите същото, без да прибягвате до функции за прозорци, е по-трудно, но съвсем реалистично:
Но да се върнем към функцията ROW_NUMBER: както подчертахме, тя може да се използва за създаване на недетерминирани изчисления, като се използва не уникално подреждане. По този начин е недопустим детерминизмът, но странното е, че той не е напълно разрешен. Това, което искам да кажа, е, че се изисква клаузата ORDER BY. Но какво, ако просто искате да получите уникални номера на редове в даден раздел, не непременно в някакъв определен ред? Можете да създадете заявка като тази:
Но, както беше посочено, клаузата ORDER BY във функциите за класиране се изисква:
Можете да бъдете умни и да дефинирате константа в списъка ORDER BY: