Автор: Дикран Хачикян
Аналитичните функции в езика SQL са мощен и гъвкав инструмент за широк кръг от изчисления, свързани с извеждане на междинни суми, кумулативни (с натрупване) суми и бройки, изчисляване на ранг, достъп до стойности от предходни или следващи редове и др.
В сървърите за бази данни, които не поддържат аналитични функции, подобни изчисления могат да се постигнат със средства, които значително усложняват заявките и забавят изчисленията. Например, с вложени заявки или със свързване на таблиците към самите себе си (self join).
В тази статия ще разгледаме три аналитични функции, с помощта на които могат лесно да се ранжират данни:
ROW_NUMBER()RANK()DENSE_RANK()Трите функции имат близък синтаксис:
|
1 2 3 4 5 |
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
