SQL の集計には LIMIT が効かない

問題提起

いくら LIMIT しても平均値が変わらないので調べたら LIMIT が効いてなかった。

require "active_record"
ActiveRecord::VERSION::STRING  # => "7.1.3.2"
ActiveSupport::LogSubscriber.colorize_logging = false
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Migration.verbose = false
ActiveRecord::Schema.define do
  create_table :items do |t|
    t.integer :score
  end
end

class Item < ActiveRecord::Base
end

としてからレコードを3つ作る。

Item.create!(score: 1)    # => #<Item id: 1, score: 1>
Item.create!(score: 2)    # => #<Item id: 2, score: 2>
Item.create!(score: 100)  # => #<Item id: 3, score: 100>

ここで LIMIT 2 とした場合のスコア平均は 1.5 になるだろう。

Item.order(:id).limit(2).average(:score).to_f  # => 34.33333333333333
# >   Item Average (0.1ms)  SELECT AVG("items"."score") FROM "items" LIMIT ?  [["LIMIT", 2]]

？？？

対策1. 長期的になら副SQLにする

sub_sql = Item.order(:id).limit(2)
Item.where(id: sub_sql).average(:score).to_f  # => 1.5
# >   Item Average (0.1ms)  SELECT AVG("items"."score") FROM "items" WHERE "items"."id" IN (SELECT "items"."id" FROM "items" ORDER BY "items"."id" ASC LIMIT ?)  [["LIMIT", 2]]

それか、すでに [1, 2] を取得済みなら where(id: [1, 2]) となるように渡す。

ちなみに id を明示したからといって LIMIT が効いたりはしない。

Item.where(id: [1, 2]).limit(1).average(:score).to_f  # => 1.5

というか無意味な LIMIT の指定があればエラーを出してほしい(小声)

対策2. 今だけでいいならRDBを使わなくてもいい

scores = Item.order(:id).limit(2).pluck(:score)
# >   Item Pluck (0.1ms)  SELECT "items"."score" FROM "items" ORDER BY "items"."id" ASC LIMIT ?  [["LIMIT", 2]]
scores.sum.fdiv(scores.size)  # => 1.5