mongodb的学习 （3）

聚合函数

- 添加基础数据:
db.local.save({contry:'中国',name:'小明',score:77});
db.local.save({contry:'中国',name:'小红',score:88});
db.local.save({contry:'中国',name:'小张',score:99});
db.local.save({contry:'美国',name:'jack',score:45});
db.local.save({contry:'美国',name:'rose',score:67});
db.local.save({contry:'美国',name:'mick',score:89});

 

- 需要求当前集合的记录数：
- `db.local.find().count();`

- 求最大值
-求整个集合的总成绩
+ db.集合名.聚合({ 组的划分规则{_id:'1',显示内容:{$sum:'$score'}} })
- 求所有人的平均分
- `db.local.aggregate({$group:{_id:'$contry',sumscore:{$avg:'$score' } }});`

- 求按国家分组，求所有国家的总分
- `db.users.aggregate({$group:{_id:'$contry',sumScore:{$sum:'$score'}}});`

 

 

 

 

联合查询

db.orders.insert([
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
{ "_id" : 3 }
]);
db.inventory.insert([
{ "_id" : 1, "sku" : "almonds", description: "product 1", "instock" : 120 },
{ "_id" : 2, "sku" : "bread", description: "product 2", "instock" : 80 },
{ "_id" : 3, "sku" : "cashews", description: "product 3", "instock" : 60 },
{ "_id" : 4, "sku" : "pecans", description: "product 4", "instock" : 70 },
{ "_id" : 5, "sku": null, description: "Incomplete" },
{ "_id" : 6 }
]);
db.orders.aggregate([
{
$lookup:
{
from: "inventory",
localField: "item",
foreignField: "sku",
as: "inventory_docs"
}
}
]);
```

效果：

> show  dbs;
admin    0.000GB
config   0.000GB
local    0.000GB
mini_db  0.001GB
> db.mini_db.insert([
...    { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
...    { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
...    { "_id" : 3  }
... ]);
BulkWriteResult({
        "writeErrors" : [ ],
        "writeConcernErrors" : [ ],
        "nInserted" : 3,
        "nUpserted" : 0,
        "nMatched" : 0,
        "nModified" : 0,
        "nRemoved" : 0,
        "upserted" : [ ]
})
> db.config.insert([
...    { "_id" : 1, "sku" : "almonds", description: "product 1", "instock" : 120 },
...    { "_id" : 2, "sku" : "bread", description: "product 2", "instock" : 80 },
...    { "_id" : 3, "sku" : "cashews", description: "product 3", "instock" : 60 },
...    { "_id" : 4, "sku" : "pecans", description: "product 4", "instock" : 70 },
...    { "_id" : 5, "sku": null, description: "Incomplete" },
...    { "_id" : 6 }
... ]);
BulkWriteResult({
        "writeErrors" : [ ],
        "writeConcernErrors" : [ ],
        "nInserted" : 6,
        "nUpserted" : 0,
        "nMatched" : 0,
        "nModified" : 0,
        "nRemoved" : 0,
        "upserted" : [ ]
})
> db.mini_db.aggregate([
...    {
...      $lookup:
...        {
...          from: "config",
...          localField: "item",
...          foreignField: "sku",
...          as: "config_docs"
...        }
...   }
... ]);
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2, "config_docs" : [ { "_id" : 1, "sku" : "almonds", "description" : "product 1", "instock" : 120 } ] }
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1, "config_docs" : [ { "_id" : 4, "sku" : "pecans", "description" : "product 4", "instock" : 70 } ] }
{ "_id" : 3, "config_docs" : [ { "_id" : 5, "sku" : null, "description" : "Incomplete" }, { "_id" : 6 } ] }
>