mongodb聚合及morphia写法

先附上mongodb官方文档

一、mongodb聚合

1、表达式

表达式	含义
$match	数据筛选
$project	翻译为投射 ,即将一个数据结果映射为另一个结果 过程中可以对某些数据进行修改 控制其最终显示的结果
$group	分组
$sort	排序
$limit	取几条
$skip	跳过几条
$sample	随机取出n条记录

2、$match

#match 用于对数据进行筛选
{"$match":{"字段":"条件"}},可以使用任何常用查询操作符$gt,$lt,$in等

#例1、select * from db1.emp where post='teacher';
db.emp.aggregate({"$match":{"post":"teacher"}})

#例2、select * from db1.emp where id > 3;  
db.emp.aggregate(
    {"$match":{"_id":{"$gt":3}}},
)

3、$project

## 先筛选在条件推单时间内的数据，再修改文档，sendCost后修改后的filed
db.OrderDataSummary.aggregate(
    {
        "$match":
            {
                "$and": [
                    { "notifyTime": { "$gte": 1605628800884 } },
                    { "notifyTime": { "$lte": 1605715199884 } }
                ]
            }

    },
    {
        "$project":
            {
                "warehouseCode": "$warehouseCode",
                "doType": "$doType",
                "sendCost": { "$subtract": ["$latestCollectTime", "$earliestOutPayTime"] }
            }
    })

4、$group

## 前两步同上，最后根据warehouseCode、doType分组，类似于mysql中的groupBy, sendCost为project后的filed
db.OrderDataSummary.aggregate(
    {
        "$match":
            {
                "$and": [
                    { "notifyTime": { "$gte": 1605628800884 } },
                    { "notifyTime": { "$lte": 1605715199884 } }
                ]

            }

    },
    {
        "$project":
            {
                "warehouseCode": "$warehouseCode",
                "doType": "$doType",
                "sendCost": { "$subtract": ["$latestCollectTime", "$earliestOutPayTime"] }
            }
    },
    {
        "$group":
            {
                "_id": { "warehouseCode": "$warehouseCode", "doType": "$doType" },
                "avgSendCost": { "$avg": "$sendCost" }
            }
    })

5、$sort、$limit、$skip

{"$sort":{"字段名":1,"字段名":-1}} #1升序，-1降序
{"$limit":n} 
{"$skip":n} #跳过多少个文档
#例1、取平均工资最高的前两个部门

db.emp.aggregate(
{
    "$group":{"_id":"$post","平均工资":{"$avg":"$salary"}}
},
{
    "$sort":{"平均工资":-1}
},
{
    "$limit":2
}
)
#例2、
db.emp.aggregate(
{
    "$group":{"_id":"$post","平均工资":{"$avg":"$salary"}}
},
{
    "$sort":{"平均工资":-1}
},
{
    "$limit":2
},
{
    "$skip":1
}
)

6、$sample

# 随机取出n条记录
#集合users包含的文档如下
{ "_id" : 1, "name" : "dave123", "q1" : true, "q2" : true }
{ "_id" : 2, "name" : "dave2", "q1" : false, "q2" : false  }
{ "_id" : 3, "name" : "ahn", "q1" : true, "q2" : true  }
{ "_id" : 4, "name" : "li", "q1" : true, "q2" : false  }
{ "_id" : 5, "name" : "annT", "q1" : false, "q2" : true  }
{ "_id" : 6, "name" : "li", "q1" : true, "q2" : true  }
{ "_id" : 7, "name" : "ty", "q1" : false, "q2" : true  }

#下述操作时从users集合中随机选取3个文档
db.users.aggregate({"$sample":{"size":3}})
随机选取n个：$sample

二、对应的morphia写法

public void aggregateSendAging(long startNotifyTime, long endNotifyTime) {
        Query<OrderDataSummaryDB> query = orderMDS.createQuery(OrderDataSummaryDB.class);
        query.field("notifyTime").greaterThanOrEq(startNotifyTime);
        query.field("notifyTime").lessThanOrEq(endNotifyTime);

        AggregationPipeline aggregation = orderMDS.createAggregation(OrderDataSummaryDB.class);
        aggregation.match(query)
                .project(Projection.projection("warehouseCode", "warehouseCode"),
                        Projection.projection("doType", "doType"),
                        Projection.projection("sendCost", Projection.subtract("$latestCollectTime", "$earliestOutPayTime")))// 这个参数得加$,不加会当成纯字符串处理
                .group(Group.id(Group.grouping("warehouseCode"), Group.grouping("doType")),
                        Group.grouping("warehouseCode", first("warehouseCode")),
                        Group.grouping("doType", first("doType")),// 这个必须要，不然聚合对象中会没有值
                        Group.grouping("avgSendCost", Group.average("sendCost")));// 这个必须要，不然聚合对象中会没有值


        Iterator<SendAgingAggregateEntry> iterator = aggregation.aggregate(SendAgingAggregateEntry.class);
		// 自行遍历
    }