在 mongodb 聚合框架中执行 case-statement [英] 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 }
聚合(硬编码)如下所示:
the aggregate (hardcoded) looks like this:
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 聚合框架中执行 case-statement的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!