Hourly Average Inverval - Rails

Question

[Information: Ubuntu 14.02, ruby ​​2.2.3p110, Rails 4.2.1]
I need a rake task to get average inserts in one hour (for example, from 00:00 to 00:59) from DB (SQLite).
I tried to do something like:

    namespace :db do
        task results: :environment do
            tweet = Tweet.group('date(created_at)').group('hour(created_at)').average()
            puts "#{tweet}"
        end

    end

But that will throw me this exception

    ActiveRecord::StatementInvalid: SQLite3::SQLException: misuse of aggregate function count(): SELECT AVG(count(*)) AS average_count_all, date(created_at) AS date_created_at, hour(created_at) AS hour_created_at FROM "tweet" GROUP BY date(created_at), hour(created_at)

Is there a way to get this average per hour using ActiveRecord?

Answer 1

You can use the nested source SQL query:

SELECT
  AVG(hourly_count) as average_count_all
FROM
  (
    SELECT
      count(*) as hourly_count,
      date_created_at,
      hour_created_at
    FROM
      (
        SELECT
          date(created_at) as date_created_at,
          hour(created_at) as hour_created_at
        FROM
          tweet
      ) as hours
    GROUP BY
      date_created_at,
      hour_created_at
  ) as hourly_counts

This is untested, but the idea is this:

• Parse the date and time (in the subquery hours)
• Get total hours (in subquery hourly_counts)
• Average hours (in an external request)

Answer 2

, :
, where, date(from_unixtime('date')) date(created_at) hour(from_unixtime('date')) strftime('%H', created_at) (SQLite hour())

, - :

select the_hour,avg(the_count) from(
    select date(created_at) as the_day, strftime('%H', created_at) as the_hour, count(*) as the_count
    from tweet group by the_day,the_hour
) s group by the_hour