聚合项目组通过Spring Data提取日,月和年 [英] Aggregation Project Group By extracted day, month and year with Spring Data
问题描述
要直接,我该怎么做:
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.
写你的更好方法r $ 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对象来自另一个,那么结果是毫秒的差异,在这种情况下,从仅提取毫秒值的纪元日期(日期(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 Date对象的毫秒值,返回的值又是BSON日期。因此,添加到表示纪元的对象会返回一个新的日期对象,但会四舍五入到当前日期。
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的helper函数试图避免的更简洁,最终运行一个单独的 $ 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.
这篇关于聚合项目组通过Spring Data提取日,月和年的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!