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驱动程序依赖项。您可以在Maven项目的pom.xml文件中添加以下依赖项：\n\nxml\n<dependency>\n <groupId>org.mongodb</groupId>\n <artifactId>mongodb-driver-sync</artifactId>\n <version>4.4.3</version>\n</dependency>\n\n\n然后，您可以使用以下Java代码来执行该查询：\n\njava\nimport com.mongodb.client.*;\nimport com.mongodb.client.model.*;\nimport org.bson.Document;\n\nimport java.time.*;\nimport java.util.*;\n\npublic class MongoDBExample {\n public static void main(String[] args) {\n // 设置查询条件\n Date start = Date.from(Instant.parse(\"2023-06-01T00:00:00.000Z\"));\n Date end = Date.from(Instant.parse(\"2023-06-08T00:00:00.000Z\"));\n String serviceType = \"DISTRIBUTOR_KOREA_SEA\";\n String front = \"$trace_inbound.trace_time\";\n String back = \"$trace_delivery_success.trace_time\";\n int day = 3;\n\n // 创建MongoDB客户端\n MongoClient client = MongoClients.create(\"mongodb://localhost:27017\");\n\n // 获取数据库和集合\n MongoDatabase database = client.getDatabase(\"your_database_name\");\n MongoCollection<Document> collection = database.getCollection(\"ceos_order\");\n\n // 构建聚合管道\n List<Bson> pipeline = new ArrayList<>();\n\n // 添加条件到管道\n Bson match1 = Filters.and(\n Filters.gte(front, start),\n Filters.lt(front, end),\n Filters.eq(\"service_type\"," serviceType)\n );\n pipeline.add(Aggregates.match(match1));\n\n Bson match2 = Filters.and(\n Filters.regex(\"sinoex_no\"," \"SE\","),\n Filters.eq(\"days\"," day)\n );\n pipeline.add(Aggregates.match(match2));\n\n // 添加分组和计算字段到管道\n Bson addFields = Aggregates.addFields(\n new Field<>(\"days\"," new Document(\"$ceil\"," new Document(\"$divide\"," Arrays.asList(\n new Document(\"$subtract\"," Arrays.asList(back, front)),\n 1000 * 60 * 60 * 24 // 将毫秒转换为天数\n )))))\n );\n pipeline.add(addFields);\n\n // 添加排序到管道\n Bson sort = Aggregates.sort(Sorts.ascending(\"_id\"));\n pipeline.add(sort);\n\n // 添加跳过和限制到管道\n Bson skip = Aggregates.skip(1);\n pipeline.add(skip);\n\n Bson limit = Aggregates.limit(10);\n pipeline.add(limit);\n\n // 添加投影到管道\n Bson project = Aggregates.project(\n Projections.fields(\n Projections.excludeId(),\n Projections.include(\"eawb_syscode\"," \"sinoex_no\"," \"tracking_no\"," \"trace_inbound.trace_time\","\n \"trace_outbound.trace_time\"," \"trace_ass.trace_time\"," \"trace_ata.trace_time\","\n \"trace_a_scan.trace_time\"," \"trace_delivery_success.trace_time\"," \"service_type\","\n \"so_code\"," \"dest_post_code\",")\n )\n );\n pipeline.add(project);\n\n // 执行聚合查询\n AggregateIterable<Document> result = collection.aggregate(pipeline);\n\n // 遍历结果\n for (Document document : result) {\n System.out.println(document.toJson());\n }\n\n // 关闭MongoDB客户端\n client.close();\n }\n}\n\n\n请确保将your_database_name替换为您的数据库名称。您还可以根据需要调整查询条件和投影字段。