SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#26 30.11.2010 15:05:02

nexus
Завсегдатай
Зарегистрирован: 26.11.2010
Сообщений: 35

Re: Группировка в запросе

НУ да! Т.е. чтобы можно было сделать пейджинг и листать по 2 группы!

Неактивен

 

#27 30.11.2010 15:10:35

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5833

Re: Группировка в запросе

Если нужны произвольные группы, то см FAQ #9. Сначала определяете id нужных групп, а потом по id получаете всю информацию.

Если листать, то это уже не произвольные, иначе у вас будут повторяться группы.

Неактивен

 

#28 10.02.2011 15:12:47

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2420

Re: Группировка в запросе

Как говорят японцы: "Прошу простить мне мое слабоумие", но...


Вот есть две таблицы - g (Группы) и p (Потомки).

DROP TABLE IF EXISTS `test`.`g`;
CREATE TABLE  `test`.`g` (
  `x` int(4) unsigned default NULL
) ENGINE=MyISAM;
insert into `test`.`g` values(3),(2),(4),(5),(1);

DROP TABLE IF EXISTS `test`.`p`;
CREATE TABLE  `test`.`p` (
  `y` int(4) unsigned default NULL,
  `data` int(4) unsigned default NULL
) ENGINE=MyISAM;
insert into `test`.`p` values
(3,172),(3,721),(3,652),(3,444),(3,781)
,(2,48),(2,27),(2,13),(2,16),(2,99)
,(4,4854),(4,2723),(4,1327),(4,1663),(4,9953)
,(5,48545),(5,27423),(5,12327),(5,16639),(5,99530)
,(1,4),(1,7),(1,3),(1,6),(1,9);

Выборка select * from g t1 join p t2 on t1.x=t2.y:

+------+------+-------+
| x    | y    | data  |
+------+------+-------+
|    3 |    3 |   172 |
|    3 |    3 |   721 |
|    3 |    3 |   652 |
|    3 |    3 |   444 |
|    3 |    3 |   781 |
|    2 |    2 |    48 |
|    2 |    2 |    27 |
|    2 |    2 |    13 |
|    2 |    2 |    16 |
|    2 |    2 |    99 |
|    4 |    4 |  4854 |
|    4 |    4 |  2723 |
|    4 |    4 |  1327 |
|    4 |    4 |  1663 |
|    4 |    4 |  9953 |
|    5 |    5 | 48545 |
|    5 |    5 | 27423 |
|    5 |    5 | 12327 |
|    5 |    5 | 16639 |
|    5 |    5 | 99530 |
|    1 |    1 |     4 |
|    1 |    1 |     7 |
|    1 |    1 |     3 |
|    1 |    1 |     6 |
|    1 |    1 |     9 |
+------+------+-------+
25 rows in set (0.00 sec)

То есть 3,2,4,5,1 - номера групп.

vasya написал:

Нужно поменять порядок условий в части where (я просто писал по смыслу без проверки).
Если сначала идет @i<@n, то при невыполнении этого условия второе не проверяется (так как они идут через and). А нам нужно, чтобы наша конструкция с if выполнялась над каждой строкой.

Поэтому должно быть:
WHERE if(@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;

Пробую сделать как указано - поставить @i<@n после условия if, получается запрос:

select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;

mysql> set @n=3, @i=0, @p=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
+------+------+------+
| x    | y    | data |
+------+------+------+
|    3 |    3 |  172 |
|    3 |    3 |  721 |
|    3 |    3 |  652 |
+------+------+------+
3 rows in set (0.00 sec)

То есть он выдал три элемента с первой группы, но ведь всего групп 5 штук!
Почему он выдал только с первой?


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

Неактивен

 

#29 11.02.2011 00:15:21

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Группировка в запросе

Ура, deadka вернулся! smile
А можно я поучаствую, да? smile

Ответ вкратце — потому что Вы пытаетесь сделать один запрос из того, что делается
пятью. Пять запросов правда будут работать быстрее: они используют индексы! А один
полнотабличный скан — это зло, грузящее и диски (полнотабличный) и процессор
(куча ифов).

Теперь чуть длиннее. Дебажить эту штуку неудобно, поэтому развернем немного запрос
наизнанку:

Код:

[silentia] root test > set @n=3, @i=0, @p=0; select *, if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and (@i<@n) f from g t1 join p t2 on t1.x=t2.y;
Query OK, 0 rows affected (0.00 sec)

+------+------+-------+------+
| x    | y    | data  | f    |
+------+------+-------+------+
|    3 |    3 |   172 |    1 |
|    3 |    3 |   721 |    1 |
|    3 |    3 |   652 |    1 |
|    3 |    3 |   444 |    0 |
|    3 |    3 |   781 |    0 |
|    2 |    2 |    48 |    1 |
|    2 |    2 |    27 |    1 |
|    2 |    2 |    13 |    1 |
|    2 |    2 |    16 |    0 |
|    2 |    2 |    99 |    0 |
|    4 |    4 |  4854 |    1 |
|    4 |    4 |  2723 |    1 |
|    4 |    4 |  1327 |    1 |
|    4 |    4 |  1663 |    0 |
|    4 |    4 |  9953 |    0 |
|    5 |    5 | 48545 |    1 |
|    5 |    5 | 27423 |    1 |
|    5 |    5 | 12327 |    1 |
|    5 |    5 | 16639 |    0 |
|    5 |    5 | 99530 |    0 |
|    1 |    1 |     4 |    1 |
|    1 |    1 |     7 |    1 |
|    1 |    1 |     3 |    1 |
|    1 |    1 |     6 |    0 |
|    1 |    1 |     9 |    0 |
+------+------+-------+------+
25 rows in set (0.00 sec)

Тут мы видим, что Вася написал нормальный код, который отлично работает
если он стоит в правильном месте.

Осталось разобраться, чем отличается положение в списке полей от положения
в WHERE. Если быть совсем честным — я не знаю. Если быть не совсем честным,
то можно поглядеть на EXPLAIN: там написано, что WHERE применяется к обеим
табличкам. В каком порядке оно применяется — никому не известно (например,
если добавить индекс на p, то выводится по две строки каждой группы). То есть
на одну строку выборки IF может вызываться и ноль, и один, и даже два раза.

Итого мое мнение: вы зря городите костыли вокруг простой задачи. Костыли
страшные, шатаются, а ваш еще и стреляет в ногу wink
Вот хороший большой костыль, который я категорически не рекомендую исполь-
зовать (но который решает задачу тем не менее):

Код:

[silentia] root test > set @n=3, @i=0, @p=0; select * from (select *, if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and (@i<@n) AS f from g t1 join p t2 on t1.x=t2.y) xx where f;
Query OK, 0 rows affected (0.00 sec)

+------+------+-------+------+
| x    | y    | data  | f    |
+------+------+-------+------+
|    3 |    3 |   172 |    1 |
|    3 |    3 |   721 |    1 |
|    3 |    3 |   652 |    1 |
|    2 |    2 |    48 |    1 |
|    2 |    2 |    27 |    1 |
|    2 |    2 |    13 |    1 |
|    4 |    4 |  4854 |    1 |
|    4 |    4 |  2723 |    1 |
|    4 |    4 |  1327 |    1 |
|    5 |    5 | 48545 |    1 |
|    5 |    5 | 27423 |    1 |
|    5 |    5 | 12327 |    1 |
|    1 |    1 |     4 |    1 |
|    1 |    1 |     7 |    1 |
|    1 |    1 |     3 |    1 |
+------+------+-------+------+
15 rows in set (0.00 sec)

Из задачек «на подумать»: почему не работает простое HAVING f? wink

Неактивен

 

#30 11.02.2011 12:19:40

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2420

Re: Группировка в запросе

Приветствую, paulus :-), cпасибо за ответ!

paulus написал:

А можно я поучаствую, да? smile

Будем польщены!

paulus написал:

Ответ вкратце — потому что Вы пытаетесь сделать один запрос из того, что делается
пятью. Пять запросов правда будут работать быстрее: они используют индексы! А один
полнотабличный скан — это зло, грузящее и диски (полнотабличный) и процессор
(куча ифов).

Ммм, речь идёт о том, что лучше сперва получить перечень id-шников групп (допустим простым select'ом) и потом уже серией запросов из каждой группы получать по три (или сколько нужно элементов) допустим вот таким запросом (select data from p where p.y = ВЫШЕПОЛУЧЕННЫЙID_ГРУППЫ limit 3 )? Конечно при  наличии индекса на p.y. Я правильно Вас понял?

Задача у меня стоит немножко другая, я ее в другом посте (http://sqlinfo.ru/forum/viewtopic.php?id=3321) озвучивал, просто задача текущего поста - часть к решению той, поэтому и пытаюсь разобраться досконально.

Что касается скорости, то в моей таблице не так много данных - несколько сотен максимум, поэтому тут оптимизация не столь острый камень.

paulus написал:

Код:

[silentia] root test > set @n=3, @i=0, @p=0; select *, if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and (@i<@n) f from g t1 join p t2 on t1.x=t2.y;
Query OK, 0 rows affected (0.00 sec)

+------+------+-------+------+
| x    | y    | data  | f    |
+------+------+-------+------+
|    3 |    3 |   172 |    1 |
|    3 |    3 |   721 |    1 |
|    3 |    3 |   652 |    1 |
|    3 |    3 |   444 |    0 |
|    3 |    3 |   781 |    0 |
|    2 |    2 |    48 |    1 |
|    2 |    2 |    27 |    1 |
|    2 |    2 |    13 |    1 |
|    2 |    2 |    16 |    0 |
|    2 |    2 |    99 |    0 |
|    4 |    4 |  4854 |    1 |
|    4 |    4 |  2723 |    1 |
|    4 |    4 |  1327 |    1 |
|    4 |    4 |  1663 |    0 |
|    4 |    4 |  9953 |    0 |
|    5 |    5 | 48545 |    1 |
|    5 |    5 | 27423 |    1 |
|    5 |    5 | 12327 |    1 |
|    5 |    5 | 16639 |    0 |
|    5 |    5 | 99530 |    0 |
|    1 |    1 |     4 |    1 |
|    1 |    1 |     7 |    1 |
|    1 |    1 |     3 |    1 |
|    1 |    1 |     6 |    0 |
|    1 |    1 |     9 |    0 |
+------+------+-------+------+
25 rows in set (0.00 sec)

Тут мы видим, что Вася написал нормальный код, который отлично работает
если он стоит в правильном месте.

А что является "правильным местом" sad? Этот запрос выводит все элементы групп (а не по три, хотя в переменную n изначально было занесено значение 3). Такое ощущение, что условие and (@i<@n) просто выбрасывается при выполнении (результат запроса совпадает с результатом запроса set @n=3, @i=0, @p=0; select *, if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) f from g t1 join p t2 on t1.x=t2.y;, а они отличаются только наличием/отсутствием and (@i<@n) ).

Хочу еще заметить, что в том варианте запроса, который я постил в прошлый раз
(select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;)
вывелись только три строки, то есть как будто условие if перестало отрабатываться после того, как @i набрало нужное значение. Это объяснимо как-то?

paulus написал:

Осталось разобраться, чем отличается положение в списке полей от положения
в WHERE. Если быть совсем честным — я не знаю. Если быть не совсем честным,
то можно поглядеть на EXPLAIN: там написано, что WHERE применяется к обеим
табличкам. В каком порядке оно применяется — никому не известно (например,
если добавить индекс на p, то выводится по две строки каждой группы). То есть
на одну строку выборки IF может вызываться и ноль, и один, и даже два раза.

explain'ы вариантов запросов отличаются только наличием/отсутствием where на t1:

mysql> explain select *, if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and (@i<@n) f from g t1 join p t2 on t1.x=t2.y;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    5 |             |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.01 sec)

paulus, а что имелось ввиду под если добавить индекс на p, то выводится по две строки каждой группы? p - это ж ведь просто пользовательская переменная, как на нее индекс добавишь?

Большое спасибо за наводку на читерское (нерекомендуемое Вами решение). Касательно having - здесь ведь нету группировки.
Если параметр GROUP BY в SELECT не задан, HAVING применяется к «группе» всех строк таблицы, полностью дублируя WHERE (допускается не во всех реализациях стандарта SQL). (c), а значит можно заменить having на where и результат получился такой же.


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

Неактивен

 

#31 11.02.2011 14:59:14

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Группировка в запросе

Ээ... можно «на ты»? Всё-таки, знаем друг друга лично? wink

В моем примере посмотри внимательно на значения f. Выборка получается тогда,
когда f = 1. Я просто перенес условие отбора из WHERE в список полей, чтобы
видеть, какие строки выбираются, а какие — отбрасываются.

p — это не пользовательская переменная, а таблица. То, что назвали переменную
@p так же, как таблицу, — я не виноват wink


Про WHERE и HAVING — тому ресурсу доверять не стоит smile Это почти никогда не
одно и то же. Сравни, например:
SELECT a AS b FROM t WHERE b = 1;   -- ошибка
SELECT a AS b FROM t HAVING b = 1;  -- отбирает строки с a = 1.
Кроме того, HAVING никогда не пытается использовать индекс.

HAVING — это фильтр после выполнения основного запроса. А WHERE — до. В этом
их принципиальное различие.

Неактивен

 

#32 14.02.2011 13:06:03

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2420

Re: Группировка в запросе

paulus написал:

Ээ... можно «на ты»? Всё-таки, знаем друг друга лично? wink

Конечно, сори smile, привык соблюдать официоз на форуме.

paulus написал:

В моем примере посмотри внимательно на значения f. Выборка получается тогда,
когда f = 1. Я просто перенес условие отбора из WHERE в список полей, чтобы
видеть, какие строки выбираются, а какие — отбрасываются.

Да, всё верно. Васины инструкции хорошо отрабатываются, когда находится в списке выбранных полей, а не в where. Но вообще с join'ом конструкция отрабатывается очень с большим скрипом - в конце-концов разве наличие индекса может поменять результат выполнения запроса (я именно про результат, а не выполнение)? А тут тесты дают след. результат:

Таблицы g и p непроиндексированы

Код:

mysql> set @n=3, @i=0, @p=0;  select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
Query OK, 0 rows affected (0.00 sec)

+------+------+------+
| x    | y    | data |
+------+------+------+
|    3 |    3 |  172 |
|    3 |    3 |  721 |
|    3 |    3 |  652 |
+------+------+------+
3 rows in set (0.00 sec)

Поле x таблицы g проиндексировано (таблица p непроиндексирована).

Код:

mysql> set @n=3, @i=0, @p=0;  select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
Empty set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Поле y таблицы p проиндексировано (таблица g непроиндексирована).

Код:

mysql> set @n=3, @i=0, @p=0;  select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
Query OK, 0 rows affected (0.00 sec)

+------+------+-------+
| x    | y    | data  |
+------+------+-------+
|    3 |    3 |   172 |
|    3 |    3 |   721 |
|    2 |    2 |    48 |
|    2 |    2 |    27 |
|    4 |    4 |  4854 |
|    4 |    4 |  2723 |
|    5 |    5 | 48545 |
|    5 |    5 | 27423 |
|    1 |    1 |     4 |
|    1 |    1 |     7 |
+------+------+-------+
10 rows in set (0.00 sec)

Поле x таблицы g проиндексировано, поле y таблицы p проиндексировано.

Код:

mysql> set @n=3, @i=0, @p=0;  select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
Query OK, 0 rows affected (0.00 sec)

+------+------+-------+
| x    | y    | data  |
+------+------+-------+
|    1 |    1 |     4 |
|    1 |    1 |     7 |
|    2 |    2 |    48 |
|    2 |    2 |    27 |
|    3 |    3 |   172 |
|    3 |    3 |   721 |
|    4 |    4 |  4854 |
|    4 |    4 |  2723 |
|    5 |    5 | 48545 |
|    5 |    5 | 27423 |
+------+------+-------+
10 rows in set (0.01 sec)

Итого - 3 разных результата на 4 выборки.

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

Крайне интересно было бы услышать по этому посту соображения Васи (метод-то его), не известно ли тебе, Paulus, когда он на форуме появится? А то не видно его с конца января.

paulus написал:

Про WHERE и HAVING — тому ресурсу доверять не стоит smile Это почти никогда не
одно и то же.

Да, правда что sad. Спасибо!

Отредактированно deadka (14.02.2011 13:07:31)


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

Неактивен

 

#33 11.06.2011 01:30:53

nexus
Завсегдатай
Зарегистрирован: 26.11.2010
Сообщений: 35

Re: Группировка в запросе

Возникла задача сделать выборку из двух таблиц:


CREATE TABLE IF NOT EXISTS `site_questionnaire` (
  `questionnaire_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` varchar(15) CHARACTER SET utf8 NOT NULL,
  `addtime` int(11) NOT NULL,
  `user_ip` int(10) unsigned NOT NULL,
  PRIMARY KEY (`questionnaire_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

INSERT INTO `site_questionnaire` (`questionnaire_id`, `project_id`, `addtime`, `user_ip`) VALUES
(2, 'rc97ji4o6', 1307735085, 3000531187),
(3, 'rc97ji4o6', 1307735326, 3000531187);

CREATE TABLE IF NOT EXISTS `site_response` (
  `response_id` int(11) NOT NULL AUTO_INCREMENT,
  `questionnaire_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `response` text NOT NULL,
  PRIMARY KEY (`response_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

INSERT INTO `site_response` (`response_id`, `questionnaire_id`, `item_id`, `response`) VALUES
(1, 2, 0, 'Иван'),
(2, 2, 1, 'test'),
(3, 2, 2, 'Вариант ответа №1'),
(4, 2, 2, 'Вариант ответа №2'),
(5, 2, 3, 'Вариант ответа №4'),
(6, 2, 4, 'Вариант ответа №2'),
(7, 2, 6, '123456789'),
(8, 3, 0, 'Иван2'),
(9, 3, 1, 'sdfsdf'),
(10, 3, 2, 'Вариант ответа №3'),
(11, 3, 3, 'Вариант ответа №1'),
(12, 3, 4, 'Вариант ответа №3'),
(13, 3, 6, '');
 


Так вот в таблице site_questionnaire хранится id ответа, а таблице site_response непосредственно сами ответы. Как мне выбрать все записи определенного количества ответов. Т.е. мне надо получить, например, 5 строк из таблицы site_questionnaire со всеми данными из таблицы site_response?

Неактивен

 

#34 13.06.2011 23:04:34

nexus
Завсегдатай
Зарегистрирован: 26.11.2010
Сообщений: 35

Re: Группировка в запросе

Нашел решение. Может кому-то пригодится:


set @n=10, @i=0, @p=0;

SELECT `c2`.*, `c1`.`response_id`, `c1`.`item_id`, `c1`.`response`
FROM (`site_response` AS c1)
JOIN `site_questionnaire` AS c2 ON `c1`.`questionnaire_id` = `c2`.`questionnaire_id`
WHERE `c2`.`project_id` = 'rc97ji4o6'
AND if(@p <> c1.questionnaire_id,(@i:=@i+1) AND (@p:=c1.questionnaire_id), @p:=c1.questionnaire_id) and @i<=@n
 


Но тут вознакает вопрос, как сделать аналог LIMIT? Т.е. чтобы можно было реализовать навигацию по страницам?

Можно конечно сделать так:


set @n=10, @i=0, @p=0, @s = 10;

SELECT `c2`.*, `c1`.`response_id`, `c1`.`item_id`, `c1`.`response`
FROM (`site_response` AS c1)
JOIN `site_questionnaire` AS c2 ON `c1`.`questionnaire_id` = `c2`.`questionnaire_id`
WHERE `c2`.`project_id` = 'rc97ji4o6'
AND if(@p <> c1.questionnaire_id,(@i:=@i+1) AND (@p:=c1.questionnaire_id), @p:=c1.questionnaire_id) and @i > @s AND @i - @s <=@n
 


где @s - номер записи с которой нужно начинать выборку

Но это как мне кажется не правильно. А если у меня в базе будет 1000000 записей и мне надо будут вывести последнюю страницу, то мне придется пройтись по всем записям! Есть ли какой-то другой способ?

Отредактированно nexus (13.06.2011 23:19:34)

Неактивен

 

#35 26.10.2011 00:51:27

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5833

Re: Группировка в запросе

deadka написал:

DROP TABLE IF EXISTS `test`.`g`;
CREATE TABLE  `test`.`g` (
`x` int(4) unsigned default NULL
) ENGINE=MyISAM;
insert into `test`.`g` values(3),(2),(4),(5),(1);

DROP TABLE IF EXISTS `test`.`p`;
CREATE TABLE  `test`.`p` (
`y` int(4) unsigned default NULL,
`data` int(4) unsigned default NULL
) ENGINE=MyISAM;
insert into `test`.`p` values
(3,172),(2,48),(3,652),(3,444),(3,781)
,(3,721),(2,27),(2,13),(2,16),(2,99)
,(5,48545),(4,2723),(4,1327),(4,1663),(4,9953)
,(4,4854),(5,27423),(5,12327),(5,16639),(5,99530)
,(1,4),(1,7),(1,3),(1,6),(1,9);


mysql> select * from g t1 join p t2 on t1.x=t2.y
   -> ;
+------+------+-------+
| x    | y    | data  |
+------+------+-------+
|    3 |    3 |   172 |
|    2 |    2 |    48 |
|    3 |    3 |   652 |
|    3 |    3 |   444 |
|    3 |    3 |   781 |
|    3 |    3 |   721 |
|    2 |    2 |    27 |
|    2 |    2 |    13 |
|    2 |    2 |    16 |
|    2 |    2 |    99 |
|    5 |    5 | 48545 |
|    4 |    4 |  2723 |
|    4 |    4 |  1327 |
|    4 |    4 |  1663 |
|    4 |    4 |  9953 |
|    4 |    4 |  4854 |
|    5 |    5 | 27423 |
|    5 |    5 | 12327 |
|    5 |    5 | 16639 |
|    5 |    5 | 99530 |
|    1 |    1 |     4 |
|    1 |    1 |     7 |
|    1 |    1 |     3 |
|    1 |    1 |     6 |
|    1 |    1 |     9 |
+------+------+-------+

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

create index g on g(x);

mysql> select * from g t1 join p t2 on t1.x=t2.y;
+------+------+-------+
| x    | y    | data  |
+------+------+-------+
|    3 |    3 |   172 |
|    3 |    3 |   652 |
|    3 |    3 |   444 |
|    3 |    3 |   781 |
|    3 |    3 |   721 |
|    2 |    2 |    48 |
|    2 |    2 |    27 |
|    2 |    2 |    13 |
|    2 |    2 |    16 |
|    2 |    2 |    99 |
|    4 |    4 |  2723 |
|    4 |    4 |  1327 |
|    4 |    4 |  1663 |
|    4 |    4 |  9953 |
|    4 |    4 |  4854 |
|    5 |    5 | 48545 |
|    5 |    5 | 27423 |
|    5 |    5 | 12327 |
|    5 |    5 | 16639 |
|    5 |    5 | 99530 |
|    1 |    1 |     4 |
|    1 |    1 |     7 |
|    1 |    1 |     3 |
|    1 |    1 |     6 |
|    1 |    1 |     9 |
+------+------+-------+
25 rows in set (0.00 sec)

Вроде уже можно запускать переменные


mysql> set @n=3, @i=0, @p=0; select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;
Query OK, 0 rows affected (0.00 sec)

Empty set (0.00 sec)

Т. е. не работает. Почему?..

Смотрим explain:

mysql> explain extended select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=
x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: g
          key: g
      key_len: 5
          ref: NULL
         rows: 5
     filtered: 80.00
        Extra: Using where; Using index; Using join buffer
2 rows in set, 1 warning (0.00 sec)

Мы видим, что where используется в обеих таблицах. Причем какие условия и в каком порядке применяются к той или иной таблице можно только предполагать, анализируя результат выборки при тех или иных значениях.
Если посмотреть на то как переписал запрос оптимизатор, то можно увидеть неожиданную вещь.
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t1`.`x` AS `x`,`test`.`t2`.`y` AS `y`,`test`.`t2`.`data`
AS `data` from `test`.`g` `t1` join `test`.`p` `t2` where ((`test`.`t1`.`x` = `
test`.`t2`.`y`) and if(((@p) = `test`.`t2`.`y`),(@i:=((@i) + 1)),((@i:=0) or (@p
:=`test`.`t2`.`y`
))) and ((@i) < (@n)))
1 row in set (0.02 sec)
Т.е. сначала считывается в буфер табличка `p` и к ней при этом применяется условие "if (@p=y, @i:=@i+1,(@i:=0) or (@p:=y))"

mysql> select * from p;
+------+-------+
| y    | data  |
+------+-------+
|    3 |   172 |
|    2 |    48 |
|    3 |   652 |
|    3 |   444 |
|    3 |   781 |
|    3 |   721 |
|    2 |    27 |
|    2 |    13 |
|    2 |    16 |
|    2 |    99 |
|    5 | 48545 |
|    4 |  2723 |
|    4 |  1327 |
|    4 |  1663 |
|    4 |  9953 |
|    4 |  4854 |
|    5 | 27423 |
|    5 | 12327 |
|    5 | 16639 |
|    5 | 99530 |
|    1 |     4 |
|    1 |     7 |
|    1 |     3 |
|    1 |     6 |
|    1 |     9 |
+------+-------+
25 rows in set (0.00 sec)

В результате данного пробега счетчик примет значение @i=4.
Судя по всему при выборке из второй таблицы сначала проверяется условие @i<@n. Таким образом результирующая выборка окажется пустой.
Косвенно эти рассуждения подтверждаются тем, что если мы добавим в таблицу `p` такую строчку, чтобы счетчик после пробега таблицы принял нулевое значение:
mysql> insert into p values(2,9);
Query OK, 1 row affected (0.00 sec)
то результат выборки измениться в соответствии с выше приведенными рассуждениями:
mysql> set @n=3, @i=0, @p=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from g t1 join p t2 on t1.x=t2.y  WHERE if (@p=x, @i:=@i+1,(@i:=
0) or (@p:=x)) and @i<@n;
+------+------+------+
| x    | y    | data |
+------+------+------+
|    3 |    3 |  172 |
|    3 |    3 |  652 |
|    3 |    3 |  444 |
+------+------+------+
3 rows in set (0.00 sec)



В качестве окончательного вывода процитирую классика

paulus написал:

Ответ вкратце — потому что Вы пытаетесь сделать один запрос из того, что делается
пятью. Пять запросов правда будут работать быстрее: они используют индексы! А один
полнотабличный скан — это зло, грузящее и диски (полнотабличный) и процессор
(куча ифов).

P.S. Предлагаю всех, не желающих делать X запросов для получения по N элементов из каждой группы, а жаждущих решить задачу одним запросом, отправлять в эту тему. Осилившие до конца - узнают истину.

Неактивен

 

#36 26.10.2011 01:37:21

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2420

Re: Группировка в запросе

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

set @n=3, @i=0, @p=0; select * from (select * from g t1 join p t2 on t1.x=t2.y order by t1.x) t WHERE if (@p=x, @i:=@i+1,(@i:=0) or (@p:=x)) and @i<@n;

Тут уже не играет роль наличие/отсутствие индексов на поле x таблицы g.

Ну и стоит наверное упомянуть еще один способ
SELECT  t.y,  t.data FROM p t  HAVING  (SELECT count(*) FROM `p` t2 WHERE t2.y = t.y AND t2.data<t.data)<3  ORDER BY  1, 2 desc;

Одним запросом, и без переменных. Правда, конечно, неоптимален.




Комментарий модератора.
Осилившим тему до конца. Истина в статье Как получить по N элементов из каждой группы. Дано 6 вариантов решения в один запрос с оценкой их эффективности с точки зрения производительности.

Отредактированно deadka (26.10.2011 01:37:47)


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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson