Подтвердить что ты не робот

Rails: как создавать статистику в день/месяц/год или как отсутствуют агностические функции SQL базы данных (например: STRFTIME, DATE_FORMAT, DATE_TRUNC)

Я искал по сети, и я понятия не имею.

  • Предположим, вам нужно создать панель управления в области администрирования вашего приложения Rails, и вы хотите иметь количество подписчиков в день.
  • Предположим, что вы используете SQLite3 для разработки, MySQL для производства (довольно стандартная настройка)

В принципе, существует два варианта:

1) Извлеките все строки из базы данных с помощью Subscriber.all и суммируйте по дням в приложении Rails с помощью Enumerable.group_by:

@subscribers = Subscriber.all
@subscriptions_per_day = @subscribers.group_by { |s| s.created_at.beginning_of_day }

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

2) Запустить SQL-запрос в базе данных с помощью функций агрегации и даты:

Subscriber.select('STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions').group('day')

который будет запущен в этом SQL-запросе:

SELECT STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions
FROM subscribers
GROUP BY day

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

... но подождите... теперь приложение должно появиться в моей программе env, которая использует MySQL! Замените STRFTIME() на DATE_FORMAT(). Что, если завтра я перейду на PostgreSQL? Замените DATE_FORMAT() на DATE_TRUNC().

Мне нравится разрабатывать SQLite. Простой и легкий. Мне также нравится идея, что Rails является агностиком базы данных. Но почему Rails не предоставляет способ переводить SQL-функции, которые делают то же самое, но имеют разные синтаксисы в каждой RDBMS (эта разница действительно глупа, но эй, слишком поздно, чтобы жаловаться это)?

Я не могу поверить, что я нашел так мало ответов в Интернете для такой базовой функции приложения Rails: подсчитайте подписку в день, месяц или год.

Скажи мне, что я чего-то не хватает:)

ИЗМЕНИТЬ

Прошло несколько лет с тех пор, как я опубликовал этот вопрос. Опыт показал, что я должен использовать ту же БД для dev и prod. Поэтому теперь я считаю, что агностическое требование базы данных не имеет значения.

Dev/prod четность FTW.

4b9b3361

ОТВЕТЫ

Ответ 1

В конце концов я написал свой собственный камень. Проверьте это и не стесняйтесь вносить свой вклад: https://github.com/lakim/sql_funk

Позволяет делать такие вызовы, как:

Subscriber.count_by("created_at", :group_by => "day")

Ответ 2

Вы говорите о довольно сложных проблемах, которые Rails, к сожалению, полностью игнорирует. Документы ActiveRecord:: Calculations написаны так, как будто они все вам нужны, но базы данных могут делать гораздо более сложные вещи. Как упоминал Донал Стиллс в своем комментарии, проблема намного сложнее, чем кажется.

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

Чтобы немного расширить проблему, я думаю, вы обнаружите, что ваше текущее решение группировки по датам неадекватно. Кажется естественным вариантом использования STRFTIME. Основная проблема заключается в том, что он не позволяет вам группироваться произвольными периодами времени. Если вы хотите выполнить агрегацию по годам, месяцам, дням, часам и/или минутам, STRFTIME будет работать нормально. Если нет, вы обнаружите, что ищете другое решение. Еще одна огромная проблема заключается в агрегации при агрегировании. Например, вы хотите группировать по месяцам, но вы хотите сделать это, начиная с 15 числа каждого месяца. Как вы это сделаете, используя STRFTIME? Вам нужно будет группировать каждый день, а затем и месяц, но затем кто-то учитывает начальное смещение 15-го числа каждого месяца. Конечная соломинка состоит в том, что группировка STRFTIME требует группировки по строковому значению, которое вы найдете очень медленным при выполнении агрегации при агрегации.

Самое эффективное и лучшее решение, к которому я пришел, - это одно, основанное на целых периодах времени. Вот выдержка из одного из моих запросов mysql:

SELECT
  field1, field2, field3,
  CEIL((UNIX_TIMESTAMP(CONVERT_TZ(date, '+0:00', @@session.time_zone)) + :begin_offset) / :time_interval) AS time_period
FROM
  some_table
GROUP BY 
  time_period

В этом случае: time_interval - это количество секунд в период группировки (например, 86400 для ежедневного) и: begin_offset - это количество секунд, чтобы компенсировать начало периода. Бизнес-аккаунт CONVERT_TZ() определяет способ, которым mysql интерпретирует даты. Mysql всегда предполагает, что поле даты находится в локальном часовом поясе mysql. Но поскольку я храню время в UTC, я должен преобразовать его из UTC в часовой пояс сеанса, если я хочу, чтобы функция UNIX_TIMESTAMP() дала мне правильный ответ. Период времени заканчивается как целое число, которое описывает количество временных интервалов с момента начала unix-времени. Это решение гораздо более гибкое, поскольку оно позволяет группировать произвольные периоды и не требует агрегации при агрегации.

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

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

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

Ответ 3

Я только что выпустил гем, который позволяет вам сделать это легко с MySQL. https://github.com/ankane/groupdate

Вы действительно должны попробовать запустить MySQL в разработке. ваша среда разработки и производства должна быть как можно ближе - меньше шансов на то, чтобы что-то поработало над разработкой и полностью сломало производство.

Ответ 4

Если dn агностицизм - это то, что вам нужно, я могу представить несколько вариантов:

Создайте новое поле (назовем его day_str) для Абонента, который хранит либо форматированную дату, либо временную метку, и использует ActiveRecord.count:

daily_subscriber_counts = Subscriber.count(:group => "day_str")

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

Вы также можете, в зависимости от того, насколько гранулированы данные, которые визуализируются, просто вызовите .count несколько раз с установленной датой даты...

((Date.today - 7)..Date.today).each |d|
    daily_subscriber_counts[d] = Subscriber.count(:conditions => ["created_at >= ? AND created_at < ?", d.to_time, (d+1).to_time)
end

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

Если вы хотите совершить транзакции с mysql и sqlite, вы можете использовать...

daily_subscriber_counts = Subscriber.count(:group => "date(created_at)")

... поскольку они имеют сходные функции date().

Ответ 5

Я бы немного уточнил/разложил ответ PBaumann и включил таблицу Dates в вашу базу данных. Вам потребуется присоединиться к вашему запросу:

SELECT D.DateText AS Day, COUNT(*) AS Subscriptions
FROM subscribers AS S
  INNER JOIN Dates AS D ON S.created_at = D.Date
GROUP BY D.DateText

... но вы получили бы хорошо отформатированное значение без вызова каких-либо функций. С PK на Dates.Date вы можете объединить соединение, и оно должно быть очень быстрым.

Если у вас есть международная аудитория, вы можете использовать DateTextUS, DateTextGB, DateTextGer и т.д., но, очевидно, это не будет идеальным решением.

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

Ответ 6

Вот как я это делаю:

У меня есть класс Stat, который позволяет хранить сырые события. (Код с первых нескольких недель я начал кодировать в Ruby, поэтому извините некоторые из них:-))

class Stat < ActiveRecord::Base
    belongs_to :statable, :polymorphic => true

    attr_accessible :statable_id, :statable_type, :statable_stattype_id, :source_url, :referral_url, :temp_user_guid

    # you can replace this with a cron job for better performance
    # the reason I have it here is because I care about real-time stats
    after_save :aggregate

    def aggregate
    aggregateinterval(1.hour)
    #aggregateinterval(10.minutes)
end

    # will aggregate an interval with the following properties:
    # take t = 1.hour as an example
    # it 5:21 pm now, it will aggregate everything between 5 and 6
    # and put them in the interval with start time 5:00 pm and 6:00 pm for today date
    # if you wish to create a cron job for this, you can specify the start time, and t
def aggregateinterval(t=1.hour)
    aggregated_stat = AggregatedStat.where('start_time = ? and end_time = ? and statable_id = ? and statable_type = ? and statable_stattype_id = ?', Time.now.utc.floor(t), Time.now.utc.floor(t) + t, self.statable_id, self.statable_type, self.statable_stattype_id)

    if (aggregated_stat.nil? || aggregated_stat.empty?)
        aggregated_stat = AggregatedStat.new
    else
        aggregated_stat = aggregated_stat.first
    end

            aggregated_stat.statable_id = self.statable_id
    aggregated_stat.statable_type = self.statable_type
    aggregated_stat.statable_stattype_id = self.statable_stattype_id
    aggregated_stat.start_time = Time.now.utc.floor(t)
    aggregated_stat.end_time = Time.now.utc.floor(t) + t
    # in minutes
    aggregated_stat.interval_size = t / 60

    if (!aggregated_stat.count)
        aggregated_stat.count = 0
    end
    aggregated_stat.count = aggregated_stat.count + 1


    aggregated_stat.save
end

end

И здесь класс AggregatedStat:

class AggregatedStat < ActiveRecord::Base
    belongs_to :statable, :polymorphic => true

    attr_accessible :statable_id, :statable_type, :statable_stattype_id, :start_time, :end_time

Каждый элемент statable, который добавляется в db, имеет statable_type и statable_stattype_id и некоторые другие общие данные статистики. Statable_type и statable_stattype_id предназначены для полиморфных классов и могут содержать такие значения, как (строка) "Пользователь" и 1, что означает, что вы сохраняете статистику о номере пользователя.

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

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

class StatableStattype < ActiveRecord::Base
    attr_accessible :name, :description

    has_many :stats
end

Теперь перейдите к классам, для которых вы хотите получить некоторую статистику, и выполните следующие действия:

class User < ActiveRecord::Base
  # first line isn't too useful except for testing
  has_many :stats, :as => :statable, :dependent => :destroy
  has_many :aggregated_stats, :as => :statable, :dependent => :destroy
end

Затем вы можете запросить агрегированную статистику для определенного Пользователя (или Расположение в примере ниже) с помощью этого кода:

Location.first.aggregated_stats.where("start_time > ?", DateTime.now - 8.month)