使用 Spring Data 按提取的日期、月份和年份聚合项目组

2022-01-20 00:00:00 mongodb mongodb-query aggregation-framework java spring-mongo

直截了当，我该怎么做:

To be direct, how do i do this:

group._id = { 
    year: { $year : [{ $subtract: [ "$timestamp", 25200000 ]}] }, 
    month: { $month : [{ $subtract: [ "$timestamp", 25200000 ]}] }, 
    day: { $dayOfMonth : [{ $subtract: [ "$timestamp", 25200000 ]}] }
};

用弹簧数据

我已经尝试过这个和其他一些形式，但没有成功

I tried this already and some other forms and were not successfull

Aggregation aggregation = Aggregation.newAggregation(
                Aggregation.match(c),
                Aggregation.project("programa", "custo", "duracao", "dataHora")
                    .andExpression("dataHora").minus(25200000).extractDayOfMonth().as("dia")
                    .andExpression("dataHora").minus(25200000).extractMonth().as("mes")
                    .andExpression("dataHora").minus(25200000).extractYear().as("ano"),
                Aggregation.group("programa", "ano", "mes", "dia")
                    .count().as("count")
                    .sum("custo").as("valorTotal")
                    .sum("duracao").as("duracaoTotal")
                    .first("dataHora").as("dataHora"),
                Aggregation.sort(Direction.ASC, "dataHora")
        );

我需要在 mongodb 中按日、月和年分组，否则我需要在代码中转换所有这些分组数据.

I need to group by day, month and year in mongodb, or else i will need to convert all this grouped data in code.

提前致谢

推荐答案

在单个 $project 阶段，实际上您可能已经将其编写为单独的 $project 因为您发现无法直接在 $group _id 目前也是.

You are running into a limitation of spring mongo in what you can do for field calculations in a single $project stage, and indeed you are likely already writing as a separate $project since you discover that you cannot project custom named fields directly in a $group _id at present either.

因此，最好将所有这些都保存在 $group 中，并使用不同的方法将调整后的日期四舍五入为当地时间.

So you would be better off keeping this all in the $group, as well as using a different method for rounding your adjusted dates to local time.

因此，编写 $group 的更好方法是:

The better way to write your $group would therefore be:

{ "$group": {
  "_id": {
    "programa": "$programa",
    "dataHora": {
      "$add": [
        { "$subtract": [
          { "$subtract": [{ "$subtract": ["$dataHora", new Date(0)] }, 25200000 ] },
          { "$mod": [
            { "$subtract": [{ "$subtract": ["$dataHora", new Date(0)] }, 25200000 ] },
            1000 * 60 * 60 * 24
          ]}
        ]},
        new Date(0)
      ]
    }
  },
  "count": { "$sum": 1 },
  "valorTotal": { "$sum": "$custo" },
  "duracaoTotal": { "$sum": "$duracao" },
  "dataHora": { "$first": "$dataHora" }
}}

当然，要在 spring-mongo 中使用这种结构，您需要自定义实现聚合阶段操作，该操作可以采用定义的 DBObject:

Of course to use this sort of structure with spring-mongo you need a custom implementation of the aggregation stage operation that can take a defined DBObject:

public class CustomGroupOperation implements AggregationOperation {
    private DBObject operation;

    public CustomGroupOperation (DBObject operation) {
        this.operation = operation;
    }

    @Override
    public DBObject toDBObject(AggregationOperationContext context) {
        return context.getMappedObject(operation);
    }
}

然后在这样的上下文中使用它:

Which you then use in context like this:

    Aggregation aggregation = Aggregation.newAggregation(
            Aggregation.match(c),
            new CustomGroupOperation(
                new BasicDBObject("$group",
                    new BasicDBObject("_id",
                        new BasicDBObject("programa","$programa")
                            .append("dataHora",
                                new BasicDBObject("$add",Arrays.asList(
                                    new BasicDBObject("$subtract",Arrays.asList(
                                        new BasicDBObject("$subtract",Arrays.asList(
                                            new BasicDBObject("$subtract",Arrays.asList(
                                                "$dataHora", new Date(0)
                                            )),
                                            25200000
                                        )),
                                        new BasicDBObject("$mod",Arrays.asList(
                                            new BasicDBObject("$subtract",Arrays.asList(
                                                new BasicDBObject("$subtract",Arrays.asList(
                                                    "$dataHora", new Date(0)
                                                )),
                                                25200000
                                            )),
                                            1000 * 60 * 60 * 24
                                        ))
                                    )),
                                    new Date(0)
                                ))
                            )
                    )
                    .append("count",new BasicDBObject("$sum",1))
                    .append("valorTotal",new BasicDBObject("$sum","$custo"))
                    .append("duracaoTotal",new BasicDBObject("$sum","$duracao"))
                    .append("dataHora",new BasicDBObject("$first","$dataHora"))
                )
            ),
            Aggregation.sort(Direction.ASC,"_id.dataHora")
    );

由于自定义类从内置辅助方法使用的同一基本类中抽象出来，因此可以与它们一起使用，如图所示.

Since the custom class abstracts from the same basic class used by the built in helper methods, it can be used alongside them as shown.

这里使用日期数学的基本过程是当你 $subtract 一个 BSON Date 对象来自另一个然后结果是毫秒的差异，在这种情况下，从仅提取毫秒值的纪元日期( Date(0) ).这使您可以通过模数( $mod ) 从一天中的毫秒数.

How the basic process with the date math works here is that when you $subtract one BSON Date object from another then the result is the milliseconds of difference, and in this case from the epoch date ( Date(0) ) which just extracts the milliseconds value. This allows you to do the math to round to the current date value by the modulo ( $mod ) from the number of milliseconds in one day.

就像你最初尝试的那样，当你 $add 将毫秒值传递给 BSON 日期对象，返回的值再次是 BSON 日期.因此，添加到表示 epoch 的对象会返回一个新的 Date 对象，但会四舍五入为当前日期.

Much as you originally tried, when you then $add that millisecond value to a BSON Date object the returned value is again a BSON Date. So adding to an object representing epoch returns a new Date Object, but rounded to the current date.

这通常比通过 日期聚合运算符，而且代码也更短一些，尤其是当你在这里做的调整 UTC 时间时.

This is usually a lot more useful than extracting parts via date aggregation operators, and also works out to be a bit shorter to code, especially when adjusting the time from UTC as you are doing here.

虽然这里 $group 的构造比 spring mongo 的帮助函数要避免的要简洁一些，但它最终比运行单独的 $project 阶段来转换您真正只需要在 $group 阶段中的字段值.

Though the contruction of the $group here is a bit more terse than the helper functions of spring mongo are trying to avoid, it is a lot more efficient in the end than running a separate $project stage to transform the field values that you really only want in the $group stage anyway.

相关文章