Как правильно подсчитать статистику в MySQL?

Question

[synapse_people]

Добрый день.
Есть задача - подсчитать статистику переходов по реф.ссылке и количество регистраций по дням.
Сейчас работает примерно вот так: pastebin.com/X2HKN3cq
Можно ли как-то оптимизировать или это оптимальный вариант для решения такой проблемы? Раньше было просто - выбор строк из таблицы переходов по ссылке и склейка(JOIN) с таблицей регистраций. При таком подходе несколько багов есть: По дням, которым не было переходов по ссылке не отображается статистика, если регистраций большей чем 1, то количество уников(переходов по ссылке) умножается на количество рег(решалось методов COUNT(DISTINCT id), но помоему - это костыль!).

Как реализовать правильно подсчет статистики по дням на MySQL?

Answer 1

[Евгений Безымянников]

Из date и partner_id сделайте составной ключ вида 13-2015-07-04 к примеру. Сделайте его уником.
При записи делайте вставку 1 в значение или апдейт +1.
При выводе графика изначально заполнять нулями все даты которые пустые (хоть создать массив по периоду заполненый нулями и влить в него массив из БД).

Comments

[synapse_people]

Да, так тоже было.. Но как-то не прикольно хранить такие данные, ведь лучше подсчитывать в реальном времени!

[Евгений Безымянников]

synapse_people: У Вас и будет в реальном времени, но сама операция суммирования вынесена в запрос вставки. Фактически при квантизации статистики по суткам глупо хранить все факты переходов, если это не нужно. Если данные в бд пишутся из принципы "сохраним все, а потом пригодится", то лучше в отдельный таблицы выносить, делать индексы и вычищать данные которые уже не нужны из основных таблиц.

[synapse_people]

Евгений Безымянников: К сожалению, все то, что сохраняется действительно нужно) Просто не сильно это проблемно каждый раз вставлять строку статистики, ее обновлять... Если можно один раз подсчитать, когда это именно нужно ?

[Евгений Безымянников]

synapse_people: если у Вас число записей на миллионы, а подсчет сумм в кабинете для статистики занимает много времени, тормозит бд и т.п., то лучше оптимизировать.
Кстати, не отменяет того факта, что можно делать 2 записи. Причем основную в бд статистики, а с составным ключем запись создавать через триггер.

[synapse_people]

Евгений Безымянников: Тут еще вопрос в том, чтобы допустим, если статистки за нужные дни нету, отображались строки показывающие 0..
То есть, партнер не рекламил неделю, требуется показать стату за эту неделю.. Должны быть 7 дней и все по нулям, а при помощи таблицы получается, что строк просто нету.. Как-то можно сделать по нормальному?

[Евгений Безымянников]

В кроне поставить в 23:50 заполнение нулями счетчиков на следующие сутки. У меня такой проблемы не было, так как почти всегда js плагин графика при отсутствии значения в массиве на дату/ключ равняет его в нуль или просто прерывает график.