Rails / Postgres: "必须出现在GROUP BY子句中或用于聚合函数"

Question

我使用的方法是：

：

  def self.lines_price_report(n)
    Income.group('date(filled_at)').having("date(filled_at) > ?", Date.today - n).sum(:lines_price)
  end

我在Heroku中遇到了这个错误：

PG::Error: ERROR:  column "incomes.filled_at" must appear in the GROUP BY clause 
or be used in an aggregate function

How can I fix this? Thank you.

Executed query:

SELECT SUM("incomes"."lines_price") AS sum_lines_price, date(filled_at)
AS date_filled_at FROM "incomes"
HAVING (date(filled_at) > '2012-12-04')
GROUP BY date(filled_at) ORDER BY filled_at ASC

期望的结果

[["2012-12-04", SUM_FOR_DATE], ["2012-12-05", SUM_FOR_DATE], ...]

- Alex

Answer 1

你的错误在于使用了默认范围中的filled_at进行排序。

你可以使用unscoped来消除默认范围：

Income.unscoped
 .group('date(filled_at)')
 .having("date(filled_at) > ?", Date.today - n)
 .sum(:lines_price)

或者

Income.unscoped
   .group('date(filled_at)')
   .having("date(filled_at) > ?", Date.today - n)
   .sum(:lines_price)
   .order('date(filled_at) ASC')

但我认为更好的做法是使用“where”而不是“having”。

Income.unscoped
  .where("date(filled_at) > TIMESTAMP ?", Date.today - n)
  .group('date(filled_at)')
  .sum(:lines_price)
  .order('date(filled_at) ASC')

SQLFiddle

使用TIMESTAMP时需要注意，因为2012-12-04会变成2012-12-04 00:00:00，所以如果不想把这一天算在结果中，可以使用Date.today - (n - 1)

如果在filled_at列上创建索引

 create index incomes_filled_at on incomes(filled_at);

迁移:

 add_index :incomes, :filled_at

如果您的表中包含大量数据，并且索引将用于筛选，那么查询应该会更快。

因此，只需编写两个查询并测试哪个更快（如果没有填充日期的索引，请先创建一个）。

- sufleR

---

@khustochka 如果你有多个默认范围，我同意使用reorder而不是unsocped。我已经修改了答案。 - sufleR

谢谢，它有效。为什么使用where比使用having更好？我该如何在filled_at上创建索引？ - Alex

@alex请仔细阅读整个答案。结合索引查询应该更快，但取决于两件事情。表中有多少数据以及将通过日期范围过滤多少数据。 - sufleR

@sufleR 这确实是一个很大的表格。而且会有很多过滤操作。 - Alex

@alex 不应该在所有列上创建索引，而是应该在经常进行过滤的列上创建索引 - 大多数情况下，在使用列进行连接（外键列）或者在where子句中使用列时，当然还有主键和唯一约束。 - sufleR

Answer 2

我猜这是因为你在GROUP BY中使用了date(filled_at)，但在ORDER中只使用了filled_at。由于我猜测默认情况下会按照范围排序，所以您需要通过reorder进行覆盖。我建议:

Income.sum(:lines_price).
    group('date(filled_at)').
    having("date(filled_at) > ?", Date.today - n).
    reorder("date(filled_at) ASC")

- khustochka

---

我同意。ORDER BY 正在将“filled_at”（不包括日期部分）作为隐式 SELECT 添加，这必须出现在 GROUP BY 中。 - Matt Gibson

它能运行，但是没用使用select_rows方法却得到了一个二维数组，这很奇怪吗？我在rails控制台输入：Income.group('date(filled_at)').having("date(filled_at) > ?", Date.today - n).reorder("date(filled_at) ASC").sum(:lines_price).to_a 获得了 [["2012-12-05", #<BigDecimal:b6dd2c8,'0.413E4',9(18)>], ...]]。 - Alex

你是对的，这就是 sum 和其他 AR 聚合函数的工作方式（它实际上返回一个哈希表，其中键为分组的列，值为总和，而 to_a 将其转换为数组的数组）。感谢您纠正我，我已经删除了我的错误解释。 - khustochka

Answer 3

当您想在PostgreSQL上使用Group By时，必须在group by中添加select选项。

Income.select('filled_at').group('date(filled_at)').having("date(filled_at) > ?", Date.today - n).sum(:lines_price)

- akbarbin