具有聚合功能和DayOfWeek的Ruby Rails Complex SQL [英] Ruby Rails Complex SQL with aggregate function and DayOfWeek

问题描述

Rails 2.3.4

我搜索过google，但没有找到解决我难题的答案。

I have searched google, and have not found an answer to my dilemma.

在此讨论中，我有两种模型。用户和条目。用户可以有多个条目（每天一个）。

For this discussion, I have two models. Users and Entries. Users can have many Entries (one for each day).

条目具有值和发送日期。

Entries have values and sent_at dates.

I想要查询并在DAY OF WEEK之前显示用户条目的平均值。因此，如果用户输入了过去3周的值，那么我想显示星期日，星期一等的平均值。在MySQL中，这很简单：

I want to query and display the average value of entries for a user BY DAY OF WEEK. So if a user has entered values for, say, the past 3 weeks, I want to show the average value for Sundays, Mondays, etc. In MySQL, it is simple:

SELECT DAYOFWEEK(sent_at) as day, AVG(value) as average FROM entries WHERE user_id = ? GROUP BY 1

该查询将返回0到7条记录，具体取决于用户拥有的天数至少有一个条目。

That query will return between 0 and 7 records, depending upon how many days a user has had at least one entry.

我看过find_by_sql，但是当我搜索Entry时，我不想返回Entry对象。相反，我需要最多7天的平均值数组。

I've looked at find_by_sql, but while I am searching Entry, I don't want to return an Entry object; instead, I need an array of up to 7 days and averages...

此外，我有点担心它的性能，因为我们要加载用户登录时将其发送给用户模型，以便可以将其显示在其仪表板上。欢迎任何建议/指针。我对Rails还是比较陌生。

Also, I am concerned a bit about the performance of this, as we would like to load this to the user model when a user logs in, so that it can be displayed on their dashboard. Any advice/pointers are welcome. I am relatively new to Rails.

推荐答案

您可以直接查询数据库，而无需使用实际的ActiveRecord对象。例如：

You can query the database directly, no need to use an actual ActiveRecord object. For example:

ActiveRecord::Base.connection.execute "SELECT DAYOFWEEK(sent_at) as day, AVG(value) as average FROM entries WHERE user_id = #{user.id} GROUP BY DAYOFWEEK(sent_at);"

这将为您提供MySql :: Result或MySql2 :: Result，然后您可以使用此枚举中的每个或全部来查看您的结果。

This will give you a MySql::Result or MySql2::Result that you can then use each or all on this enumerable, to view your results.

对于缓存，我建议您使用memcached，但其他任何Rails缓存策略也可以使用。 memcached的一个好处是您可以使缓存在一定时间后过期。例如：

As for caching, I would recommend using memcached, but any other rails caching strategy will work as well. The nice benefit of memcached is that you can have your cache expire after a certain amount of time. For example:

result = Rails.cache.fetch('user/#{user.id}/averages', :expires_in => 1.day) do
  # Your sql query and results go here
end

这会将您的结果放入在用户//平均键下进行了一天的内存缓存。例如，如果您是ID为10的用户，则平均值将存储在 user / 10 / average下的memcached中，而下次您执行该查询时（同一天之内），将使用缓存的版本，而不是实际点击数据库。

This would put your results into memcached for one day under the key 'user//averages'. For example if you were user with id 10 your averages would be in memcached under 'user/10/average' and the next time you went to perform this query (within the same day) the cached version would be used instead of actually hitting the database.

这篇关于具有聚合功能和DayOfWeek的Ruby Rails Complex SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！