MongoDB中计算的分组字段 [英] Calculated group-by fields in MongoDB

问题描述

对于 MongoDB 文档中的这个示例，如何使用 MongoTemplate 编写查询?

For this example from the MongoDB documentation, how do I write the query using 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 }
        }
      }
   ]
)

或者一般来说，我如何按计算字段分组?

Or in general, how do I group by a calculated field?

推荐答案

实际上你可以先用 "project" 做这样的事情，但对我来说，要求 $project 有点违反直觉前阶段:

You can actually do something like this with "project" first, but to me it's a little counter-intuitive to require a $project stage before hand:

    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 阶段声明所有这些，但助手似乎并没有这样做大大地.序列化有点有趣(用数组包装日期运算符参数)但它似乎确实有效.但是，这仍然是两个流水线阶段，而不是一个.

Like I said, counter-intuitive as you should just be able to declare all of this under $group stage, but the helpers don't seem to work this way. The serialization comes out a bit funny ( wraps the date operator arguments with arrays ) but it does seem to work. But still, this is two pipeline stages rather than one.

这有什么问题?好吧，通过将阶段分开，项目"部分会强制处理管道中的所有文档以获得计算字段，这意味着它在进入小组阶段之前会通过所有内容.

What is the problem with this? Well by separating the stages the stages the "project" portion forces the processing of all of the documents in the pipeline in order to get the calculated fields, that means it passes through everything before moving on to the group stage.

通过以两种形式运行查询可以清楚地看到处理时间的差异.使用单独的项目阶段，在我的硬件上执行的时间是在组"操作期间计算所有字段的查询的三倍.

The difference in processing time can be clearly seen by running the queries in both forms. With a separate project stage, on my hardware takes three times longer to execute than the query where all fields are calculated during the "group" operation.

因此，目前唯一正确构建它的方法似乎是自己构建管道对象:

So it seems the only present way to construct this properly is by building the pipeline object yourself:

    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:

Or if all of that seems to terse to you, then you can always work with the JSON source and parse that. But of course, it has to be valid 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);

这篇关于MongoDB中计算的分组字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！