在SQL语句中如要做统计一般是这种方式:
在MONGODB中的架构图
在SPRING DATA MONGODB中是这样写的:
Cat1UpdateCount.java
SELECT ..,
SUM(
1)
FROM ..
WHERE ..
GROUP BY ..
HAVING ..
SORT ..
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;
}
}
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;
}
}
Video.COLLECTION_NAME为mongo数据表的名称
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 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 + "]";
}
}