Основы интервью: SQL Rank и оконные функции

база данных
Основы интервью: SQL Rank и оконные функции

Общественный номер: You Er Hut
Автор: Питер
Редактор: Питер

Рейтинг LeetCode-SQL-178-Score

Всем привет, меня зовут Питер~

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

В этой статье содержится 178-й вопрос LeetCode-SQL:

  • Введение в тему
  • анализ идеи
  • 3 различные оконные функции
  • Оконная функция, реализованная в MySQL5

Впервые я столкнулся с ранжированием в SQL в книге, написанной японским автором MICK: «SQL Advanced Tutorial», Если вам интересно, вы можете внимательно прочитать ее, что очень полезно для улучшения SQL.

тема

Сначала представьте конкретную тему: напишите SQL-запрос для достижения рейтинга. Если две оценки одинаковы, две оценки имеют одинаковый ранг. Обратите внимание, что следующий ранг после четного разделения должен быть следующим последовательным целочисленным значением. Другими словами, между рейтингами не должно быть «разрыва».

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

Например, учитывая приведенную выше таблицу результатов, ваш запрос должен возвращать результат (в порядке убывания количества баллов): одна и та же оценка занимает один и тот же ранг, и между следующими рангами нет разрыва.

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

идеи

Идея 1

Этому подходу я научился из книг японских авторов.

select 
    s1.Score  -- 分数
    ,(select count(distinct s2.Score)  -- 大于等于此分数的分数值的不重复个数
      from Scores s2 
      where s2.Score > s1.Score) + 1 as `Rank`  -- 排名
from Scores s1
order by 2;  -- 2表示第2个字段

-- 变换方式
select 
	s1.score,
	(select count(*) + 1   -- 排名不能从1开始
   from (select distinct score from Scores) s2   -- 1、找出表中有多少种不同的分数：有多少分数就有多少排名
   where s2.score > s1.score) as Rank   -- 2、筛选大于某个分数
from Scores s1 
order by 2;

Идея 2

Идеи идеи 2 и идеи 1 одинаковы, и используется оператор where

select 
    s1.Score
    ,count(distinct(s2.Score)) `Rank`
from Scores s1, Scores s2
where s1.Score <= s2.Score
group by s1.Id
order by 2;  -- 2表示第2个字段

Код интерпретируется как:

1. Команды для двух одинаковых таблиц — s1 и s2 соответственно.

2. Учитывая s1.Score, выяснить, сколько баллов удовлетворяет: s2.Score >= s1.Score. Например, s1.Score=3,65, тогда: [4,00, 4,00, 3,85, 3,65, 3,65] соответствуют требованиям, но один и тот же результат имеет одинаковый рейтинг, поэтому результат дедуплицируется: count(distinct s2. Балл), то 3.65 ранг равен 3

3. группировать по группам и ранжировать данные s1, иначе будет возвращен только один фрагмент данных

4. Ранжирование в порядке возрастания

целостное мышление

Будь то идея 1 или идея 2, она в основном реализуется в два этапа:

  • Первая часть - это баллы в порядке убывания
  • Вторая часть - это ранг, соответствующий каждому баллу.

1. О реализации первой части: реализация прямого ранжирования по убыванию

select a.Score   
from Scores a
order by a.Score DESC   -- 直接根据分数降序实现

2. Относительно реализации второй части: Предположим, вам дан балл S, как нам рассчитать его ранг?

Мы можем сначала извлечь все наборы оценок H, большие или равные S, а количество элементов после дедупликации H будет ранжированием S. Например, на определенном экзамене Сяомин набрал 98 баллов, а результат выше, чем у Сяомина, равен [100,99,100,99,98]; после дедупликации это [100,99,98], всего 3 элемента; поэтому Рейтинг Сяомин занимает третье место.

Сначала извлеките набор H, соответствующий требованиям:

select b.Score 
from Scores b 
where b.Score >= S;

Затем в качестве ранжирования используется количество наборов H после дедупликации:

select count(distinct b.Score)   -- 去重后的个数当做排名
from Scores b 
where b.Score >= S as Rank;

На самом деле S здесь соответствует a.Score:

select 
	a.Score as Score,
	(select count(distinct b.Score) 
   from Scores b 
   where b.Score >= a.Score) as Rank  -- 将S替代成了a.Score
from Scores a
order by a.Score DESC;

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

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

грамматика

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

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

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

  • Специализированные оконные функции, такие как ранг, плотность_ранга, номер_строки и т. д.
  • Агрегатные функции, такие как сумма, среднее, количество, максимум, минимум и т. д.

Функции

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

rank/dense_rank/row_number

В MySQL8.X или кусте есть три специальные оконные функции:

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

Используйте пример, чтобы проиллюстрировать, где отражается разница в сортировке трех функций. Теперь, учитывая пять оценок: 93, 93, 85, 80, 75, результаты, полученные с использованием трех различных оконных функций, таковы:

1. Использование DENSE_RANK() для ранжирования даст: 1, 1, 2, 3, 4

2. Использование RANK() для ранжирования даст: 1, 1, 3, 4, 5

3, используйте row_number() для ранжирования: 1, 2, 3, 4, 5

Наконец, для иллюстрации разницы используется таблица: на следующем рисунке показаны данные, подлежащие сортировке.

Таблица и разница после ранжирования по 3 функциям:

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

MySQL5 реализует оконные функции

В MySQL 8 уже есть встроенные оконные функции, но их нет в MySQL.Ниже описано, как реализовать функции трех вышеуказанных оконных функций в MySQL 5.

1. Реализовать функцию row_number: непрерывное ранжирование

Процесс реализации не сложен: прямой порядок убывания, просто нужно добавить ранжирование, функция автоматического добавления 1, реализация row_number относится к английской статье;Woohoo. MySQL tutorial.org/MySQL-row_you…

SET @row_number = 0;   -- 设置变量

SELECT 
    (@row_number:=@row_number + 1) AS num,  -- 变量的自加1
    firstName, 
    lastName
FROM
    employees
ORDER BY firstName, lastName    
LIMIT 5;


set @rank := 0;  -- 设置初始值为0
select 
	name, 
	price,
	(@rank := @rank + 1) as row_number 
from products
order by price desc;

2. Реализуйте функцию ранга: прыгайте бок о бок

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;

3. Реализуйте функцию плотности_ранга: сопоставление непрерывного ранжирования

select p1.name, p1.price,
    (select count(distinct p2.price)   -- 价格去重处理
     from products p2 
     where p2.price > p1.price) + 1 as dense_rank
from products p1
order by dense_rank;

Суммировать

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

  • У Hive или MySQL8 уже есть функции, которые можно реализовать
  • В MySQL 5 вам нужно писать статистику сценариев в соответствии с потребностями различных сценариев.
  • Использование трех видов функций открытого окна должно быть освоено
Категории