MongoDB聚合最小/最大年份

bz4sfanl  于 2022-12-29  发布在  Go
关注(0)|答案(1)|浏览(100)

我遇到了这个问题。考虑到我们有很多包含电影和发行年份的文档。下面是这些文档的一个示例:

{
    "_id" : ObjectId("63a994974ac549c5ea982d2b"),
    "title" : "Destroyer",
    "year" : 2018
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d2a"),
    "title" : "Aquaman",
    "year" : 2014
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d29"),
    "title" : "On the Basis of Sex",
    "year" : 1998   
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d28"),
    "title" : "Holmes and Watson",
    "year" : 1940
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d27"),
    "title" : "Conundrum: Secrets Among Friends",
    "year" : 1957
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d26"),
    "title" : "Welcome to Marwen",
    "year" : 2000
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d25"),
    "title" : "Mary Poppins Returns",
    "year" : 1997
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d24"),
    "title" : "Bumblebee",
    "year" : 2018
},

因此,我想统计在文件中登记的最高年份与20年前(即2018年和1998年)之间的电影。
我尝试的是如下:

var query1 = {"$addFields": {maxium: {$max: "$year"}, minimum : {$subtract: [{$max: "$year"}, 20]}}}

var filter = {"year": {"$lte": maximum, "$gte": minimum}}

var logic = {$match: {$and: [filter]}}

var query1 = {$group: {"_id": null, "count": {$sum:1}}}

var stage = [logic, query1]

db.movies.aggregate(stage)

但我无法得到正确的输出。我得到的是以下输出:

{
    "message" : "maximum is not defined",
    "stack" : "script:3:32"
}

我不知道我做错了什么。对于前面的例子,这将是正确的输出:

"_id": null,
     "count": 4

我怎么能解决这个问题呢?我怎么能用$max和$subtract来计算两年之间的所有电影呢?
最好的问候!谢谢!!!

4ioopgfo

4ioopgfo1#

为了获得最大year,您需要对文档进行分组或使用$setWindowFields来比较它们。一个选项是使用$setWindowFields,它允许您避免将所有文档分组为一个大文档,因为文档有大小限制:

db.collection.aggregate([
  {$setWindowFields: {
      sortBy: {year: -1},
      output: {
        maxYear: {
          $max: "$year",
          window: {documents: ["unbounded", "current"]}
        }
      }
  }},
  {$match: {$expr: {$lt: [{$subtract: ["$maxYear", "$year"]}, 20]}}},
  {$group: {_id: 0, count: {$sum: 1}}}
])

了解它在playground example上的工作原理

相关问题