我在mongodb中有以下集合:
{
"_id" : ObjectId("558c108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 183,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c108b209c022c947b0056"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 185,
"weekofyear" : 23,
"productcount" : 2,
"count" : 23,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c108b209c022c947b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 182,
"weekofyear" : 24,
"productcount" : 1,
"count" : 24,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "11",
"filtername" : "Sub Category"
}
{
"_id" : ObjectId("558c108b209c022c947b0022"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 186,
"weekofyear" : 26,
"productcount" : 25,
"count" : 25,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "11",
"filtername" : "Sub Category"
}
{
"_id" : ObjectId("558c108b209c022c947b0032"),
"term" : "aero",
"year" : 2015,
"month" : 7,
"day" : 1,
"hour" : 17,
"dayofyear" : 182,
"weekofyear" : 26,
"productcount" : 23,
"count" : 12,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "11",
"filtername" : "Sub Category"
}
{
"_id" : ObjectId("5348c108b09c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 170,
"weekofyear" : 26,
"productcount" : 235,
"count" : 1,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Sub Category"
}
{
"_id" : ObjectId("658c108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 185,
"weekofyear" : 26,
"productcount" : 235,
"count" : 1,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "12",
"filtername" : "price Range"
}
{
"_id" : ObjectId("558c108b209c022c947a0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 181,
"weekofyear" : 26,
"productcount" : 235,
"count" : 1,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "12",
"filtername" : "price Range"
}
{
"_id" : ObjectId("213c108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 184,
"weekofyear" : 27,
"productcount" : 0,
"count" : 27,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "12",
"filtername" : "price Range"
}
{
"_id" : ObjectId("558c108b209c082c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 183,
"weekofyear" : 26,
"productcount" : 0,
"count" : 13,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "12",
"filtername" : "price Range"
}
{
"_id" : ObjectId("5589108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 184,
"weekofyear" : 21,
"productcount" : 0,
"count" : 21,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "12",
"filtername" : "price Range"
}
{
"_id" : ObjectId("558a108b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 185,
"weekofyear" : 26,
"productcount" : 0,
"count" : 16,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "13",
"filtername" : "price Range"
}
{
"_id" : ObjectId("558c118b209c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 170,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "13",
"filtername" : "price Range"
}
{
"_id" : ObjectId("558c108b209c033c947b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 182,
"weekofyear" : 24,
"productcount" : 1,
"count" : 24,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "14",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c108b909c033c947b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 188,
"weekofyear" : 24,
"productcount" : 1,
"count" : 24,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "10",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c108b209c033c937b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 162,
"weekofyear" : 24,
"productcount" : 1,
"count" : 24,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "14",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c101b209c033c937b0026"),
"term" : "aero",
"year" : 2014,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 363,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "10",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c101b299c033c937b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 183,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "16",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c101b199c033c937b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 183,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "10",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c101b199c033c939b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 185,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "18",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c101b199c044c937b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 163,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "11",
"filtername" : "brand"
}
{
"_id" : ObjectId("558c191b199c033c939b0026"),
"term" : "aero",
"year" : 2014,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 178,
"weekofyear" : 24,
"productcount" : 0,
"count" : 24,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "10",
"filtername" : "concern"
}
{
"_id" : ObjectId("558c198b909c033c947b0026"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 188,
"weekofyear" : 24,
"productcount" : 1,
"count" : 24,
"position" : "2","
"productsclickedi"d" : "8156",
"sessionid" : "14"",
"filtername" : "c"oncern"
} "
"
"
{ "
"_id" : ObjectId(""558c118b310c022c947b0055"),
"term" : "aero st"orm tour",
"year" : 2015, "
"month" : 6, "
"day" : 1, "
"hour" : 17, "
"dayofyear" : 188,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "16",
"filtername" : "concern"
}
{
"_id" : ObjectId("539c118b310c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 4,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "18",
"filtername" : "concern"
}
{
"_id" : ObjectId("558c118b310c022c947b1145"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 4,
"weekofyear" : 22,
"productcount" : 0,
"count" : 21,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "18",
"filtername" : "concern"
}
{
"_id" : ObjectId("558c992b310c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 1,
"weekofyear" : 22,
"productcount" : 0,
"count" : 2,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c118b123c022c947b0055"),
"term" : "aero storm tour",
"year" : 2014,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 364,
"weekofyear" : 22,
"productcount" : 0,
"count" : 32,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "17",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c223c310c022c947b0055"),
"term" : "aero storm tour",
"year" : 2014,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 365,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "14",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c220c310c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 196,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "autocomplete",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c108b209c311c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 195,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "10",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c999b209c311c947b0055"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 195,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "8156",
"sessionid" : "19",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c999c310c022c947b0055"),
"term" : "aero storm tour",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 196,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "2",
"productsclickedid" : "7156",
"sessionid" : "14"
}
{
"_id" : ObjectId("558c220c310c022c749b0055"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 194,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "15",
"filtername" : "Product Category"
}
{
"_id" : ObjectId("558c730c310c022c749b0055"),
"term" : "aero",
"year" : 2015,
"month" : 6,
"day" : 1,
"hour" : 17,
"dayofyear" : 195,
"weekofyear" : 22,
"productcount" : 0,
"count" : 22,
"position" : "autocomplete",
"productsclickedid" : "8156",
"sessionid" : "10",
"filtername" : "Product Category"
}
我试图在指定的日期范围内找到项和filtername的计数。
My query:
db.filterclick.aggregate(
{$match:
{$or :[
{ $and :[{dayofyear:{ $gte : 1, $lte : 4 }},{year : 2015}]},
{ $and :[{dayofyear:{ $gte : 363, $lte : 365 }},{year : 2014}]}
]
}
},
{$group:{
_id: "$term",
_id: "$filtername",
term :{$first :"$term"},
filtername :{$first : "$filtername"},
totalcount : {
$sum : "$count"
}
}
},
{
$sort : {totalcount : -1}
},{
$project: {
"term" : 1,
"filtername" : 1,
"totalcount" : 1,
"_id" : 0
}
}
)
:
{
"result" : [
{
"term" : "aero storm tour",
"filtername" : "Product Category",
"totalcount" : 56
},
{
"term" : "aero storm tour",
"filtername" : "concern",
"totalcount" : 43
},
{
"term" : "aero",
"filtername" : "brand",
"totalcount" : 24
}
],
"ok" : 1.0000000000000000
}
期望输出:
{
"result" : [
{
"term" : "aero storm tour",
"filtername" : "Product Category",
"totalcount" : 4
},
{
"term" : "aero storm tour",
"filtername" : "concern",
"totalcount" : 44
},
{
"term" : "aero",
"filtername" : "brand",
"totalcount" : 24
},
{
"term" : "aero",
"filtername" : "concern",
"totalcount" : 21
}
],
"ok" : 1.0000000000000000
}
查询应该根据term和filtername的组合来聚合和计算计数，但是它没有。
我知道我的查询没有创建filtername和term的组合。怎么做呢?
有什么建议吗
更正您的 $group 。_id需要是一个组合键。你是怎么写的?你是"覆盖"_id的值，所以它只对$filtername进行分组:
{ "$group": {
"_id": { "term": "$term", "filtername": "$filtername" },
"term": { "$first":"$term" },
"filtername": { "$first": "$filtername" },
"totalcount" : { "$sum": "$count" }
}}
所以_id是"分组键"，像BSON文档中的所有键一样"只能有一个"。这就是为什么在您的分组中，总数不会作为这两个字段的"组合"出现。