SQL: Изчисляване на ранг с аналитични функции

Автор: Дикран Хачикян

Аналитичните функции в езика SQL са мощен и гъвкав инструмент за широк кръг от изчисления, свързани с извеждане на междинни суми, кумулативни (с натрупване) суми и бройки, изчисляване на ранг, достъп до стойности от предходни или следващи редове и др.

В сървърите за бази данни, които не поддържат аналитични функции, подобни изчисления могат да се постигнат със средства, които значително усложняват заявките и забавят изчисленията. Например, с вложени заявки или със свързване на таблиците към самите себе си (self join).

В тази статия ще разгледаме три аналитични функции, с помощта на които могат лесно да се ранжират данни:

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()

Трите функции имат близък синтаксис:

като задължителната клауза при тях е ORDER BY, с която се определя
дали ранга ще се изчислява във възходящ или низходящ ред. Ако във функцията е включена клаузата PARTITION BY, то при промяна на стойността в колона посочена в тази клауза, функцията започва да изчислява ранга отново.
Така може да се изчисли ранг по групи. Например, ранг на клиентите по държави и за всяка държава да има 1,2,3 и т.н.

Следващата таблица нагледно представя разликите в резултатите при трите функции, ако в ORDER BY се посочи колоната sales и изчисленията са в низходящ ред:

salesrow numberrankdense rank
2500111
2500211
2000332
2000432
1000553

От таблицата се вижда, че ROW_NUMBER() просто номерира последователно редовете в резултата, независимо от това, че данните на някои от редовете съвпадат (в практиката, ROW_NUMBER() често се използва за филтриране на редовете с определени номера).

Функцията RANK() ранжира резултата като редовете с еднакви стойности получават еднакъв ранг като в номерацията може да има пропуски заради съвпадение на стойности в колоната sales. Подобен резултат се получава и от DENSE_RANK(), с тази разлика, че няма пропуски в номерацията.

Имайки предвид действието на трите функции, ако трябва да филтрираме резултата, така че да останат трите продажби с най-вискока стойност, то при ROW_NUMBER() това ще бъдат просто първите три реда, при RANK() ще се върнат първите четири реда, а при DENSE_RANK() - всичките пет.

Ако в колоната sales нямаше съвпадащи стойности, резултатът от трите функции щеше да е еднакъв - първите три реда.

Подробности за трите функции и как те действат на практика, можете да видите в следващото видео: Как да изведем трите най-продавани продукта от всяка категория продукти

Искате да научите повече за заявките в SQL?

Включете се в предстоящия курс Анализ на данни и изготвяне на отчети с SQL

© Copyright 2019 DeviseExpert Всички права запазени
envelopephone-handsetmap-marker linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram