mongodb 日期分组聚合_mongodb 聚合查询-按日期分组统计 和对应mongoTemplate写法

1.$match 定义查询条件

db.collection.aggregate(

{

"$match":{

"merchno" : "1561985080187",

"createTime": {

$lte:ISODate("2019-07-03T15:59:59.000Z"),

$gte: ISODate("2019-07-02T16:00:00.000Z")

}

}

}

)

对应的mongoTemplate 写法

Aggregation aggregation = Aggregation.newAggregation(

Aggregation.match(

Criteria.where("merchno").is("1561985080187")

.and("createTime").lte(sdf.parse("20190703 23:59:59")).gte(sdf.parse("20190703 00:00:00"))

)

);

AggregationResults outputTypeCount = mongoTemplate.aggregate(aggregation, QuickTransEntity.class, Map.class);

2. 用$project筛选字段

$project用于筛选显示的字段时 1 显示 ,0 显示 , 0和1 不可以同时使用

db.collection.aggregate(

{

"$project": {

"merchno": 1,

"amount": 1,

"createTime": 1,

"date": {

$dateToString: {

format:"%Y-%m-%d",

date: {

"$add": ["$createTime", 8*60*60*1000]

}

}

},

"date2": {

$dateToString: {

format: "%Y-%m-%d",

date: "$createTime",

timezone: 'Asia/Shanghai'

}

}

}

}

)

由于mongodb时间是UTC的时间，比中国时区少8个小时,所以需要将时间处理为 中国时区

date和date2为两种处理方法,取其中一种即可

date: 将创建时间处理为日期格式,后续用于分组,将时间加上8小时 转为中国时区的时间

date2: 设置时区为'Asia/Shanghai'

对应的mongoTemplate 写法

Aggregation aggregation = Aggregation.newAggregation(

Aggregation.match(

Criteria.where("merchno").is("1561985080187")

.and("createTime").lte(sdf.parse("20190703 23:59:59")).gte(sdf.parse("20190702 00:00:00"))

),

Aggregation.project("merchno", "amount", "createTime")

.andExpression("{ $dateToString: {format: '%Y-%m-%d', date: {$add: {'$createTime', 28800000}}}}").as("date1")

.andExpression("{ $dateToString: {format: '%Y-%m-%d', date: {$add: {'$createTime', 8*60*60*1000}}}}").as("date2")

.andExpression("{ $dateToString: {format: '%Y-%m-%d', date: '$createTime', timezone: 'Asia/Shanghai' }}").as("date3")

.andExpression("{ $dateToString: {format: '%Y-%m-%d', date: '$createTime', timezone: '+08:00' }}").as("date4")

);

AggregationResults outputTypeCount = mongoTemplate.aggregate(aggregation, QuickTransEntity.class, Map.class);

date1,date2,date3,date4 结果一样

在andExpression中,存在数组时,需要用{ } 表示

如 { $add: ['$createTime', 8 * 60 * 60 * 1000] } 需要改为 {$add: {'$createTime', 8 * 60 * 60 * 1000} }

3.$group 分组统计

3.1 不分组统计所有数据

db.collection.aggregate(

{"$match":{"merchno" : "1561985080187"}}

,{"$group":{"_id": null, "num":{"$sum":1}, "amount":{"$sum":"$amount"}}}

)

3.2 按日期分组统计

db.collection.aggregate(

{

"$match":{

"merchno" : "1561985080187",

"createTime": {

$lte:ISODate("2019-07-03T15:59:59.000Z"),

$gte: ISODate("2019-07-02T16:00:00.000Z")

}

}

},

{

"$project": {

"merchno": 1,

"amount": 1,

"date": {

$dateToString: {

format: "%Y-%m-%d",

date: "$createTime",

timezone: 'Asia/Shanghai'

}

}

}

},

{

"$group": {

"_id":"$date",

"num": {"$sum": 1},

"totalAmount": {"$sum": "$amount"}

}

}

)

$group 中的_id为分组条件, 如条件有多个则

{"_id": {"merchno": "$merchno", "date": "$date"}}

按日期分组统计 对应的mongoTemplate 写法

Aggregation aggregation = Aggregation.newAggregation(

Aggregation.match(

Criteria.where("merchno").is("1561985080187")

.and("createTime").lte(sdf.parse("20190703 23:59:59")).gte(sdf.parse("20190702 00:00:00"))

),

Aggregation.project("merchno", "amount")

.andExpression("{ $dateToString:{format:'%Y-%m-%d',date: '$createTime',timezone: 'Asia/Shanghai' }}").as("date"),

Aggregation.group("date").sum("amount").as("totalAmount").count().as("num")

);

AggregationResults outputTypeCount = mongoTemplate.aggregate(aggregation, QuickTransEntity.class, Map.class);