How to get average of the last values by each table from database

Question

I have 2 models: a station and sensor_value that are related by the station id.

class Station < ApplicationRecord
  has_many :sensor_values, primary_key: :station_id, foreign_key: :station_id, dependent: :destroy
  validates :station_id, presence: true, uniqueness: true
end

class SensorValue < ApplicationRecord
  belongs_to :station, primary_key: :station_id, foreign_key: :station_id
  validates :value, :type, :station_id, presence: true
end

One station can have several sensor values, each of sensor values saves different types of values (temperature, humidity, light).

I need to get the average for all the latest indicators from each station.My current solution to get the average temperature across all stations:

def last_sensors_values
  temperature = []

  Station.all.each do |st|
    next if st.sensor_values.empty?

    temperature_val = st.sensor_values.where(type: 'temperature').last.value
    temperature.push(temperature_val) unless temperature_val.zero?
  end

  {
    temperature: temperature.sum / temperature.size
  }
end

But I think it can be done with SQL, any ideas on how to improve this code?

Thanks for any help)

Updated: Rails 5.2.5 Database PostgreSQL >= 10

https://stackoverflow.com/questions/31879150/group-by-and-count-using-activerecord ... but use `maximum` instead of count, then you should be able to divide the sum by the size ... ? — Jad, Aug 01 '21 at 22:18

score 1 · Answer 1 · answered Aug 01 '21 at 22:36

First a small style comment... The model should be Sensor with an attribute value, instead of SensorValue with a attribute value.

This query gives you what you need, it's not written entirely in sql, but it generates a single sql query, which you can look at and use if that's your preference.

Station.joins(:sensors).select("avg(sensors.value) as avg_val").group("sensors.station_id")

Each Station model returned will have a #avg_val method containing the calculation you are looking for. Of course, you can add a where clause to limit to temperature sensors.

How to get average of the last values by each table from database

1 Answers1