Average per hour inverval - Rails

Question

[Info: Ubuntu 14.02, ruby 2.2.3p110, Rails 4.2.1]
I need a rake task to fetch the average inserts per one hour range [like 00:00 to 00:59] from the DB (SQLite).
I was trying 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 this 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?

score 1 · Accepted Answer · answered May 20 '15 at 20:24

You could use a nested raw 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 out the date and hour (in hours subquery)
Get the total hourly counts (in hourly_counts subquery)
Average the hourly counts (in outer query)

Thanks @aaron, just a bit more info: `hour(created_at)` won't work on SQLite, a workaround is `strftime('%H', created_at)`. Also, its possible to parse the hour and date at the same subquery that we get the `hourly_counts` and in outer query we can take the `hour_created_at` and `group by` it. So as result we'll got a set of `hour_created_at => average_count_all`. — Munizart, May 20 '15 at 21:21

score 0 · Answer 2 · edited May 23 '17 at 11:43

I solved my problem using the query of this answer:
I edited the table name, removed where clause, changed date(from_unixtime('date')) to date(created_at) and changed hour(from_unixtime('date')) to strftime('%H', created_at) (SQLite does not have a hour() function)

So I got something like:

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

Average per hour inverval - Rails

2 Answers2