SQL Advanced — сортировка и оконные функции

база данных

SQL Advanced-3-Sort и оконные функции

При использовании базы данных для получения различных статистических данных необходимо сортировать данные, например, по"Оценка, продажи, количество человек"Существует два вида методов сортировки:

  • Рейтинг после пропуска
  • Не пропускайте последующее ранжирование

оконная функция

Оконные функции работают только в последнихMySQLПоддерживается только в версии!

Оконные функции работают только в последнихMySQLПоддерживается только в версии!

Оконные функции работают только в последнихMySQLПоддерживается только в версии!

Ссылка: https://zhuanlan.zhihu.com/p/92654574

что такое оконные функции

оконная функция, также называемаяOLAPФункция (онлайн-аналитическая обработка, онлайн-аналитическая обработка), которая может выполнять аналитическую обработку данных базы данных в реальном времени.

грамматика

Основной синтаксис оконных функций:

<窗口函数> over (partition by <用于分组的字段名>  -- partition子句可省略，不指定分组
             order by <用于排序的列名>)

<窗口函数>Есть два вида функций, которые могут быть размещены в позиции:

  • специальные оконные функции,rank、dense_rank、row_numberЖдать
  • Агрегатные функции, такие какsum、avg、count、max、minЖдать

Потому что оконная функция правильнаяwhereилиgroup byРезультат обработки предложения обрабатывается, поэтому"В принципе, оконные функции могут быть записаны только в предложении select.".

Функции

  • Он также имеет функцию группировки и сортировки
  • Не изменять количество строк в исходной таблице
  • В принципе, оконные функции могут быть написаны только наselectв пункте

реальная сцена

  • Проблема ранжирования: каждый отдел ранжируется по производительности
  • topNВопрос: Найдите высший ранг в каждом отделеNсотрудники получают вознаграждение

rank/dense_rank/row_number

Пример

  • rank: Рейтинг прыжков с ничьей
  • dense_rank: Ничья в последовательном рейтинге
  • row_number: Непрерывный рейтинг

Разницу между этими тремя функциями хорошо видно через колонку:

select 
	name,price,
	rank() over (order by price desc) as rank_1,
	dense_rank() (order by price desc) as rank_2,
	row_number() (order by price desc) as rank_3
from products;

в заключении 

name         price    rank_1    rank_2      rank_3
橘子          100          1          1       1
西瓜           80          2          2       2
苹果           50          3          3       3
香蕉           50          3          3       4
葡萄           50          3          3       5 
柠檬           30          6          4       6

Суммировать:

  • rank()После того, как появляется тот же ранг, тот же ранг пропускается
  • dense_rank()то такой же ранг не пропускается
  • row_number()Расположить в порядке натуральных чисел
❝

В приведенных выше трех специальных оконных функциях круглые скобки после функции не нуждаются в каких-либо параметрах, просто оставьте () пустым.

❞

пример Чжиху

Реализовать ранг() 

select *,
   rank() over (partition by 班级   -- 先分组
                 order by 成绩 desc) as ranking  -- 再排序
from 班级表

Не меняйте количество строк

Неэквивалентное соединение реализует rank() 

select p1.name,p1.price,
			(select count(p2.price) 
       from products p2
       where p2.price > p1.price) + 1
       as rank_1
from products
order by rank_1;
  • Функция подзапроса состоит в том, чтобы вычислить отношение(p1)высокий рекорд и сделать его своим собственным рейтингом
  • Например, цена после дедупликации{100,80,50}Анализировать и сортировать, сравнивать100Большее число равно 0, число больше 80 равно 1, а число больше 50 равно 2.
  • +1Рейтинг после этого - это то, что будет упомянуто ниже.dense_rank()ранжирование функций
цена ранжирование +1
100 0 1
80 1 2
50 2 3

"Если вы хотите, чтобы сортировка начиналась с 0, удалите и добавьте 1":

Неэквивалентное соединение реализует плотное_rank() 

mysql> select p1.name, p1.price,
    (select count(distinct p2.price) from products p2 where p2.price > p1.price) + 1 as rank_1
from products p1 
order by rank_1;

Реализовать row_number() с использованием переменных

существуетMySQL5.7.28реализация переменных вrow_numberфункция функции

mysql> select p.name, p.price, (@pro_rank := @pro_rank + 1) row_Number
    -> from products p,(select @pro_rank := 0) r
    -> order by price desc;

если более продвинутыйMySQLверсия, использовать напрямуюrow_number()реализация функции

select name, price,
		row_number() over (partition by name order by price desc) as rowNumber
from products

Агрегатные функции как оконные функции

Агрегатная оконная функция точно такая же, как выделенная оконная функция, упомянутая выше, только нужно поместить"Агрегатная функция"Его можно записать в позиции оконной функции

  • "Скобки после функции не могут быть пустыми"
  • Вам нужно указать имя агрегированного столбца

требуется в продвинутомMySQLверсия илиhiveреализовано в

mysql> select *,
    -> sum(price) over (order by name) as rank_sum,
    -> avg(price) over (order by name) as rank_avg,
    -> max(price) over (order by name) as rank_max,
    -> count(price) over (order by name) as rank_count
    -> from products;
Категории