Аналитичните функции в езика SQL са мощен и гъвкав инструмент за широк кръг от изчисления, свързани с извеждане на междинни суми, кумулативни (с натрупване) суми и бройки, изчисляване на ранг, достъп до стойности от предходни или следващи редове и др.
В сървърите за бази данни, които не поддържат аналитични функции, подобни изчисления могат да се постигнат със средства, които значително усложняват заявките и забавят изчисленията. Например, с вложени заявки или със свързване на таблиците към самите себе си (self join).
В тази статия ще разгледаме три аналитични функции, с помощта на които могат лесно да се ранжират данни:
ROW_NUMBER()RANK()DENSE_RANK()
Трите функции имат близък синтаксис:
ROW_NUMBER() OVER( PARTITION BY col_list ORDER BY col1 ASC|DESC, ... )
RANK() OVER( PARTITION BY col_list ORDER BY col1 ASC|DESC, ...)
DENSE_RANK() OVER( PARTITION BY col_list ORDER BY col1 ASC|DESC, ...)като задължителната клауза при тях е ORDER BY, с която се определя дали ранга ще се изчислява във възходящ или низходящ ред. Ако във функцията е включена клаузата PARTITION BY, то при промяна на стойността в колона посочена в тази клауза, функцията започва да изчислява ранга отново. Така може да се изчисли ранг по групи. Например, ранг на клиентите по държави и за всяка държава да има 1,2,3 и т.н.
Следващата таблица нагледно представя разликите в резултатите при трите функции, ако в ORDER BY се посочи колоната sales и изчисленията са в низходящ ред:
| sales | row number | rank | dense rank |
|---|---|---|---|
| 2500 | 1 | 1 | 1 |
| 2500 | 2 | 1 | 1 |
| 2000 | 3 | 3 | 2 |
| 2000 | 4 | 3 | 2 |
| 1000 | 5 | 5 | 3 |
От таблицата се вижда, че ROW_NUMBER() просто номерира последователно редовете в резултата, независимо от това, че данните на някои от редовете съвпадат (в практиката, ROW_NUMBER() често се използва за филтриране на редовете с определени номера).
Функцията RANK() ранжира резултата като редовете с еднакви стойности получават еднакъв ранг като в номерацията може да има пропуски заради съвпадение на стойности в колоната sales. Подобен резултат се получава и от DENSE_RANK(), с тази разлика, че няма пропуски в номерацията.
Имайки предвид действието на трите функции, ако трябва да филтрираме резултата, така че да останат трите продажби с най-вискока стойност, то при ROW_NUMBER() това ще бъдат просто първите три реда, при RANK() ще се върнат първите четири реда, а при DENSE_RANK() – всичките пет.
Ако в колоната sales нямаше съвпадащи стойности, резултатът от трите функции щеше да е еднакъв – първите три реда.
Искате да научите повече за заявките в SQL?
Включете се в предстоящия курс Анализ на данни и изготвяне на отчети с SQL
Автор: Дикран Хачикян