Автор: Дикран Хачикян
Аналитичните функции в езика 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