Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / FAQ: Выборка первой/последней записи в группах / 25 сообщений из 37, страница 1 из 2
  1  все
16.08.2009, 08:45
    #36146200
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FAQ: Выборка первой/последней записи в группах
Задача: Выборка первой/последней записи в группах.

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

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

Задача требует уточнения: возможны 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, 15:42
    #36146396
mahoune
mahoune
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FAQ: Выборка первой/последней записи в группах
Вах, спасибо большое! Видно тебя в последнее время утомили вопросы на эту тему по 5 штук за день! :)

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

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

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

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

П.П.С

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

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

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

Код: plaintext
.mahoune.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.09.2009, 18:31
    #36218198
Яростный Меч
Яростный Меч
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FAQ: Выборка первой/последней записи в группах
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, 19:46
    #36218285
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FAQ: Выборка первой/последней записи в группах
Яростный Меч, -- зачет!

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

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

.mahoune.

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

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

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

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

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

>> А разве запрос С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:44
    #36977378
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FAQ: Выборка первой/последней записи в группах
javajdbcДолжно быть:Поправил в исходном посте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
15.06.2012, 17:48
    #37840139
Шогал
Шогал
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FAQ: Выборка первой/последней записи в группах
Вариант, когда нужно вывести по 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
| Ответить | Цитировать | Написать  
26.06.2012, 08:03
    #37854270
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FAQ: Выборка первой/последней записи в группах
замечание: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
| Ответить | Цитировать | Написать  
30.06.2012, 23:22
    #37861839
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FAQ: Выборка первой/последней записи в группах
Шогал,

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

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

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

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

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

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

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

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

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

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

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


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