Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / MySQL |
![]() ![]() |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
Задача: Выборка первой/последней записи в группах. Примеры задач: -- выбрать самый последний пост каждого юзера -- выбрать самый крупный заказ по каждому товару -- выбрать для каждого отдела работника с самой крупной зарплатой, и так далее Предварительный анализ. Задача требует уточнения: возможны 4 варианта логики (назовем их Т1, Т2, Т3 и Т4): Т1: Для ВСЕХ отделов вывести ОДНОГО работника. Если в отделе нет работников, вывести NULL, если двое и больше работников имеют одинаковые максимальные зарплаты то вывести первого по ИД. Т2: Для ВСЕХ отделов вывести ОДНОГО ИЛИ БОЛЕЕ работников с максимальной зарплатой. Если в отделе нет работников, вывести NULL Т3: Для НЕПУСТЫХ отделов вывести ОДНОГО работника. Если двое и больше работников имеют одинаковые максимальные зарплаты то вывести первого по ИД. Т4: Для НЕПУСТЫХ отделов вывести ОДНОГО ИЛИ БОЛЕЕ работников с максимальной зарплатой. Постановка тестовой задачи и структура таблиц: create table user( id int not null auto_INCREMENT, name varchar(10), PRIMARY KEY (id) )ENGINE=innodb; create table post( id int not null auto_INCREMENT, user_id int, topic varchar(10), score int, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES user(id) )ENGINE=InnoDB; CREATE INDEX user_score_idx ON post (user_id,score ); Примерный бизнес смысл: юзеры создают сообщения, которые имеют некую оценку (например другие юзеры оценивают *интересность* сообщения). Надо выбрать для каждого юзера самый интересный (по оценкам) пост. Варинаты запросов. Все задачи (Т1..Т4) можно решить несколькими способами. Рассмотрим варинаты с указанием подходяшей задачи Для удобства назовем СКЛ-ы С1..С5 С1: Агрегатный подселект в FROM блоке -- задача Т4 select u.id,name,topic,score from ( select p1.user_id, max(p1.score) max_score from post p1 group by p1.user_id ) zz join post p on zz.max_score=p.score and zz.user_id = p.user_id join user u on u.id=p.user_id С2: MAX(salary) подселект в WHERE блоке -- решение для Т3 select u.id, u.name, p0.topic, p0.score from user u join post p0 on p0.id = (select max(id) from post p1 where p1.user_id=u.id and p1.score = (select max(p2.score) from post p2 where p2.user_id=p1.user_id)) C3: (ORDER BY salary LIMIT 1) подселект в WHERE блоке -- задача Т3 select u.id, u.name, p0.topic, p0.score from user u join post p0 on p0.id = ( select p1.id from post p1 where p1.user_id=u.id order by -p1.score, -p1.id limit 1) C4: Двойной левый джоинт с неравенством и проверкой на NULL -- задача Т2 select u.id, u.name, p1.topic, p1.score from user u left join post p1 on u.id = p1.user_id left join post p2 on p1.user_id=p2.user_id and p1.score < p2.score where p2.id is null С5: Использование переменных -- относительно сложный метод -- смотрите по ссылкам ниже. Рекомендации: Разные меторы решения могут быть быстрее или медленнее в зависимости от задачи и размера таблиц. Т1 -- С2, C3, С5, С4 Т2 -- С4, С2, Т3 -- С2, С4 Т4 -- С1, С5 Референсы: //https://www.sql.ru/forum/actualthread.aspx?bid=6&tid=613714 //https://www.sql.ru/forum/actualthread.aspx?bid=6&tid=611929&pg=3 P.S. Собрать коллекцию СКЛ-ов помогла дискуссия с ![]() ![]() Сообщение было отредактировано: 26 фев 14, 21:15 |
16 авг 09, 08:45 [7543220] Ответить | Цитировать Сообщить модератору |
mahoune Member Откуда: Moscow Сообщений: 5653 |
Вах, спасибо большое! Видно тебя в последнее время утомили вопросы на эту тему по 5 штук за день! :).mahoune. |
16 авг 09, 15:42 [7543589] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
mahoune, Задача вполне интересная и реально часто встречается. На самом деле вопросы не утомили, но почти всегда нужно дополнительно тратить время на уточнение точной логики. При наличии 4 подзадач и по 3-5 решений для каждого случая матрица рекомендованых решений будет полезна. П.С. а можно добавить в основной пост: Собрать коллекцию СКЛ-ов помогла дискуссия с Lonely.K и Alex_Ustinov. П.П.С В моем броузере (FF-352) ссылки получаются направлены на какую-то фигню, типа "http://xn---1-mlc7bv/". Ето можно подправить? |
17 авг 09, 07:11 [7544422] Ответить | Цитировать Сообщить модератору |
mahoune Member Откуда: Moscow Сообщений: 5653 |
Текст добавил, топик отформатировал, добавил в список FAQ..mahoune. |
17 авг 09, 12:36 [7545771] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
mahoune, Спасибо! |
17 авг 09, 17:05 [7547796] Ответить | Цитировать Сообщить модератору |
mahoune Member Откуда: Moscow Сообщений: 5653 |
Тебе спасибо! У меня мозг сломался на втором посте, когда я стал разбираться что к чему! :).mahoune. |
17 авг 09, 18:02 [7548161] Ответить | Цитировать Сообщить модератору |
Яростный Меч Member [скрыт] Откуда: Сообщений: 28874 |
Вариант решения с одним обращением к таблице. Пример. Имеется табличка posts create table posts (usr int, dt datetime, post varchar(100));Какой юзер в какое время что написал. А вот сабжевый запрос: SELECT usr, SUBSTR(MAX(CONCAT(dt, post)), 1, 19) dt, SUBSTR(MAX(CONCAT(dt, post)), 20) post FROM posts GROUP BY usr post "паровозиком" цепляется к датевремени и попадает в максимум, потом оттуда извлекается. Насчет универсальности метода не знаю, но во многих случаях прокатит. |
||
25 сен 09, 18:31 [7710123] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
Яростный Меч, -- зачет! вертелась у меня похожая мысль, вот здесь используется сложение ИД и цислового значения для нахождения махимума по обоим параметрам https://www.sql.ru/forum/actualthread.aspx?bid=6&tid=613714#6445977 Похоже, что метод будет вполне универсальным для задачь типа Т3 (да и Т1). Можно добавить в конкат ИД или любой другой параметер -- главное что бы он был фиксированой или (подзаполненой до фиксированой) длины. .mahoune. а можно поставить предыдуший пост сразу после основного, а обсуждения может даже убрать? |
25 сен 09, 19:46 [7710394] Ответить | Цитировать Сообщить модератору |
mahoune Member Откуда: Moscow Сообщений: 5653 |
Да, обсуждения порежу, а пост поставить вверх не выйдет. А что касательно метода, у меня давно руки чесались, я так в экселе обычно его юзаю, и все думал, как его можно к БД прикрутить .mahoune. |
25 сен 09, 19:47 [7710398] Ответить | Цитировать Сообщить модератору |
miksoft Member Откуда: Сообщений: 38692 |
|
||
25 сен 09, 19:48 [7710399] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
miksoft, НУ для повседневных типов перевод в строчку -- сортировку не изменит (варчар добить пробелами справа, а номер добить нулями слева). МОжет для каких-то екзоотических типов (нех? юникоде?) ето и проблема. |
26 сен 09, 03:51 [7711021] Ответить | Цитировать Сообщить модератору |
qwerty90210 Member Откуда: Сообщений: 1 |
А как модифицировать С2, стобы сразу получать имя сотрудника с мин. и макс. зарплатой? |
22 окт 09, 12:52 [7823321] Ответить | Цитировать Сообщить модератору |
lonely.k Member Откуда: default city Сообщений: 1332 |
qwerty90210, поясните, что Вы имеете ввиду? для каждого отдела вывести двух сотрудников (мин и макс)? с какой логикой (Т1, Т2, Т3 и Т4)? |
22 окт 09, 16:30 [7825327] Ответить | Цитировать Сообщить модератору |
Между сообщениями интервал более 1 года. |
не понятно
Guest |
А разве запрос С1 верный? Почему там join по max(score) = id ? Зарплата приравнивается к ID ? Да и user_id не учитывается |
25 ноя 10, 12:57 [9838012] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
не понятно, >> А разве запрос С1 верный? Почему там join по max(score) = id ? >> Зарплата приравнивается к ID ? Угу , есть такое дело..... типография приносит извинения. Должно быть: select u.id,name,topic,score from ( select max(p1.score) max_score from post p1 group by p1.user_id ) zz join post p on zz.max_score=p.score join user u on u.id=p.user_id >> Да и user_id не учитывается Юзер подвязывается в последней строчке. Тут вроде все правильно. |
25 ноя 10, 16:40 [9840111] Ответить | Цитировать Сообщить модератору |
miksoft Member Откуда: Сообщений: 38692 |
|
||
25 ноя 10, 16:44 [9840140] Ответить | Цитировать Сообщить модератору |
Между сообщениями интервал более 1 года. |
Шогал Member Откуда: Калининград Сообщений: 1339 |
Вариант, когда нужно вывести по N постов каждого юзера (в примере 3):select u.id, u.name, p.topic, p.score, find_in_set(p.id,( select group_concat(p2.id order by p2.score desc) from post p2 where p.user_id = p2.user_id )) r from user u left join post p on u.id = p.user_id having (r between 1 and 3) or r is null order by u.id, r Функция зависима от СУБД, но в нормальных СУБД давно есть rank over. Данный пример что-то вроде эмуляции этой функции. Также не стоит забывать про ограничение group_concat, но по дефолту там около 1024 символов, для выборки небольшого кол-ва N с числовым id хватит за глаза. |
15 июн 12, 17:48 [12722307] Ответить | Цитировать Сообщить модератору |
tanglir Member Откуда: Сообщений: 28966 |
замечание:
|
||||
26 июн 12, 08:03 [12774203] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
Шогал, да, интересное добавление. В другом FAQ есть описание "ранк" и "денсе ранк" имплементация для таких запросов MYSQL. (a.k.a. Top-N query) FAQ: Нумерация строк и другие вопросы про использование переменных Я подозреваю что на переменный будет быстрее, хотя интересно будет проверить... |
30 июн 12, 23:22 [12799719] Ответить | Цитировать Сообщить модератору |
javajdbc Member Откуда: Montreal Сообщений: 17715 |
Да, конечно...Вы правы. Наверно каждую задачу можно решить каждым методом. например Т4 можно сделать методом С4 если убрать ЛЕФТ в третьей строчке. |
||
30 июн 12, 23:30 [12799744] Ответить | Цитировать Сообщить модератору |
tanglir Member Откуда: Сообщений: 28966 |
javajdbc, наверное, можно, но показаны, как я понимаю, наиболее подходящие. А тут я просто заметил явное несоответствие матрицы решений с описанием решения C3. |
1 июл 12, 04:17 [12800190] Ответить | Цитировать Сообщить модератору |
Между сообщениями интервал более 1 года. |
MasterZiv Member Откуда: Питер Сообщений: 34657 |
С1 должно наверное бытьselect u.id,name,topic,score from ( select p1.user_id, max(p1.score) max_score from post p1 group by p1.user_id ) zz join post p on zz.max_score=p.score and zz.user_id = p.user_id join user u on u.id=p.user_id Сообщение было отредактировано: 26 фев 14, 21:11 |
25 фев 14, 11:14 [15624271] Ответить | Цитировать Сообщить модератору |
MasterZiv Member Откуда: Питер Сообщений: 34657 |
Это вы ребята глубоко заблуждаетесь... |
||
25 фев 14, 11:28 [15624401] Ответить | Цитировать Сообщить модератору |
miksoft Member Откуда: Сообщений: 38692 |
|
||||
25 фев 14, 12:33 [15624852] Ответить | Цитировать Сообщить модератору |
MasterZiv Member Откуда: Питер Сообщений: 34657 |
miksoft, Перевод даты в формат не YYYYMMDD, или в любой формат с месяцем в виде названия. |
26 фев 14, 10:20 [15630639] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
Все форумы / MySQL | ![]() |