SINOEX_STD_NBT_UK1 线路数据统计 - 2023年6月
<p>// 查询条件 >= start and < end/nvar start = new Date('2023-06-10T00:00:00.000+08:00');/nvar end = new Date('2023-06-30T00:00:00.000+08:00');/nvar service_type = 'SINOEX_STD_NBT_UK1';/n/n// 分位计算逻辑/nvar quantile = function(arr, percentile) {/n if (!Array.isArray(arr) || arr.length === 0) {/n return null;/n }/n var newArray = []/n // 除去空值/n for (var i = 0; i < arr.length; i++) {/n if (arr[i] != null) {/n // 转换数值型/n newArray.push(Number(arr[i]))/n }/n }/n /n arr = newArray/n /n var sortedArr = arr.slice().sort(function(a, b) {/n return a - b;/n });/n /n /n var n = sortedArr.length;/n var k = Math.floor(n * percentile);/n var kn = sortedArr[k];/n /n return kn;/n}/n// 分组计算 /ndb.ceos_order.aggregate([/n {/n // where/n $match: {/n 'trace_first_mile_outbound.trace_time': {/n $gte: start,/n $lt: end/n },/n 'service_type': service_type/n }/n },/n {/n // group/n $group: {/n _id: {/n 'date': {/n $dateToString: {/n date: '$trace_first_mile_outbound.trace_time',/n format: '%Y-%m-%d',/n timezone: '+08:00'/n }/n },/n 'service_type': '$service_type',/n 'so_code': '$so_code'/n },/n // 发运票数/n total: {/n $sum: 1/n },/n // 接收预报-发运 分位数原数据/n firstMileOutboundDiff: {/n $push: {/n $subtract: ['$trace_first_mile_outbound.trace_time', '$pre_alert_time']/n }/n },/n // 外运入库票数/n inboundCount: {/n $sum: {/n $cond: [{/n $ifNull: ['$trace_inbound.trace_time', false]/n }, 1, 0]/n }/n },/n // 异常量/n exceptionCount: {/n $sum: {/n $cond: [{/n $ifNull: ['$trace_unass.trace_time', false]/n }, 1, 0]/n }/n },/n // 发运-外运入库 分位数原数据/n inboundDiff: {/n $push: {/n $subtract: ['$trace_inbound.trace_time', '$trace_first_mile_outbound.trace_time']/n }/n },/n // 外运入库-起飞 分位数原数据/n atdDiff: {/n $push: {/n $subtract: ['$trace_atd.trace_time', '$trace_inbound.trace_time']/n }/n },/n // 起飞量 /n ataCound: {/n $sum: {/n $cond: [{/n $ifNull: ['$trace_atd.trace_time', false]/n }, 1, 0]/n }/n },/n // 起飞-降落 分位数原数据/n ataDiff: {/n $push: {/n $subtract: ['$trace_ata.trace_time', '$trace_atd.trace_time']/n }/n },/n // 降落量/n atdCound: {/n $sum: {/n $cond: [{/n $ifNull: ['$trace_ata.trace_time', false]/n }, 1, 0]/n }/n },/n // 降落-尾程揽收 分位数原数据/n aScanDiff: {/n $push: {/n $subtract: ['$trace_a_scan.trace_time', '$trace_ata.trace_time']/n }/n },/n // 尾程揽收量/n aScanCound: {/n $sum: {/n $cond: [{/n $ifNull: ['$trace_a_scan.trace_time', false]/n }, 1, 0]/n }/n },/n // 尾程揽收-妥投 分位数原数据/n deliverySuccessDiff: {/n $push: {/n $subtract: ['$trace_delivery_success.trace_time', '$trace_a_scan.trace_time']/n }/n },/n // 妥投量 AA.DELIVERY 不为空，同事aa.roe不为空/n deliveryCount: {/n $sum: {/n $cond: [{/n $and: [/n {/n $ifNull: ['$trace_delivery_success.trace_time', false]/n },/n {/n $ifNull: ['$trace_a_scan.trace_time', false]/n }/n ]/n }, 1, 0]/n }/n },/n // 发运-妥投 分位数原数据/n deliverySuccess2firstMileOutboundDiff: {/n $push: {/n $subtract: ['$trace_delivery_success.trace_time', '$trace_first_mile_outbound.trace_time']/n }/n },/n /n }/n },/n {/n // 排序/n $sort: {/n _id: 1/n }/n },/n {/n // as /n $project: {/n _id: 0,/n '发运日期': '$_id.date',/n '线路': '$_id.service_type',/n '结算账号': '$_id.so_code',/n '发运票数': '$total',/n '接收预报-发运-95分位': {/n // 四舍五入/n $round: [{/n // 除法/n $divide: [/n {/n // 分位函数/n $function: {/n body: quantile,/n args: ['$firstMileOutboundDiff', 0.95],/n lang: 'js'/n }/n }/n ,/n 60 * 60 * 1000/n ]/n }, 2]/n },/n '外运入库票数': '$inboundCount',/n '异常量': '$exceptionCount',/n '有效包裹量': {/n $subtract: ['$inboundCount', '$exceptionCount']/n },/n // '达标数': '$qualify',/n '发运-外运入库-95分位': {/n // 四舍五入/n $round: [{/n // 除法/n $divide: [/n {/n // 分位函数/n $function: {/n body: quantile,/n args: ['$inboundDiff', 0.95],/n lang: 'js'/n }/n }/n ,/n 60 * 60 * 1000/n ]/n }, 2]/n },/n '外运入库票数/发运票数': {/n // 四舍五入/n $round: [{/n $cond: [/n {/n $eq: ['$total', 0]/n }, // 检查除数是否为0/n 0, // 如果除数为0，设定默认值为0/n {/n // 除法/n $divide: ['$inboundCount', '$total']/n } // 除数不为0时，执行除法运算/n ]/n }/n , 4]/n },/n '外运入库-起飞-95分位': {/n // 四舍五入/n $round: [{/n // 除法/n $divide: [/n {/n // 分位函数/n $function: {/n body: quantile,/n args: ['$atdDiff', 0.95],/n lang: 'js'/n }/n }/n ,/n 60 * 60 * 1000/n ]/n }, 2]/n },/n '起飞量': '$atdCound',/n '起飞量/有效包裹量': {/n // 四舍五入/n $round: [{/n $cond: [/n {/n $eq: [{/n $subtract: ['$inboundCount', '$exceptionCount']/n }, 0]/n }, // 检查除数是否为0/n 0, // 如果除数为0，设定默认值为0/n {/n $divide: ['$atdCound', {/n $subtract: ['$inboundCount', '$exceptionCount']/n }]/n } // 除数不为0时，执行除法运算/n ]/n }/n , 4]/n },/n '起飞-降落-95分位': {/n // 四舍五入/n $round: [{/n // 除法/n $divide: [/n {/n // 分位函数/n $function: {/n body: quantile,/n args: ['$ataDiff', 0.95],/n lang: 'js'/n }/n }/n ,/n 60 * 60 * 1000/n ]/n }, 2]/n },/n '降落量': '$ataCound',/n '降落量/起飞量': {/n // 四舍五入/n $round: [{/n $cond: [/n {/n $eq: ['$atdCound', 0]/n }, // 检查除数是否为0/n 0, // 如果除数为0，设定默认值为0/n {/n $divide: ['$ataCound', '$atdCound']/n } // 除数不为0时，执行除法运算/n ]/n }/n , 4]/n },/n '降落-尾程揽收-95分位': {/n // 四舍五入/n $round: [{/n // 除法/n $divide: [/n {/n // 分位函数/n $function: {/n body: quantile,/n args: ['$aScanDiff', 0.95],/n lang: 'js'/n }/n }/n ,/n 60 * 60 * 1000/n ]/n }, 2]/n },/n '尾程揽收量': '$aScanCound',/n '尾程揽收量/降落量': {/n // 四舍五入/n $round: [{/n $cond: [/n {/n $eq: ['$ataCound', 0]/n }, // 检查除数是否为0/n 0, // 如果除数为0，设定默认值为0/n {/n $divide: ['$aScanCound', '$ataCound']/n } // 除数不为0时，执行除法运算/n ]/n }/n , 4]/n },/n '尾程揽收-妥投-95分位': {/n // 四舍五入/n $round: [{/n // 除法/n $divide: [/n {/n // 分位函数/n $function: {/n body: quantile,/n args: ['$deliverySuccessDiff', 0.95],/n lang: 'js'/n }/n }/n ,/n 60 * 60 * 1000/n ]/n }, 2]/n },/n '妥投量': '$deliveryCount',/n '妥投量/尾程揽收量': {/n // 四舍五入/n $round: [{/n $cond: [/n {/n $eq: ['$aScanCound', 0]/n }, // 检查除数是否为0/n 0, // 如果除数为0，设定默认值为0/n {/n $divide: ['$deliveryCount', '$aScanCound']/n } // 除数不为0时，执行除法运算/n ]/n }/n , 4]/n },/n '发运-妥投-95分位': {/n // 四舍五入/n $round: [{/n // 除法/n $divide: [/n {/n // 分位函数/n $function: {/n body: quantile,/n args: ['$deliverySuccess2firstMileOutboundDiff', 0.95],/n lang: 'js'/n }/n }/n ,/n 60 * 60 * 1000/n ]/n }, 2]/n }/n }/n }/n], {/n allowDiskUse: true/n})/n/n转成java编程内容：import java.util.ArrayList;/nimport java.util.Date;/nimport java.util.List;/n/nimport org.bson.Document;/nimport org.bson.conversions.Bson;/n/nimport com.mongodb.BasicDBObject;/nimport com.mongodb.MongoClient;/nimport com.mongodb.client.AggregateIterable;/nimport com.mongodb.client.MongoCollection;/nimport com.mongodb.client.MongoCursor;/nimport com.mongodb.client.MongoDatabase;/n/npublic class Main {/n/tpublic static void main(String[] args) {/n/t/t// 查询条件 >= start and < end/n/t/tDate start = new Date('2023-06-10T00:00:00.000+08:00');/n/t/tDate end = new Date('2023-06-30T00:00:00.000+08:00');/n/t/tString service_type = 'SINOEX_STD_NBT_UK1';/n/n/t/t// 分位计算逻辑/n/t/tBson quantile = new Document('$function',/n/t/t/t/t/tnew Document('body', 'function(arr, percentile) { if (!Array.isArray(arr) || arr.length === 0) { return null; } var newArray = []; for (var i = 0; i < arr.length; i++) { if (arr[i] != null) { newArray.push(Number(arr[i])); } } arr = newArray; var sortedArr = arr.slice().sort(function(a, b) { return a - b; }); var n = sortedArr.length; var k = Math.floor(n * percentile); var kn = sortedArr[k]; return kn; }')/n/t/t/t/t/t/t/t.append('args', new ArrayList<String>() {/n/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/tadd('$firstMileOutboundDiff');/n/t/t/t/t/t/t/t/t/tadd('0.95');/n/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t}).append('lang', 'js')));/n/n/t/t// 分组计算/n/t/tList<Bson> pipeline = new ArrayList<>();/n/t/tpipeline.add(new Document('$match', new Document('trace_first_mile_outbound.trace_time',/n/t/t/t/t/tnew Document('$gte', start).append('$lt', end)).append('service_type', service_type)));/n/n/t/tDocument groupBy = new Document('_id',/n/t/t/t/t/tnew Document('date',/n/t/t/t/t/t/t/tnew Document('$dateToString',/n/t/t/t/t/t/t/t/t/tnew Document('date', '$trace_first_mile_outbound.trace_time').append('format',/n/t/t/t/t/t/t/t/t/t/t/t'%Y-%m-%d').append('timezone', '+08:00')))/n/t/t/t/t/t/t/t/t/t/t/t.append('service_type', '$service_type').append('so_code',/n/t/t/t/t/t/t/t/t/t/t/t/t'$so_code'));/n/n/t/tgroupBy.append('total', new Document('$sum', 1));/n/t/tgroupBy.append('firstMileOutboundDiff',/n/t/t/t/t/tnew Document('$push', new Document('$subtract',/n/t/t/t/t/t/t/tnew ArrayList<String>() {/n/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t add('$trace_first_mile_outbound.trace_time');/n/t/t/t/t/t/t/t/t/t add('$pre_alert_time');/n/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t})));/n/t/tgroupBy.append('inboundCount',/n/t/t/t/t/tnew Document('$sum',/n/t/t/t/t/t/t/tnew Document('$cond',/n/t/t/t/t/t/t/t/tnew Document('$ifNull', new ArrayList<Object>() {/n/t/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t/t add('$trace_inbound.trace_time');/n/t/t/t/t/t/t/t/t/t/t add(false);/n/t/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t/t}))));/n/n/t/tgroupBy.append('exceptionCount',/n/t/t/t/t/tnew Document('$sum',/n/t/t/t/t/t/t/tnew Document('$cond',/n/t/t/t/t/t/t/t/tnew Document('$ifNull', new ArrayList<Object>() {/n/t/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t/t add('$trace_unass.trace_time');/n/t/t/t/t/t/t/t/t/t/t add(false);/n/t/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t/t}))));/n/n/t/tgroupBy.append('inboundDiff',/n/t/t/t/t/tnew Document('$push', new Document('$subtract',/n/t/t/t/t/t/t/tnew ArrayList<String>() {/n/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t add('$trace_inbound.trace_time');/n/t/t/t/t/t/t/t/t/t add('$trace_first_mile_outbound.trace_time');/n/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t})));/n/n/t/tgroupBy.append('atdDiff',/n/t/t/t/t/tnew Document('$push', new Document('$subtract',/n/t/t/t/t/t/t/tnew ArrayList<String>() {/n/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t add('$trace_atd.trace_time');/n/t/t/t/t/t/t/t/t/t add('$trace_inbound.trace_time');/n/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t})));/n/n/t/tgroupBy.append('ataCound',/n/t/t/t/t/tnew Document('$sum',/n/t/t/t/t/t/t/tnew Document('$cond',/n/t/t/t/t/t/t/t/tnew Document('$ifNull', new ArrayList<Object>() {/n/t/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t/t add('$trace_atd.trace_time');/n/t/t/t/t/t/t/t/t/t/t add(false);/n/t/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t/t}))));/n/n/t/tgroupBy.append('ataDiff',/n/t/t/t/t/tnew Document('$push', new Document('$subtract',/n/t/t/t/t/t/t/tnew ArrayList<String>() {/n/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t add('$trace_ata.trace_time');/n/t/t/t/t/t/t/t/t/t add('$trace_atd.trace_time');/n/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t})));/n/n/t/tgroupBy.append('atdCound',/n/t/t/t/t/tnew Document('$sum',/n/t/t/t/t/t/t/tnew Document('$cond',/n/t/t/t/t/t/t/t/tnew Document('$ifNull', new ArrayList<Object>() {/n/t/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t/t add('$trace_ata.trace_time');/n/t/t/t/t/t/t/t/t/t/t add(false);/n/t/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t/t}))));/n/n/t/tgroupBy.append('aScanDiff',/n/t/t/t/t/tnew Document('$push', new Document('$subtract',/n/t/t/t/t/t/t/tnew ArrayList<String>() {/n/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t add('$trace_a_scan.trace_time');/n/t/t/t/t/t/t/t/t/t add('$trace_ata.trace_time');/n/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t})));/n/n/t/tgroupBy.append('aScanCound',/n/t/t/t/t/tnew Document('$sum',/n/t/t/t/t/t/t/tnew Document('$cond',/n/t/t/t/t/t/t/t/tnew Document('$ifNull', new ArrayList<Object>() {/n/t/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t/t add('$trace_a_scan.trace_time');/n/t/t/t/t/t/t/t/t/t/t add(false);/n/t/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t/t}))));/n/n/t/tgroupBy.append('deliverySuccessDiff',/n/t/t/t/t/tnew Document('$push', new Document('$subtract',/n/t/t/t/t/t/t/tnew ArrayList<String>() {/n/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t add('$trace_delivery_success.trace_time');/n/t/t/t/t/t/t/t/t/t add('$trace_a_scan.trace_time');/n/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t})));/n/n/t/tgroupBy.append('deliveryCount',/n/t/t/t/t/tnew Document('$sum',/n/t/t/t/t/t/t/tnew Document('$cond',/n/t/t/t/t/t/t/t/tnew Document('$and', new ArrayList<Object>() {/n/t/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t/t add(new Document('$ifNull', new ArrayList<Object>() {/n/t/t/t/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t/t/t/t add('$trace_delivery_success.trace_time');/n/t/t/t/t/t/t/t/t/t/t/t/t add(false);/n/t/t/t/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t/t/t/t}));/n/t/t/t/t/t/t/t/t/t/t add(new Document('$ifNull', new ArrayList<Object>() {/n/t/t/t/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t/t/t/t add('$trace_a_scan.trace_time');/n/t/t/t/t/t/t/t/t/t/t/t/t add(false);/n/t/t/t/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t/t/t/t}));/n/t/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t/t}))));/n/n/t/tgroupBy.append('deliverySuccess2firstMileOutboundDiff',/n/t/t/t/t/tnew Document('$push', new Document('$subtract',/n/t/t/t/t/t/t/tnew ArrayList<String>() {/n/t/t/t/t/t/t/t/t{/n/t/t/t/t/t/t/t/t/t add('$trace_delivery_success.trace_time');/n/t/t/t/t/t/t/t/t/t add('$trace_first_mile_outbound.trace_time');/n/t/t/t/t/t/t/t/t}/n/t/t/t/t/t/t/t})));/n/n/t/tpipeline.add(new Document('$group', groupBy));/n/n/t/tpipeline.add(new Document('$sort', new Document('_id', 1)));/n/n/t/tpipeline.add(new Document('$project/</p>
原文地址: https://www.cveoy.top/t/topic/o58O 著作权归作者所有。请勿转载和采集!