Задать вопрос
@one2four
mysql

Как сделать sql запрос для показа топ 10 ключевых слов с группировкой и суммой метрик из другой таблицы?

Имеются 2 таблицы:

-- Таблица ключевых слов
CREATE TABLE `keywords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `profession` varchar(255) NOT NULL,
  `region` varchar(255) NOT NULL,
  `rating` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `keywords_rating_index` (`name`,`region`,`profession`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `keywords` (`name`, `profession`, `region`, `rating`) VALUES ("git",  "front-end developer",  "москва",  10), ("git",  "дизайнер",  "москва",  3), ("git",  "backend developer",  "москва",  12), ("git",  "backend developer",  "новосибирск",  7), ("MySql",  "backend developer",  "новосибирск",  7), ("MySql",  "аналитик",  "новосибирск",  5);

-- Таблица зарплат
CREATE TABLE `salaries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keywordId` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
   `freq` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_keywordId` (`keywordId`),
  CONSTRAINT `fk_keywordId` FOREIGN KEY (`keywordId`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `salaries` (`keywordId`, `salary`, `freq`) VALUES (1, 100000, 3), (1, 150000, 2),  (1, 200000, 1), (2, 100000, 5), (2, 130000, 3), (2, 150000, 7), (3, 100000, 1), (3, 150000, 7), (3, 170000, 4), (4, 100000, 5), (4, 125000, 5), (4, 150000, 2), (5, 100000, 8), (5, 150000, 1);

Цель:
1. Выбрать из таблицы `keywords` слова, с профессией "backend developer", и, просуммировав rating, определить топ 10 слов.
2. Выбрать из таблицы `salaries` зарплаты тех ключевых слов, которые участвовали в группировке в первом запросе.

<!-- топ ключевых слов, для профессии "backend developer",  -->
+-------+-------------+
| name  | sum(rating) |
+-------+-------------+
| git   |          19 |
| MySql |           7 |
+-------+-------------+

<!-- зарплаты для ключевого слова git, которые участвовали в группировке -->
+----+-----------+--------+-----------+
| id | keywordId | salary | sum(freq) |
+----+-----------+--------+-----------+
|  7 |         3 | 100000 |         6 |
| 11 |         4 | 125000 |         5 |
|  8 |         3 | 150000 |         9 |
|  9 |         3 | 170000 |         4 |
+----+-----------+--------+-----------+

Как получить последний результат используя простой SQL запрос?

все записи из обоих таблиц
mysql> select * from keywords;
+----+-------+---------------------+------------------------+--------+
| id | name  | profession          | region                 | rating |
+----+-------+---------------------+------------------------+--------+
|  1 | git   | front-end developer | москва                 |     10 |
|  2 | git   | дизайнер            | москва                 |      3 |
|  3 | git   | backend developer   | москва                 |     12 |
|  4 | git   | backend developer   | новосибирск            |      7 |
|  5 | MySql | backend developer   | новосибирск            |      7 |
|  6 | MySql | аналитик            | новосибирск            |      5 |
+----+-------+---------------------+------------------------+--------+

mysql> select * from salaries;
+----+-----------+--------+------+
| id | keywordId | salary | freq |
+----+-----------+--------+------+
|  1 |         1 | 100000 |    3 |
|  2 |         1 | 150000 |    2 |
|  3 |         1 | 200000 |    1 |
|  4 |         2 | 100000 |    5 |
|  5 |         2 | 130000 |    3 |
|  6 |         2 | 150000 |    7 |
|  7 |         3 | 100000 |    1 |
|  8 |         3 | 150000 |    7 |
|  9 |         3 | 170000 |    4 |
| 10 |         4 | 100000 |    5 |
| 11 |         4 | 125000 |    5 |
| 12 |         4 | 150000 |    2 |
| 13 |         5 | 100000 |    8 |
| 14 |         5 | 150000 |    1 |
+----+-----------+--------+------+

PS. в обоих таблицах больше 10 млн записей.
  • Вопрос задан
  • 590 просмотров
33 комментария
Подписаться 3 Простой 33 комментария
Пригласить эксперта
Ответы на вопрос 1
@DoctorGarfild
1.
with t as (
 select name, sum(rating), row_number() over(order by sum(rating)) r 
 from keywords 
 where profession = 'backend developer'
 group by name
)
select * 
from t 
where r<=10


2.
with t as (
 select id, name, sum(rating), row_number() over(order by sum(rating)) r 
 from keywords 
 where profession = 'backend developer'
 group by id, name
)
select  s.id , s.keywordId , s.salary, , sum(freq) 
from t  left join salaries  s on t.id = s.keywordId
where r<=10
group by s.id , s.keywordId , s.salary


3.
select  s.id , s.keywordId , s.salary,  sum(freq) 
from keywords k left join salaries  s on k.id = s.keywordId
where profession = 'backend developer'
group by s.id , s.keywordId , s.salary
order by sum(rating)
limit 10
Ответ написан
Комментировать
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
Вакансии с Хабр Карьеры
Программист C++ Builder / базы данных MySQL
RU Electronics • Москва
от 180 000 до 200 000 ₽
Второй ведущий разработчик (Fullstack, PHP, Laravel, Vue, Mysql)
Donatov.net
от 100 000 до 300 000 ₽
PHP FullStack Developer (Middle+)
ГК «Талант» • Сочи
от 100 000 до 200 000 ₽
Ещё вакансии
Заказы с Хабр Фриланса
Разработать скрипт на терафлрме для деплоймеета
06 мая 2024, в 01:10
30000 руб./за проект
Правки в Laravel админку с тг ботами
05 мая 2024, в 23:43
8000 руб./за проект
Прикрутить новую верстку
05 мая 2024, в 23:39
3000 руб./за проект
Ещё заказы