对于MongoDB文档中的这个例子,如何使用MongoTemplate编写查询？
db.sales.aggregate(
[
{
$group : {
_id : { month: { $month: "$date" },day: { $dayOfMonth: "$date" },year: { $year: "$date" } },totalPrice: { $sum: { $multiply: [ "$price","$quantity" ] } },averageQuantity: { $avg: "$quantity" },count: { $sum: 1 }
}
}
]
)
或者一般来说,我如何按计算字段进行分组？
解决方法
您可以先用“项目”来做这样的事情,但对我来说,这是一个有点反直觉,需要一个$项目的舞台：
Aggregation agg = newAggregation(
project("quantity")
.andExpression("dayOfMonth(date)").as("day")
.andExpression("month(date)").as("month")
.andExpression("year(date)").as("year")
.andExpression("price * quantity").as("totalAmount"),group(fields().and("day").and("month").and("year"))
.avg("quantity").as("averavgeQuantity")
.sum("totalAmount").as("totalAmount")
.count().as("count")
);
就像我所说的,反直觉,你应该能够宣布所有这些在$group阶段,但帮助者似乎不这样工作.序列化有点有趣(用数组包装日期操作符参数),但它似乎工作.但是,这是两个管道阶段,而不是一个.
这是什么问题？通过分阶段,“项目”部分强制处理流水线中的所有文档,以获得计算的字段,这意味着它在移动到组阶段之前通过所有的阶段.
通过以两种形式运行查询可以清楚地看到处理时间的差异.在单独的项目阶段,我的硬件执行时间要比在“组”操作期间计算所有字段的查询多三倍.
所以现在看来唯一正确的方法是通过自己建立管道对象：
ApplicationContext ctx =
new AnnotationConfigApplicationContext(SpringMongoConfig.class);
MongoOperations mongoOperation = (MongoOperations) ctx.getBean("mongoTemplate");
BasicDBList pipeline = new BasicDBList();
String[] multiplier = { "$price","$quantity" };
pipeline.add(
new BasicDBObject("$group",new BasicDBObject("_id",new BasicDBObject("month",new BasicDBObject("$month","$date"))
.append("day",new BasicDBObject("$dayOfMonth","$date"))
.append("year",new BasicDBObject("$year","$date"))
)
.append("totalPrice",new BasicDBObject(
"$sum",new BasicDBObject(
"$multiply",multiplier
)
))
.append("averageQuantity",new BasicDBObject("$avg","$quantity"))
.append("count",new BasicDBObject("$sum",1))
)
);
BasicDBObject aggregation = new BasicDBObject("aggregate","collection")
.append("pipeline",pipeline);
System.out.println(aggregation);
CommandResult commandResult = mongoOperation.executeCommand(aggregation);
或者如果所有这些似乎都简化了,那么你可以随时使用JSON源并解析.但当然,它必须是有效的JSON：
String json = "[" +
"{ \"$group\": { "+
"\"_id\": { " +
"\"month\": { \"$month\": \"$date\" }," +
"\"day\": { \"$dayOfMonth\":\"$date\" }," +
"\"year\": { \"$year\": \"$date\" } " +
"}," +
"\"totalPrice\": { \"$sum\": { \"$multiply\": [ \"$price\",\"$quantity\" ] } }," +
"\"averageQuantity\": { \"$avg\": \"$quantity\" }," +
"\"count\": { \"$sum\": 1 } " +
"}}" +
"]";
BasicDBList pipeline = (BasicDBList)com.mongodb.util.JSON.parse(json);
