MongoDB 聚合查询：时间范围筛选和分组计算

// 查询条件 >= start and < end\nvar start = new Date("2023-06-1T00:00:00.000+08:00")\nvar end = new Date("2023-06-8T00:00:00.000+08:00")\nvar service_type = "DISTRIBUTOR_KOREA_SEA"\n\nvar front = "$trace_inbound.trace_time"\nvar back = "$trace_delivery_success.trace_time"\nvar day = 3\n\n// 分组计算 \ndb.ceos_order.aggregate([\n {\n $addFields: {\n days: {\n $ceil: {\n $divide: [\n {\n $subtract: [back, front]\n },\n 1000 * 60 * 60 * 24 // 将毫秒转换为天数\n ]\n }\n }\n }\n },\n {\n // where\n $match: {\n "trace_inbound.trace_time": {\n $gte: start,\n $lte: end\n },\n "service_type": service_type\n }\n },\n {\n // where\n $match: {\n 'sinoex_no': {\n '$regex': 'SE'\n },\n 'days': day\n }\n },{\n // 排序\n $sort: {\n _id: 1\n }\n },\n {$skip: 1},\n {$limit:10},\n {\n // as \n $project: {\n _id: 0,\n "eawbsyscode": "$eawb_syscode",\n "eawbprintcode": "$sinoex_no",\n "eawbreference": "$tracking_no",\n "fcinbound": "$trace_inbound.trace_time",\n "fcoutbound": "$trace_outbound.trace_time",\n "ass": "$trace_ass.trace_time",\n "adc": "$trace_ata.trace_time",\n "roe": "$trace_a_scan.trace_time",\n "delivery": "$trace_delivery_success.trace_time",\n "eawbservicetype": "$service_type",\n// "route": "中外运-韩国海运",\n "eawbsocode": "$so_code",\n// "customername": "深圳市达旦外贸科技有限公司",\n// "destinyname": "韩国",\n "eawbpostcode": "$dest_post_code"\n }\n }\n], {\n allowDiskUse: true\n})\n\n\n\n用java开发内容：在Java中执行这段聚合查询，可以使用MongoDB的Java驱动程序。首先，确保已经导入MongoDB的Java驱动程序依赖项。然后，可以使用以下代码执行聚合查询：\n\njava\nimport com.mongodb.MongoClient;\nimport com.mongodb.client.MongoCollection;\nimport com.mongodb.client.MongoDatabase;\nimport org.bson.Document;\n\nimport java.util.Arrays;\n\npublic class MongoAggregationExample {\n\n public static void main(String[] args) {\n // 连接到MongoDB数据库\n MongoClient mongoClient = new MongoClient("localhost", 27017);\n MongoDatabase database = mongoClient.getDatabase("your_database_name");\n\n // 获取集合\n MongoCollection<Document> collection = database.getCollection("ceos_order");\n\n // 构建聚合管道\n Document match1 = new Document("$match", new Document("trace_inbound.trace_time", new Document("$gte", start).append("$lte", end)).append("service_type", service_type));\n Document match2 = new Document("$match", new Document("sinoex_no", new Document("$regex", "SE")).append("days", day));\n Document sort = new Document("$sort", new Document("_id", 1));\n Document skip = new Document("$skip", 1);\n Document limit = new Document("$limit", 10);\n Document project = new Document("$project", new Document("_id", 0)\n .append("eawbsyscode", "$eawb_syscode")\n .append("eawbprintcode", "$sinoex_no")\n .append("eawbreference", "$tracking_no")\n .append("fcinbound", "$trace_inbound.trace_time")\n .append("fcoutbound", "$trace_outbound.trace_time")\n .append("ass", "$trace_ass.trace_time")\n .append("adc", "$trace_ata.trace_time")\n .append("roe", "$trace_a_scan.trace_time")\n .append("delivery", "$trace_delivery_success.trace_time")\n .append("eawbservicetype", "$service_type")\n .append("eawbsocode", "$so_code")\n .append("eawbpostcode", "$dest_post_code"));\n\n // 执行聚合查询\n AggregateIterable<Document> result = collection.aggregate(Arrays.asList(\n 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))))),\n match1,\n match2,\n sort,\n skip,\n limit,\n project\n ));\n\n // 处理查询结果\n for (Document document : result) {\n System.out.println(document.toJson());\n }\n\n // 关闭连接\n mongoClient.close();\n }\n}\n\n\n请确保将上述代码中的"your_database_name"更改为您实际使用的数据库名称。此外，您还需要根据实际情况调整连接MongoDB的主机名和端口号。\n\n以上代码将查询结果打印到控制台。您可以根据自己的需求进行进一步处理和使用查询结果。