CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Andres Perez
Andres Perez

Reputation: 159

Grouping by 4 Hours Intervals

I'm trying to get the average of some data that I have in 4 hours intervals, my app is in Ruby on Rails, so I don't know if I should write a PostgreSQL query or do it in the Model of the application, nor I don't know how I will do it in either. The simple version of the model is something like this:

id    heart_rate    timestamp

I also need for 1 hour, and 1 day interval but I already know how to do that, on the SQL side at least:

SELECT AVG(heart_rate), to_char(timestamp, 'YYYY-MM-DD') AS ts
FROM vital_signs
GROUP BY ts
ORDER BY ts ASC;

Upvotes: 0

Views: 879

Answers (3)

Kimooz
Kimooz

Reputation: 961

Simulating the mathematical remainder is a good option as :

Model.count(group: 'CAST((24 - hour(table.created_at))/4 as UNSIGNED)')

It will give you a hash with count of each group of four hours as this 

{0=>2, 1=>1, 3=>1, 6=>2}

6 represents start of day 0:00 till 4:00 and so on

You can get the average using the following :

Model.average(:field ,group: 'CAST((24 - hour(table.created_at))/4 as UNSIGNED)')

Upvotes: 1

Roger
Roger

Reputation: 7612

24 hrs / 4 = 6 segments.

Convert your date to hours, then do a integer division (DIV). That will give you 0-5 numbers. Each remainder representing a segment.

This example is for MySQL (and not Rails), replace the DIV (Integer division function of MySQL) with Postgress equivalent.

SELECT 
    AVG(heart_rate), HOUR(timestamp) DIV 4, COUNT(*)
FROM 
    vital_signs
GROUP BY 
    DATE(timestamp), 
    HOUR(timestamp) DIV 4;

You can group on the date seperately, so per day you getting the avg. results.

You can execute SQL directly within Rails via:

ActiveRecord::Base.connection.execute "<sql>"

Upvotes: 1

rorra
rorra

Reputation: 9693

Try with:

MyModel.select("(CASE 
  WHEN hour(created_at) < 4 then '00-04'
  WHEN hour(created_at) >= 4 and hour(created_at) < 8 then '04-08'
  WHEN hour(created_at) >= 8 and hour(created_at) < 12 then '08-12'
  WHEN hour(created_at) >= 12 and hour(created_at) < 16 then '12-16'
  WHEN hour(created_at) >= 16 and hour(created_at) < 20 then '16-20'
  ELSE '20-24' END) AS delta,
  count(*) as qty").
group("delta")

Or simply:

ActiveRecord::Base.connection.execute("SELECT (CASE 
  WHEN hour(created_at) < 4 then '00-04'
  WHEN hour(created_at) >= 4 and hour(created_at) < 8 then '04-08'
  WHEN hour(created_at) >= 8 and hour(created_at) < 12 then '08-12'
  WHEN hour(created_at) >= 12 and hour(created_at) < 16 then '12-16'
  WHEN hour(created_at) >= 16 and hour(created_at) < 20 then '16-20'
  ELSE '20-24' END) AS delta,
  count(*) as qty
  FROM  my_table
  group by delta;")

But the idea is to let your database engine handle the grouping instead of trying to do something like that in ruby code.

Upvotes: 0

Related Questions