ruby-on-rails - Postgres、Rails 和选择不在组子句中的列

Question

我有以下查询，我想在其中按 treatment_selections.treatment_id 分组并选择要调用的 treatments.name 列:

@search = Trial.joins(:quality_datum, treatment_selections: :treatment)
.select('DISTINCT ON (treatment_selections.treatment_id) treatment_selections.treatment_id, treatments.name, AVG(quality_data.yield) as yield')
.where("EXTRACT(year from season_year) BETWEEN #{params[:start_year]} AND #{params[:end_year]}")

我得到了可怕的错误:

PG::GroupingError: ERROR:  column "treatment_selections.treatment_id" must appear in the GROUP BY clause or be used in an aggregate function

所以我切换到以下查询:

@search = Trial.joins(:quality_datum, treatment_selections: :treatment)
.select('treatments.name, treatment_selections.treatment_id, treatments.name, AVG(quality_data.yield) as yield')
.where("EXTRACT(year from season_year) BETWEEN #{params[:start_year]} AND #{params[:end_year]}")  
.group('treatment_selections.treatment_id')

我知道这是行不通的，因为在组子句中没有引用 treatments.name。但我认为 top 方法应该有效，因为我没有按任何分组。我知道使用 AVG 和 SUM 等方法不需要在 group 子句中引用，但是不引用任何聚合函数的列呢？

我已经看到嵌套查询是实现我所追求的目标的一种可能方式，但我不确定如何最好地使用上述查询来实现它。希望有人能帮我解决这个问题。

日志

SELECT treatment_selections.treatment_id, treatment.name, AVG(quality_data.yield) as yield FROM "trials" INNER JOIN "treatment_selections" ON "treatment_selections"."trial_id" = "trials"."id" INNER JOIN "quality_data" ON "quality_data"."treatment_selection_id" = "treatment_selections"."id" INNER JOIN "treatment_selections" "treatment_selections_trials" ON "treatment_selections_trials"."trial_id" = "trials"."id" INNER JOIN "treatments" ON "treatments"."id" = "treatment_selections_trials"."treatment_id" WHERE (EXTRACT(year from season_year) BETWEEN 2018 AND 2018) GROUP BY treatment_selections.treatment_id)

Answer 1

选择多个列(没有聚合)并同时使用聚合函数是不可能的，除非您按所选列分组 - 否则无法确定应如何计算平均值(整个数据集与按某些分组) ).你可以这样做 -

@search = Trial.joins(:quality_datum, treatment_selections: :treatment)
.select('treatment_selections.treatment_id, treatments.name, AVG(quality_data.yield) as yield')
.where("EXTRACT(year from season_year) BETWEEN ? AND ?", params[:start_year], params[:end_year])  
.group('treatment_selections.treatment_id, treatments.name')

尽管如果一个 treatments.id 可以与多个 treatment.name 相关联，这可能不适用于您的用例