使用 Sequelize 列中分组数据的平均值 [英] Average of grouped data in column using Sequelize

问题描述

我正在尝试查找具有相同VenueId"的所有数据的评分"列的平均值.我让它使用原始代码，但需要在 Sequelize 中对其进行调整.工作原始代码是

I'm trying to find the average of a 'ratings' column for all the data that has the same 'VenueId'. I have it working with raw code but need to adapt it in Sequelize. The working raw code is

SELECT venueId, AVG(rating) AS average FROM reviews GROUP BY VenueId

我试过的代码是

Review.findAll({
    order: [[Sequelize.fn('AVG', Sequelize.col('rating'))]]
}).then(function(Venues) {})

我收到一个错误:

执行(默认):SELECT id、review、rating、createdAt、updatedAt、VenueId 来自评论作为评论订单按最大(评级)；未处理拒绝 SequelizeDatabaseError: UNKNOWN_CODE_PLEASE_REPORT:ORDER BY 的表达式#1 包含聚合函数并适用于非聚合查询的结果.

Executing (default): SELECT id, review, rating, createdAt, updatedAt, VenueId FROM Reviews AS Review ORDER BY max(rating); Unhandled rejection SequelizeDatabaseError: UNKNOWN_CODE_PLEASE_REPORT: Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query.

有什么想法吗?

推荐答案

这可能需要一些调整，因为我不知道你的模型，但我建议你尝试以下方法:

This might need some tweaking as I don't know your models, but I suggest you try the following:

Review.findAll({
   attributes: ['venueId', [models.sequelize.fn('AVG', models.sequelize.col('venue_id')), 'venueIdCount']],
   group: 'venue_id'
   order: [[models.sequelize.fn('AVG', models.sequelize.col('venue_id')), 'DESC']]
}).then(function() {
   //Do something
}})

需要注意的重要一点:当我使用venueId"时，我指的是模型上的属性名称，而当我使用venue_id"时，我指的是数据库表中列的名称.它们可能相同或不同，因此请随意调整.

Important to notice: When I use "venueId" I mean the name of the attribute on your model, and when I use "venue_id" I mean the name of the column in your database table. They might be the same or different, so feel free to tweak it.

这篇关于使用 Sequelize 列中分组数据的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！