Форум MySQL

Вроде вполне обычная задача, но что-то не могу придумать как её попроще решить.

Дана таблица:

id | group_id | some_field  1  | 1        | aaa  2  | 1        | bbb  3  | 1        | ccc  4  | 2        | ddd  5  | 2        | eee  6  | 2        | fff  7  | 2        | ggg  8  | 3        | hhh  9  | 3        | iii

Нужно одним запросом отгруппировать по полю group_id и получить по случайной строке у каждой группы. Под словом "случайной" я подразумеваю, что при каждом запросе должны возвращаться разные результаты. В каждой группе количество строк будет 1-5, не более. При обычном GROUP BY выдаются всегда одни и те же строки, как правило первые, которые встречаются, то есть:

id | group_id | some_field  1  | 1        | aaa  4  | 2        | ddd  8  | 3        | hhh

Мне же нужны рандомные строки, но обязательно по одной из каждой группы. Я чувствую, что к решению не с той стороны подхожу и можно как-то иначе сделать.

Нет GROUP BY эту проблему не решить... чисто решить эту задачу можно при помощи самообъединения. Если вам интересен этот путь, посмотрите тему по ссылке http://softtime.ru/forum/read.php?id_forum=3&id_theme=78954.
Можно конечно, попытаться сформировать список при помощи GROUP_CONCAT() и из него извлечь случайное значение при помощи запроса

SELECT   group_id,   GROUP_CONCAT(some_field ORDER BY RAND() SEPARATOR ', ') AS value FROM tbl GROUP BY group_id

Однако, у MySQL довольно скудные средства для обработки списков, поэтому первое значение придется извлекать самостоятельно уже не стороне клиента.

С GROUP_CONCAT вариант не подходит, так как нужно получать полностью строки, а не только some_field, это я для примера максимально упрощённую таблицу привёл. В оригинале там ещё связка с другой таблицей. Я так подумал, так как строк у меня будет заведомо немного в каждой группе, то мне проще получить их все в скрипт, а в нём уже выбрать случайно любые строки. Работать будет даже быстрее, чем городить ещё одну связку. Да и приходилось много раз слышать критику на ORDER BY RAND().

Спасибо.

Фактически это связано с мат.изъяном реляционной реализации (по крайней мере в MySQL). Декартово произведение - это всегда произведение кортежей, т.е. упорядоченных множеств, а вы сами знаете, что в таблице строки хранятся без определенного порядка, т.е. множество не упорядоченное, ORDER BY применяется уже после декартова произведения - отсюда и проблемы. Ни упорядочить, ни разупорядочить ничего нельзя.

Кстати в MySQL же ещё есть HAVING.

Не только в MySQL, это стандартное групповое условие, тут им вряд ли что-то можно сделать... по крайней мере ничего путного в голову не приходит.

Я вроде где-то на хабре читал, что HAVING - это фича MySQL, и в других БД её нет. Сам не знаю, потому что кроме MySQL ничего не использовал.

Нет, это какая-то ошибка, HAVING помоему ещё раньше SQL92 был введен в стандарт SQL, по крайней мере в SQL92 он уже точно был.

есть у меня одно непроверенное предположение...

SELECT t.* FROM (SELECT * FROM tbl_name ORDER BY RAND()) t GROUP BY group_id;

я думаю при любой величине таблицы, это будет быстрее чем ваш вариант "разборок" на РНР

Сделал денормализацию, засунул часть полей, которые нужны для выборки в основную таблицу, из которой буду выбирать данные. Так что теперь буду работать с одной таблицей. Попробую ваш запрос. Ну и проведу тесты производительности.

у меня запрос занял 24 секунды при количестве строк 270 тыс
причем что индексированные поля что нет на скорость не влияет
скиньте ваш вариант на РНР проверю на скорость
кстати денормализацию скорее всего придется обратно нормализовать)) на остальную часть запроса это повлияет не сильно

Готового варианта на php нет, так как у меня структура таблицы сложнее. Я ещё кое-что забыл. Неправильно задачу изначальную обрисовал. У меня не одна группа, а две. И из обоих нужно делать случайные выборки. Одна группа вложена в другую.

id | group1_id | group2_id | some_field -----------------------------------------  1  | 1         | 1         | 1-1-1  2  | 1         | 1         | 1-1-2  3  | 1         | 2         | 1-2-1  4  | 1         | 2         | 1-2-2  5  | 1         | 3         | 1-3-1  6  | 1         | 3         | 1-3-2  7  | 2         | 1         | 2-1-1  8  | 2         | 1         | 2-1-2  9  | 2         | 2         | 2-2-1  10 | 2         | 2         | 2-2-2  11 | 2         | 3         | 2-3-1  12 | 2         | 3         | 2-3-2  13 | 3         | 1         | 3-1-1  14 | 3         | 1         | 3-1-2  15 | 3         | 2         | 3-2-1  16 | 3         | 2         | 3-2-2  17 | 3         | 3         | 3-3-1  18 | 3         | 3         | 3-3-2 -----------------------------------------

Вот один из вариантов, который должен выдать запрос:

id | group1_id | group2_id | some_field -----------------------------------------  2  | 1         | 1         | 1-1-2  4  | 1         | 2         | 1-2-2  5  | 1         | 3         | 1-3-1  8  | 2         | 1         | 2-1-2  10 | 2         | 2         | 2-2-2  12 | 2         | 3         | 2-3-2  13 | 3         | 1         | 3-1-1  15 | 3         | 2         | 3-2-1  18 | 3         | 3         | 3-3-2 -----------------------------------------

SELECT t.* FROM (SELECT * FROM tbl_name ORDER BY RAND()) t GROUP BY group_id, group2_id;

или так

SELECT t.* FROM (SELECT * FROM tbl_name ORDER BY RAND()) t GROUP BY group2_id, group_id ;

__
а изначально group_id и group2_id были в разных таблицах?

>а изначально group_id и group2_id были в разных таблицах?
Да, были в разных изначально.

тогда покажите как было раньше :)
естественно указав все поля участвующие в запросе
(однотипные поля условия можно сократить до одного)

<?php $q=mysql_query("   SELECT *    FROM `table`  "); $rows=array(); while($row=mysql_fetch_assoc($q)){   $rows[$row['group1_id']]['group2_id']=$row; } $results_rows=array(); foreach($data as $row){   $group1_id=array_rand($row);   $group2_id=array_rand($row[$group1_id]);   $results_rows[]=$row[$group1_id][$group2_id]; } ?>

Вот написал вариант на PHP для структуры таблицы, как я описал выше. В оригинале конечно запрос не будет получать всю таблицу, так как присутствует ещё третья группа, по которой в условии WHERE выбирается только небольшая часть таблицы.
Код не тестировал, но должно работать.

по которой в условии WHERE выбирается только небольшая часть таблицы
это очень важное замечание, не стоит умалчивать о таких вещах говорите о них сразу
___
на мой взгляд лучше отказаться от идеи разбора на РНР

Ну вообще в WHERE там выборка происходит по 5-6 полям. Я ещё не знаю как мне лучше индексы в таблице сделать. Думал по-разному потестировать.

SELECT t.*      FROM (SELECT *                 FROM tbl_name                 WHERE [условия выборки]                 ORDER BY RAND()) t      GROUP BY group_id, group2_id;

Я боюсь выражения

SELECT * FROM tbl_name ORDER BY RAND()

так как всюду ругают подобный подход. Хотя в мануале вроде бы так и предлагается делать.

скорее всего ругают если запрос без условия, дайте ссылку на ругань

Например вот здесь http://habrahabr.ru/blogs/mysql/54176/ в комментариях.
Наверно вы правы, там человек выбирает случайные записи из всей таблицы.

>так как всюду ругают подобный подход. Хотя в мануале вроде бы так и предлагается делать.
Этот запрос был не очень эффективен в ранних версиях MySQL, сейчас это помоему исправили (хотя могу ошибаться).

сейчас проверил с условием, запрос занял 0.04 сек из 78 тыс строк выбирает 66 и рандомит из них одну
из 230 тыс с 5-ю значениями условия 5 строк выбрал за 0.15 сек
из 230 тыс с 5-ю значениями условия только с группировкой по другому полю 150 строк выбрал за 0.16 сек

Только что забил таблицы со своей структурой данными, тоже тестировать буду.

Блин. Я запутался. Всё таки группа одна, а не две.

Вариант с PHP и без него работают одинаково по времени. Сейчас попробую увеличить количество данных в таблицах.

UPD:
Сделал составной индекс на 6 полей, которые проверяются в WHERE. Количество данных оставил прежнее. Теперь вариант с PHP работает немного быстрее. А вариант со сложным запросом работает или чуть медленнее, чем без составного индекса, или почти так же, точно не могу сказать.

покажите вариант запроса без РНР

SELECT `links2`.*          FROM (SELECT `links`.`link_id`,                       `platforms`.`platform_id`,                       `platforms`.`platform_position`,                       `platforms`.`platform_number_of_shows`,                       `links`.`link_number_of_shows`,                       `links`.`link_html_code`                FROM `platforms`                LEFT JOIN `links`                ON `platforms`.`platform_id`=`links`.`platform_id`                WHERE `platforms`.`block_id`=$block_id and                      `platforms`.`platform_is_active`=1 and                      `platforms`.`platform_number_of_shows`<`platforms`.`platform_limit_of_shows` and                      `platform_number_of_hits`<`platform_limit_of_hits` and                      $time<`platform_ending_time`                ORDER BY rand()          ) as `links2`          GROUP BY `links2`.`platform_position`          ORDER BY `links2`.`platform_position`

$block_id и $time устанавливаются из скрипта

Этот же запрос с постобработкой скриптом выглядит так (сам php код не привожу):

SELECT `links`.`link_id`,                 `platforms`.`platform_id`,                 `platforms`.`platform_position`,                 `platforms`.`platform_number_of_shows`,                 `links`.`link_number_of_shows`,                 `links`.`link_html_code`          FROM `platforms`          LEFT JOIN `links`          ON `platforms`.`platform_id`=`links`.`platform_id`          WHERE `platforms`.`block_id`=$block_id and                `platforms`.`platform_is_active`=1 and                `platforms`.`platform_number_of_shows`<`platforms`.`platform_limit_of_shows` and                `platform_number_of_hits`<`platform_limit_of_hits` and                $time<`platform_ending_time`

Увеличил количество строк в таблицах в 9 раз. Теперь вариант с PHP работает в десять раз быстрее.

Немного изменил архитектуру. Теперь запрос выглядит так:

SELECT `links2`.*          FROM (SELECT `links`.`link_id`,                       `platforms`.`platform_id`,                       `platforms`.`platform_limit_of_shows`,                       `platforms`.`platform_position`,                       `links`.`link_html_code`                FROM `platforms`                LEFT JOIN `links`                ON `platforms`.`platform_id`=`links`.`platform_id`                WHERE `platforms`.`block_id`=$block_id and                      `platforms`.`platform_is_active`=1 and                      `platforms`.`platform_limit_of_shows`>0 and                      `platforms`.`platform_limit_of_hits`>0 and                       $time<`platform_ending_time`                 ORDER BY rand()          ) as `links2`          GROUP BY `links2`.`platform_position`          ORDER BY `links2`.`platform_position`

ну и как результаты?

Ещё не тестировал с большим объёмом данных. Немного занят, у меня День Рождения )))

поздравляю
__
опять нет повода не выпить :)

У меня каждый день повод не выпить. Даже сегодня. Я из принципа не пью спиртного. =) Спасибо за поздравление.

а лет сколько если не секрет? и как давно бросили пить?))

22 года исполнилось. Я в общем-то пить и не начинал. Мог выпить шампанское или рюмку вина на большом празднике, но вот уже года четыре вообще не пью. =)

Как вы наверно уже догадались, запрос должен показывать ротацию рекламы на сайте. Я вот сейчас думаю, как лучше реализовать статистику. Статистика по браузерам и IP не нужна. Нужны только показы и переходы. Минимальная единица учёта: 1 час. Я сейчас сделал такую таблицу:

CREATE TABLE `statistics`(          `link_id` int(11) unsigned not null default 0,          `time` DATETIME,          `number_of_shows` int(11) unsigned not null default 0,          `number_of_hits` int(11) unsigned not null default 0,          UNIQUE (`link_id`,`time`)        ) ENGINE=InnoDB DEFAULT CHARSET=utf8

time устанавливается с интервалом в один час, то есть строки в таблице будут примерно такие:

link_id | time                | number_of_shows | number_of_hits  ------------------------------------------------------------------  1       | 2011-08-23 14:00:00 | 134             | 12               2       | 2011-08-23 14:00:00 | 110             | 11               3       | 2011-08-23 14:00:00 | 112             | 8               1       | 2011-08-23 15:00:00 | 234             | 19               3       | 2011-08-23 15:00:00 | 345             | 32               1       | 2011-08-23 16:00:00 | 123             | 13               1       | 2011-08-23 17:00:00 | 543             | 24               1       | 2011-08-23 18:00:00 | 534             | 28              ------------------------------------------------------------------

Уникальный составной индекс UNIQUE (`link_id`,`time`) обеспечивает, чтобы каждой ссылке (link_id) в каждый час (time) соответствовала одна строка (ну или вообще ни одной), ну и по этим полям будет статистика выбираться, так что индекс в любом случае нужен.
При каждом показе какой-либо ссылки нужно увеличить поле number_of_shows на 1 (точно так же и с переходами, но с полем number_of_hits). Решалось бы просто апдейтом. Но проблема в том, что строки может и не быть, если в данный час с данной ссылки ещё не было ни одного показа или перехода. То есть при первом показе или переходе нужно не апдейтить, а создавать строку. В MySQL есть такая конструкция:

INSERT            INTO `statistics`            (`link_id`,`time`,`number_of_shows`,`number_of_hits`)            VALUES (...), (...), (...)            ON DUPLICATE KEY            UPDATE `number_of_shows`=`number_of_shows`+1

где (...), (...), (...) - это список данных для обновляемых/вставляемых ссылок (так как за один раз показывается несколько ссылок и обновлять показы для них всех одновременно нужно. Эта конструкция работает, но я не знаю оптимально ли? Насколько велики накладные расходы в подобном запросе. По сути инсерт будет выполняться один раз в час для каждой ссылки (либо вообще не будет, если ссылка в течении часа показана не будет), а вот апдейт уже при всех последующих показах данной ссылки в рамках данного часа.

позвольте слегка освежить вам память :)
много можно почерпнуть из той темы, в частности буферную таблицу, куда можно инсёртить в темную голову
и кстати если пока не нужна статистика по браузерам или чему-то другому, все равно лучше проектировать таблицу так чтобы при необходимости это можно было легко добавить.
составной индекс UNIQUE (`link_id`,`time`) - скорее всего лично я бы так не сделал

Помню эту тему =) Как давно это было. Странно, что вы её нашли.

>> Будь моя воля, я бы Вам приказал книги читать ;)

Таки почитал трошки :D :D :D

Мне не хочется делать буферную таблицу. Сейчас я делаю исключительно для себя и я знаю, что статистика будет не нужна. Да и сисадмин моего хостинга мне руки оторвёт, если узнает, что я инсетры в таблицу фигачу при каждом показе рекламных ссылок. Например на каждой странице сайта показывается 5 рекламных ссылок. Это пять инсертов при каждом посещении страницы. Таблица будет быстро раздуваться. Да и если пересчитывать допустим раз в сутки данные этой таблицы и заносить в таблицу статистики, то в течении текущего дня нельзя будет посмотреть статистику, а это бывает очень важно для рекламодателей. Ну вообще-то можно при просмотре статистики учитывать ещё и данные из буферной таблицы. Но всё равно мне кажется лучше просто увеличивать поле-счётчик в таблице статистики.

>> составной индекс UNIQUE (`link_id`,`time`) - скорее всего лично я бы так не сделал

Составной индекс нужен мне для использования совмещённого запроса INSERT + UPDATE, иначе не будет работать.

я инсетры в таблицу фигачу при каждом показе рекламных
а упдэйты фигачить не ломает?))
инсерт будет гораздо проще, тем более что в буфере, я бы не стал поля индексировать
Да и если пересчитывать допустим раз в сутки данные этой таблицы и заносить в таблицу статистики, то в течении текущего дня нельзя будет посмотреть статистику, а это бывает очень важно для рекламодателей. Ну вообще-то можно при просмотре статистики учитывать ещё и данные из буферной таблицы.
да, давно было... аж два года назад)) забыли уже, что пересчитывать можно в любой момент времени, хоть каждую секунду)
___
как делать это уже вам решать, тем более если уже все работает, то забейте)

Ещё вопрос. Очень плохо будет если в запрос на выборку случайных ссылок я вставлю FOR UPDATE и заключу в транзакцию этот запрос и UPDATE показов? Просто если не использовать FOR UPDATE, возникает эффект гонки. Запускал в цикле код в 30 параллельных потоков, в итоге количество показов часто получается больше, чем указанный лимит (всего лишь на один показ, но всё же нехорошо). С FOR UPDATE всё работает вроде как надо, но медленнее на общем фоне, если использовать 30 параллельных клиентов, что и понятно, так как таблица блокируется даже для выборки.

у вас тип таблицы какой?

InnoDB же