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

1、$avg

计算平均值
基础语法:{ $avg: <expression> }
{ $avg: [ <expression1>, <expression2> ... ] }
注：
（1）$avg计算的时候会忽略非数值数据（包括缺失值）
（2）在$group中使用时，若expression为数组，$avg会忽略这个expression进行计算
（3）在其他管道符中使用时，对于单个expression，若其为数组，$avg会遍历整个数组进行计算；对于多个expression，若其中含数组，$avg会忽略这个expression

2、$sum

数字求和，
基础语法:{ $sum: <expression> }
{ $sum: [ <expression1>, <expression2> ... ] }
注：
（1）如果expression包含数值和非数值情况，$sum返回值为全部数值字段的和
（2）如果expression为不存在字段，$sum返回值为0
（3）如果expression全部为非数值情况，$sum返回值为全0
（4）在$group中使用时，若expression为数组，$sum会忽略这个expression进行计算
（5）在其他管道符中使用时，对于单个expression，若其为数组，$sum会遍历整个数组进行计算；对于多个expression，若其中含数组，$sum会忽略这个expression

3、$min

求最小值，
基础语法:{ $min: <expression> }
{ $min: [ <expression1>, <expression2> ... ] }
注：
（1）$min计算的时null值和空值不参与比较
（2）在$group中使用时，若expression为数组，$min会将expression当成一个整体进行比较
（3）在其他管道符中使用时，对于单个expression，若其为数组，$min会遍历整个数组进行比较；对于多个expression，若其中含数组，$min会忽略这个expression

4、$max

求最大值，
基础语法:{ $max: <expression> }
{ $max: [ <expression1>, <expression2> ... ] }
注：
（1）$max计算的时null值和空值不参与比较
（2）在$group中使用时，若expression为数组，$max会将expression当成一个整体进行比较
（3）在其他管道符中使用时，对于单个expression，若其为数组，$max会遍历整个数组进行比较；对于多个expression，若其中含数组，$max会忽略这个expression

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

$group
$project
$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}

运行结果

最后编辑于：2021.01.06 14:43:05

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,332评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,508评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,812评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,607评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,728评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,919评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,071评论 3赞 410
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,802评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,256评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,576评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,712评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,389评论 4赞 332
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,032评论 3赞 316
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,798评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,026评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,473评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,606评论 2赞 350

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

1、$avg

2、$sum

3、$min

4、$max

示例

推荐阅读更多精彩内容