使用Rails，如何查询数据库的汇总号并按周分组？ [英] With Rails, how can I query the db for an aggregate number and grouped by week?

问题描述

给出如下表格：

日志：

id, user_id, points, created_at

使用Rails，如何查询数据库由user_id分配，然后通过created_at按周分组，然后我可以得到类似的东西：

With Rails, how can I query the database by user_id and then GROUPED by week via created_at where I can then having something like:

   WEEK of X/XX/XXXX - 3011 total points
   WEEK of X/XX/XXXX - 320 total points
   WEEK of X/XX/XXXX - 31 total points
   WEEK of X/XX/XXXX - 30330 total points

谢谢

推荐答案

points_by_week = Log.where(user_id: user_id).group("DATE_TRUNC('year', created_at)", "DATE_TRUNC('week', created_at)").sum(:points)

将产生类似

{[2014, 4]=>3,
 [2014, 8]=>7,
 [2015, 4]=>26,
 [2015, 6]=>19,
 [2015, 12]=>50,
 [2015, 32]=>48,
 [2016, 2]=>78,
 [2016, 3]=>45,
 [2016, 4]=>49,
 [2016, 5]=>110,
 [2016, 45]=>30,
 [2017, 4]=>130,
 [2017, 11]=>185}

其中键为[年，周]，则可以使用Date.commercial来获取

where the key is [year, week], then you can use Date.commercial to get the week of

points_by_week.each do |(year, week), count|
  date = Date.commercial(year, week)
  puts "Week of #{date.strftime('%d/%m/%Y')} - #{count} total points"
end

然后（只是我可以），查询的mysql版本如下所示

And (just cause I can), the mysql version of the query looks like this

points_by_week = Log.where(user_id: user_id).group("year(created_at)", "week(created_at)").sum(:points)

默认情况下，假设一周从星期一开始，而一年从一年的第一个星期一开始

This by default assumes the week starts on Monday, and the first week of a year is starts on the first Monday of the year

这篇关于使用Rails，如何查询数据库的汇总号并按周分组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！