ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / FAQ: Выборка первой/последней записи в группах
25 сообщений из 37, страница 1 из 2
  1  все
FAQ: Выборка первой/последней записи в группах
    #36146200
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Задача: Выборка первой/последней записи в группах.

Примеры задач:
-- выбрать самый последний пост каждого юзера
-- выбрать самый крупный заказ по каждому товару
-- выбрать для каждого отдела работника с самой крупной зарплатой, и так далее

Предварительный анализ.

Задача требует уточнения: возможны 4 варианта логики (назовем их Т1, Т2, Т3 и Т4):

Т1: Для ВСЕХ отделов вывести ОДНОГО работника. Если в отделе нет
работников, вывести NULL, если двое и больше работников имеют
одинаковые максимальные зарплаты то вывести первого по ИД.

Т2: Для ВСЕХ отделов вывести ОДНОГО ИЛИ БОЛЕЕ работников с
максимальной зарплатой. Если в отделе нет
работников, вывести NULL

Т3: Для НЕПУСТЫХ отделов вывести ОДНОГО работника.
Если двое и больше работников имеют
одинаковые максимальные зарплаты то вывести первого по ИД.

Т4: Для НЕПУСТЫХ отделов вывести ОДНОГО ИЛИ БОЛЕЕ работников с
максимальной зарплатой.

Постановка тестовой задачи и структура таблиц:

Код: sql
\r\ncreate table user(\r\n  id int not null auto_INCREMENT, \r\n  name varchar(10), \r\n  PRIMARY KEY (id)\r\n)ENGINE=innodb;\r\n\r\ncreate table post(\r\n  id int not null auto_INCREMENT,\r\n  user_id int,\r\n  topic varchar(10),\r\n  score int,\r\n  PRIMARY KEY (id),\r\n  FOREIGN KEY (user_id) REFERENCES user(id)\r\n)ENGINE=InnoDB;\r\n\r\nCREATE INDEX user_score_idx ON post (user_id,score );\r\n
\r\n

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

Варинаты запросов.

Все задачи (Т1..Т4) можно решить несколькими способами.
Рассмотрим варинаты с указанием подходяшей задачи
Для удобства назовем СКЛ-ы С1..С5

С1: Агрегатный подселект в FROM блоке -- задача Т4

Код: sql
\r\nselect u.id,name,topic,score from (\r\n     select p1.user_id, max(p1.score) max_score \r\n       from post p1\r\n      group by p1.user_id ) zz\r\n  join post p on zz.max_score=p.score and zz.user_id = p.user_id\r\n  join user u on u.id=p.user_id\r\n
\r\n

С2: MAX(salary) подселект в WHERE блоке -- решение для Т3

Код: sql
\r\n   select u.id, u.name, p0.topic, p0.score \r\n    from user u join post p0\r\n      on p0.id = (select max(id) from post p1 \r\n                   where p1.user_id=u.id\r\n                     and p1.score = (select max(p2.score) \r\n                                       from post p2 \r\n                                      where p2.user_id=p1.user_id))\r\n
\r\n


C3: (ORDER BY salary LIMIT 1) подселект в WHERE блоке -- задача Т3

Код: sql
\r\n    select u.id, u.name, p0.topic, p0.score \r\n      from user u join post p0\r\n        on p0.id = ( select p1.id \r\n                       from post p1 \r\n                      where p1.user_id=u.id \r\n                      order by -p1.score, -p1.id \r\n                      limit 1)\r\n
\r\n

C4: Двойной левый джоинт с неравенством и проверкой на NULL -- задача Т2

Код: sql
\r\n   select u.id, u.name, p1.topic, p1.score \r\n    from \r\n       user u left join post p1 \r\n                     on u.id = p1.user_id\r\n              left join post p2 \r\n                     on p1.user_id=p2.user_id and p1.score < p2.score \r\n    where p2.id is null\r\n
\r\n

С5: Использование переменных -- относительно сложный метод
-- смотрите по ссылкам ниже.

Рекомендации:

Разные меторы решения могут быть быстрее или медленнее в
зависимости от задачи и размера таблиц.

Т1 -- С2, C3, С5, С4
Т2 -- С4, С2,
Т3 -- С2, С4
Т4 -- С1, С5

Референсы:

///topic/613714
///topic/611929&pg=3

P.S. Собрать коллекцию СКЛ-ов помогла дискуссия с Lonely.K и Alex_Ustinov
...
Рейтинг: 0 / 0
16.08.2009, 08:45
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36146396
Фотография mahoune
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mahoune
Участник
Вах, спасибо большое! Видно тебя в последнее время утомили вопросы на эту тему по 5 штук за день! :)

Код: plaintext
.mahoune.
...
Рейтинг: 0 / 0
16.08.2009, 15:42
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36146818
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
mahoune,

Задача вполне интересная и реально часто встречается.
На самом деле вопросы не утомили, но почти всегда нужно
дополнительно тратить время на уточнение точной логики.
При наличии 4 подзадач и по 3-5 решений для каждого случая
матрица рекомендованых решений будет полезна.

П.С. а можно добавить в основной пост:

Собрать коллекцию СКЛ-ов помогла дискуссия с Lonely.K и Alex_Ustinov.

П.П.С

В моем броузере (FF-352) ссылки получаются направлены на
какую-то фигню, типа "http://xn---1-mlc7bv/".
Ето можно подправить?
...
Рейтинг: 0 / 0
17.08.2009, 07:11
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36147381
Фотография mahoune
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mahoune
Участник
Текст добавил, топик отформатировал, добавил в список FAQ .

Код: plaintext
.mahoune.
...
Рейтинг: 0 / 0
17.08.2009, 12:36
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36148329
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
mahoune,

Спасибо!
...
Рейтинг: 0 / 0
17.08.2009, 17:05
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36148489
Фотография mahoune
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mahoune
Участник
Тебе спасибо! У меня мозг сломался на втором посте, когда я стал разбираться что к чему! :)

Код: plaintext
.mahoune.
...
Рейтинг: 0 / 0
17.08.2009, 18:02
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36218198
Фотография Яростный Меч
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Яростный Меч
Участник
javajdbc Задача: Выборка первой/последней записи в группах.

Примеры задач:
-- выбрать самый последний пост каждого юзера
Вариант решения с одним обращением к таблице.

Пример.

Имеется табличка posts
Код: plaintext
1.
create table posts (usr int, dt datetime, post varchar( 100 ));
Какой юзер в какое время что написал.

А вот сабжевый запрос:
Код: plaintext
1.
2.
3.
SELECT usr, SUBSTR(MAX(CONCAT(dt, post)),  1 ,  19 ) dt, SUBSTR(MAX(CONCAT(dt, post)),  20 ) post
FROM posts
GROUP BY usr

post "паровозиком" цепляется к датевремени и попадает в максимум, потом оттуда извлекается.
Насчет универсальности метода не знаю, но во многих случаях прокатит.
...
Рейтинг: 0 / 0
25.09.2009, 18:31
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36218285
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Яростный Меч, -- зачет!

вертелась у меня похожая мысль, вот здесь используется
сложение ИД и цислового значения для нахождения махимума по обоим параметрам
/topic/613714#6445977

Похоже, что метод будет вполне универсальным для задачь типа Т3 (да и Т1).
Можно добавить в конкат ИД или любой другой параметер -- главное что бы
он был фиксированой или (подзаполненой до фиксированой) длины.

.mahoune.

а можно поставить предыдуший пост сразу после основного,
а обсуждения может даже убрать?
...
Рейтинг: 0 / 0
25.09.2009, 19:46
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36218288
Фотография mahoune
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mahoune
Участник
Да, обсуждения порежу, а пост поставить вверх не выйдет.

А что касательно метода, у меня давно руки чесались, я так в экселе обычно его юзаю, и все думал, как его можно к БД прикрутить

Код: plaintext
.mahoune.
...
Рейтинг: 0 / 0
25.09.2009, 19:47
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36218289
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
javajdbcМожно добавить в конкат ИД или любой другой параметер -- главное что бы
он был фиксированой или (подзаполненой до фиксированой) длины.Не только. Еще нужно чтобы строковое (или то, к которому приводится) представление сортировалось так же, как исходное. Иначе MAX может сработать неправильно.
...
Рейтинг: 0 / 0
25.09.2009, 19:48
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36218612
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
miksoft,

НУ для повседневных типов перевод в строчку -- сортировку не изменит
(варчар добить пробелами справа, а номер добить нулями слева).
МОжет для каких-то екзоотических типов (нех? юникоде?) ето и проблема.
...
Рейтинг: 0 / 0
26.09.2009, 03:51
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36266629
qwerty90210
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwerty90210
Участник
А как модифицировать С2, стобы сразу получать имя сотрудника с мин. и макс. зарплатой?
...
Рейтинг: 0 / 0
22.10.2009, 12:52
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36267453
lonely.k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
lonely.k
Участник
qwerty90210,

поясните, что Вы имеете ввиду? для каждого отдела вывести двух сотрудников (мин и макс)? с какой логикой (Т1, Т2, Т3 и Т4)?
...
Рейтинг: 0 / 0
22.10.2009, 16:30
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
FAQ: Выборка первой/последней записи в группах
    #36976642
не понятно
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не понятно
Гость
А разве запрос С1 верный? Почему там join по max(score) = id ? Зарплата приравнивается к ID ? Да и user_id не учитывается
...
Рейтинг: 0 / 0
25.11.2010, 12:57
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36977363
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
не понятно,

>> А разве запрос С1 верный? Почему там join по max(score) = id ?
>> Зарплата приравнивается к ID ?

Угу , есть такое дело..... типография приносит извинения.
Должно быть:

Код: plaintext
1.
2.
3.
4.
5.
 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 не учитывается

Юзер подвязывается в последней строчке.
Тут вроде все правильно.
...
Рейтинг: 0 / 0
25.11.2010, 16:40
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #36977378
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
javajdbcДолжно быть:Поправил в исходном посте.
...
Рейтинг: 0 / 0
25.11.2010, 16:44
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
FAQ: Выборка первой/последней записи в группах
    #37840139
Шогал
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Шогал
Участник
Вариант, когда нужно вывести по N постов каждого юзера (в примере 3):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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 хватит за глаза.
...
Рейтинг: 0 / 0
15.06.2012, 17:48
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #37854270
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
замечание:javajdbc C3: (ORDER BY salary LIMIT 1) подселект в WHERE блоке -- задача Т3
javajdbc Рекомендации:

Разные методы решения могут быть быстрее или медленнее в
зависимости от задачи и размера таблиц.

Т1 -- С2, C3, С5, С4
Т2 -- С4, С2,
Т3 -- С2, С4 --где С3?
Т4 -- С1, С5
...
Рейтинг: 0 / 0
26.06.2012, 08:03
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #37861839
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Шогал,

да, интересное добавление.

В другом FAQ есть описание "ранк" и "денсе ранк"
имплементация для таких запросов MYSQL.
(a.k.a. Top-N query)
FAQ: Нумерация строк и другие вопросы про использование переменных

Я подозреваю что на переменный будет быстрее, хотя интересно будет проверить...
...
Рейтинг: 0 / 0
30.06.2012, 23:22
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #37861842
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
tanglirТ1 -- С2, C3, С5, С4
Т2 -- С4, С2,
Т3 -- С2, С4 --где С3?
Т4 -- С1, С5[/quot]

Да, конечно...Вы правы.

Наверно каждую задачу можно решить каждым методом.

например Т4 можно сделать методом С4 если убрать ЛЕФТ
в третьей строчке.
...
Рейтинг: 0 / 0
30.06.2012, 23:30
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #37861906
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
javajdbc, наверное, можно, но показаны, как я понимаю, наиболее подходящие. А тут я просто заметил явное несоответствие матрицы решений с описанием решения C3.
...
Рейтинг: 0 / 0
01.07.2012, 04:17
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
FAQ: Выборка первой/последней записи в группах
    #38571322
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
С1 должно наверное быть
Код: sql
1.
2.
3.
4.
5.
6.
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
...
Рейтинг: 0 / 0
25.02.2014, 11:14
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #38571351
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
javajdbcmiksoft,

НУ для повседневных типов перевод в строчку -- сортировку не изменит
(варчар добить пробелами справа, а номер добить нулями слева).
МОжет для каких-то екзоотических типов (нех? юникоде?) ето и проблема.

Это вы ребята глубоко заблуждаетесь...
...
Рейтинг: 0 / 0
25.02.2014, 11:28
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #38571438
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
MasterZivjavajdbcmiksoft,

НУ для повседневных типов перевод в строчку -- сортировку не изменит
(варчар добить пробелами справа, а номер добить нулями слева).
МОжет для каких-то екзоотических типов (нех? юникоде?) ето и проблема.

Это вы ребята глубоко заблуждаетесь...Контрпример будет?
...
Рейтинг: 0 / 0
25.02.2014, 12:33
| Ответить | Цитировать | Написать  
FAQ: Выборка первой/последней записи в группах
    #38572491
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
miksoft,

Перевод даты в формат не YYYYMMDD, или в любой формат с месяцем в виде названия.
...
Рейтинг: 0 / 0
26.02.2014, 10:20
| Ответить | Цитировать | Написать  
25 сообщений из 37, страница 1 из 2
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / FAQ: Выборка первой/последней записи в группах
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]