Сложный mysql запрос. Подробности под катом

Question

[alesto]

Есть таблица с 3-мя полями
id — primary key
Action — несколько предустановленных значений
user_id — ид пользователя, число

Нужно выбрать 20 записей, но так что если в подряд идущих рядках одинаковый user_id и action, то сколько бы их подряд не шло, они считаются за одну запись. То есть мы объеденяем в одну единицу контента подряд идущие рядки с одинаковыми user_id, action.

В голову только приходит выбирать по 20 штук и при необходимости мёрджить в контроллере. Но это не очень красиво :(

Comments

[alesto]

Мне нужно чтобы их всего было 20 с учётом повторений. то есть если там 50 штук идут подряд а затем 19 не подряд, то я выберу 59 записей. По сути нужно посчитать сколько записей выбрать чтобы из них можно было в шаблоне сформировать 20 единиц контента

Где поставить галочку чтобы на почту приходили новые уведомления об ответах?

[alesto]

В уточнении выше имелось ввиду не 59 а 69

Answer 1

[vinxru]

select id, action, user_id
from (select ifnull(@a<>action or @b<>user_id, 1) as w, id, @a:=action as action, @b:=user_id as user_id from table1 order by id) as t
where w


Или упрощенный вариант. Если ID это прямой первичный ключ, то сортировка не нужна, и следовательно не нужен подзапрос.

select * from table1 where (@b:=user_id)+(@a:=action)+(@w:=ifnull(@a<>action or @b<>user_id, 1))+1 and @w


На входе
Id, action, user_id
100 1 1
101 1 1
102 1 2
103 1 1
104 1 2

На выходе
id action user_id
100 1 1
102 1 2
103 1 1
104 1 2

P.S. Подзапрос можно исключить,

Comments

[alesto]

Спасибо! практически то что мне нужно! мне правда нужно подсчитать какой сделать offset от начала чтобы получилось 20 «единиц контента»

[vinxru]

Просто добавьте LIMIT в конец запроса.

select * from table1 where (@b:=user_id)+(@a:=action)+(@w:=ifnull(@a<>action or @b<>user_id, 1))+1 and @w limit 20

[alesto]

Я не стал нагружать вопрос подробностями. У таблицы есть ещё поля по которым будет подгружаться контент. То есть мне нужно получить их все чтобы в шаблоне сделать из них 20 штук.

[vinxru]

P.S. Выполните еще перед этим запросом "set @a:=@b:=0" на всякий случай. Если хочется обойтись одним запросом, то можно в подзапрос завернуть: select id,action,user_id from (select *, @a:=@b:=0 from table1) as t where (@b:=user_id)+(@a:=action)+(@w:=ifnull(@a<>action or @b<>user_id, 1))+1 and @w limit 20. Способов много.

[alesto]

В вашем запросе получается что я вытащу только те у которых соседние ряды не одинаковые. А мне нужно вытащить все, но так чтобы в шаблоне сделать из них 20 уникальных. Как я писал к уточнении вопросу мне по сути нужно вытащить количество записей которые образуют 20 уникальных.

[vinxru]

То есть вам нужно количество объединенных (пропущеных) записей. Можно воспользоваться GROUP BY, вот так:

select min(id), action, user_id, count(*) from (
select *, @w:=@w+case when ifnull(@a<>action or @b<>user_id, 1) then 1 else 0 end as w, @a:=action, @b:=user_id from (
select *, @a:=0, @b:=0, @w:=0 from table1 order by id
) as t
) as t
group by action, user_id, w order by id

Можно ввести дополнительную переменную @n. Можно (попытаться) написать это без под запросов, но это будет менее наглядно.

[vinxru]

UPD: Можно ввести дополнительную переменную @n вместо COUNT(*).

[alesto]

А как Вам вот такое решение?

set @count:=0;
select
*
from
note
where
(@b:=user)+(@a:=action)+(@count:=@count+ifnull(@a<>action or @b<>user, 1))
and @count<21
LIMIT
1,1000

Answer 2

[rakot]

Может недопонял, но разве не так?
SELECT `id`, `Action`, `user_id` FROM `table` GROUP BY `Action`, `user_id` LIMIT 20;
или
SELECT `id`, `Action`, `user_id` FROM (SELECT * FROM `table` LIMIT 20) GROUP BY `Action`, `user_id`;

Comments

[rakot]

Совсем забыл count(*).
SELECT `id`, `Action`, `user_id`, count(*) FROM `table` GROUP BY `Action`, `user_id` LIMIT 20;
Вот так, а то GROUP BY будет недоволен.

[Максим]

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

[yield]

т.е. для такой таблицы:
id action user_id
100 1 1
101 1 1
102 1 2
103 1 1
104 1 2

результат должен быть:
action user_id
1 1
1 2
1 1
1 2

верно?

[Максим]

угу.
я не топикстартер, но задачка заинтересовала, быть может всё-таки невозможное возможно =)

[alesto]

Не совсем. мне нужно чтобы их всего было 20 с учётом повторений. то есть если там 50 штук идут подряд а затем 19 не подряд, то я выберу 59 записей. По сути нужно посчитать сколько записей выбрать чтобы из них можно было в шаблоне сформировать 20 единиц контента

[alesto]

ай не 59 а 69

Answer 3

[Юрий]

SELECT `id`, `action`, `user_id` FROM `table` a
WHERE 
(SELECT MAX(b.`id`) FROM table b WHERE b.`id` < a.`id`) 
!= 
(SELECT MAX(c.`id`) FROM `table` c WHERE c.`action`=a.`action` AND c.`user_id` = a.`user_id` AND c.`id` < a.`id`)

Может где ошибся, или можно попроще, но идея, думаю, понятна…

Comments

[Юрий]

ну и LIMIT 0,20 добавить надо

[Юрий]

О, еще в WHERE добавить
AND a.id > (SELECT MIN(d.`id`) FROM `table` d)
иначе самую первую строчку скорее всего не выведет.

[Юрий]

Глючу, надо OR a.`id` = (SELECT MIN(d.`id`) FROM `table` d)

Answer 4

[stanishevsky]

Если у вас данные типа

Id, action, user_id
100 1 1
101 1 1
102 1 2
103 1 1
104 1 2

, то очевидно, столбец Id вам не нужен, поскольку не имеет смысла. Вам нужны только action, user_id.

Тогда это будет SELECT DISTINCT action, user_id FROM table ORDER BY user_id LIMIT 120,10; и все тут.

Comments

[stanishevsky]

А, прошу прощения, загвоздка именно в подряд идущих. Да, тогда я все неправильно написал :)