Mongoose/Mongodb Aggregate - 对多个字段进行分组和平均 [英] Mongoose/Mongodb Aggregate - group and average multiple fields

问题描述

我有一个包含 2 个字段的 Post 模型:日期和评级.我将如何获得每个日期的平均综合评分?所以首先按日期分组，然后平均该日期所有帖子的评分.我需要在 mongoose 中执行此操作，但他们的文档很难理解.

const PostSchema = new Schema({日期: {类型:字符串，default: getToday()//这只是一个新的Date()格式},评分: {类型:数字，要求:真实}},)

这给了我所有日期的平均值，但我不知道如何按日期过滤它:

Post.aggregate([{ $group: { _id: null, avgRating: { $avg: '$rating' }}}]).then(功能(资源){console.log(res[0][avgRating"]);})

解决方案

这对我有用:

 Post.aggregate([{ $group: { _id: "$date", avgRating: { $avg: '$rating' }}}]).然后(功能(资源){控制台日志(res)；})

输出:

<预><代码>[{_id:'2021 年 8 月 18 日'，平均评分:3.0212234706616727}，{_id:'2021 年 8 月 19 日'，平均评分:2.9680319680319682}，{_id:'2021 年 8 月 20 日'，平均评分:3.023976023976024}，{_id:'2021 年 8 月 17 日'，平均评分:2.9600665557404326}，{_id:'2021 年 8 月 21 日'，平均评分:3.072661217075386}]

但是如果我能根据其他因素以某种方式过滤它，那就太好了.例如，每个帖子都有一个作者(参考 User 模型).我将如何根据作者的国家/地区名称或性别进行过滤?

用户模型:

const userSchema = new Schema({电子邮件: {类型:字符串，要求:真实，独特:真实},生日: {类型:日期，要求:真实，},性别:{类型:字符串，要求:真实},国家:{姓名: {类型:字符串，要求:真实},旗帜: {类型:字符串，//默认值:/images/flags/US.png";}},头像: AvatarSchema,显示名称:字符串，生物:字符串，封面颜色:{类型:字符串，默认值:#343a40"},帖子: [{类型:Schema.Types.ObjectId，参考:发布"}],注释: [{类型:Schema.Types.ObjectId，参考:评论"}],今日发布:{类型:布尔型，默认值:假},今日邮报:{类型:字符串}})

像这样

 Post.aggregate([{$match: {日期":今天}}，{$group: {_id: {"country": "$author.country.name"}, avgRating: {$avg: "$rating"}}}]).那么(功能(资源){控制台日志(res)})

I have a Post model with 2 fields : date and rating. How would I go about getting an average aggregate rating for each date? So group by date first and then average the rating across all posts for that date. I need to do this within mongoose but their docs are so difficult to understand.

const PostSchema = new Schema({
    date: {
      type: String,
      default: getToday() //this is just a new Date() formatted
    },
    rating: {
      type: Number,
      required: true
    }
  }, 
) 

This gives me the average across all dates but I can't figure out how to filter it by date:

Post.aggregate([
    { $group: { _id: null, avgRating: { $avg: '$rating' }}}
  ])
  .then(function (res) {
    console.log(res[0]["avgRating"]); 
  })

解决方案

This worked for me:

  Post.aggregate([
    { $group: { _id: "$date", avgRating: { $avg: '$rating' }}}
  ]).
  then(function (res) {
    console.log(res); 
  })

Output:

[
  { _id: 'Aug 18, 2021', avgRating: 3.0212234706616727 },
  { _id: 'Aug 19, 2021', avgRating: 2.9680319680319682 },
  { _id: 'Aug 20, 2021', avgRating: 3.023976023976024 },
  { _id: 'Aug 17, 2021', avgRating: 2.9600665557404326 },
  { _id: 'Aug 21, 2021', avgRating: 3.072661217075386 }
]

BUT it would be great if I could somehow filter this based on other factors. For example, each post has an author (reference to User model). How would I go about filtering based on the author's country.name or gender?

User model:

const userSchema = new Schema({
email: {
    type: String,
    required: true,
    unique: true
},
birthday: {
    type: Date,
    required: true,
},
gender:{
    type: String,
    required: true
},
country:{
    name: {
        type: String,
        required: true
    },
    flag: {
        type: String,
        // default: "/images/flags/US.png"
    }
},
avatar: AvatarSchema,
displayName: String,
bio: String,
coverColor: {
    type: String,
    default: "#343a40"
},
posts: [ 
    {
    type: Schema.Types.ObjectId,
    ref: "Post" 
    }
],
comments: [
    {
        type: Schema.Types.ObjectId,
        ref: "Comment"
    }
],
postedToday: {
    type: Boolean,
    default: false
},
todaysPost: {
    type: String
}
}) 

Something like this

    Post.aggregate([
    {$match: {"date": today}},
    {$group: {_id: {"country": "$author.country.name"}, avgRating: {$avg: "$rating"}}}
]).then(function(res) {
    console.log(res)
})

这篇关于Mongoose/Mongodb Aggregate - 对多个字段进行分组和平均的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！