java mongodb 聚合分组查询 mongo 聚合统计

1. 关于MongoDB聚合

聚合是MongoDB的高级查询语言，它允许我们通过转化合并由多个文档的数据来生成新的在单个文档里不存在的文档信息。MongoDB中聚合(aggregate)主要用于处理数据（例如分组统计平均值、求和、最大值等），并返回计算后的数据结果，有点类似sql语句中的 count(*)、group by。

在MongoDB中，有两种方式计算聚合：Pipeline 和 MapReduce。Pipeline查询速度快于MapReduce，但是MapReduce的强大之处在于能够在多台Server上并行执行复杂的聚合逻辑。MongoDB不允许Pipeline的单个聚合操作占用过多的系统内存。

MongoDB的聚合管道将MongoDB文档在一个管道处理完毕后将结果传递给下一个管道处理，管道操作是可以重复的。

聚合常用操作符：

• $sum：计算总和。
• $avg：计算平均值
• $min：获取集合中所有文档对应值得最小值。
• $max：获取集合中所有文档对应值得最大值。
• $push：在结果文档中插入值到一个数组中。
• $addToSet：在结果文档中插入值到一个数组中，但不创建副本。
• $first：根据资源文档的排序获取第一个文档数据。
• $last：根据资源文档的排序获取最后一个文档数据

管道常用操作符：

• $project：修改输入文档的结构。可以用来重命名、增加或删除域，也可以用于创建计算结果以及嵌套文档。
• $match：用于过滤数据，只输出符合条件的文档。match使用MongoDB的标准查询操作。
• $limit：用来限制MongoDB聚合管道返回的文档数。
• $skip：在聚合管道中跳过指定数量的文档，并返回余下的文档。
• $unwind：将文档中的某一个数组类型字段拆分成多条，每条包含数组中的一个值。
• $group：将集合中的文档分组，可用于统计结果。
• $sort：将输入文档排序后输出。
• $geoNear：输出接近某一地理位置的有序文档。

2. 表关联

select * from card_tbl_trade_m_orc where carduser_id in (select id from card_tbl_carduser_orc where companycustomer_id > “1”)

表card_tbl_trade_m_orc与card_tbl_carduser_orc关联查询，card_tbl_trade_m_orc中的carduser_id字段与card_tbl_trade_m_orc中的id关联。

db.card_tbl_trade_m_orc.aggregate([
   {
     $lookup:
       {
         from: "card_tbl_carduser_orc",
         localField: "carduser_id",
         foreignField: "id",
         as: "inventory_docs"
       }
  },
  { $match : {"card_tbl_carduser_orc.companycustomer_id":{"$gt" :"1"}} }
])

3. 分组取最大值

3.1. 方法一：分组聚合方法

首先，通过match取发生时间在2014-01-01到2021-02-28期间的数据；
接着，通过group按carduser_id分组，使用max取最大发生时间，以及使用data字段取首条数据记录；
最后，通过project，显示取回内容。

db.getCollection('card_tbl_trade_m_orc').aggregate([
{ $match : {"occurtime":{"$gt" :"2014-01-01","$lt" :"2021-02-28 23:59:00"}} },
{"$group":{'_id': "$carduser_id",max_value:{"$max":"$occurtime"},
data:{$first: '$$ROOT'}}},
{$project:{_id:1,max_value:1,data:{id:1,balance:1,carduser_id:1}}}
], 
{
    allowDiskUse: true
})

Add allowDiskUse and set it to true at the end of your query so it becomes like the following

增加and逻辑，and后面必须是array符[]。

db.getCollection('card_tbl_trade_m_orc').aggregate([
{ $match : {'$and':[{"occurtime":{"$gt" :"2013-12-31"},"occurtime":{"$lt" :"2021-03-01"}}]} },
{"$group":{'_id': "$carduser_id",max_value:{"$max":"$occurtime"}}}
], 
{
    allowDiskUse: true
})

3.2. 方法二：排序最大值

db.getCollection('card_tbl_trade_m_orc').find().sort({"occurtime":-1}).limit(1)

4. 分组计数

查询命令行说明：

• match：限定分组范围为，时间大于2014年，交易类型为消费；
• project：使用$toInt转换字符串为整数
• group：carduser_id 与发生月份联合分组，组合提取月消费合计
• project：从_id子集中，提取carduser_id等下层字段
• group：按carduser_id 分组，取最大月消费额
• project：重新提前需要使用的列名
• match：筛选大于-500000数据

db.getCollection('card_tbl_trade_m_orc').aggregate([
{ $match : {'trade_type':{'$in':['150','151','153']},'occurtime':{'$gt':'2014-01-01 00:00:00'}}},
{$project:{'carduser_id':'$carduser_id','occurtime':'$occurtime','amount':{'$toInt':'$amount'}}},
{"$group":{'_id': {"carduser_id":"$carduser_id",'yearmonth':{'$substr':['$occurtime',0,7]}},'total':{"$sum":"$amount"}}},
{$project : {'_id': 0, 'carduser_id' : '$_id.carduser_id','yearmonth':'$_id.yearmonth', "total" : 1}},
{'$group':{'_id':'$carduser_id','min_amount':{'$min':'$total'}}},
{$project : {'_id':0,'carduser_id':'$_id','min_amount': 1}},
{ $match : {'min_amount':{'$gt':-500000}}}
], 
{
    allowDiskUse: true
})

聚合后排序，增加“$sort”关键字。

db.getCollection('card_tbl_trade_m_orc').aggregate([
{ $match : {'trade_type':{'$in':['150','151','153']},'occurtime':{'$gt':'2014-01-01 00:00:00'}}},
{$project:{'carduser_id':'$carduser_id','occurtime':'$occurtime','amount':{'$toInt':'$amount'}}},
{"$group":{'_id': {"carduser_id":"$carduser_id",'yearmonth':{'$substr':['$occurtime',0,7]}},'total':{"$sum":"$amount"}}},
{$sort: {'_id.carduser_id':1}}
], 
{
    allowDiskUse: true
})

获取最后的总数，group的_id为null。

db.getCollection('card_tbl_trade_m_orc').aggregate([
{ $match : {'trade_type':{'$in':['150','151','153']},'occurtime':{'$gt':'2014-01-01 00:00:00'}}},
{$project:{'carduser_id':'$carduser_id','occurtime':'$occurtime','amount':{'$toInt':'$amount'}}},
{"$group":{'_id': {"carduser_id":"$carduser_id",'yearmonth':{'$substr':['$occurtime',0,7]}},'total':{"$sum":"$amount"}}},
{$project : {'_id': 0, 'carduser_id' : '$_id.carduser_id','yearmonth':'$_id.yearmonth', "total" : 1}},
{'$group':{'_id':'$carduser_id','min_amount':{'$min':'$total'}}},
{$project : {'_id':0,'carduser_id':'$_id','min_amount': 1}},
{ $match : {'min_amount':{'$lte':-500000}}},
{$group:{'_id':null,'total':{$sum:1}}}
], 
{
    allowDiskUse: true
})

5. 其他相关命令

5.1. 查询返回新增字段/列

db.getCollection('card_tbl_trade_m_orc').find({'trade_type':{'$in':['150','151','153','100','101','102','104']},'occurtime':{'$gt':'2014-01-01','$lt':'2021-02-28 23:59:00'}},
{'_id':0,'new_key':'$org_id', 'org_id' :1, 'balance' :1 , 'goodscategory_id' : 1, 'volumn' :1, 'trade_type' : 1,  'carduser_id' :1 , 'discount' :1, 'realamount':1, 'amount' :1, 'occurtime' :1, 'id':1})

'new_key':'$org_id'为查询返回new_key，使用$符号匹配使用字段。

5.2. 去重命令

语法：db.collection_name.distinct(field,query,options)

• field -----指定要返回的字段(string)
• query-----条件查询(document)
• options-----其他的选项(document)

db.getCollection('card_tbl_trade_m_orc').distinct('org_id',{'carduser_id':'1874102'})

5.3. 命令客户端

在客户端，使用命令行登录服务：

mongo "mongodb://192.168.x.x:27017"

和程序里的连接串一致。

5.4. 正则表达式

通过$regex匹配月份，按年月查询：

db.getCollection('card_tbl_trade_m_orc').find({'carduser_id':'119314572','occurtime':{'$regex':'2017-11'}}).sort({'occurtime':1})

5.5. 查询数量方法

方法一：查询取数量

db.getCollection('card_tbl_trade_m_orc').find({'occurtime':{$gt:'2013-12-31'}}).count()

1383083

方法二：

db.getCollection('card_tbl_trade_m_orc').find({'occurtime':{$gt:'2013-12-31'},'trade_type':{'$in':['150','151','153','100','101','102','104']}}).count()

1309092

5.6. 查询返回类型转换

字符串转换为整型：$toInt。