Структура базы данных для хранения статистики по дням, неделям, месяцам, годам

Вместо обновления счетчиков за день, неделю и т. Д. Просто ВСТАВЬТЕ строку в таблицу каждый раз, когда действие происходит следующим образом:

insert into activities (activity_date, activity_info) 
values (CURRENT_TIMESTAMP, 'whatever');

Теперь ваши отчеты очень простые, например:

select count(*) from activities
where activity_date between '2008-01-01' and '2008-01-07';

or

select YEARWEEK(`activity_date`) as theweek, count(*)
group by theweek

Вы можете просто добавлять записи в таблицу и SELECT их с помощью агрегатных функций.

Если по каким-то причинам вам необходимо вести агрегированную статистику, вы можете использовать:

CREATE TABLE aggregates (type VARCHAR(20), part VARCHAR(10) NOT NULL PRIMARY KEY, activity INT)

INSERT INTO aggregates (type, part, activity)
VALUES ('year', SUBSTRING(SYSDATE(), 1, 4), 1)
ON DUPLICATE KEY UPDATE activity = activity + 1

INSERT INTO aggregates (type, part, activity)
VALUES ('month', SUBSTRING(SYSDATE(), 1, 7), 1)
ON DUPLICATE KEY UPDATE activity = activity + 1

INSERT INTO aggregates (type, part, activity)
VALUES ('day', SUBSTRING(SYSDATE(), 1, 10), 1)
ON DUPLICATE KEY UPDATE activity = activity + 1

Это автоматически обновит существующие строки и при необходимости вставит несуществующие.

1. таблица событий: id, id активности, datetime, userid.
2. таблица пользователей: id, имя пользователя и т. д.
3. таблица действий: id, название активности и т. д.

Просто введите новую строку в событиях, когда событие произойдет. Затем вы можете анализировать события, но манипулировать временем, датой, пользователем, активностью и т. Д.

Для начала вы, вероятно, вообразите одну таблицу, так как это будет наиболее нормализованная форма. В таблице будет просто запись для каждого полученного вами попадания, причем каждая строка будет содержать дату / время этого попадания.

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

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

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

Другое ваше предложение заключалось в том, чтобы агрегировать его с самого начала, никогда не сохраняя каждое попадание в виде строки. Вы, вероятно, сделаете это, как и раньше, с строкой на каждый час. При каждом обращении соответствующая строка часов увеличивалась на единицу. У вас будут данные только в одном месте, и они уже будут достаточно хорошо обобщены.

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

Используйте звездообразный дизайн схемы. (или, возможно, дизайн снежинки).

Дизайн звездообразной схемы

В конечном итоге вы будете выполнять вставку в таблицу фактов для каждого нового действия. См. Предложение Тони.

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

Ваш вопрос относится к таблице размеров временных рамок. Назовем его «Альманах». Выберите степень детализации. Скажем день. В альманахе будет одна строка в день. Первичным ключом может быть дата. Ваша таблица фактов должна включать этот первичный ключ в качестве внешнего ключа, чтобы упростить объединение. (Неважно, объявляете ли вы его как внешний ключ. Это влияет только на ссылочную целостность во время процесса обновления.)

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

Вот статья, в которой сравнивается ER и DM. Я необычен тем, что мне нравятся оба метода, выбирая подходящий метод для соответствующей задачи.

http://www.dbmsmag.com/9510d05.html

Ответ Тони Эндрюса простейший, однако в приложениях хранилища данных иногда используется структура «снежинка»: таблица, в которой подсчитываются все действия, другая - для действий за день, третья - для действий за месяц, а третья - для действий за год. С такой структурой активность между любыми двумя датами может быть вычислена очень эффективно. https://en.wikipedia.org/wiki/Snowflake_schema

Ваш вопрос относится к таблице размеров временных рамок. Назовем его «Альманах». Выберите степень детализации. Скажем день. В альманахе будет одна строка в день. Первичным ключом может быть дата. Ваша таблица фактов должна включать этот первичный ключ в качестве внешнего ключа, чтобы упростить объединение. (Неважно, объявляете ли вы его как внешний ключ. Это влияет только на ссылочную целостность во время процесса обновления.)