我在mongodb的聚合操作中遇到了困难。前提是,我必须获取特定广告在一段时间范围内的数据。
例如,如果我查询4月22日至4月24日的广告,则应该从source1获取支出总和、收入、会话、跳出率等信息,从source2获取广告支出总和。
[{ "_id" : ObjectId("560bbd5dfabc614611000e95"),
"spend": 470,
"revenue": 440,
"sessions": 3
},....
]
这是我尝试的查询语句,可以给出正确的数据,但执行时间太长了——仅22k条记录就需要24秒。
db.getCollection('tests').aggregate([{
$match: {
ad_account_id: 40
}
}, {
"$unwind": "$source1"
}, {
"$unwind": "$source2"
}, {
"$group": {
"_id": "$internal_id",
"transactionrevenue": {
"$sum": {
"$cond": [{
"$and": [{
"$gte": [
"$source1.created_at", ISODate("2015-04-22T00:00:00.000Z")
]
}, {
"$lte": [
"$source1.created_at", ISODate("2015-04-25T00:00:00.000Z")
]
}]
}, "$source1.transactionrevenue", 0]
}
},
"sessions": {
"$sum": {
"$cond": [{
"$and": [{
"$gte": [
"$source1.created_at", ISODate("2015-04-22T00:00:00.000Z")
]
}, {
"$lte": [
"$source1.created_at", ISODate("2015-04-25T00:00:00.000Z")
]
}]
}, "$source1.sessions", 0]
}
},
"spend": {
"$sum": {
"$cond": [{
"$and": [{
"$gte": [
"$source2.created_at", ISODate("2015-04-22T00:00:00.000Z")
]
}, {
"$lte": [
"$source2.created_at", ISODate("2015-04-25T00:00:00.000Z")
]
}]
}, "$source2.spend", 0]
}
}
},
}]);
问题在于如何多次解开,如何获取源1中多个事物的总和而不必一遍又一遍地进行聚合?对于22个条目,需要24秒...请建议我应该索引什么(我没有任何索引),并且如果平均4mb的文档大小表明架构有问题吗?
即使聚合通常在mongodb中被认为更快,但是否认为map reduce会更好?
如果您认为文档设计有问题,我全神贯注,因为我们只是在进行迁移。 现在矫正事情比以后要好得多。
下面是一个样本文件。
{
"_id" : ObjectId("560bbd5dfabc614611000e95"),
"internal_id": 1,
"created_at" : ISODate("2015-04-21T00:50:02.593Z"),
"updated_at" : ISODate("2015-09-15T12:20:39.154Z"),
"name" : "LookalikeUSApr21_06h19m",
"ad_account_id" : 40,
"targeting" : {
"age_max" : 44,
"age_min" : 35,
"genders" : [
1
],
"page_types" : [
"desktopfeed"
]
},
"auto_optimization" : false,
"source1" : [
{
"id" : 119560952,
"created_at" : ISODate("2015-04-23T12:35:09.467Z"),
"updated_at" : ISODate("2015-05-19T05:20:58.374Z"),
"transactionrevenue" : 320,
"sessions" : 1,
"bounces" : 1
},
{
"id" : 119560955,
"created_at" : ISODate("2015-05-01T12:35:09.467Z"),
"updated_at" : ISODate("2015-05-19T05:20:58.374Z"),
"transactionrevenue" : 230,
"sessions" : 10,
"bounces" : 1
},
{
"id" : 119560954,
"created_at" : ISODate("2015-04-23T10:35:09.467Z"),
"updated_at" : ISODate("2015-05-19T05:20:58.374Z"),
"transactionrevenue" : 120,
"sessions" : 2,
"bounces" : 1
},
{
"id" : 119560953,
"created_at" : ISODate("2015-04-25T12:35:09.467Z"),
"updated_at" : ISODate("2015-05-19T05:20:58.374Z"),
"transactionrevenue" : 100,
"sessions" : 3,
"bounces" : 2
}
],
"source2" : [
{
"id" : 219560952,
"created_at" : ISODate("2015-04-22T12:35:09.467Z"),
"updated_at" : ISODate("2015-05-19T05:20:58.374Z"),
"spend" : 300
},
{
"id" : 219560955,
"created_at" : ISODate("2015-04-23T12:35:09.467Z"),
"updated_at" : ISODate("2015-05-19T05:20:58.374Z"),
"spend" : 170
},
{
"id" : 219560954,
"created_at" : ISODate("2015-04-25T10:35:09.467Z"),
"updated_at" : ISODate("2015-05-19T05:20:58.374Z"),
"spend" : 450
}
]
}