Mongo的独特值计算Java中的两个字段

MongoDB有一个聚合框架和管道,有点类似于SQL"GROUP BY",但这些阶段可以做更高级的工作.我们将展示一个三阶段示例,以获得出现不止一次的不同组合的计数.

考虑到你的意思是在文档中将requestId和telNum的相同值出现在一起,并将其视为相应的SQL

SELECT requestId, telNum, count(*) as counter
from collection
GROUP BY requestId, telNum

蒙戈壳位是组上的_id键从两个值组合.因此:

db.collection.aggregate([
    {$group: { _id: { requestId: "$requestId", telNum: "$telNum" }, count: {$sum: 1} } }
])

所以它在Java中的要点:

// Construct our _id to group on
DBObject fields = new BasicDBObject( "requestId", "$requestId" );
fields.put( "telNum", "$telNum" );

// Contruct group element
DBObject groupFields = new BasicDBObject( "_id", fields );
groupFields.put( "count", new BasicDBObject( "$sum", 1 ) );
DBObject group = new BasicDBObject( "$group", groupFields );



// Run aggregation
AggregationOutput output = collection.aggregate( group );

所以这里的输出将匹配上面的SQL.

更进一步,让我们考虑完整的SQL给我们不止一次的不同计数:

SELECT count(*) 
FROM ( 
  SELECT requestId, telNum, count(*) as counter
  FROM collection
  GROUP BY requestId, telNum
) a
WHERE a.counter > 1

因此,我们可以更改代码以将更多阶段添加到聚合管道,再次匹配(WHERE/HAVING)和$ group(GROUP BY):

// Construct a match on things with a count of more than 1
DBObject greaterThan = new BasicDBObject( "$gt", 1 );
DBObject matchFields = new BasicDBObject( "count", greaterTen );
DBObject match = new BasicDBObject( "$match", matchFields );

// Count the documents that match
DBObject newGroupFields = new BasicDBObject( "_id", null );
newGroupFields.put( "count", new BasicDBOject( "$sum", 1 ) );
DBObject group2 = new BasicDBObject( "$group", newGroupFields );

// Run aggregation
AggregationOutput output = collection.aggregate( group, match, group2 );

所以这链在链中的三个阶段

对得到的字段的不同值进行分组

过滤掉只有1个不同值的文档,因此只留下重复项

从过滤器中计算出不同的对

聚合允许您将这样的各个阶段"链接"在一起以获得结果.它非常适合这类工作,值得注意的是它比使用Map-Reduce要快得多,尽管它仍然有它的用途.

根据需要编辑和播放.

还有一个有用的榜样这里