带有聚合函数和 DayOfWeek 的 Ruby Rails 复杂 SQL

发布于 2024-10-15 23:01:46 字数 548 浏览 2 评论 0原文

Rails 2.3.4

我已经搜索了谷歌，但没有找到解决我的困境的答案。

对于这个讨论，我有两个模型。用户和条目。用户可以有多个条目（每天一个）。

条目具有值和发送日期。

我想查询并显示用户一周内条目的平均值。因此，如果用户输入了过去 3 周的值，我想显示星期日、星期一等的平均值。在 MySQL 中，这很简单：

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

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

我查看了 find_by_sql，但是当我搜索 Entry 时，我不想返回 Entry 对象；相反，我需要一个最多 7 天的数组和平均值...

另外，我有点担心它的性能，因为我们希望在用户登录时将其加载到用户模型中，以便它可以显示在他们的仪表板上。欢迎任何建议/指示。我对 Rails 还比较陌生。

原文

Rails 2.3.4

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 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

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

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...

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风尘浪孓 2024-10-22 23:01:46

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

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，然后您可以使用此枚举中的每个或全部来查看结果。

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

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

这会将您的结果放入 memcached 中，在“user//averages”键下保留一天。例如，如果您是 ID 为 10 的用户，您的平均值将位于“user/10/average”下的 memcached 中，并且下次您执行此查询时（同一天之内），将使用缓存版本，而不是实际命中数据库。

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);"

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.

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

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.

回复收藏 0 原文

得不到的就毁灭 2024-10-22 23:01:46

未经测试，但类似这样的东西应该可以工作：

@user.entries.select('DAYOFWEEK(sent_at) as day, AVG(value) as average').group('1').all

注意：当您使用 select 显式指定列时，返回的对象是只读。 Rails 无法可靠地确定哪些列可以修改，哪些列不能修改。在这种情况下，您可能不会尝试修改所选列，但您也可以通过结果对象修改 sent_at 或 value 列。

查看 ActiveRecord 查询指南，以一种相当新手友好的格式详细了解这里发生的情况。哦，如果该查询不起作用，请发回，以便其他可能偶然发现此问题的人可以看到（我可能可以更新）。

由于 entries 返回数组，这将不起作用，因此我们可以尝试使用 join 来代替：

User.where(:user_id => params[:id]).joins(:entries).select('...').group('1').all

同样，我不知道这是否有效。通常你可以在joins之后指定where，但我还没有看到select在那里组合。这里棘手的一点是，select 可能根本不会返回有关用户 的任何数据。避免使用 find_by_* 方法，而在 Entry 模型中编写一个仅使用 select_all 调用查询的方法可能更有意义（ docs）并跳过关联映射。

Untested, but something like this should work:

@user.entries.select('DAYOFWEEK(sent_at) as day, AVG(value) as average').group('1').all

NOTE: When you use select to specify columns explicitly, the returned objects are read only. Rails can't reliably determine what columns can and can't be modified. In this case, you probably wouldn't try to modify the selected columns, but you can'd modify your sent_at or value columns through the resulting objects either.

Check out the ActiveRecord Querying Guide for a breakdown of what's going on here in a fairly newb-friendly format. Oh, and if that query doesn't work, please post back so others that may stumble across this can see that (and I can possibly update).

Since that won't work due to entries returning an array, we can try using join instead:

User.where(:user_id => params[:id]).joins(:entries).select('...').group('1').all

Again, I don't know if this will work. Usually you can specify where after joins, but I haven't seen select combined in there. A tricky bit here is that the select is probably going to eliminate returning any data about the user at all. It might make more sense just to eschew find_by_* methods in favor of writing a method in the Entry model that just calls your query with select_all (docs) and skips the association mapping.