Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 FAQ: Выборка первой/последней записи в группах  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17121
Задача: Выборка первой/последней записи в группах.

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

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

Задача требует уточнения: возможны 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

Референсы:

//http://www.sql.ru/forum/actualthread.aspx?bid=6&tid=613714
//http://www.sql.ru/forum/actualthread.aspx?bid=6&tid=611929&pg=3

P.S. Собрать коллекцию СКЛ-ов помогла дискуссия с Lonely.K и Alex_Ustinov
16 авг 09, 08:45    [7543220]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
mahoune
Member

Откуда: Moscow
Сообщений: 5663
Вах, спасибо большое! Видно тебя в последнее время утомили вопросы на эту тему по 5 штук за день! :)

.mahoune.
16 авг 09, 15:42    [7543589]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17121
mahoune,

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

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

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

П.П.С

В моем броузере (FF-352) ссылки получаются направлены на
какую-то фигню, типа "http://xn---1-mlc7bv/".
Ето можно подправить?
17 авг 09, 07:11    [7544422]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
mahoune
Member

Откуда: Moscow
Сообщений: 5663
Текст добавил, топик отформатировал, добавил в список FAQ.

.mahoune.
17 авг 09, 12:36    [7545771]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17121
mahoune,

Спасибо!
17 авг 09, 17:05    [7547796]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
mahoune
Member

Откуда: Moscow
Сообщений: 5663
Тебе спасибо! У меня мозг сломался на втором посте, когда я стал разбираться что к чему! :)

.mahoune.
17 авг 09, 18:02    [7548161]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
Яростный Меч
Member [скрыт]

Откуда:
Сообщений: 29285
javajdbc
Задача: Выборка первой/последней записи в группах.

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

Вариант решения с одним обращением к таблице.

Пример.

Имеется табличка 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]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17121
Яростный Меч, -- зачет!

вертелась у меня похожая мысль, вот здесь используется
сложение ИД и цислового значения для нахождения махимума по обоим параметрам
http://www.sql.ru/forum/actualthread.aspx?bid=6&tid=613714#6445977

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

.mahoune.

а можно поставить предыдуший пост сразу после основного,
а обсуждения может даже убрать?
25 сен 09, 19:46    [7710394]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
mahoune
Member

Откуда: Moscow
Сообщений: 5663
Да, обсуждения порежу, а пост поставить вверх не выйдет.

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

.mahoune.
25 сен 09, 19:47    [7710398]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
miksoft
Member

Откуда:
Сообщений: 36323
javajdbc
Можно добавить в конкат ИД или любой другой параметер -- главное что бы
он был фиксированой или (подзаполненой до фиксированой) длины.
Не только. Еще нужно чтобы строковое (или то, к которому приводится) представление сортировалось так же, как исходное. Иначе MAX может сработать неправильно.
25 сен 09, 19:48    [7710399]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17121
miksoft,

НУ для повседневных типов перевод в строчку -- сортировку не изменит
(варчар добить пробелами справа, а номер добить нулями слева).
МОжет для каких-то екзоотических типов (нех? юникоде?) ето и проблема.
26 сен 09, 03:51    [7711021]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
qwerty90210
Member

Откуда:
Сообщений: 1
А как модифицировать С2, стобы сразу получать имя сотрудника с мин. и макс. зарплатой?
22 окт 09, 12:52    [7823321]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
lonely.k
Member

Откуда: default city
Сообщений: 1332
qwerty90210,

поясните, что Вы имеете ввиду? для каждого отдела вывести двух сотрудников (мин и макс)? с какой логикой (Т1, Т2, Т3 и Т4)?
22 окт 09, 16:30    [7825327]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: FAQ: Выборка первой/последней записи в группах  [new]
не понятно
Guest
А разве запрос С1 верный? Почему там join по max(score) = id ? Зарплата приравнивается к ID ? Да и user_id не учитывается
25 ноя 10, 12:57    [9838012]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17121
не понятно,

>> А разве запрос С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]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
miksoft
Member

Откуда:
Сообщений: 36323
javajdbc
Должно быть:
Поправил в исходном посте.
25 ноя 10, 16:44    [9840140]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: FAQ: Выборка первой/последней записи в группах  [new]
Шогал
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]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
tanglir
Member

Откуда:
Сообщений: 30154
замечание:
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
26 июн 12, 08:03    [12774203]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17121
Шогал,

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

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

Я подозреваю что на переменный будет быстрее, хотя интересно будет проверить...
30 июн 12, 23:22    [12799719]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17121
tanglir
Т1 -- С2, C3, С5, С4
Т2 -- С4, С2,
Т3 -- С2, С4 --где С3?
Т4 -- С1, С5
[/quot]

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

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

например Т4 можно сделать методом С4 если убрать ЛЕФТ
в третьей строчке.
30 июн 12, 23:30    [12799744]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
tanglir
Member

Откуда:
Сообщений: 30154
javajdbc, наверное, можно, но показаны, как я понимаю, наиболее подходящие. А тут я просто заметил явное несоответствие матрицы решений с описанием решения C3.
1 июл 12, 04:17    [12800190]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: FAQ: Выборка первой/последней записи в группах  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 33438
С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
25 фев 14, 11:14    [15624271]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 33438
javajdbc
miksoft,

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


Это вы ребята глубоко заблуждаетесь...
25 фев 14, 11:28    [15624401]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
miksoft
Member

Откуда:
Сообщений: 36323
MasterZiv
javajdbc
miksoft,

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


Это вы ребята глубоко заблуждаетесь...
Контрпример будет?
25 фев 14, 12:33    [15624852]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Выборка первой/последней записи в группах  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 33438
miksoft,

Перевод даты в формат не YYYYMMDD, или в любой формат с месяцем в виде названия.
26 фев 14, 10:20    [15630639]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / MySQL Ответить