在mongodb聚合框架中执行案例陈述 [英] Performing case-statement in mongodb aggregation framework

问题描述

我正在评估MongoDB聚合框架如何满足我们的需求，因为我们目前正在SQL Server之上运行.我很难执行特定的查询:

I'm evaluating how well the MongoDB aggregation framework suits our needs as we are currently running on top of SQL Server. I'm having a hard time performing a specific query:

说我有以下伪记录(在sql表中建模为列，在mongodb集合中建模为完整文档)

Say I have the following pseudo records (modeled as columns in a sql table and as a full document in a mongodb collection)

{
   name: 'A',
   timespent: 100,
},
{
   name: 'B',
   timespent: 200,
},
{
   name: 'C',
   timespent: 300,
},
{
   name: 'D',
   timespent: 400,
},
{
   name: 'E',
   timespent: 500,
}

我想将时间段字段分组为范围并计数出现次数，因此我将得到以下伪记录:

I want to group the timespent field in to ranges and count the occurrences so I will get e.g. the following pseudo-records:

results{
   0-250: 2,
   250-450: 2,
   450-650: 1
}

请注意，这些范围(250、450和650)是动态的，用户可能会随时间改变.在SQL中，我们使用以下内容提取结果:

Note that these ranges (250, 450 and 650) are dynamic and will likely be altered over time by the user. In SQL we extracted the results with something like this:

select range, COUNT(*) as total from (
select case when Timespent <= 250 then '0-250'
when Timespent <= 450 then '200-450'
else '450-600' end as range
from TestTable) as r
group by r.range

同样，请注意，此sql是由我们的应用程序动态构造的，以适合任何时候可用的特定范围.

Again, note that this sql is constructed dynamically by our app to fit the specific ranges available at any one time.

我正努力在mongodb聚合框架中找到合适的结构来执行此类查询.我可以通过在管道中插入$ match来查询单个范围的结果(即获取单个范围的结果)，但是我无法理解如何在单个管道查询中提取所有范围及其计数.

I'm struggling to find the appropriate constructs in the mongodb aggregation framework to perform such queries. I can query for the results of a single range by inserting a $match into the pipeline(i.e. getting the result of a single range) but I cannot grok how to extract all the ranges and their counts in a single pipeline query.

推荐答案

与聚合框架是$ cond运算符(请参见手册).可以嵌套$ cond语句以模拟"when-then"和"else"，但是我选择了另一种方法，因为它更易于阅读(并生成，请参见下文):我将使用$ concat运算符进行编写范围字符串，然后用作分组键.

what corresponds to the "case" SQL statement in the aggregation framework, is the $cond operator (see manual). $cond statements can be nested to simulate "when-then" and "else", but I have chosen another approach, because it is easier to read (and to generate, see below): I'll use the $concat operator to write the range string, which then serves as grouping key.

对于给定的集合:

db.xx.find()
{ "_id" : ObjectId("514919fb23700b41723f94dc"), "name" : "A", "timespent" : 100 }
{ "_id" : ObjectId("514919fb23700b41723f94dd"), "name" : "B", "timespent" : 200 }
{ "_id" : ObjectId("514919fb23700b41723f94de"), "name" : "C", "timespent" : 300 }
{ "_id" : ObjectId("514919fb23700b41723f94df"), "name" : "D", "timespent" : 400 }
{ "_id" : ObjectId("514919fb23700b41723f94e0"), "name" : "E", "timespent" : 500 }

汇总(硬编码)如下:

db.xx.aggregate([
  { $project: {
    "_id": 0,
    "range": {
      $concat: [{
        $cond: [ { $lte: ["$timespent", 250] }, "range 0-250", "" ]
      }, {
        $cond: [ { $and: [
          { $gte: ["$timespent", 251] }, 
          { $lt:  ["$timespent", 450] } 
        ] }, "range 251-450", "" ]
      }, {
        $cond: [ { $and: [
          { $gte: ["$timespent", 451] }, 
          { $lt:  ["$timespent", 650] } 
        ] }, "range 450-650", "" ]
      }]
    }
  }},
  { $group: { _id: "$range", count: { $sum: 1 } } },
  { $sort: { "_id": 1 } },
]);

结果是:

{
    "result" : [
        {
            "_id" : "range 0-250",
            "count" : 2
        },
        {
            "_id" : "range 251-450",
            "count" : 2
        },
        {
            "_id" : "range 450-650",
            "count" : 1
        }
    ],
    "ok" : 1
}

为了生成聚合命令，您必须将范围"投影构建为JSON对象(或者可以生成一个字符串，然后使用JSON.parse(string))

In order to generate the aggregate command, you have to build the "range" projection as a JSON object ( or you could generate a string and then use JSON.parse(string) )

生成器如下:

var ranges = [ 0, 250, 450, 650 ];
var rangeProj = {
  "$concat": []
};

for (i = 1; i < ranges.length; i++) {
  rangeProj.$concat.push({
    $cond: {
      if: {
        $and: [{
          $gte: [ "$timespent", ranges[i-1] ]
        }, {
          $lt: [ "$timespent", ranges[i] ]
        }]
      },
      then: "range " + ranges[i-1] + "-" + ranges[i],
      else: ""
    }
  })
}

db.xx.aggregate([{
  $project: { "_id": 0, "range": rangeProj }
}, {
  $group: { _id: "$range", count: { $sum: 1 } }
}, {
  $sort: { "_id": 1 }
}]);

将返回与上面相同的结果.

which will return the same result as above.

这篇关于在mongodb聚合框架中执行案例陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！