MySQL(ActiveRecord)计算日期列在分组的月份和年份之前或之前的行

Question

我目前正在计算在过去 12 个月的月/年组合的单个查询中在特定月份或之前创建的用户分配。然而，事实证明这些比我希望的要慢，我想看看是否有办法将所有这些查询合并为一个。

这是通过连接到 MySQL 数据库 (5.6.10) 的 ActiveRecord 模型从 Ruby on Rails (5.0) 应用程序运行的。

这个 ActiveRecord 查询

User.joins(:child_classrooms).where('assignments.created_at <= ?', DateTime.new(month_year[0], month_year[1]).end_of_month).count('DISTINCT users.id')

生成这个 mysql 查询

SELECT COUNT(DISTINCT users.id) 
FROM `users` INNER JOIN `assignments` ON `assignments`.`user_id` = `users`.`id` AND `assignments`.`role_id` = 3 AND `assignments`.`assignable_type` = 'Student' 
             INNER JOIN `students` ON `students`.`id` = `assignments`.`assignable_id` 
             INNER JOIN `classrooms_students` ON `classrooms_students`.`student_id` = `students`.`id` 
             INNER JOIN `classrooms` ON `classrooms`.`id` = `classrooms_students`.`classroom_id` 
WHERE (assignments.created_at <= '2019-04-30 23:59:59')

上面的查询生成了 12 次(过去 12 个月中的每个月一次，更改 where 子句时间戳)，相反我希望有一个查询返回按年和月分组的相同数据，计算 assignments.created_at <=分组的年份和月份。

我熟悉如何按年和月分组，但我感到困惑的是如何只计算那些创建时间戳在分组的年和月之前或之前的行。

Answer 1

您还需要选择年份和月份，因此它们各占一行。然后你可以按那些列分组:

SELECT year(assignments.created_at),month(assignments.created_at),COUNT(DISTINCT users.id) 
FROM `users` INNER JOIN `assignments` ON `assignments`.`user_id` = `users`.`id` AND `assignments`.`role_id` = 3 AND `assignments`.`assignable_type` = 'Student' 
             INNER JOIN `students` ON `students`.`id` = `assignments`.`assignable_id` 
             INNER JOIN `classrooms_students` ON `classrooms_students`.`student_id` = `students`.`id` 
             INNER JOIN `classrooms` ON `classrooms`.`id` = `classrooms_students`.`classroom_id` 
WHERE (assignments.created_at <= '2019-04-30 23:59:59')
GROUP BY year(assignments.created_at),month(assignments.created_at)

编辑您可能不需要 WHERE 子句