问题描述
直截了当,我该怎么做:
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", "dura***", "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("dura***").as("dura***Total") .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" }, "dura***Total": { "$sum": "$dura***" }, "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("dura***Total",new BasicDBObject("$sum","$dura***")) .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.