Упрощение тройного запроса. / MySQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Упрощение тройного запроса.

3 сообщений из 3, страница 1 из 1

Упрощение тройного запроса.

#38627103

Demise

Гость

Есть таблица, в ней - ID, дата, количество просмотров, количество ответов.
Есть задача - просуммировать для каждого ID просмотры и ответы за последние три дня.

Поправка: дни - это не сегодня, вчера, позавчера, это - последние три записи. То есть сегодня могло не быть просмотров и ответов вообще, значит - запись не создалась, а значит берем вчера, позавчера, позапозавчера. Нам нужны именно "непустые" дни, поэтому вариант решения с созданием "нулевой" статистики и потом расчетом по сегодня, вчера и позавчера - не подходит.

Получилось решить лишь таким запросом с несколькими уровнями вложенности:

Код: sql

1.
2.
3.
4.
5.
6.
7.

set @num := 0, @aid := '';

SELECT ans_id, SUM(v), SUM(a) FROM (
SELECT *,   @num := if(@aid = ans_id, @num + 1, 1) as day, @aid := ans_id as temp FROM (
SELECT ans_id,date,SUM(d_views) as v, SUM(d_answers) as a FROM `details` GROUP BY ans_id, date ORDER BY ans_id ASC, date DESC
) as `raw`
) as `sorted` WHERE day <= 3 GROUP BY ans_id

1. Самый "глубокий", первый запрос:

Код: sql

SELECT ans_id,date,SUM(d_views) as v, SUM(d_answers) as a FROM `details` GROUP BY ans_id, date ORDER BY ans_id ASC, date DESC

получает id, дату, просмотры и ответы, сортирует их так, чтобы id шли подряд, а у этих id - даты в порядке убывания (дата записана в формате '2014-03-01')

2. Второй запрос:

Код: sql

SELECT *,   @num := if(@aid = ans_id, @num + 1, 1) as day, @aid := ans_id as temp FROM (...) as `raw`

Номерует следующие друг за другом строки. Благодаря тому, что по id у нас уже отсортировано, а даты идут по убыванию - мы получаем при смене id сброс переменной @num, а самые свежие данные (за последнюю дату) будут иметь число 1 в поле day

3. Внешний, третий запрос:

Код: sql

SELECT ans_id, SUM(v), SUM(a) FROM (...) as `sorted` WHERE day <= 3 GROUP BY ans_id

Откидываем все то, что имеет поле day больше 3х (то есть получаем в остатке лишь последние три дня для каждого id) и уже группируем по id, суммируя количество просмотров и ответов.

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

...

Рейтинг:

0 / 0

27.04.2014, 19:40:19

| Ответить | Цитировать | Написать

Упрощение тройного запроса.

#38627122

javajdbc

Участник

Откуда: Montreal

Сообщения: 18 207

Рейтинг: 0 / 0

Demise,

TOP-N query: 7489069

вопшемто у вас аналогичный подход
Вот мой оригинальный СКЛ с комментариями

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

select *      -- здесь у вас сумма по ИД
from 
(
select ta.*,  
if(@typex=ta.type_a, 
@rownum:=@rownum+1, 
@rownum:=1+least(0,@typex:=ta.type_a)) rown 
from 
  tableA ta,  -- здесь у вас подселект для группировки ответовс пределах каждого дня
  (select @rownum:=1, @typex:='_') zz
order by type_a, date_a DESC
) yy
where rown < 4
--   здесь у вас груп бу для суммы

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

Конечно же надо оптимизировать индекс(ы) для скорости.

Серьезным улучшением будет предрасчет (пре-агрегация)
самого глубоково подселекта с созданием постоянной таблицы.
Каждый день туда добавлять записи за предыдуший день.

Другой вариант тойже идеи -- сделать триггер на
инсерт в основную таблицы для апдейта пре-агрегатной таблицы

...

Рейтинг:

0 / 0

27.04.2014, 19:59:57

| Ответить | Цитировать | Написать

Упрощение тройного запроса.

#38627138

Demise

Гость

javajdbc,

С памятью проблем нет, на сервере ее свободно еще порядка 80Гб. Так понимаю, что максимум - поковыряться в конфигах самого MySQL для оптимальной обработки таких запросов (в таблице их может быть около 2.000.000 - это с архивированием старых).

Спасибо за ответ.

...

Рейтинг:

0 / 0

27.04.2014, 20:39:22

| Ответить | Цитировать | Написать

3 сообщений из 3, страница 1 из 1

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Упрощение тройного запроса.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=47&fpage=180&tid=1834921]:	0ms
get settings:	5ms
get forum list:	9ms
check forum access:	2ms
check topic access:	2ms
track hit:	23ms
get topic data:	5ms
get forum data:	2ms
get page messages:	31ms
get tp. blocked users:	1ms
others:	190ms

total:	270ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы