mongodb使用aggregate、group、match实现mysql中的having(count(1)>1)的功能

.stu.insert({cid:1,age:14,name:‘gom1‘});db.stu.insert({cid:1,age:12,name:‘jack2‘});db.stu.insert({cid:2,age:13,name:‘Lily3‘});db.stu.insert({cid:2,age:14,name:‘tony4‘});db.stu.insert({cid:2,age:9,name:‘Harry5‘});db.stu.insert({cid:2,age:13,name:‘Vincent6‘});db.stu.insert({cid:1,age:14,name:‘bill7‘});db.stu.insert({cid:2,age:17,name:‘tim8‘});db.stu.insert({cid:1,age:10,name:‘bruce9‘});db.stu.insert({cid:3,age:20,name:‘luxi10‘});

执行窗口显示如下：

mongos> use test;switched to db testmongos> mongos> db.stu.insert({cid:1,age:14,name:‘gom1‘});mongos> db.stu.insert({cid:1,age:12,name:‘jack2‘});mongos> db.stu.insert({cid:2,age:13,name:‘Lily3‘});mongos> db.stu.insert({cid:2,age:14,name:‘tony4‘});mongos> db.stu.insert({cid:2,age:9,name:‘Harry5‘});mongos> db.stu.insert({cid:2,age:13,name:‘Vincent6‘});mongos> db.stu.insert({cid:1,age:14,name:‘bill7‘});mongos> db.stu.insert({cid:2,age:17,name:‘tim8‘});mongos> db.stu.insert({cid:1,age:10,name:‘bruce9‘});mongos> db.stu.insert({cid:3,age:20,name:‘luxi10‘});

var group=([ {group:{_id:"cid", max_age: {max:"age"},count: { sum: 1 }}},       {sort:{count:-1}} ]) 执行窗口如下：

mongos> var group=([ ... {$group:{_id:"$cid", max_age: {$max:"$age"},count: { $sum: 1 }}},... {$sort:{count:-1}}... ])mongos>

var match ={“match":{"count" : {"gt” : 1}}}; 执行窗口如下：

mongos> var match ={"$match":{"count" : {"$gt" : 1}}};mongos>

mongos> db.stu.aggregate(group, match);{ "result" : [ { "_id" : 2, "max_age" : 17, "count" : 5 }, { "_id" : 1, "max_age" : 14, "count" : 4 }, { "_id" : 3, "max_age" : 20, "count" : 1 } ], "ok" : 1}mongos> 

PS：这里match无效，出来count为1的数据，没有match成功，问题在哪里呢？

网友Traveller的指点： db.stu.aggregate(group, match); 看出来了吗？你这样调用相当于把match作为options传进去了，当然没有用，这种方法得不到最终的数据，因为每个管道是数组的一个元素，而group单独是一个数组，match是一个元素。除非把它加进数组里面，看如下写法， group.push(match); db.stu.aggregate(group);

然后自己去看官网进一步研究： http://docs.mongoing.com/manual-zh/reference/method/db.collection.aggregate.html#db.collection.aggregate，查看了aggregate介绍，aggregate的方法签名是function (pipeline, aggregateOptions)， 第一个参数是piplines，所有的对数据的判断条件都应该在这里面。 第二个参数是options，指定查询时候使用的一些选项。 The options document can contain the following fields and values:包括explain、allowDiskUse、cursor，所以我传入的match是针对group的判断条件，所以不能作为第二个参数，而应该放到第一个参数里面去，所以需要用到group的push功能，将match加入第一个参数里面去才能使count>1的判断生效。

mongos> group.push(match);3mongos> db.stu.aggregate(group);{ "result" : [ { "_id" : 2, "max_age" : 17, "count" : 5 }, { "_id" : 1, "max_age" : 14, "count" : 4 } ], "ok" : 1}mongos> 

看到已经过滤掉了count=1的记录组了，表示push成功了。

mongodb使用aggregate、group、match实现mysql中的having(count(1)>1)的功能

标签：

小编还为您整理了以下内容，可能对您也有帮助：

mongoDB应用篇-mongo聚合查询

如果我们在日常操作中，将部分数据存储在了MongoDB中，但是有需求要求我们将存储进去的文档数据，按照一定的条件进行查询过滤，得到想要的结果便于二次利用，那么我们就可以尝试使用MongoDB的聚合框架。

前面我们在学习文档查询的过程中，也介绍过一些查询的操作符，其中就有一部分是简单的查询聚合函数，例如 count 、 distinct 、 group 等，如果是简单的数据分析过滤，完全可以使用这些自带的聚合函数以及查询的操作符来完成文档的过滤查询操作

如果我们遇到了一些数据需要跨多个文本或者统计等操作，这个时候可能文档自身也较为复杂，查询操作符已经无法满足的时候，这个时候就需要使用MongoDB的聚合查询框架了。

使用聚合框架可以对集合中的文档进行变换和组合查询，基本上我们使用的时候，都是使用多个构件创建一个管道，用于对一连串的文档进行处理。这里的构件包括 筛选(filter) 、 投射(projecting) 、 分组(grouping) 、 排序(sorting) 、 (limiting) 以及 跳过(skipping)

MongoDB中需要使用聚合操作，一般使用 aggregate 函数来完成多个聚合之间的连接，aggregate() 方法的基本语法格式如下 :

现在假设我们有个集合articles，里面存储了文章的集合，大致如下:

但这时我们需要查询出来每一个作者写的文章数量，需要使用aggregate()计算 ，大致如下：

输出的结果为:

通过这个简单的案例我们就能输出想要的数据和属性名，大概分析一下刚刚的聚合查询语句， by_user字段进行分组，代表每个用户一条数据，而num_tutorial则是定义了数值类型计算的结果字段，$sum则是计算总和，相当于每个用户出现一次，都会+1，最终计算出来的总和通过num_tutorial字段进行输出

注：如果管道没有给出预期的结果，就需要进行调试操作，调试的时候，可以尝试先给一个管道操作符的条件，如果这个时候查询出来的结果是我们想要的，那么我们需要再去指定第二个管道操作符，依次操作，最后就会定位到出了问题的操作符

前面我们提到聚合查询会使用管道操作符，而每一个操作符就会接受一连串的文档，对这些文档进行一些类型转换，最后将转换以后的文档结果传递给下一个管道操作符来执行后续的操作，如果当前是最后一个管道操作符，那么则会显示给用户最后的文档数据。不同的管道操作符是可以按照顺序组合在一起使用，并且可以被重复执行多次，例如我们可以先使用$match然后再去、 match操作。

match管道操作符可以使用$gt、$lt、$in等操作符，进行过滤，不过需要注意的是不能在$match管道操作符中使用空间地理操作符。

在实际使用的过程中，尽可能的将 match操作符以后，再去投射或者执行分组操作的话，是可以利用索引的。

相比较一般的查询操作而言，使用管道操作，尤其是其中的投射操作更加强大。我们可以在查询文档结束以后利用 $project 操作符从文档中进行字段的提取，甚至于我们可以重命名字段，将部分字段映射成我们想要展示出去的字段，也可以对一部分字段进行一些有意义的处理。需要注意的是， $project 操作符可以传入两个参数，第一个是需要处理的属性名称，第二个则是0或者1，如果传入1，则代表当前的属性是需要显示出来的，如果是0或者不写，默认都是代表这个字段不需要显示出来

当然第二个参数也可以是一个表达式或者查询条件，满足当前表达式的数据也可以进行显示，接下来我们先准备一点数据:

接下来，我们来查询，条件是item字段为abc，quantity要大于5，并且我们只要item和price字段的结果，其他都排除掉:

可以看到结果为:

如果我们想要在原基础上改变某个字段的名称，例如将item改为item_code,可以利用$来完成，如下:

可以看到我们指定的名称item_code，而这个别名对应的字段item使用$作为前缀标记，代表将item字段映射为item_code,可以看到结果:

我们在投影的时候，除了可以将某个字段映射成其他字段以外，还可以针对某个字段进行一些简单的运算，最常见的就是 四则运算 ，即

加法（ subtract ）、乘法（ divide ）、求模（ $mod ） ，

除此之外，还支持对字段进行 关系运算 (大小比较（ " eq" ）、大于（ " gte" ）、小于（ " lte" ）、不等于（ " ifNull" ） )、

逻辑运算 （与（ " or" ）、非 （ " concat" ）、截取（ " toLower" ） )等

我们基于上面的需求，假设每一个价格是按照元为单位，现在要求输出W为单位，那么我们就需要对price进行除法运算,如下:

除此之外，我们也可以将计算完毕的price改名为priceW，即:

可以看到输出的结果为:

这时有一个需求，要求我们返回数据的同时还要yyyy-MM-dd格式的时间字符串，这个时候我们就需要对date字段进行时间函数和字符串混合处理了,如下:

这里需要注意的一点是， year:" substr函数将date字段的结果截取成字符串即可实现拼接

group的_id上，代表按照当前字段进行分组，例如，我们这里根据item进行分组:

在我们针对某个字段进行分组以后，我们可以针对每个分组进行一些操作符的使用，常见的例如: $sum 、 $avg 、 $min 、 $max 、 $first 、 $last 。

$avg 操作符用来返回每一个分组内的平均值

现在我们基于前面item的分组，我们想要算出来每个组内的平均价格是多少，如下:

$min 和 $max 操作符用于返回分组内最大的值和最小的值

除了平均值以外，我们现在将最贵的和最便宜的价格也要列出来，这个时候就可以使用这两个操作符了，如下:

$first 、 $last 则是可以获取当前分组中第一个或者最后一个的某个字段的结果，如下:

除此之外，我们还可以在分组的时候使用数组操作符，例如 $addToSet 可以判断，当前数组如果不包含某个条件，就添加到当前数组中， $push 则不管元素是否存在，都直接添加到数组中

注意：大部分管道操作符都是流式处理的，只要有新的文档进入，就可以对新的文档进行处理，但是 $group 代表必须收到全部文档以后才可以进行分组操作，才会将结果传递给后续的管道操作符，这就意味着，如果当前mongo是存在分片的，会先在每个分片上执行完毕以后，再把结果传递mongos进行统一的分组，剩下的管道操作符也不会在每个分片，而是mongos上执行了

如果我们现在遇到一些文档比较复杂，比如存在内嵌文档的存在，某个属性里面嵌套了一个数组，但是我们需要对内嵌的数组文档进行分析过滤等查询处理，这个时候就可以使用 $unwind 操作符将每一个文档中的嵌套数组文件拆分为一个个的文档便于进行后续的处理,例如我们需要将之前的set集合中关于请求的url以及ip的信息拆分出来，原始的格式如下:

我们可以使用命令进行拆分，如下:

结果为:

可以看到数据则是按照每一条信息的方式展示出来了，方便后续的计算以及输出，但是需要注意的一点是，这种方式，如果该文档中没有拆分的字段，或者是空数组，默认会直接排除，如果我们需要空数组等也输出计算出来，则可以指定 preserveNullAndEmptyArrays 参数，设置为true，则代表空数组或者不存在的文档也要拆分输出出来，即:

我们可以在管道查询的过程中，按照某个属性值或者多个属性的结果进行顺序排序，排序的方式与普通查询操作符中的sort操作符表现一致，与其他管道操作符一样，可以在任何阶段使用，但是，需要注意的一点是，建议在管道操作符第一阶段进行排序，因为此时的排序是可以触发索引的，如果在后续阶段进行排序，会消耗大量内存，并且耗时会很久，尤其是在有 $group 的情况下，如果放在 $group 操作符后面，会发现等到的时间很久，不仅仅是无法触发索引的问题，还和 $group 操作符是等待所有数据完毕才会触发的特性有关，因此需要格外注意。

结果如下，按照我们想要的结果进行了排序:

limit，只返回前两条数据，如下:

结果如下:

除了 skip，与之前的查询操作符作用也是一样的，用于在已经查询完毕的结果集中跳过前N条数据以后进行返回，我们将$skip加在刚刚的查询后面，如下:

这个时候可以看到返回的结果为空，什么结果都没有了，这是因为前一步管道已经了仅仅返回2条，而接着我们又跳过了前两条文档，因此返回的结果为空，我们将顺序调换一下，看看:

可以看到结果如下，与刚才的结果无异:

管道查询操作符有很多，除了上面学习的常用的部分，还有几十个，需要了解全部的可以参考官网:

https://docs.mongodb.com/manual/reference/command/aggregate/

除此之外，我们在学习的过程中了解到，部分查询操作符是可以触发索引的，例如 $project 、 $group 或者 $unwind 操作符，因此我们也建议 如果可以的话，尽量先使用这类管道操作符进行数据过滤，可以有效减少数据集大小和数量，而且管道如果不是直接从原先的集合中使用数据，那就无

法在筛选和排序中使用索引 ，例如我们先进行管道操作，再去将过滤好的数据进行 $sort 排序，会导致无法使用索引，效率大幅度下降，因此如果我们需要涉及到 $sort 操作的时候，如果可以尽可能在最开始就处理，这个时候可以使用索引，效率较高，然后再去进行管道查询筛选与分组等其他操作，可以有效的提高查询的效率。另外需要注意的一点是，在 MongoDB中会对每一个管道查询做，例如某一步管道查询操作导致内存占用超过20%，这个时候就会报错，无法继续使用管道 ，因为mongoDB本身每次最大是16Mb的数据量，为了尽可能避免或者减少这种问题，建议可以考虑尽可能的使用 $match 操作符过滤无用数据，减少数据总大小。同时也 因为管道查询是多步执行，例如 $group 则是等待所有数据完毕才会执行，因此可能会导致整体执行时间较久 ，也因为这样，才不建议在较高的实时查询需求上使用管道和查询，而是在 设计的时候尽可能直接使用查询操作符进行数据查询，触发更多的索引，更快的销量查询出来想要的结果。

mongo中的高级查询之聚合操作(distinct,count,group)与数据去重

Mongodb中自带的基本聚合函数有三种：count、distinct和group。下面我们分别来讲述一下这三个基本聚合函数。

（1）count

作用：简单统计集合中符合某种条件的文档数量。

使用方式：db.collection.count(<query>)或者db.collection.find(<query>).count()

参数说明：其中<query>是用于查询的目标条件。如果出了想限定查出来的最大文档数，或者想统计后跳过指定条数的文档，则还需要借助于limit，skip。

举例：

db.collection.find(<query>).limit();

db.collection.find(<query>).skip();

（2）distinct

作用：用于对集合中的文档针进行去重处理

使用方式：db,collection.distinct(field,query)

参数说明：field是去重字段，可以是单个的字段名，也可以是嵌套的字段名；query是查询条件，可以为空；

举例：

db.collection.distinct("user",{“age":{$gt:28}});//用于查询年龄age大于28岁的不同用户名

除了上面的用法外，还可以使用下面的另外一种方法：

db.runCommand({"distinct":"collectionname","key":"distinctfied","query":<query>})

collectionname：去重统计的集合名，distinctfield：去重字段,，<query>是可选的条件；

举例：

这两种方式的区别：第一种方法是对第二种方法的封装，第一种只返回去重统计后的字段值集合，但第二种方式既返回字段值集合也返回统计时的细节信息。

（3）group

作用：用于提供比count、distinct更丰富的统计需求，可以使用js函数控制统计逻辑

使用方式：db.collection.group(key,rece,initial[,keyf][,cond][,finalize])

备注说明：在2.2版本之前，group操作最多只能返回10000条分组记录，但是从2.2版本之后到2.4版本，mongodb做了优化，能够支持返回20000条分组记录返回，如果分组记录的条数大于20000条，那么可能你就需要其他方式进行统计了，比如聚合管道或者MapRece；

===========================================================

mongo中的高级查询之聚合操作(distinct,count,group)

1.distinct的实现：

2.count的实现

3.group的实现

(1).分组求和:类似于mysql中的 select act,sum(count) from consumerecords group by act

(2).分组求和,过滤。类似mysql中的select act,sum(count) from consumerecords group by act having act='charge';

(3).将时间格式化并且按时间分组求count,不推荐使用这种方法。

db.playerlogs.aggregate({ concat:[{ year:" substr:[{ start"},0,4]},{ dayOfMonth:" group:{_id:" sum:1}}},{$sort:{con:1}}) ;

group按时间分组(时间格式化)

http://www.tuicool.com/articles/EjUnQz

javascriptz中时间的相关函数参考:

http://blog.csdn.net/npp616/article/details/7181730

(4).group 分组方法实现的讲解。

group 的完整语法是。

参数解释:

key:需要分组的键或是函数(function),group分组实例3中的key就是一个函数值

initial:声明并且初始化变量。每一组共享一个变量值。多个变量之间用逗号隔开

$rece:循环体,集合中有多少个文档，就会循环多少次。函数(function)中变量doc表示当前文档对象,

prev表示累积处理的结果对象(这个地方可能描述的不是很情况,自己慢慢体会)

finalize:可选参数,可以简单理解为对分组之后的结果的再次处理,doc表示group之后的文档对象(这一步也是一个循环体

condition:可选参数,对已经分组好的结果进行过滤,有点类似于mysql中的having

4.mapRece:暂缺,要想玩转这个方法需要有很强的JavaScript功能。

据说mapRece 可以实现很复杂的查询,可以将一个复杂的查询分拆到多个机器上运行,

然后把各个结果集组合起来,形成最终结果。但是很慢。

maprece用法大全

http://www.cnblogs.com/yuechaotian/archive/2013/02/22/2922268.html

maprece性能调优

http://www.iteye.com/news/28013

数组中去除重复值示例

http://www.cnblogs.com/sosoft/archive/2013/12/08/3463830.html

5.db.runCommand的相关命令.

db.runCommand({distinct:"consumerecords",key:"userId"}):键值去重 类似于mysql中的 select distinct userId from consumerecords

db.runCommand({distinct:"consumerecords",key:"userId"}).values.length:去重之后求记录数,类似于mysql中的 select count(distinct userId) from consumerecords

db.runCommand({distinct:"consumerecords",key:"userId",query:{act:"charge"}}):去重之后求记录数,类似于mysql中的 select distinct userId from consumerecords where act="charge"

db.runCommand(

... {

... group:

... {

... ns:"test2", # 集合名

... key:{iname:true}, # 分组字段

... initial:{dd:0}, # 按照来初始化该值

... rece之后 ,function返回的值

... {

... prev.dd=doc.iage+prev.dd #

... }

...

... }

... }

... )

数值以字符串形式存储的解决方案：

db.runCommand(

{

group:

{

ns:"consumerecords",

key:{act:true},

initial:{ct:100,tt:0},

$rece:function(doc,prev)

{

},

}

}

)

去重

1, 直接使用distinct 语句查询, 这种查询会将所有查询出来的数据返回给用户, 然后对查询出来的结果集求总数(耗内存,耗时一些)

var len = db.student.distinct("name",{"age" : 18}).length;

print(len)

注,使用这种方法查询时,查询的结果集大于16M 时会查询失败,失败信息如下:

{“message” : “distinct failed: MongoError: distinct too big, 16mb cap”,”stack” : “script:1:20”}

2, 使用聚合函数,多次分组统计结果,最终将聚合的结果数返回给用户

db.student.aggregate([

{ project:{"name":true}},

{ name"}},

{ sum:1}}}

])

注,这种查询数据量大时就不会出现如上查询失败的情况,而且这种查询不管是内存消耗还是时间消耗都优于上面一种查询

ps：根据id分组，id指定为组合项的话，因为id不会重复，所以作用相当于把组合项去重了。

假如现在需要拿出collection的其他field的话，可以使用$push关键字

// 根据name和sex分组

// 把分组后的name,sex,age放到对应的Document下，形成一个数组

db.student.aggregate(

[

{

name", sex: " push: " push: " push: "$age"}

}

}

]

). forEach(function(x){

db.temp.insert(

{

name: x.name,

sex : x.sex,

age: x.age

}

);

});

Mongodb Aggregation group()分组操作

Mongo的分组操作有两种方式:aggregate({$group:{}})和group()

{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... } }

为必选字段，为被分组字段，可为空或null

为可选字段，其中可包含一下运算符：

1.仅分组，对issue_xjtf表中sp1，sp2进行分组

相当于sql

db.collection.group({ key, rece, initial [, keyf] [, cond] [, finalize] })
前三个是必备参数，“[]”中是可选参数

可以放用来分组的字段，并且会返回其中字段(group by 后面的字段)

是在分组操作期间对文档进行操作的聚合函数。可以返回总和或计数。该函数有两个参数：当前文档；该组的聚合结果文档。

对结果中文档，字段进行初始化

对数据筛选的条件，相当于where

1.count:取xbgi表中，article_pubdate值大于2000-01-01的数据，并分组计数

2.max:取sjwd表中，ric_publication_coden为9529a8f7-3eef-431a-a0cd-e49d601417df，用article_year分组计数，取其最晚日期。

3.sum:在表total_journal_issue中以journal_id分组，并获取article_count总数

用Navicat 执行group()时，分组值超过20000，会报如下错误（未检测具体原因）：

Mongodb Aggregation group()分组操作

Mongo的分组操作有两种方式:aggregate({$group:{}})和group()

{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... } }

为必选字段，为被分组字段，可为空或null

为可选字段，其中可包含一下运算符：

1.仅分组，对issue_xjtf表中sp1，sp2进行分组

相当于sql

db.collection.group({ key, rece, initial [, keyf] [, cond] [, finalize] })
前三个是必备参数，“[]”中是可选参数

可以放用来分组的字段，并且会返回其中字段(group by 后面的字段)

是在分组操作期间对文档进行操作的聚合函数。可以返回总和或计数。该函数有两个参数：当前文档；该组的聚合结果文档。

对结果中文档，字段进行初始化

对数据筛选的条件，相当于where

1.count:取xbgi表中，article_pubdate值大于2000-01-01的数据，并分组计数

2.max:取sjwd表中，ric_publication_coden为9529a8f7-3eef-431a-a0cd-e49d601417df，用article_year分组计数，取其最晚日期。

3.sum:在表total_journal_issue中以journal_id分组，并获取article_count总数

用Navicat 执行group()时，分组值超过20000，会报如下错误（未检测具体原因）：

sql语句，在同一张表中筛选出重复的行 至少 2个字段都是重复的行，这个语句应该怎么写？

SELECT t.* FROM 表1 t1

INNER JOIN

(

select

字段1,字段2

from 表1

group by 字段1,字段2

having count(字段1)>1

) t2

ON t1.字段1=t2.字段1

AND t1.字段2=t2.字段2追问

这个我试了一下  ，貌似不行哈？、、就是类似上图的表 我是想找出 有没有重复的行（列0 和列1 都同时重复）        或者是 找出   列0 和列1 都不同时重复的行、  谢谢了 朋友 能不能写的具体点 新手~~~

在SQL中查询年纪一样大（出生年份一样）的两个不同学生的姓名，以及他们的年龄

--好歹给张表的截图啊

--创建student表

create table student

( s_id number

,s_name varchar2(20)

,s_birthday date

)

--导入三组数据

insert into student values('001','liuzhao',to_date('1992-04-02 01:01:01','yyyy-mm-dd hh:mi:ss'));

insert into student values('002','lisi',to_date('1992-04-02 01:01:01','yyyy-mm-dd hh:mi:ss'));

insert into student values('003','wangwu',to_date('1990-02-10 01:01:01','yyyy-mm-dd hh:mi:ss'))

--查询出生年份相同的学生姓名和年龄

select s_name ,to_number(to_char(sysdate,'yyyy'))-to_number(to_char(s_birthday,'yyyy')) age

from student

where to_char(s_birthday,'yyyy') in

(select to_char(s_birthday,'yyyy')

from student

group by to_char(s_birthday,'yyyy')

having count(1)>1)