java mongodb avg,MongoDB 简单统计$avg,$sum,$min,$max

计算平均值

基础语法:{ $avg: }

{ $avg: [ , ... ] }

注：

(1)$avg计算的时候会忽略非数值数据(包括缺失值)

(2)在$group中使用时，若expression为数组，$avg会忽略这个expression进行计算

(3)在其他管道符中使用时，对于单个expression，若其为数组，$avg会遍历整个数组进行计算；对于多个expression，若其中含数组，$avg会忽略这个expression

数字求和，

基础语法:{ $sum: }

{ $sum: [ , ... ] }

注：

(1)如果expression包含数值和非数值情况，$sum返回值为全部数值字段的和

(2)如果expression为不存在字段，$sum返回值为0

(3)如果expression全部为非数值情况，$sum返回值为全0

(4)在$group中使用时，若expression为数组，$sum会忽略这个expression进行计算

(5)在其他管道符中使用时，对于单个expression，若其为数组，$sum会遍历整个数组进行计算；对于多个expression，若其中含数组，$sum会忽略这个expression

求最小值，

基础语法:{ $min: }

{ $min: [ , ... ] }

注：

(1)$min计算的时null值和空值不参与比较

(2)在$group中使用时，若expression为数组，$min会将expression当成一个整体进行比较

(3)在其他管道符中使用时，对于单个expression，若其为数组，$min会遍历整个数组进行比较；对于多个expression，若其中含数组，$min会忽略这个expression

求最大值，

基础语法:{ $max: }

{ $max: [ , ... ] }

注：

(1)$max计算的时null值和空值不参与比较

(2)在$group中使用时，若expression为数组，$max会将expression当成一个整体进行比较

(3)在其他管道符中使用时，对于单个expression，若其为数组，$max会遍历整个数组进行比较；对于多个expression，若其中含数组，$max会忽略这个expression

上述内容可以在一下几个管道符中使用

$addFields (在3.4及以后的版本总可用)

$set (在4.2及以后的版本总可用)

$replaceRoot (在3.4及以后的版本总可用)

$replaceWith (在4.2及以后的版本总可用)

$match stage that includes an $expr expression

示例

在管道符$group中的应有

{"_id":1,"item":"a","price":10,"quantity":2, "arry":["1",2,3], "num":9, "num1":10 },

{"_id":2,"item":"b","price":20,"quantity":1, "arry":[4,5,6], "num":10, "num1":"11"},

{"_id":3,"item":"c","price":5, "quantity":5, "arry":[], "num":null,"num1":"12"},

{"_id":4,"item":"a","price":10,"quantity":10,"arry":[7,8,""], "num":11, "num1":["11","12","13"]},

{"_id":5,"item":"c","price":5, "quantity":10,"arry":["10","11"],"num":12, "num1":"14"},

{"_id":6,"item":"d","price":15,"quantity":10,"arry":[""], "num":"13","num1":"15"},

{"_id":7,"item":"d","price":15,"quantity":10,"arry":[12,""], "num":14 },

{"_id":8,"item":"d","price":15,"quantity":10,"arry":[""], "num":15, "num1":["11","9","8"] }

例一：求均值，求和

db.getCollection("test").aggregate([{

$group: {

_id: "$item",

//求均值

avg_Amount: { $avg: { $multiply: [ "$price", "$quantity" ] } },//两字段乘积

avg_quantity: { $avg: "$quantity" },//单字段

avg_arry: { $avg: "$arry" },//数组

avg_num: { $avg: "$num" },//涉及null值,字符串值，"item":"c"，"item":"d"

avg_num1: { $avg: "$num1" },//涉及数组、字符串、部分字段不存在，"item":"a"，"item":"d"

avg_num2: { $avg: "$num2" },//全部不存在值

//求和

sum_Amount: { $sum: { $multiply: [ "$price", "$quantity" ] } },//两字段乘积

sum_quantity: { $sum: "$quantity" },//单字段

sum_arry: { $sum: "$arry" },//数组

sum_num: { $sum: "$num" },//涉及null值,字符串值，"item":"c"，"item":"d"

sum_num1: { $sum: "$num1" },//涉及数组、字符串、部分字段不存在，"item":"a"，"item":"d"

sum_num2: { $sum: "$num2" },//全部不存在值

}

}])

运行结果

{"_id":"a","avg_Amount":60,"avg_quantity":6,"avg_arry":null,"avg_num":10,"avg_num1":10,"avg_num2":null,

"sum_Amount":120,"sum_quantity":12,"sum_arry":NumberInt("0"),"sum_num":20,"sum_num1":10,"sum_num2":NumberInt("0")},

{"_id":"b","avg_Amount":20,"avg_quantity":1,"avg_arry":null,"avg_num":10,"avg_num1":null,"avg_num2":null,

"sum_Amount":20,"sum_quantity":1,"sum_arry":NumberInt("0"),"sum_num":10,"sum_num1":NumberInt("0"),"sum_num2":NumberInt("0")},

{"_id":"c","avg_Amount":37.5,"avg_quantity":7.5,"avg_arry":null,"avg_num":12,"avg_num1":null,"avg_num2":null,

"sum_Amount":75,"sum_quantity":15,"sum_arry":NumberInt("0"),"sum_num":12,"sum_num1":NumberInt("0"),"sum_num2":NumberInt("0")},

{"_id":"d","avg_Amount":150,"avg_quantity":10,"avg_arry":null,"avg_num":14.5,"avg_num1":null,"avg_num2":null,

"sum_Amount":450,"sum_quantity":30,"sum_arry":NumberInt("0"),"sum_num":29,"sum_num1":NumberInt("0"),"sum_num2":NumberInt("0")}

运行结果

例二：最大值，最小值

db.getCollection("test").aggregate([{

$group: {

_id: "$item",

//最小值

min_Amount: { $min: { $multiply: [ "$price", "$quantity" ] } },//两字段乘积

min_quantity: { $min: "$quantity" },//单字段

min_arry: { $min: "$arry" },//数组

min_num: { $min: "$num" },//涉及null值,字符串值，"item":"c"，"item":"d"

min_num1: { $min: "$num1" },//涉及数组、字符串、部分字段不存在，"item":"a"，"item":"d"

min_num2: { $min: "$num2" },

//最大值

max_Amount: { $max: { $multiply: [ "$price", "$quantity" ] } },//两字段乘积

max_quantity: { $max: "$quantity" },//单字段

max_arry: { $max: "$arry" },//数组

max_num: { $max: "$num" },//涉及null值,字符串值，"item":"c"，"item":"d"

max_num1: { $max: "$num1" },//涉及数组、字符串、部分字段不存在，"item":"a"，"item":"d"

max_num2: { $max: "$num2" },//全部不存在值

}

}])

运行结果

//最小值

{"_id":"a","min_Amount":20, "min_quantity":2, "min_arry":[7,8,""],"min_num":9, "min_num1":10, "min_num2":null},

{"_id":"b","min_Amount":20, "min_quantity":1, "min_arry":[4,5,6], "min_num":10,"min_num1":"11","min_num2":null},

{"_id":"c","min_Amount":25, "min_quantity":5, "min_arry":[], "min_num":12,"min_num1":"12","min_num2":null},

{"_id":"d","min_Amount":150,"min_quantity":10,"min_arry":[12,""], "min_num":14,"min_num1":"15","min_num2":null

//最大值

{"_id":"a","max_Amount":100,"max_quantity":10,"max_arry":["1",2,3], "max_num":11, "max_num1":["11","12","13"],"max_num2":null},

{"_id":"b","max_Amount":20, "max_quantity":1, "max_arry":[4,5,6], "max_num":10, "max_num1":"11", "max_num2":null},

{"_id":"c","max_Amount":50, "max_quantity":10,"max_arry":["10","11"],"max_num":12, "max_num1":"14", "max_num2":null},

{"_id":"d","max_Amount":150,"max_quantity":10,"max_arry":[""], "max_num":"13","max_num1":["11","9","8"], "max_num2":null},

运行结果

在管道符$project中的应有

{"_id":1,"arry":["1",2,3], "num":9, "num1":10 },

{"_id":2,"arry":[4,5,6], "num":10, "num1":"11"},

{"_id":3,"arry":[""], "num":11, "num1":null},

{"_id":4,"arry":[7,8,""], "num":"12" },

{"_id":5,"arry":["10","11"],"num1":"14","num":null },

{"_id":6,"arry":[], "num1":"15" }

例三：求均值，求和

db.getCollection("test").aggregate([{

$project: {

//求均值

avg_num: { $avg: "$num" },//单字段

avg_arry: { $avg: "$arry" },//数组

avg_num_arry: { $avg: ["$num" ,"$arry"]},//数字和数组

avg_num_num1: { $avg: ["$num" ,"$num1"]},//涉及null值(_id:3、5),字符串值(_id:2、4、5、6)

avg_num_num2: { $avg: ["$num" ,"$num2"] },//涉及字段不存在

//求和

sum_num: { $sum: "$num" },//单字段

sum_arry: { $sum: "$arry" },//数组

sum_num_arry: { $sum: ["$num" ,"$arry"]},//数字和数组

sum_num_num1: { $sum: ["$num" ,"$num1"] },//涉及null值(_id:3、5),字符串值(_id:2、4、5、6)

sum_num_num2: { $sum: ["$num" ,"$num2"] },//涉及字段不存在

}

}])

运行结果

//求均值

{"_id":1,"avg_num":9, "avg_arry":2.5, "avg_num_arry":9, "avg_num_num1":9.5, "avg_num_num2":9},

{"_id":2,"avg_num":10, "avg_arry":5, "avg_num_arry":10, "avg_num_num1":10, "avg_num_num2":10},

{"_id":3,"avg_num":11, "avg_arry":null,"avg_num_arry":11, "avg_num_num1":11, "avg_num_num2":11},

{"_id":4,"avg_num":null,"avg_arry":7.5, "avg_num_arry":null,"avg_num_num1":null,"avg_num_num2":null},

{"_id":5,"avg_num":null,"avg_arry":null,"avg_num_arry":null,"avg_num_num1":null,"avg_num_num2":null},

{"_id":6,"avg_num":null,"avg_arry":null,"avg_num_arry":null,"avg_num_num1":null,"avg_num_num2":null},

//求和

{"_id":1,"sum_num":9,"sum_arry":5,"sum_num_arry":9,"sum_num_num1":19,"sum_num_num2":9},

{"_id":2,"sum_num":10,"sum_arry":15,"sum_num_arry":10,"sum_num_num1":10,"sum_num_num2":10},

{"_id":3,"sum_num":11,"sum_arry":NumberInt("0"),"sum_num_arry":11,"sum_num_num1":11,"sum_num_num2":11},

{"_id":4,"sum_num":NumberInt("0"),"sum_arry":15,"sum_num_arry":NumberInt("0"),"sum_num_num1":NumberInt("0"),"sum_num_num2":NumberInt("0")},

{"_id":5,"sum_num":NumberInt("0"),"sum_arry":NumberInt("0"),"sum_num_arry":NumberInt("0"),"sum_num_num1":NumberInt("0"),"sum_num_num2":NumberInt("0")},

{"_id":6,"sum_num":NumberInt("0"),"sum_arry":NumberInt("0"),"sum_num_arry":NumberInt("0"),"sum_num_num1":NumberInt("0"),"sum_num_num2":NumberInt("0")},

运行结果

例四：最大值，最小值

db.getCollection("test").aggregate([{

$project: {

//最小值

min_num: { $min: "$num" },//单字段

min_arry: { $min: "$arry" },//数组

min_num_arry: { $min: ["$num" ,"$arry"]},//数字和数组

min_num_num1: { $min: ["$num" ,"$num1"]},//涉及null值(_id:3、5),字符串值(_id:2、4、5、6)

min_num_num2: { $min: ["$num" ,"$num2"] },//涉及字段不存在

//最大值

max_num: { $max: "$num" },//单字段

max_arry: { $max: "$arry" },//数组

max_num_arry: { $max: ["$num" ,"$arry"]},//数字和数组

max_num_num1: { $max: ["$num" ,"$num1"]},//涉及null值(_id:3、5),字符串值(_id:2、4、5、6)

max_num_num2: { $max: ["$num" ,"$num2"] },//涉及字段不存在

}

}])

运行结果

//最小值

{"_id":1,"min_num":9, "min_arry":2, "min_num_arry":9, "min_num_num1":9, "min_num_num2":9},

{"_id":2,"min_num":10, "min_arry":4, "min_num_arry":10, "min_num_num1":10, "min_num_num2":10},

{"_id":3,"min_num":11, "min_arry":"", "min_num_arry":11, "min_num_num1":11, "min_num_num2":11},

{"_id":4,"min_num":"12","min_arry":7, "min_num_arry":"12", "min_num_num1":"12","min_num_num2":"12"},

{"_id":5,"min_num":null,"min_arry":"10","min_num_arry":["10","11"],"min_num_num1":"14","min_num_num2":null},

{"_id":6,"min_num":null,"min_arry":null,"min_num_arry":[], "min_num_num1":"15","min_num_num2":null},

//最大值

{"_id":1,"max_num":9, "max_arry":"1", "max_num_arry":["1",2,3], "max_num_num1":10, "max_num_num2":9},

{"_id":2,"max_num":10, "max_arry":6, "max_num_arry":[4,5,6], "max_num_num1":"11","max_num_num2":10},

{"_id":3,"max_num":11, "max_arry":"", "max_num_arry":[""], "max_num_num1":11, "max_num_num2":11},

{"_id":4,"max_num":"12","max_arry":"", "max_num_arry":[7,8,""], "max_num_num1":"12","max_num_num2":"12"},

{"_id":5,"max_num":null,"max_arry":"11","max_num_arry":["10","11"],"max_num_num1":"14","max_num_num2":null},

{"_id":6,"max_num":null,"max_arry":null,"max_num_arry":[], "max_num_num1":"15","max_num_num2":null}

运行结果