MongoDB 之聚合操作
公司目前一部分数据在MongoDB中存储，很郁闷的是数据结构还比较复杂，所以在做聚合操作时比较费劲。
下面两段代码足以应付负载查询了
这是公司的数据结构
{
"_id" : ObjectId("5c36afcea856498115161c5e"),
"startDate" : ISODate("2019-01-10T02:34:00.000Z"),
"endDate" : ISODate("2019-01-10T02:37:00.000Z"),
"frequentness" : 1,
"resultList" : [
{
"officeCode" : "AA",
"fltNum" : "EE",
"fltDate" : ISODate("2019-02-16T10:30:00.000Z"),
"segOrgn" : "BB",
"segDstn" : "CC",
"paxId" : "104c060edd3b36447f8c954ed0218b3f",
"pnr" : "DD",
"eventCode" : "Issue",
"eventTime" : ISODate("2019-01-10T02:34:03.000Z"),
"expireDate" : ISODate("2019-01-12T02:34:03.906Z")
}
}
mango做聚合操作
sql
select officeCode,segOrgn,segDstn,count(num) from agent
group officeCode,segOrgn,segDstn,count(num)
order by num
mango
db.getCollection('agent').aggregate([ {$group: {_id : {"officeCode":"$officeCode", "segOrgn":"$segOrgn"，"segDstn:$segDstn"}, num: {$sum : 1}}} ])
java
public List<AgentDO1> aggregate()
{
Aggregation aggregation1 = Aggregation.newAggregation(Aggregation.group("officeCode:$officeCode","segOrgn:$segOrgn","segDstn:$segDstn").count().as("num"));
AggregationResults<AgentDO1> outputTypeCount1 =
mongoTemplate.aggregate(aggregation1, "agent", AgentDO1.class);
List<AgentDO1> tagCount = outputTypeCount1.getMappedResults();
return tagCount;
}
mango有子列表做聚合操作复杂查询
sql
select startDate,endDate,frequentness,count(num),resultList.officeCode, resultList.segOrgn,resultList.segDstn
from agent
where startDate >=? and startDate<?
group by startDate,endDate,frequentness,count(num)
order by num
注意：
resultList是一个文档中的文档，还有就是返回的救国不能保存在resultList下面的属性中！
所以没有办法我只能再从新建一个DO专门负责存储聚合后的结果，然后再再java里转化成我需要的数据结构。
目的：如果不聚合数据直接读取在用java代码聚合会浪费大量的内存以及在传输时候的资源，说白了就是得到处理结果得到的时间可能会高达好几分钟。
java
@Override
public List<SalesExceptionMidResultDO> findAllDataByStartDateAndEndDate(Date startDate, Date endDate) {
// TODO Auto-generated method stub
Aggregation aggregation1 = Aggregation.newAggregation(Aggregation.unwind("resultList"),
Aggregation.match(Criteria.where("endDate").gt(startDate).lt(endDate)),
Aggregation.group("startDate:$startDate","endDate:$endDate","frequentness:$frequentness","eventCode:$resultList.eventCode","officeCode:$resultList.officeCode", "segOrgn:$resultList.segOrgn", "segDstn:$resultList.segDstn").count().as("num"));
AggregationResults<SalesExceptionMidResultDO> outputTypeCount1 = mongoTemplate.aggregate(aggregation1,
"sales_exception", SalesExceptionMidResultDO.class);
List<SalesExceptionMidResultDO> tagCount = outputTypeCount1.getMappedResults();
return tagCount;
}
这里有一个关于时间查询的例子 因为普通的时候和mango里的时间不太一样会差8个小时！
db.getCollection('sales_exception').find({ "startDate" :
{ "$gte" : ISODate("2019-01-08T09:00:00Z"), "$lt" : ISODate("2019-01-08T12:00:00Z") }})
包括函数关联聚合查询
