在SPRING DATA MONGODB中使用聚合统计查询
原文地址:http://blog.****.net/u010084868/article/details/52622938
- </pre><p>在SQL语句中如要做统计一般是这种方式</p><p></p><pre name="code" class="sql">SELECT ..,SUM(1)
- FROM ..
- WHERE ..
- GROUP BY ..
- HAVING ..
- SORT ..
在MONGODB中的架构图:
在SPRING DATA MONGODB中是这样写的:
- public class VideoRepositoryImpl implements VideoRepositoryCustom{
- private static Logger logger = LoggerFactory.getLogger(VideoRepositoryImpl.class);
- @Autowired
- private MongoTemplate mongoTemplate;
- public List<Cat1UpdateCount> getVideoWithUpdateFrag(List<String> importantCat1List) {
- logger.info(new Date().toString());
- /**
- * db.videos.aggregate(
- [
- { $match: { "frags.isnew" : true } },
- { $unwind: "$frags" },
- { $match: { "frags.isnew" : true } },
- { $group: {
- _id: {cat1:"$cat1"},
- count: { $sum: 1 },
- publishdate2: { $max: "$publishdate"}
- }
- }
- ]
- )
- */
- Aggregation agg = newAggregation(
- project("frags","cat1","publishdate"),//挑选所需的字段
- match(
- Criteria.where("frags.isnew").is(Boolean.TRUE)
- .and("cat1").in(importantCat1List)
- ),//筛选符合条件的记录
- unwind("frags"),//如果有MASTER-ITEM关系的表,需同时JOIN这两张表的,展开子项LIST,且是内链接,即如果父和子的关联ID没有的就不会输出
- match(Criteria.where("frags.isnew").is(Boolean.TRUE)),
- group("cat1")//设置分组字段
- .count().as("updateCount")//增加COUNT为分组后输出的字段
- .last("publishdate").as("publishDate"),//增加publishDate为分组后输出的字段
- project("publishDate","cat1","updateCount")//重新挑选字段
- .and("cat1").previousOperation()//为前一操作所产生的ID FIELD建立别名
- );
- AggregationResults<Cat1UpdateCount> results = mongoTemplate.aggregate(agg, Video.COLLECTION_NAME, Cat1UpdateCount.class);
- List<Cat1UpdateCount> cat1UpdateCountList = results.getMappedResults();
- return cat1UpdateCountList;
- }
- }
Cat1UpdateCount.java
- import java.io.Serializable;
- public class Cat1UpdateCount implements Serializable{
- private static final long serialVersionUID = 4240876746984930098L;
- private String cat1;
- private int updateCount;
- private String publishDate;
- public String getCat1() {
- return cat1;
- }
- public void setCat1(String cat1) {
- this.cat1 = cat1;
- }
- public int getUpdateCount() {
- return updateCount;
- }
- public void setUpdateCount(int updateCount) {
- this.updateCount = updateCount;
- }
- public String getPublishDate() {
- return publishDate;
- }
- public void setPublishDate(String publishDate) {
- this.publishDate = publishDate;
- }
- public String toString() {
- return "Cat1UpdateCount [cat1=" + cat1 + ", updateCount=" + updateCount
- + ", publishDate=" + publishDate + "]";
- }
- }
案例:
- public class ForbiddingUser {
- private long uid;
- private Date deadline;
- public long getUid() {
- return uid;
- }
- public ForbiddingUser setUid(long uid) {
- this.uid = uid;
- return this;
- }
- public Date getDeadline() {
- return deadline;
- }
- public ForbiddingUser setDeadline(Date deadline) {
- this.deadline = deadline;
- return this;
- }
- }
- public List<ForbiddingUser> findForbiddingUserId(String scopeCode, long tenant) {
- String collectionName = mongoOperations.getCollectionName(UserRecord.class);
- Aggregation aggregation = newAggregation(
- match(where("deadline").gt(new Date())
- .and("tenant").is(tenant)
- .and("scopeCode").is(scopeCode)),
- project("uid", "deadline")
- );
- AggregationResults<ForbiddingUser> results = mongoOperations.aggregate(
- aggregation, collectionName, ForbiddingUser.class);
- return results.getMappedResults();
案例:
- @Override
- public int countForbidUserAmount(Date begin, Date end, Set<String> scopeCodes, long tenant) {
- Aggregation aggregation = newAggregation(
- match(where("tenant").is(tenant)
- .and("scopeCode").in(scopeCodes)
- .andOperator(where("time").gte(begin),
- where("time").lte(end))),
- project("uids"),
- unwind("uids"),
- group("uids"),
- MongoUtils.aggregationCount()
- );
- AggregationResults<Map> results = mongoOperations.aggregate(
- aggregation, getForbidCollection(), Map.class);
- return MongoUtils.parseAggregationCount(results);
- }