mysql aggregate是什么类型_mongodb 之聚合框架--aggregate

淩亂°似流年 2022-10-25 00:56 10阅读 0赞

Mongodb中的聚合框架

1 .Mongodb 中的管道操作符之简单操作,

Mongo 中的数据查询语句db.collection.find(\{\},\{\})中的第一个大括号是用来控制过滤条件的，

第二个大括号是控制key的显示的。如果想要对查询出来的结果集中的字段重命令，求平均值,分组等操作

的时候这个find就实现不了。

要想对查询出来的结果集做进一步处理，mongo提供一种实现的方式，聚合框架(aggreagate) 。

下面我们来进一步讨论这个aggreagate怎么使用。。

在使用aggregate实现聚合操作之前,我们首先来认识下几个常用的聚合操作符。

$project:：可以对结果集中的键 重命令，控制键是否显示,对列进行计算。

$match: 过滤结果集

$group:分组，聚合，求和，平均数，等

$skip: 在显示结果的时候跳过前几行

$sort:对即将显示的结果集排序

$limit:控制结果集的大小

(1).$project:：可以对结果集中的键 重命令，控制键是否显示。如果在操作中,

实例一：

> db.test.find(\{\},\{\_id:0\}).limit(2);

\{ "id" : 1, "iname" : "ToneyDeng", "iage" : 21, "iadd" : "ShengZheng", "mark" : "" \}

\{ "id" : 2, "iname" : "Jack", "iage" : 24, "iadd" : "ShangHai", "mark" : "" \}

> db.test.aggregate(\{$project:\{did:"$id",dname:"$iname",dage:"$iage",daddr:"$iadd",\_id:0\}\},\{$limit:2\});

"result" : \[

"did" : 1,

"dname" : "ToneyDeng",

"dage" : 21,

"daddr" : "ShengZheng"

\},

"did" : 2,

"dname" : "Jack",

"dage" : 24,

"daddr" : "ShangHai"

\],

"ok" : 1

在以上实例中，隐藏了\_id,然后将其他所有的key的名字重新设置了下。

这里需要重点注意的是在对key重命令后，如果该key下面有索引，将使查询无法使用到索引。

所以尽量在查询后进行重命令。

(2)$match: 这个函数的功能其实和find(\{\})差不多,主要过滤结果集的。

实例二：

> db.test.find(\{iage:\{$lt:25\}\},\{\}).limit(2);

\{ "\_id" : ObjectId("54c1fe77570ca4579ca978e5"), "id" : 1, "iname" : "ToneyDeng", "iage" : 21, "iadd" : "ShengZheng", "mark" : "" \}

\{ "\_id" : ObjectId("54c1febb570ca4579ca978e6"), "id" : 2, "iname" : "Jack", "iage" : 24, "iadd" : "ShangHai", "mark" : "" \}

> db.test.aggregate(\{$match:\{iage:\{$lt:25\}\}\},\{$limit:2\})

"result" : \[

"\_id" : ObjectId("54c1fe77570ca4579ca978e5"),

"id" : 1,

"iname" : "ToneyDeng",

"iage" : 21,

"iadd" : "ShengZheng",

"mark" : ""

\},

"\_id" : ObjectId("54c1febb570ca4579ca978e6"),

"id" : 2,

"iname" : "Jack",

"iage" : 24,

"iadd" : "ShangHai",

"mark" : ""

\],

"ok" : 1

以上两个查询的结果集一样只是展示的方式不一样而已。

在进行聚合group之前最好执行match，减少数据量，提高工作效率。另外如果过滤的key上有索引,

那么查询会走索引。

(3). $group:分组，聚合，求和，平均数，等

实例三：

> db.test.aggregate(\{$group:\{\_id:"$iname",ct:\{$sum:1\},avg:\{$avg:"$iage"\}\}\},\{$limit:3\});

"result" : \[

"\_id" : "Tom",

"ct" : 3,

"avg" : 25.666666666666668

\},

"\_id" : "Owen",

"ct" : 2,

"avg" : 26

\},

"\_id" : "Smith",

"ct" : 1,

"avg" : 27

\],

"ok" : 1

以上是一个简单的$group操作,这里需要重点解释一下几个字段

\_id:分组字段，该字段的值必须为”$key”,并且\_id是固定的不能随意改变，该列表示需要的分组的字段，可以为单字段分组，也可以多字段分组(后面演示)。

ct：表示总的记录数,

avg:平均值。

(4).$skip: 在显示结果的时候跳过前几行

实例四：

> db.test.find(\{\}).sort(\{id:1\});

\{ "\_id" : ObjectId("54c1fe77570ca4579ca978e5"), "id" : 1, "iname" : "ToneyDeng", "iage" : 21, "iadd" : "ShengZheng", "mark" : "" \}

\{ "\_id" : ObjectId("54c1febb570ca4579ca978e6"), "id" : 2, "iname" : "Jack", "iage" : 24, "iadd" : "ShangHai", "mark" : "" \}

\{ "\_id" : ObjectId("54c1ff22570ca4579ca978e7"), "id" : 3, "iname" : "Smith", "iage" : 27, "iadd" : "ShangHai", "mark" : "" \}

\{ "\_id" : ObjectId("54c1ff36570ca4579ca978e8"), "id" : 4, "iname" : "Owen", "iage" : 27, "iadd" : "BeiJing", "mark" : "" \}

\{ "\_id" : ObjectId("54c1ff44570ca4579ca978e9"), "id" : 5, "iname" : "Owen", "iage" : 25, "iadd" : "BeiJing", "mark" : "" \}

\{ "\_id" : ObjectId("54c1ffc2570ca4579ca978ea"), "id" : 6, "iname" : "Tom", "iage" : 25, "iadd" : "WuHan", "mark" : "" \}

\{ "\_id" : ObjectId("54c1ffd3570ca4579ca978eb"), "id" : 7, "iname" : "Tom", "iage" : 25, "iadd" : "BeiJing", "mark" : "" \}

\{ "\_id" : ObjectId("54c2005e570ca4579ca978ec"), "id" : 8, "iname" : "Tom", "iage" : 27, "iadd" : "GuangZhou", "mark" : "nihao" \}

\{ "\_id" : ObjectId("54c20076570ca4579ca978ed"), "id" : 9, "iname" : "Jack", "iage" : 23, "iadd" : "GuangZhou", "mark" : "Yahoo" \}

> db.test.aggregate(\{$sort:\{id:1\}\},\{$skip:6\});

"result" : \[

"\_id" : ObjectId("54c1ffd3570ca4579ca978eb"),

"id" : 7,

"iname" : "Tom",

"iage" : 25,

"iadd" : "BeiJing",

"mark" : ""

\},

"\_id" : ObjectId("54c2005e570ca4579ca978ec"),

"id" : 8,

"iname" : "Tom",

"iage" : 27,

"iadd" : "GuangZhou",

"mark" : "nihao"

\},

"\_id" : ObjectId("54c20076570ca4579ca978ed"),

"id" : 9,

"iname" : "Jack",

"iage" : 23,

"iadd" : "GuangZhou",

"mark" : "Yahoo"

\],

"ok" : 1

通过上面的两个结果集的对比可以发现结果二成功跳过了前6行。

(5)$sort:对即将显示的结果集排序,   $limit:控制结果集的大小

关于这两个管道操作符的的使用方法比较简单，在实例四和实例三中有使用到。

2.Mongodb 中的管道操作符之复杂操作,

实例五:分组后，重命名字段。

> db.test.aggregate(\{$group:\{\_id:"$iname",count:\{$sum:1\},avg:\{$avg:"$iage"\}\}\},\{$project:\{id:"$\_id",ct:"$count",ag:"$avg",\_id:0\}\})

"result" : \[

"id" : "Tom",

"ct" : 3,

"ag" : 25.666666666666668

\},

"id" : "Owen",

"ct" : 2,

"ag" : 26

\},

"id" : "Smith",

"ct" : 1,

"ag" : 27

\},

"id" : "Jack",

"ct" : 2,

"ag" : 23.5

\},

"id" : "ToneyDeng",

"ct" : 1,

"ag" : 21

\],

"ok" : 1

实例六：重命名字段再分组。

> db.test.aggregate(\{$project:\{\_id:0,aid:"$id",aname:"$iname",age:"$iage"\}\},\{$group:\{\_id:"$aname",ct:\{$sum:1\},ag:\{$avg:"$age"\}\}\});

"result" : \[

"\_id" : "Tom",

"ct" : 3,

"ag" : 25.666666666666668

\},

"\_id" : "Owen",

"ct" : 2,

"ag" : 26

\},

"\_id" : "Smith",

"ct" : 1,

"ag" : 27

\},

"\_id" : "Jack",

"ct" : 2,

"ag" : 23.5

\},

"\_id" : "ToneyDeng",

"ct" : 1,

"ag" : 21

\],

"ok" : 1

实例七：过滤再分组.

> db.test.aggregate(\{$match:\{id:\{$lt:6\}\}\},\{$group:\{\_id:"$iname",ct:\{$sum:1\},ag:\{$avg:"$iage"\}\}\});

"result" : \[

"\_id" : "Owen",

"ct" : 2,

"ag" : 26

\},

"\_id" : "Smith",

"ct" : 1,

"ag" : 27

\},

"\_id" : "Jack",

"ct" : 1,

"ag" : 24

\},

"\_id" : "ToneyDeng",

"ct" : 1,

"ag" : 21

\],

"ok" : 1

实例八：分组再过滤

> db.test.aggregate(\{$group:\{\_id:"$iname",ct:\{$sum:1\},ag:\{$avg:"$iage"\}\}\},\{$match:\{ct:\{$lte:2\}\}\});

"result" : \[

"\_id" : "Owen",

"ct" : 2,

"ag" : 26

\},

"\_id" : "Smith",

"ct" : 1,

"ag" : 27

\},

"\_id" : "Jack",

"ct" : 2,

"ag" : 23.5

\},

"\_id" : "ToneyDeng",

"ct" : 1,

"ag" : 21

\],

"ok" : 1

总结:这几个聚合操作符之间没有严格的使用顺序，只要按照一定的规则搭配使用，一般的聚合都可以实现。

问题一：在聚合框架中，如何统计结果集的总数。

问题二：在聚合框架中，如何查看执行计划，

3.Mongodb 中的管道表达式之常用表达式。

管道表达式

project:部分

\#数学表达式

$add:一个或多个表达式相加

$subtract:接收两个表达式,返回两表达式的之差

$multiply:接收一个或多个表达式,返回它们相乘的结果集

$divide:接收两个表达式,返回第一个除以第二个表达式的商

$mod:接收两个表达式,返回第一个表达式除以第二个表达式的余数(模型)

\#日期表达式

$year:年份

$month:月份

$week:一年中的第几周

$dayOfMonth:本月的第几天

$dayOfWeek:一个星期的第几天

$dayOfYear:一年中的第几天

$hour:获取小时部分

$minute:取得分钟数

$second:取得秒数

\#字符串表达式

$substr:字符串截取函数,相当于mysql中substr

$concat:字符串连接函数

$toLower:将字符串值变为小写

$toUpper:将字符串值变为大写

\#逻辑表达式

$cpm:接收两个表达式\[exp1,exp2\],如果exp1exp2就返回1

$cond:\[bool,exp1,exp2\],如果bool为true就返回exp1，否则返回exp2

$ifnull:\[exp1,exp2\],如果exp1为null,就返回exp2

$strcasecmp:比较两个字符串的大小

$eq/$ne/$gt/$gte/$lt/$lte:以此为(等于/不等于/大于/大于或等于/小于/小于或等于)

$and:并且

$or:或

$not:否

$in:相当于mysql中in

$ne:不等于。

$exists:列是存在。

\#数学表达式

(1)$add:一个或多个表达式相加

> db.test.aggregate(\{$project:\{iage:\{$add:\["$iage",1\]\},\_id:0\}\},\{$limit:3\});

"result" : \[

"iage" : 22

\},

"iage" : 25

\},

"iage" : 28

\],

"ok" : 1

(2).$subtract:接收两个表达式,返回两表达式的之差

> db.test.aggregate(\{$project:\{iage:\{$add:\["$iage",1\]\},subtr:\{$subtract:\["$iage",20\]\},\_id:0\}\},\{$limit:3\});

"result" : \[

"iage" : 22,

"subtr" : 1

\},

"iage" : 25,

"subtr" : 4

\},

"iage" : 28,

"subtr" : 7

\],

"ok" : 1

(3).$multiply:接收一个或多个表达式,返回它们相乘的结果集

> db.test.aggregate(\{$project:\{multi:\{$multiply:\["$iage",2\]\},\_id:0\}\},\{$limit:3\});

"result" : \[

"multi" : 42

\},

"multi" : 48

\},

"multi" : 54

\],

"ok" : 1

(4).$divide:接收两个表达式,返回第一个除以第二个表达式的商

> db.test.aggregate(\{$project:\{divide:\{$divide:\["$iage",11\]\},\_id:0\}\},\{$limit:3\});

"result" : \[

"divide" : 1.9090909090909092

\},

"divide" : 2.1818181818181817

\},

"divide" : 2.4545454545454546

\],

"ok" : 1

(5).$mod:接收两个表达式,返回第一个表达式除以第二个表达式的余数(模型)

> db.test.aggregate(\{$project:\{iage:1,mod:\{$mod:\["$iage",20\]\},\_id:0\}\},\{$limit:3\});

"result" : \[

"iage" : 21,

"mod" : 1

\},

"iage" : 24,

"mod" : 4

\},

"iage" : 27,

"mod" : 7

\],

"ok" : 1

这个表达式很特别,可以在find,和match中使用但是好像是有点问题.

> db.test.find(\{id:\{$mod:\[21,4\]\}\}).limit(3);

\{ "\_id" : ObjectId("54c1ff36570ca4579ca978e8"), "id" : 4, "iname" : "Owen", "iage" : 27, "iadd" : "BeiJing", "mark" : "" \}

> db.test.find(\{id:\{$mod:\[7,4\]\}\}).limit(3);

\{ "\_id" : ObjectId("54c1ff36570ca4579ca978e8"), "id" : 4, "iname" : "Owen", "iage" : 27, "iadd" : "BeiJing", "mark" : "" \}

以上两个查询中,在正常情况下，查询出来的id应为,1,3不应该为4,。

(6).\#日期表达式部分.

$year:年份

$month:月份

$week:一年中的第几周

$dayOfMonth:本月的第几天

$dayOfWeek:一个星期的第几天

$dayOfYear:一年中的第几天

$hour:获取小时部分

$minute:取得分钟数

$second:取得秒数

用法很单一，这里就用$year为例。

> db.test.aggregate(\{$project:\{\_id:0,iname:1,year:\{$year:\[new Date()\]\}\}\},\{$limit:2\});

"result" : \[

"iname" : "ToneyDeng",

"year" : 2015

\},

"iname" : "Jack",

"year" : 2015

\],

"ok" : 1

(7).\#字符串表达式

$substr:字符串截取函数,相当于mysql中substr

> db.test.aggregate(\{$project:\{\_id:0,id:1,iname:1,substr:\{$substr:\["$iname",0,4\]\}\}\},\{$limit:2\});

"result" : \[

"id" : 1,

"iname" : "ToneyDeng",

"substr" : "Tone"

\},

"id" : 2,

"iname" : "Jack",

"substr" : "Jack"

\],

"ok" : 1

(8).$concat:字符串连接函数

> db.test.aggregate(\{$project:\{\_id:0,id:1,iname:1,iadd:1,concat:\{$concat:\["$iname","  ","$iadd"\]\}\}\},\{$limit:2\});

"result" : \[

"id" : 1,

"iname" : "ToneyDeng",

"iadd" : "ShengZheng",

"concat" : "ToneyDeng  ShengZheng"

\},

"id" : 2,

"iname" : "Jack",

"iadd" : "ShangHai",

"concat" : "Jack  ShangHai"

\],

"ok" : 1

(9).$toLower:将字符串值变为小写,$toUpper:将字符串值变为大写

$toLower和$toUpper的使用方法一样,

> db.test.aggregate(\{$project:\{\_id:0,iname:1,toLower:\{$toLower:\["$iname"\]\}\}\},\{$limit:2\});

"result" : \[

"iname" : "ToneyDeng",

"toLower" : "toneydeng"

\},

"iname" : "Jack",

"toLower" : "jack"

\],

"ok" : 1

(10)。\#逻辑表达式

$cpm:接收两个表达式\[exp1,exp2\],如果exp1exp2就返回

> db.test.aggregate(\{$project:\{\_id:0,iname:1,iadd:1,cmp:\{$cmp:\["$iname","$iadd"\]\}\}\},\{$limit:2\});

"result" : \[

"iname" : "ToneyDeng",

"iadd" : "ShengZheng",

"cmp" : 1

\},

"iname" : "Jack",

"iadd" : "ShangHai",

"cmp" : -1

\],

"ok" : 1

(11).$strcasecmp:比较两个字符串的大小(这个具体还不知道是怎么回事！，不过语法没有问题。)

> db.test.aggregate(\{$project:\{\_id:0,iname:1,iadd:1,strcasecmp:\{$strcasecmp:\["w","w"\]\}\}\},\{$limit:2\});

"result" : \[

"iname" : "ToneyDeng",

"iadd" : "ShengZheng",

"strcasecmp" : 0

\},

"iname" : "Jack",

"iadd" : "ShangHai",

"strcasecmp" : 0

\],

"ok" : 1

(12).$cond:\[bool,exp1,exp2\],如果bool为true就返回exp1，否则返回exp2

> db.test.aggregate(\{$project:\{\_id:0,iname:1, con:\{$cond:\[false,"$iage","$iadd"\]\}\}\},\{$limit:2\});

"result" : \[

"iname" : "ToneyDeng",

"con" : "ShengZheng"

\},

"iname" : "Jack",

"con" : "ShangHai"

\],

"ok" : 1

(14) 运算表达式,这几个表达式可以在find和match中用来过滤文档数量

$eq/$ne/$gt/$gte/$lt/$lte:以此为(等于/不等于/大于/大于或等于/小于/小于或等于)

$and:并且

$or:或

$not:否

$in:相当于mysql中in

$exists:某个键是否存在。

相当于 select \* from tableName where iname <>”ToneyDeng”and  iage<25

> db.test.aggregate(\{$match:\{$and:\[\{iname:\{$ne:"ToneyDeng"\}\},\{iage:\{$lt:25\}\}\]\}\});

"result" : \[

"\_id" : ObjectId("54c1febb570ca4579ca978e6"),

"id" : 2,

"iname" : "Jack",

"iage" : 24,

"iadd" : "ShangHai",

"mark" : ""

\},

"\_id" : ObjectId("54c20076570ca4579ca978ed"),

"id" : 9,

"iname" : "Jack",

"iage" : 23,

"iadd" : "GuangZhou",

"mark" : "Yahoo"

\],

"ok" : 1

相当于:select \* from  tableName  where id not in (1,3,5,6,7,8)  or  iname<>”ToneyDeng”and iage<25

> db.test.aggregate(\{$match: \{$or:\[\{id:\{$not:\{$in:\[1,3,5,6,7,8\]\}\}\}  ,\{$and:\[\{iname:\{$ne:"ToneyDeng"\}\},\{iage:\{$lt:25\}\}\]\}\]\}\});

"result" : \[

"\_id" : ObjectId("54c1febb570ca4579ca978e6"),

"id" : 2,

"iname" : "Jack",

"iage" : 24,

"iadd" : "ShangHai",

"mark" : ""

\},

"\_id" : ObjectId("54c1ff36570ca4579ca978e8"),

"id" : 4,

"iname" : "Owen",

"iage" : 27,

"iadd" : "BeiJing",

"mark" : ""

\},

"\_id" : ObjectId("54c20076570ca4579ca978ed"),

"id" : 9,

"iname" : "Jack",

"iage" : 23,

"iadd" : "GuangZhou",

"mark" : "Yahoo"

\],

"ok" : 1

键iname是否存在

> db.test.aggregate(\{$match:\{iname:\{$exists:true\}\}\},\{$limit:2\});

"result" : \[

"\_id" : ObjectId("54c1fe77570ca4579ca978e5"),

"id" : 1,

"iname" : "ToneyDeng",

"iage" : 21,

"iadd" : "ShengZheng",

"mark" : ""

\},

"\_id" : ObjectId("54c1febb570ca4579ca978e6"),

"id" : 2,

"iname" : "Jack",

"iage" : 24,

"iadd" : "ShangHai",

"mark" : ""

\],

"ok" : 1

(15).group:聚合操作符

\#算术操作符

$sum:求和

$avg:平均数

$max:最大值

$min:最小值

$first:第一个值

$last:最后一个值

> db.test.aggregate(\{$group:\{\_id:"$iname",count:\{$sum:1\},sum:\{$sum:"$iage"\},avg:\{$avg:"$iage"\},max:\{$max:"$iage"\},min:\{$min:"$iage"\}\}\},\{$limit:2\});

"result" : \[

"\_id" : "Tom",

"count" : 3,

"sum" : 77,

"avg" : 25.666666666666668,

"max" : 27,

"min" : 25

\},

"\_id" : "Owen",

"count" : 2,

"sum" : 52,

"avg" : 26,