sql - 优化检索一段时间内项目计数的 SQL 查询

Question

有没有办法更好地优化这个函数中的查询？我希望它尽可能只进行一个 SQL 查询。该代码获取 2 周内生成的事件数。谢谢。

def items_chart_data
    @current_student = Student.find(current_user.student_id)
    (2.weeks.ago.to_date..DateTime.now).map do |date|
        {
            created_at: date,
            item_count: Item.where("date(created_at) = ? AND student_id = ?", date, @current_student.id).count
        }
    end
end

Answer 1

您可以在一个查询中执行此操作，如下所示:

items = Item.select('date(created_at) as date_created, count(id) as id_count').
          where('student_id = ? and created_at >= ?', current_user.student_id, 2.weeks.ago.beginning_of_day).
          group('date(created_at)').map do |item|
  { created_at: item.date_created, item_count: item.id_count }
end

要将包含 0 个项目的缺失日期放入数组中，您可以这样做:

(2.weeks.ago.to_date..Date.current).each do |date|
  date = date.strftime('%Y-%m-%d')

  unless items.any? { |h| h.value?(date) } # Check if date exists already
    items << { created_at: date, item_count: 0 } # Add item_count: 0 if not
  end
end

items.sort_by! { |h| h[:created_at] } # Put array in correct order

请注意，将零项天数添加到数组中不会执行任何查询。数组 items 仅包含 14 个元素，因此(特别是与查询数据库 14 次相比)这段代码应该非常快。