mongodb:查询特定列的总和大于或等于 C 的前几行 [英] mongodb: query first few rows where sum of specific column is greater or equal than C

问题描述

假设我们有一个包含 2 列的 mongodb 集合:id, c

Suppose we have a mongodb collection with 2 columns: id, c

1,2
2,6
3,1
...

现在我想选择 c 列的总和大于或等于 C

Now I would like to select first few rows where sum of column c is greater or equal than C

在上述情况下，如果 C=1，则返回前 1 行.如果 C=8，则返回前 2 行.如果 C=9，则返回前 3 行.

In the above case, if C=1, return first 1 row. if C=8, return first 2 rows. if C=9, return first 3 rows.

推荐答案

查询

可以使用聚合框架来完成.考虑下一个聚合管道

It could be done using aggregation framework. Consider the next aggregation pipeline

db.collectionName.aggregate([
  {
    $group: 
      { 
        "_id": null, 
        "ds": { $push: "$$ROOT" }, 
        "cs": { $push: "$c" } 
      } 
  }, /* (1) */
  { $unwind: "$ds" }, /* (2) */
  { 
    $project: 
      { 
        "_id": "$ds._id", 
        "c": "$ds.c", 
        "cs": { $slice: [ "$cs", "$ds._id" ] } 
      } 
  }, /* (3):  */
  { $unwind: "$cs" },  /* (4) */
  { 
    $group: 
      { 
        "_id": "$_id", 
        "c": { $first: "$c" }, 
        "csum": { $sum: "$cs" } 
      } 
  }, /* (5) */
  { 
    $group: 
      { 
        "_id": null, 
        "ds": { $push: "$$ROOT" }, 
        "gteC": 
          { 
            $push: 
              { 
                $cond: 
                  { 
                    if: { "$gte": [ "$csum", SET_DESIRED_VALUE_FOR_C_HERE ] }, 
                    then: "$$ROOT", 
                    else: { } 
                  } 
              } 

          } 
      } 
  }, /* (6) */
  { 
    $project: 
      { 
        "_id": 0,
        "docs": 
          { 
            $filter: 
              { 
                input: "$ds", 
                "as": "doc", 
                cond: { $lte: [ "$$doc.csum", { $min: "$gteC.csum" } ] }
              }
          }
      }
  }, /* (7) */
  { $unwind: "$docs" }, /* (8) */ 
  { $project: { "_id": "$docs._id", "c": "$docs.c" } } /* (9) */
]);

结果

C = 1 =>{ "_id": 1, "c": 2 }

C = 8 =>[ { "_id": 2, "c": 6 }, { "_id": 1, "c": 2 } ]

C = 9 =>[ { "_id": 3, "c": 1 }, { "_id": 2, "c": 6 }, { "_id": 1, "c": 2}]

C = 10 =>

说明

其背后的基本思想是为集合中的每个文档构建helper 数组(阶段 1-3)

The basic idea behind it is to construct helper array for each document in the collection (stages 1-3)

{ "_id" : 1, "c" : 2 } -> cs = [ 2 ]
{ "_id" : 2, "c" : 6 } -> cs = [ 2, 6 ]
{ "_id" : 3, "c" : 1 } -> cs = [ 2, 6, 1 ]

使用 $slice 数组聚合运算符，然后将其替换为它包含的所有元素的总和(阶段 4-5)

using $slice array aggregation operator and then replace it with sum of all elements it contains (stages 4-5)

{ "_id" : 1, "c" : 2 } -> csum = 2
{ "_id" : 2, "c" : 6 } -> csum = 8
{ "_id" : 3, "c" : 1 } -> csum = 9

使用 $unwind 阶段和 $sum 组累加器运算符.

然后用 csum >= C (stage 6) 构造另一个文档辅助数组

Then construct another helper array of documents with csum >= C (stage 6)

/* Ex. (C = 8) */
gteC = [ { "_id" : 3, "c" : 1, "csum" : 9 }, { "_id" : 2, "c" : 6, "csum" : 8 } ]

最后一步是检索所有带有 csum <= Min { gteC.csum } 的文档.这是使用 $filter<完成的/strong> 数组聚合运算符(阶段 7).

The last step is to retrieve all documents with csum <= Min { gteC.csum }. This is done using $filter array aggregation operator (stage 7).

但是，我不确定确定这是实现您想要的最有效的聚合管道(将感谢任何改进建议).

However, I am not sure this is the most efficient aggregation pipeline (will be grateful for any improvement suggestions) to achieve what you want.

PS 在测试查询之前不要忘记更改集合的名称并替换 SET_DESIRED_VALUE_FOR_C_HERE.

PS Before testing the query don't forget to change the name of collection and replace SET_DESIRED_VALUE_FOR_C_HERE.

这篇关于mongodb:查询特定列的总和大于或等于 C 的前几行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！