MongoDB 聚合查询：按时间范围筛选、计算天数、分组和排序
// 查询条件 >= start and < end var start = new Date('2023-06-1T00:00:00.000+08:00') var end = new Date('2023-06-8T00:00:00.000+08:00') var service_type = 'DISTRIBUTOR_KOREA_SEA'
var front = '$trace_inbound.trace_time' var back = '$trace_delivery_success.trace_time' var day = 3
// 分组计算
db.ceos_order.aggregate([
{
$addFields: {
days: {
$ceil: {
$divide: [
{
$subtract: [back, front]
},
1000 * 60 * 60 * 24 // 将毫秒转换为天数
]
}
}
}
},
{
// where
$match: {
'trace_inbound.trace_time': {
$gte: start,
$lte: end
},
'service_type': service_type
}
},
{
// where
$match: {
'sinoex_no': {
'$regex': 'SE'
},
'days': day
}
},
{
// 排序
$sort: {
_id: 1
}
},
{
$skip: 1
},
{
$limit: 10
},
{
// as
$project: {
_id: 0,
'eawbsyscode': '$eawb_syscode',
'eawbprintcode': '$sinoex_no',
'eawbreference': '$tracking_no',
'fcinbound': '$trace_inbound.trace_time',
'fcoutbound': '$trace_outbound.trace_time',
'ass': '$trace_ass.trace_time',
'adc': '$trace_ata.trace_time',
'roe': '$trace_a_scan.trace_time',
'delivery': '$trace_delivery_success.trace_time',
'eawbservicetype': '$service_type',
// 'route': '中外运-韩国海运',
'eawbsocode': '$so_code',
// 'customername': '深圳市达旦外贸科技有限公司',
// 'destinyname': '韩国',
'eawbpostcode': '$dest_post_code'
}
}
], {
allowDiskUse: true
})
java编码内容：import org.bson.Document; import com.mongodb.MongoClient; import com.mongodb.client.MongoCollection; import com.mongodb.client.MongoDatabase; import com.mongodb.client.model.Filters; import com.mongodb.client.model.Sorts; import java.util.ArrayList; import java.util.Arrays; import java.util.List;
public class Main {
public static void main(String[] args) {
// Connect to MongoDB
MongoClient mongoClient = new MongoClient('localhost', 27017);
MongoDatabase database = mongoClient.getDatabase('your-database-name');
MongoCollection
// Set query parameters
Document start = new Document('$gte', new Date('2023-06-01T00:00:00.000+08:00'));
Document end = new Document('$lt', new Date('2023-06-08T00:00:00.000+08:00'));
String serviceType = 'DISTRIBUTOR_KOREA_SEA';
String front = '$trace_inbound.trace_time';
String back = '$trace_delivery_success.trace_time';
int day = 3;
// Build the aggregation pipeline
List<Document> pipeline = Arrays.asList(
new Document('$addFields', new Document('days', new Document('$ceil',
new Document('$divide', Arrays.asList(
new Document('$subtract', Arrays.asList(back, front)),
1000 * 60 * 60 * 24
)))))),
new Document('$match', new Document('trace_inbound.trace_time', start)
.append('trace_inbound.trace_time', end)
.append('service_type', serviceType)),
new Document('$match', new Document('sinoex_no', new Document('$regex', 'SE'))
.append('days', day)),
new Document('$sort', new Document('_id', 1)),
new Document('$skip', 1),
new Document('$limit', 10),
new Document('$project', new Document('_id', 0)
.append('eawbsyscode', '$eawb_syscode')
.append('eawbprintcode', '$sinoex_no')
.append('eawbreference', '$tracking_no')
.append('fcinbound', '$trace_inbound.trace_time')
.append('fcoutbound', '$trace_outbound.trace_time')
.append('ass', '$trace_ass.trace_time')
.append('adc', '$trace_ata.trace_time')
.append('roe', '$trace_a_scan.trace_time')
.append('delivery', '$trace_delivery_success.trace_time')
.append('eawbservicetype', '$service_type')
.append('eawbsocode', '$so_code')
.append('eawbpostcode', '$dest_post_code'))
);
// Execute the aggregation pipeline
List<Document> results = new ArrayList<>();
collection.aggregate(pipeline).into(results);
// Print the results
for (Document result : results) {
System.out.println(result);
}
// Disconnect from MongoDB
mongoClient.close();
}
}
原文地址: https://www.cveoy.top/t/topic/pKBE 著作权归作者所有。请勿转载和采集!