Масштабируемая структура таблицы для периодически обновляемых статистических данных, которые агрегируются с течением времени

Я получаю статистические данные каждые 30 секунд, которые я хочу сохранить в своей базе данных, чтобы потом анализировать. Например, каждые 30 секунд я мог получить количество апельсинов, проданных в магазине за последние 30 секунд. Позже я хочу получить эти данные из базы данных и использовать их для создания диаграмм, показывающих информацию, такую ​​как количество апельсинов, проданных для магазина за последние 24 часа, последние x недель, последние х месяцев и последние х лет.

Если я просто сбрасываю все в один стол, кажется, что он будет расти очень быстро, особенно если у вас много источников данных (магазинов). Я думал, что данные могут быть усреднены, так что с течением времени он был менее зернистым. То есть, вести подробные записи за последние пару часов (записи в БД на каждые 30 секунд), то, возможно, средние 15-минутные промежутки времени в течение последних нескольких недель, а затем сохраняют средние значения каждого дня за последние несколько месяцев и т. Д. .

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

Имеет ли смысл этот подход? Есть ли лучший подход? Как мне организовать это в таблицу? Будет ли это несколько таблиц? Является ли SQL (вероятно, MySQL) хорошей подгонкой или что-то лучше работает? Любые мысли об этом будут очень признательны!

6 голосов | спросил Venesectrix 20 +04002011-10-20T23:12:23+04:00312011bEurope/MoscowThu, 20 Oct 2011 23:12:23 +0400 2011, 23:12:23

3 ответа


5

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

Помните ...

  

Вы не можете усреднять средние значения ...

В терминах структур данных я бы взял следующий подход:

  • detailed_data - таблица для хранения наиболее гранулированного уровня данных, которые у вас есть
  • minute_data - данные суммируются на минутном уровне
  • hour_data - данные, суммированные на уровне часа
  • day_data - etc
  • week_data - etc
  • month_data - etc

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

Вариант 1 - Создать хранимую процедуру для сохранения данных

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

Вариант 2 - создание триггеров в таблицах данных

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

Что анализировать

Когда у вас есть данные, суммированные таким образом, вы можете анализировать их на любом уровне, который хотите, и вы можете присоединиться к информации о дате /времени в таблицах измерений, чтобы получить более хороший уровень анализа и фильтрации - см. мой ответ на этот пост для дополнительной информации https://stackoverflow.com /questions /3249877 /mysql-using-the-date-in-a-between-condition-for-the-results

ответил Dave Rix 21 +04002011-10-21T13:36:17+04:00312011bEurope/MoscowFri, 21 Oct 2011 13:36:17 +0400 2011, 13:36:17
6

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

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

Хотя, может быть, верно, что вы не заботитесь о 30-секундных периодах через несколько недель, не говоря уже через год, в какой момент вы перестаете увеличивать отчетный период? Вы останавливаетесь на днях или идете на несколько недель? (или месяцев? или четвертей?)

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

Относительно «Как мне организовать это в таблицу?» лучшим способом было бы иметь начальную и конечную дату /время (до второго) за представленный период, а также средний показатель за этот период. Для удобства вы можете также включить атрибут разбиения на разделы, который описывает длину периода, например. 'm', 'h', 'd', 'w', 'M', 'q', 'y' или что имеет смысл для ваших резюме.

ответил Joel Brown 21 +04002011-10-21T03:51:21+04:00312011bEurope/MoscowFri, 21 Oct 2011 03:51:21 +0400 2011, 03:51:21
1

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

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

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

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

База данных SQL подходит для этого, потому что ее объекты запросов намного лучше, чем те, которые доступны из баз данных nosql и (что более важно), потому что они будут хорошо играть с сторонними инструментами отчетности. Если вы хотите провести значительную аналитическую работу, вам может быть лучше с PostgreSQL, чем MySQL.

ответил ConcernedOfTunbridgeWells 21 +04002011-10-21T12:47:09+04:00312011bEurope/MoscowFri, 21 Oct 2011 12:47:09 +0400 2011, 12:47:09

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132