MongoDB 聚合查询：时间范围筛选、分组计算和数据投影

// 查询条件 \u003e= start and \u003c 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 // 排序\n $sort: {\n _id: 1\n }\n },\n {\n $skip: 1\n },\n {\n $limit: 10\n },\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\njava编码内容：// 查询条件 \u003e= start and \u003c end\nDate start = new Date("2023-06-1T00:00:00.000+08:00");\nDate end = new Date("2023-06-8T00:00:00.000+08:00");\nString service_type = "DISTRIBUTOR_KOREA_SEA";\n\nString front = "$trace_inbound.trace_time";\nString back = "$trace_delivery_success.trace_time";\nint day = 3;\n\n// 分组计算 \nList\u003cBson\u003e pipeline = Arrays.asList(\n Aggregates.addFields(\n new Field\u003c"days"\u003e("days",\n new Document("$ceil",\n new Document("$divide",\n Arrays.asList(\n new Document("$subtract", Arrays.asList(back, front)),\n 1000 * 60 * 60 * 24 // 将毫秒转换为天数\n )\n )\n )\n )\n ),\n Aggregates.match(\n Filters.and(\n Filters.gte("trace_inbound.trace_time", start),\n Filters.lt("trace_inbound.trace_time", end),\n Filters.eq("service_type", service_type)\n )\n ),\n Aggregates.match(\n Filters.and(\n Filters.regex("sinoex_no", "SE"),\n Filters.eq("days", day)\n )\n ),\n Aggregates.sort(Sorts.ascending("_id")),\n Aggregates.skip(1),\n Aggregates.limit(10),\n Aggregates.project(\n Projections.fields(\n Projections.excludeId(),\n Projections.include("eawb_syscode", "sinoex_no", "tracking_no", "trace_inbound.trace_time", "trace_outbound.trace_time",\n "trace_ass.trace_time", "trace_ata.trace_time", "trace_a_scan.trace_time", "trace_delivery_success.trace_time",\n "service_type", "so_code", "dest_post_code")\n )\n )\n);\n\nMongoCollection\u003cDocument\u003e collection = database.getCollection("ceos_order");\nList\u003cDocument\u003e results = collection.aggregate(pipeline).allowDiskUse(true).into(new ArrayList\u003c\u003e());