mongodb（五)：聚合操作（python）

2022-09-16 20:22:22

pymongo的聚合操作

数据类型样式

/* 1 */
{
    "_id" : ObjectId("5e5a32fe2a89d7c2fc05b9fc"),
    "user_id" : "1",
    "amount" : 500,
    "status" : "A"
}

/* 2 */
{
    "_id" : ObjectId("5e5a33092a89d7c2fc05ba07"),
    "user_id" : "1",
    "amount" : 250,
    "status" : "A"
}

/* 3 */
{
    "_id" : ObjectId("5e5a33152a89d7c2fc05ba13"),
    "user_id" : "2",
    "amount" : 200,
    "status" : "A"
}

/* 4 */
{
    "_id" : ObjectId("5e5a33262a89d7c2fc05ba1c"),
    "user_id" : "1",
    "amount" : 300,
    "status" : "B"
}

$match：过滤数据，返回符合条件的数据

    def aggregate(self):
        match_dict = {"$match":{"status":"A"}}
        result = self.db["test_info"].aggregate([match_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)



<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FF1648>
{‘_id‘: ObjectId(‘5e5a32fe2a89d7c2fc05b9fc‘), ‘user_id‘: ‘1‘, ‘amount‘: 500, ‘status‘: ‘A‘}
{‘_id‘: ObjectId(‘5e5a33092a89d7c2fc05ba07‘), ‘user_id‘: ‘1‘, ‘amount‘: 250, ‘status‘: ‘A‘}
{‘_id‘: ObjectId(‘5e5a33152a89d7c2fc05ba13‘), ‘user_id‘: ‘2‘, ‘amount‘: 200, ‘status‘: ‘A‘}

$group：将过滤后的数据进行分组

    def aggregate_match_group(self):
        match_dict = {"$match": {"status": "A"}}
        group_dict = {"$group":{"_id":"$user_id"}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FEF708>
{‘_id‘: ‘2‘}
{‘_id‘: ‘1‘}

# 注意： {"$group":{"_id":"$user_id"}} 分组的名称必须是_id才行换成其他key或者自己重新命名key报错：pymongo.errors.OperationFailure: The field ‘user_id‘ must be an accumulator object

分组后，我们要求，每组的amount的总和是多少？

    def aggregate_match_group(self):
        match_dict = {"$match": {"status": "A"}}
        group_dict = {"$group":{"_id":"$user_id","amount_total":{"$sum":"$amount"}}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)



<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FECD48>
{‘_id‘: ‘2‘, ‘amount_total‘: 200}
{‘_id‘: ‘1‘, ‘amount_total‘: 750}

# 注意：虽然分了两组，但是其实第二组，包含了两个内容

怎么才能显示，每个里面成员的数量呢？

    def aggregate_match_group(self):
        match_dict = {"$match": {"status": "A"}}
        group_dict = {"$group":{"_id":"$user_id","part_quantity":{"$sum":1}}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FF0E08>
{‘_id‘: ‘2‘, ‘part_quantity‘: 1}
{‘_id‘: ‘1‘, ‘part_quantity‘: 2}

# 注意： {"$sum":1} 表示组内有一个，按照1递增， {"$sum":2} 就变成了 {‘_id‘: ‘1‘, ‘part_quantity‘: 4} 也就是按照2递增！

如果我们想知道整个文档里面符合$match过滤条件的文档有多少个呢？

    def aggregate_match_group(self):
        match_dict = {"$match": {"status": "A"}}
        group_dict = {"$group":{"_id":None,"part_quantity":{"$sum":1}}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)

<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FEBFC8>
{‘_id‘: None, ‘part_quantity‘: 3}

如果想知道整个collection里面有多少个文档呢？

    def aggregate_match_group(self):
        match_dict = {"$match": {}}
        group_dict = {"$group":{"_id":None,"part_quantity":{"$sum":1}}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FF1D48>
{‘_id‘: None, ‘part_quantity‘: 4}

将

/* 1 */
{
    "_id" : ObjectId("5e5a41b22a89d7c2fc05c1c5"),
    "user_id" : "1",
    "name" : "科比",
    "hometown" : "费城",
    "age" : "100",
    "gender" : "男"
}

/* 2 */
{
    "_id" : ObjectId("5e5a41db2a89d7c2fc05c1dc"),
    "user_id" : "2",
    "name" : "纳什",
    "hometown" : "加拿大",
    "age" : "100",
    "gender" : "男"
}

/* 3 */
{
    "_id" : ObjectId("5e5a42022a89d7c2fc05c1f1"),
    "user_id" : "3",
    "name" : "蔡徐坤",
    "hometown" : "不详",
    "age" : "100",
    "gender" : "女"
}

/* 4 */
{
    "_id" : ObjectId("5e5a42252a89d7c2fc05c204"),
    "user_id" : "4",
    "name" : "gigi",
    "hometown" : "洛杉矶",
    "age" : "100",
    "gender" : "女"
}

怎么获取不同性别的人的所有user_id呢？

    def aggregate_match_group(self):
        match_dict = {"$match": {}}
        group_dict = {"$group":{"_id":"$gender","user_id":{"$push":"$user_id"}}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


{‘_id‘: ‘女‘, ‘user_id‘: [‘3‘, ‘4‘]}
{‘_id‘: ‘男‘, ‘user_id‘: [‘1‘, ‘2‘]}

# 注意：$push：将结果追加到列表中

 def aggregate_match_group(self):
        match_dict = {"$match": {}}
        group_dict = {"$group":{"_id":"$gender","user_id":{"$push":"$$ROOT"}}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FF0DC8>
{‘_id‘: ‘女‘, ‘user_id‘: [{‘_id‘: ObjectId(‘5e5a42022a89d7c2fc05c1f1‘), ‘user_id‘: ‘3‘, ‘name‘: ‘蔡徐坤‘, ‘hometown‘: ‘不详‘, ‘age‘: ‘100‘, ‘gender‘: ‘女‘}, {‘_id‘: ObjectId(‘5e5a42252a89d7c2fc05c204‘), ‘user_id‘: ‘4‘, ‘name‘: ‘gigi‘, ‘hometown‘: ‘洛杉矶‘, ‘age‘: ‘100‘, ‘gender‘: ‘女‘}]}
{‘_id‘: ‘男‘, ‘user_id‘: [{‘_id‘: ObjectId(‘5e5a41b22a89d7c2fc05c1c5‘), ‘user_id‘: ‘1‘, ‘name‘: ‘科比‘, ‘hometown‘: ‘费城‘, ‘age‘: ‘100‘, ‘gender‘: ‘男‘}, {‘_id‘: ObjectId(‘5e5a41db2a89d7c2fc05c1dc‘), ‘user_id‘: ‘2‘, ‘name‘: ‘纳什‘, ‘hometown‘: ‘加拿大‘, ‘age‘: ‘100‘, ‘gender‘: ‘男‘}]}

# $$sort将整个文档放入列表中

$gorup分组条件的 "_id" 多条件分组

    def aggregate_match_group(self):
        match_dict = {"$match": {}}
        group_dict = {"$group":{"_id":{"user_id":"$user_id","name":"$name","hometown":"$hometown","age":"$age","gender":"$gender"}}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


{‘_id‘: {‘user_id‘: ‘4‘, ‘name‘: ‘gigi‘, ‘hometown‘: ‘洛杉矶‘, ‘age‘: ‘100‘, ‘gender‘: ‘女‘}}
{‘_id‘: {‘user_id‘: ‘3‘, ‘name‘: ‘蔡徐坤‘, ‘hometown‘: ‘不详‘, ‘age‘: ‘100‘, ‘gender‘: ‘女‘}}
{‘_id‘: {‘user_id‘: ‘2‘, ‘name‘: ‘纳什‘, ‘hometown‘: ‘加拿大‘, ‘age‘: ‘100‘, ‘gender‘: ‘男‘}}
{‘_id‘: {‘user_id‘: ‘1‘, ‘name‘: ‘科比‘, ‘hometown‘: ‘费城‘, ‘age‘: ‘100‘, ‘gender‘: ‘男‘}}

   def aggregate_match_group(self):
        match_dict = {"$match": {}}
        group_dict = {"$group":{"_id":{"name":"$name","age":"$age","gender":"$gender"}}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)



<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002D4EE48>
{‘_id‘: {‘name‘: ‘gigi‘, ‘age‘: ‘100‘, ‘gender‘: ‘女‘}}
{‘_id‘: {‘name‘: ‘蔡徐坤‘, ‘age‘: ‘100‘, ‘gender‘: ‘女‘}}
{‘_id‘: {‘name‘: ‘纳什‘, ‘age‘: ‘100‘, ‘gender‘: ‘男‘}}
{‘_id‘: {‘name‘: ‘科比‘, ‘age‘: ‘100‘, ‘gender‘: ‘男‘}}

多条件分组，并统计数量

    def aggregate_match_group(self):
        match_dict = {"$match": {}}
        group_dict = {"$group":{"_id":{"年龄":"$age","性别":"$gender"},"人数":{"$sum":1}}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FECD88>
{‘_id‘: {‘年龄‘: ‘100‘, ‘性别‘: ‘女‘}, ‘人数‘: 2}
{‘_id‘: {‘年龄‘: ‘100‘, ‘性别‘: ‘男‘}, ‘人数‘: 2}

对查询数据进行修改

/* 1 */
{
    "_id" : ObjectId("5e5a41b22a89d7c2fc05c1c5"),
    "user_id" : "1",
    "name" : "科比",
    "hometown" : "费城",
    "age" : "42",
    "gender" : "男"
}

/* 2 */
{
    "_id" : ObjectId("5e5a41db2a89d7c2fc05c1dc"),
    "user_id" : "2",
    "name" : "纳什",
    "hometown" : "加拿大",
    "age" : "40",
    "gender" : "男"
}

/* 3 */
{
    "_id" : ObjectId("5e5a42022a89d7c2fc05c1f1"),
    "user_id" : "3",
    "name" : "蔡徐坤",
    "hometown" : "不详",
    "age" : "3",
    "gender" : "女"
}

/* 4 */
{
    "_id" : ObjectId("5e5a42252a89d7c2fc05c204"),
    "user_id" : "4",
    "name" : "gigi",
    "hometown" : "洛杉矶",
    "age" : "14",
    "gender" : "女"
}

获取年龄年龄大于3岁的信息

$match

    def aggregate_match_group(self):
        match_dict = {"$match":{"age":{"$gt":"3"}}}
        result = self.db["test_info"].aggregate([match_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)

<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FF1C48>
{‘_id‘: ObjectId(‘5e5a41b22a89d7c2fc05c1c5‘), ‘user_id‘: ‘1‘, ‘name‘: ‘科比‘, ‘hometown‘: ‘费城‘, ‘age‘: ‘42‘, ‘gender‘: ‘男‘}
{‘_id‘: ObjectId(‘5e5a41db2a89d7c2fc05c1dc‘), ‘user_id‘: ‘2‘, ‘name‘: ‘纳什‘, ‘hometown‘: ‘加拿大‘, ‘age‘: ‘40‘, ‘gender‘: ‘男‘}

# 查询错误：gigi的年龄也是大于3，不显示，我们将数据里面的年龄类型从str换成int类型，继续查看

    def aggregate_match_group(self):
        match_dict = {"$match":{"age":{"$gt":3}}}
        result = self.db["test_info"].aggregate([match_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FF1C88>
{‘_id‘: ObjectId(‘5e5a41b22a89d7c2fc05c1c5‘), ‘user_id‘: ‘1‘, ‘name‘: ‘科比‘, ‘hometown‘: ‘费城‘, ‘age‘: 42, ‘gender‘: ‘男‘}
{‘_id‘: ObjectId(‘5e5a41db2a89d7c2fc05c1dc‘), ‘user_id‘: ‘2‘, ‘name‘: ‘纳什‘, ‘hometown‘: ‘加拿大‘, ‘age‘: 40, ‘gender‘: ‘男‘}
{‘_id‘: ObjectId(‘5e5a42252a89d7c2fc05c204‘), ‘user_id‘: ‘4‘, ‘name‘: ‘gigi‘, ‘hometown‘: ‘洛杉矶‘, ‘age‘: 14, ‘gender‘: ‘女‘}

# 查询正确：因此当进行比较值的操作，注意字段类型必须是int类型

获取年龄大于3岁，不同性别的人数

 def aggregate_match_group(self):
        match_dict = {"$match":{"age":{"$gt":3}}}
        group_dict = {"$group":{"_id":"$gender","数量":{"$sum":1}}}
        result = self.db["test_info"].aggregate([match_dict,group_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FF1C88>
{‘_id‘: ‘女‘, ‘数量‘: 1}
{‘_id‘: ‘男‘, ‘数量‘: 2}

$preject类型与find里面的limit，需要显示的设置为1，不显示的设置为0

    def aggregate_project(self):
        project_dict = {"$project":{"_id":0,"name":1,"hometown":1}}
        result = self.db["test_info"].aggregate([project_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FE9F88>
{‘name‘: ‘科比‘, ‘hometown‘: ‘费城‘}
{‘name‘: ‘纳什‘, ‘hometown‘: ‘加拿大‘}
{‘name‘: ‘蔡徐坤‘, ‘hometown‘: ‘不详‘}
{‘name‘: ‘gigi‘, ‘hometown‘: ‘洛杉矶‘}

# 注意：其他字段没有赋值1就不显示，但是_id字段除外，不设置，默认显示

    def aggregate_project(self):
        group_dict = {"$group":{"_id":"$gender","quantity":{"$sum":1}}}
        project_dict = {"$project":{"_id":1,"quantity":1}}
        result = self.db["test_info"].aggregate([group_dict,project_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


{‘_id‘: ‘女‘, ‘quantity‘: 2}
{‘_id‘: ‘男‘, ‘quantity‘: 2}

$sort：排序命令

年龄从小到大返回排序好的数据

  def aggregate_sort(self):
        sort_dict = {"$sort":{"age":1}}
        result = self.db["test_info"].aggregate([sort_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000003012148>
{‘_id‘: ObjectId(‘5e5a42022a89d7c2fc05c1f1‘), ‘user_id‘: ‘3‘, ‘name‘: ‘蔡徐坤‘, ‘hometown‘: ‘不详‘, ‘age‘: 3, ‘gender‘: ‘女‘}
{‘_id‘: ObjectId(‘5e5a42252a89d7c2fc05c204‘), ‘user_id‘: ‘4‘, ‘name‘: ‘gigi‘, ‘hometown‘: ‘洛杉矶‘, ‘age‘: 14, ‘gender‘: ‘女‘}
{‘_id‘: ObjectId(‘5e5a41db2a89d7c2fc05c1dc‘), ‘user_id‘: ‘2‘, ‘name‘: ‘纳什‘, ‘hometown‘: ‘加拿大‘, ‘age‘: 40, ‘gender‘: ‘男‘}
{‘_id‘: ObjectId(‘5e5a41b22a89d7c2fc05c1c5‘), ‘user_id‘: ‘1‘, ‘name‘: ‘科比‘, ‘hometown‘: ‘费城‘, ‘age‘: 42, ‘gender‘: ‘男‘}

年龄从大到小返回排序好的数据

    def aggregate_sort(self):
        sort_dict = {"$sort":{"age":-1}}
        result = self.db["test_info"].aggregate([sort_dict])
        print(type(result))
        print(result)
        for i in result:
            print(i)


<class ‘pymongo.command_cursor.CommandCursor‘>
<pymongo.command_cursor.CommandCursor object at 0x0000000002FE5F88>
{‘_id‘: ObjectId(‘5e5a41b22a89d7c2fc05c1c5‘), ‘user_id‘: ‘1‘, ‘name‘: ‘科比‘, ‘hometown‘: ‘费城‘, ‘age‘: 42, ‘gender‘: ‘男‘}
{‘_id‘: ObjectId(‘5e5a41db2a89d7c2fc05c1dc‘), ‘user_id‘: ‘2‘, ‘name‘: ‘纳什‘, ‘hometown‘: ‘加拿大‘, ‘age‘: 40, ‘gender‘: ‘男‘}
{‘_id‘: ObjectId(‘5e5a42252a89d7c2fc05c204‘), ‘user_id‘: ‘4‘, ‘name‘: ‘gigi‘, ‘hometown‘: ‘洛杉矶‘, ‘age‘: 14, ‘gender‘: ‘女‘}
{‘_id‘: ObjectId(‘5e5a42022a89d7c2fc05c1f1‘), ‘user_id‘: ‘3‘, ‘name‘: ‘蔡徐坤‘, ‘hometown‘: ‘不详‘, ‘age‘: 3, ‘gender‘: ‘女‘}

数据类型

/* 10 */
{
    "_id" : ObjectId("5e58c4102a89d7c2fc051ba4"),
    "vaccine_name" : "破伤风",
    "vaccine_id" : "2",
    "user_id" : "110",
    "farm_id" : "110",
    "fold_id" : "110",
    "farm_name" : "110牧场",
    "fold_name" : "110圈舍",
    "animal_number" : "133",
    "equipment_number" : "133",
    "type" : "goat",
    "inject_quantity" : "100",
    "vaccine_time" : ISODate("2020-06-15T15:45:22.000Z"),
    "is_delete" : "0"
}

/* 11 */
{
    "_id" : ObjectId("5e5a510d2a89d7c2fc05cac7"),
    "vaccine_name" : "破伤风",
    "vaccine_id" : "2",
    "user_id" : "110",
    "farm_id" : "110",
    "fold_id" : "110",
    "farm_name" : "110牧场",
    "fold_name" : "110圈舍",
    "animal_number" : "133",
    "equipment_number" : "133",
    "type" : "goat",
    "inject_quantity" : "100",
    "vaccine_time" : ISODate("2020-07-15T15:45:22.000Z"),
    "is_delete" : "0"
}

/* 12 */
{
    "_id" : ObjectId("5e5a511b2a89d7c2fc05cad2"),
    "vaccine_name" : "破伤风",
    "vaccine_id" : "2",
    "user_id" : "110",
    "farm_id" : "110",
    "fold_id" : "110",
    "farm_name" : "110牧场",
    "fold_name" : "110圈舍",
    "animal_number" : "133",
    "equipment_number" : "133",
    "type" : "goat",
    "inject_quantity" : "100",
    "vaccine_time" : ISODate("2020-08-15T15:45:22.000Z"),
    "is_delete" : "0"
}

/* 13 */
{
    "_id" : ObjectId("5e5a51282a89d7c2fc05cada"),
    "vaccine_name" : "破伤风",
    "vaccine_id" : "2",
    "user_id" : "110",
    "farm_id" : "110",
    "fold_id" : "110",
    "farm_name" : "110牧场",
    "fold_name" : "110圈舍",
    "animal_number" : "133",
    "equipment_number" : "133",
    "type" : "goat",
    "inject_quantity" : "100",
    "vaccine_time" : ISODate("2020-10-15T15:45:22.000Z"),
    "is_delete" : "0"
}

/* 14 */
{
    "_id" : ObjectId("5e5a51422a89d7c2fc05caec"),
    "vaccine_name" : "破伤风",
    "vaccine_id" : "2",
    "user_id" : "110",
    "farm_id" : "110",
    "fold_id" : "110",
    "farm_name" : "110牧场",
    "fold_name" : "110圈舍",
    "animal_number" : "133",
    "equipment_number" : "133",
    "type" : "goat",
    "inject_quantity" : "100",
    "vaccine_time" : ISODate("2020-11-15T15:45:22.000Z"),
    "is_delete" : "0"
}

/* 15 */
{
    "_id" : ObjectId("5e5a514d2a89d7c2fc05caf5"),
    "vaccine_name" : "破伤风",
    "vaccine_id" : "2",
    "user_id" : "110",
    "farm_id" : "110",
    "fold_id" : "110",
    "farm_name" : "110牧场",
    "fold_name" : "110圈舍",
    "animal_number" : "133",
    "equipment_number" : "133",
    "type" : "goat",
    "inject_quantity" : "100",
    "vaccine_time" : ISODate("2020-12-15T15:45:22.000Z"),
    "is_delete" : "0"
}

需求：获取equipment_number=13，vaccine_time按照时间倒叙排列，返回数据

    def get_all_by_time_object(self,collection):
        """按照时间类型排序 vaccine_time的类型是 ISODate("2020-12-15T15:45:22.000Z")类型"""
        if self.connect_result:
            match_dict = {"$match":{"equipment_number":"133","type":"goat"}}
            sort_dict = {"$sort":{"vaccine_time":-1}}
            result = self.db[collection].aggregate([match_dict,sort_dict])
            for i in result:
                print(i)

{‘_id‘: ObjectId(‘5e5a514d2a89d7c2fc05caf5‘), ‘vaccine_name‘: ‘破伤风‘, ‘vaccine_id‘: ‘2‘, ‘user_id‘: ‘110‘, ‘farm_id‘: ‘110‘, ‘fold_id‘: ‘110‘, ‘farm_name‘: ‘110牧场‘, ‘fold_name‘: ‘110圈舍‘, ‘animal_number‘: ‘133‘, ‘equipment_number‘: ‘133‘, ‘type‘: ‘goat‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 12, 15, 15, 45, 22), ‘is_delete‘: ‘0‘}
{‘_id‘: ObjectId(‘5e5a51422a89d7c2fc05caec‘), ‘vaccine_name‘: ‘破伤风‘, ‘vaccine_id‘: ‘2‘, ‘user_id‘: ‘110‘, ‘farm_id‘: ‘110‘, ‘fold_id‘: ‘110‘, ‘farm_name‘: ‘110牧场‘, ‘fold_name‘: ‘110圈舍‘, ‘animal_number‘: ‘133‘, ‘equipment_number‘: ‘133‘, ‘type‘: ‘goat‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 11, 15, 15, 45, 22), ‘is_delete‘: ‘0‘}
{‘_id‘: ObjectId(‘5e5a51282a89d7c2fc05cada‘), ‘vaccine_name‘: ‘破伤风‘, ‘vaccine_id‘: ‘2‘, ‘user_id‘: ‘110‘, ‘farm_id‘: ‘110‘, ‘fold_id‘: ‘110‘, ‘farm_name‘: ‘110牧场‘, ‘fold_name‘: ‘110圈舍‘, ‘animal_number‘: ‘133‘, ‘equipment_number‘: ‘133‘, ‘type‘: ‘goat‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 10, 15, 15, 45, 22), ‘is_delete‘: ‘0‘}
{‘_id‘: ObjectId(‘5e5a511b2a89d7c2fc05cad2‘), ‘vaccine_name‘: ‘破伤风‘, ‘vaccine_id‘: ‘2‘, ‘user_id‘: ‘110‘, ‘farm_id‘: ‘110‘, ‘fold_id‘: ‘110‘, ‘farm_name‘: ‘110牧场‘, ‘fold_name‘: ‘110圈舍‘, ‘animal_number‘: ‘133‘, ‘equipment_number‘: ‘133‘, ‘type‘: ‘goat‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 8, 15, 15, 45, 22), ‘is_delete‘: ‘0‘}
{‘_id‘: ObjectId(‘5e5a510d2a89d7c2fc05cac7‘), ‘vaccine_name‘: ‘破伤风‘, ‘vaccine_id‘: ‘2‘, ‘user_id‘: ‘110‘, ‘farm_id‘: ‘110‘, ‘fold_id‘: ‘110‘, ‘farm_name‘: ‘110牧场‘, ‘fold_name‘: ‘110圈舍‘, ‘animal_number‘: ‘133‘, ‘equipment_number‘: ‘133‘, ‘type‘: ‘goat‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 7, 15, 15, 45, 22), ‘is_delete‘: ‘0‘}
{‘_id‘: ObjectId(‘5e58c4102a89d7c2fc051ba4‘), ‘vaccine_name‘: ‘破伤风‘, ‘vaccine_id‘: ‘2‘, ‘user_id‘: ‘110‘, ‘farm_id‘: ‘110‘, ‘fold_id‘: ‘110‘, ‘farm_name‘: ‘110牧场‘, ‘fold_name‘: ‘110圈舍‘, ‘animal_number‘: ‘133‘, ‘equipment_number‘: ‘133‘, ‘type‘: ‘goat‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 6, 15, 15, 45, 22), ‘is_delete‘: ‘0‘}

过滤掉一些字段，选择性显示需要的字段

    def get_all_by_time_object(self,collection):
        """按照时间类型排序 vaccine_time的类型是 ISODate("2020-12-15T15:45:22.000Z")类型"""
        if self.connect_result:
            match_dict = {"$match":{"equipment_number":"133","type":"goat"}}
            sort_dict = {"$sort":{"vaccine_time":-1}}
            project_dict = {"$project":{"_id":0,"animal_number":1,"inject_quantity":1,"vaccine_time":1,"vaccine_name":1}}
            result = self.db[collection].aggregate([match_dict,sort_dict,project_dict])
            for i in result:
                print(i)

{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 12, 15, 15, 45, 22)}
{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 11, 15, 15, 45, 22)}
{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 10, 15, 15, 45, 22)}
{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 8, 15, 15, 45, 22)}
{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 7, 15, 15, 45, 22)}
{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 6, 15, 15, 45, 22)}

$limit ：限制返回的条数

    def get_all_by_limit(self,collection):
        if self.connect_result:
            match_dict = {"$match": {"equipment_number": "133", "type": "goat"}}
            sort_dict = {"$sort": {"vaccine_time": -1}}
            project_dict = {
                "$project": {"_id": 0, "animal_number": 1, "inject_quantity": 1, "vaccine_time": 1, "vaccine_name": 1}}
            limit_dict = {"$limit":2}
            result = self.db[collection].aggregate([match_dict, sort_dict, project_dict,limit_dict])
            for i in result:
                print(i)



{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 12, 15, 15, 45, 22)}
{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 11, 15, 15, 45, 22)}

$skip：跳过指定数量，返回剩余数量的内容

    def get_all_by_skip(self,collection):
        if self.connect_result:
            match_dict = {"$match": {"equipment_number": "133", "type": "goat"}}
            sort_dict = {"$sort": {"vaccine_time": -1}}
            project_dict = {
                "$project": {"_id": 0, "animal_number": 1, "inject_quantity": 1, "vaccine_time": 1, "vaccine_name": 1}}
            skip_dict = {"$skip":2}
            result = self.db[collection].aggregate([match_dict, sort_dict, project_dict,skip_dict])
            for i in result:
                print(i)



{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 10, 15, 15, 45, 22)}
{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 8, 15, 15, 45, 22)}
{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 7, 15, 15, 45, 22)}
{‘vaccine_name‘: ‘破伤风‘, ‘animal_number‘: ‘133‘, ‘inject_quantity‘: ‘100‘, ‘vaccine_time‘: datetime.datetime(2020, 6, 15, 15, 45, 22)}

$ match过滤条件的或查询

数据结构如下

/* 1 */
{
    "_id" : ObjectId("5e5a41b22a89d7c2fc05c1c5"),
    "user_id" : "1",
    "name" : "科比",
    "hometown" : "费城",
    "age" : 42,
    "gender" : "男"
}

/* 2 */
{
    "_id" : ObjectId("5e5a41db2a89d7c2fc05c1dc"),
    "user_id" : "2",
    "name" : "纳什",
    "hometown" : "加拿大",
    "age" : 40,
    "gender" : "男"
}

/* 3 */
{
    "_id" : ObjectId("5e5a42022a89d7c2fc05c1f1"),
    "user_id" : "3",
    "name" : "蔡徐坤",
    "hometown" : "不详",
    "age" : 3,
    "gender" : "女"
}

/* 4 */
{
    "_id" : ObjectId("5e5a42252a89d7c2fc05c204"),
    "user_id" : "4",
    "name" : "gigi",
    "hometown" : "洛杉矶",
    "age" : 14,
    "gender" : "女"
}

查询年龄大于小于14岁或者大于40岁的人的信息

    def get_all_by_or_match(self,collection):
        if self.connect_result:
            match_dict = {"$match": {"$or":[{"age":{"$gt":40}},{"age":{"$lt":14}}]}}
            result = self.db[collection].aggregate([match_dict])
            for i in result:
                print(i)


{‘_id‘: ObjectId(‘5e5a41b22a89d7c2fc05c1c5‘), ‘user_id‘: ‘1‘, ‘name‘: ‘科比‘, ‘hometown‘: ‘费城‘, ‘age‘: 42, ‘gender‘: ‘男‘}
{‘_id‘: ObjectId(‘5e5a42022a89d7c2fc05c1f1‘), ‘user_id‘: ‘3‘, ‘name‘: ‘蔡徐坤‘, ‘hometown‘: ‘不详‘, ‘age‘: 3, ‘gender‘: ‘女‘}

$ match过滤条件的范围查询

    def get_all_by_in_match(self,collection):
        if self.connect_result:
            match_dict = {"$match": {"hometown":{"$in":["加拿大","洛杉矶","费城"]}}}
            result = self.db[collection].aggregate([match_dict])
            for i in result:
                print(i)



{‘_id‘: ObjectId(‘5e5a41b22a89d7c2fc05c1c5‘), ‘user_id‘: ‘1‘, ‘name‘: ‘科比‘, ‘hometown‘: ‘费城‘, ‘age‘: 42, ‘gender‘: ‘男‘}
{‘_id‘: ObjectId(‘5e5a41db2a89d7c2fc05c1dc‘), ‘user_id‘: ‘2‘, ‘name‘: ‘纳什‘, ‘hometown‘: ‘加拿大‘, ‘age‘: 40, ‘gender‘: ‘男‘}
{‘_id‘: ObjectId(‘5e5a42252a89d7c2fc05c204‘), ‘user_id‘: ‘4‘, ‘name‘: ‘gigi‘, ‘hometown‘: ‘洛杉矶‘, ‘age‘: 14, ‘gender‘: ‘女‘}

查询年龄在[14,4,3]内的人的信息

    def get_all_by_in_match(self,collection):
        if self.connect_result:
            match_dict = {"$match":{"age":{"$in":[14,40,3]}}}
            result = self.db[collection].aggregate([match_dict])
            for i in result:
                print(i)


{‘_id‘: ObjectId(‘5e5a41db2a89d7c2fc05c1dc‘), ‘user_id‘: ‘2‘, ‘name‘: ‘纳什‘, ‘hometown‘: ‘加拿大‘, ‘age‘: 40, ‘gender‘: ‘男‘}
{‘_id‘: ObjectId(‘5e5a42022a89d7c2fc05c1f1‘), ‘user_id‘: ‘3‘, ‘name‘: ‘蔡徐坤‘, ‘hometown‘: ‘不详‘, ‘age‘: 3, ‘gender‘: ‘女‘}
{‘_id‘: ObjectId(‘5e5a42252a89d7c2fc05c204‘), ‘user_id‘: ‘4‘, ‘name‘: ‘gigi‘, ‘hometown‘: ‘洛杉矶‘, ‘age‘: 14, ‘gender‘: ‘女‘}

数据结构如下

/* 1 */
{
    "_id" : ObjectId("5e5b99052a89d7c2fc0653a0"),
    "farm_id" : "1",
    "animal_number" : "1",
    "milking_time" : ISODate("2020-02-01T15:45:22.000Z"),
    "milking_quantity" : 100
}

/* 2 */
{
    "_id" : ObjectId("5e5b993d2a89d7c2fc0653cf"),
    "farm_id" : "1",
    "animal_number" : "2",
    "milking_time" : ISODate("2020-02-01T18:46:33.000Z"),
    "milking_quantity" : 120
}

/* 3 */
{
    "_id" : ObjectId("5e5b996f2a89d7c2fc0653eb"),
    "farm_id" : "1",
    "animal_number" : "1",
    "milking_time" : ISODate("2020-02-02T08:45:22.000Z"),
    "milking_quantity" : 150
}

/* 4 */
{
    "_id" : ObjectId("5e5b9a042a89d7c2fc06543e"),
    "farm_id" : "1",
    "animal_number" : "2",
    "milking_time" : ISODate("2020-02-02T09:33:22.000Z"),
    "milking_quantity" : 90
}

/* 5 */
{
    "_id" : ObjectId("5e5b9a2b2a89d7c2fc065455"),
    "farm_id" : "1",
    "animal_number" : "1",
    "milking_time" : ISODate("2020-02-03T10:30:30.000Z"),
    "milking_quantity" : 98
}

/* 6 */
{
    "_id" : ObjectId("5e5b9a452a89d7c2fc065464"),
    "farm_id" : "1",
    "animal_number" : "2",
    "milking_time" : ISODate("2020-02-03T11:45:22.000Z"),
    "milking_quantity" : 110
}

需求：牧场1下的所有羊，每天的产奶量平均值是多少，每三天的产奶量平均值是多少？

    def get_all_by_avg_milk(self,collection):
        if self.connect_result:
            s_time = datetime(2020,2,1,00,00,00)
            e_time = datetime(2020,2,1,23,59,59)
            match_dict = {"$match": {"farm_id":"1","milking_time":{"$gte":s_time,"$lte":e_time}}}
            group_dict = {"$group":{"_id":None,"2020-2-1日产奶量平均值为":{"$avg":"$milking_quantity"}}}
            result = self.db[collection].aggregate([match_dict,group_dict])
            for i in result:
                print(i)


{‘_id‘: None, ‘2020-2-1日产奶量平均值为‘: 110.0}

    def get_all_by_avg_milk(self,collection):
        if self.connect_result:
            s_time = datetime(2020,2,1,00,00,00)
            e_time = datetime(2020,2,3,23,59,59)
            match_dict = {"$match": {"farm_id":"1","milking_time":{"$gte":s_time,"$lte":e_time}}}
            group_dict = {"$group":{"_id":None,"三天产奶量平均值为":{"$avg":"$milking_quantity"}}}
            result = self.db[collection].aggregate([match_dict,group_dict])
            for i in result:
                print(i)


{‘_id‘: None, ‘三天产奶量平均值为‘: 111.33333333333333}

(100 + 120 + 150 + 90 + 98 + 110 )/3 = 222.6666

mogno给的结果是 222.666/2 = 111.3333 分组后一共取出六条数据，除以6了，造成结果错误，怎么解决呢？

先求总产量，然后分布计算结果

    def get_all_by_avg_milk(self,collection):
        if self.connect_result:
            s_time = datetime(2020,2,1,00,00,00)
            e_time = datetime(2020,2,3,23,59,59)
            match_dict = {"$match": {"farm_id":"1","milking_time":{"$gte":s_time,"$lte":e_time}}}
            group_dict = {"$group":{"_id":None,"三天产奶量总和为":{"$sum":"$milking_quantity"}}}
            result = self.db[collection].aggregate([match_dict,group_dict])
            for i in result:
                print(i)
                print("三天产奶量的平均值是%s"%(str(i.get("三天产奶量总和为")/3)))


{‘_id‘: None, ‘三天产奶量总和为‘: 668}
三天产奶量的平均值是222.66666666666666

需求：输出2020-2-1号产奶量最低的羊的编号和最高的羊的编号

    def get_all_by_avg_milk(self,collection):
        if self.connect_result:
            s_time = datetime(2020,2,1,00,00,00)
            e_time = datetime(2020,2,1,23,59,59)
            match_dict = {"$match": {"farm_id":"1","milking_time":{"$gte":s_time,"$lte":e_time}}}
            group_dict = {"$group":{"_id":None,"max_quantity":{"$max":"$milking_quantity"}}}
            result = self.db[collection].aggregate([match_dict,group_dict])
            for i in result:
                print(i)


{‘_id‘: None, ‘max_quantity‘: 120}

max_quantity查库获得animal_nubmer

$unwind：针对文档里面的数组进行操作

数据类型

{
    "_id" : ObjectId("5e5ccf222a89d7c2fc06e9d0"),
    "user_id" : "A",
    "data" : [ 
        {
            "city" : "beijing",
            "income" : 100000
        }, 
        {
            "city" : "shanghai",
            "income" : 150000
        }, 
        {
            "city" : "shanghai",
            "income" : 150000
        }
    ]
}

结果是：将列表中的每一个内容和外面的键重新组合形成一条数据

    def find_list(self,collection):
        unwind_dict = {"$unwind":"$data"}
        result = self.db[collection].aggregate([unwind_dict])
        print(result)
        print(type(result))
        for i in result:
            print(i)


<pymongo.command_cursor.CommandCursor object at 0x0000000002D58488>
<class ‘pymongo.command_cursor.CommandCursor‘>
{‘_id‘: ObjectId(‘5e5ccf222a89d7c2fc06e9d0‘), ‘user_id‘: ‘A‘, ‘data‘: {‘city‘: ‘beijing‘, ‘income‘: 100000}}
{‘_id‘: ObjectId(‘5e5ccf222a89d7c2fc06e9d0‘), ‘user_id‘: ‘A‘, ‘data‘: {‘city‘: ‘shanghai‘, ‘income‘: 150000}}
{‘_id‘: ObjectId(‘5e5ccf222a89d7c2fc06e9d0‘), ‘user_id‘: ‘A‘, ‘data‘: {‘city‘: ‘shanghai‘, ‘income‘: 150000}}

需求，计算A在上海收入的总和是多少？

    def find_list_for_sum(self,collection):
        match_dict1 = {"$match":{"user_id":"A"}}
        unwind_dict = {"$unwind":"$data"}
        match_dict2 = {"$match":{"data.city":"shanghai"}}
        group_dict = {"$group":{"_id":"$data.city","收入总和":{"$sum":"$data.income"}}}
        result = self.db[collection].aggregate([match_dict1,unwind_dict,match_dict2,group_dict])
        print(result)
        print(type(result))
        for i in result:
            print(i)


<pymongo.command_cursor.CommandCursor object at 0x0000000002FE9FC8>
<class ‘pymongo.command_cursor.CommandCursor‘>
{‘_id‘: ‘shanghai‘, ‘收入总和‘: 300000}

# 补充一下，如果是列表，怎么给列表里面添加数据，怎么给从列表里面删除数据呢？

需求：给上面的数据的data列表中添加一条数据 {"city":"shenzhen","income":30000}

    def add_to_list(self,collection):
        query_dict = dict()
        query_dict["user_id"] = "A"
        result = self.db[collection].update(query_dict,{"$addToSet":{"data":{"city":"shenzhen","income":30000}}})
        if result.get("nModified") == 1:
            print("添加成功")
            

{
    "_id" : ObjectId("5e5ccf222a89d7c2fc06e9d0"),
    "user_id" : "A",
    "data" : [ 
        {
            "city" : "beijing",
            "income" : 100000
        }, 
        {
            "city" : "shanghai",
            "income" : 150000
        }, 
        {
            "city" : "shanghai",
            "income" : 150000
        }, 
        {
            "city" : "shenzhen",
            "income" : 30000
        }
    ]
}

# 问题：这种天界方式：不能向data列表里面添加相同的键值对，连续插入{"city":"shenzhen","income":20000}，并不会成功！

# TODO 待续

2020-3-20

需求：多个牧场下，每一个羊的饮水总数小于2的，返回其equipment_number

数据样式：

/* 1 */
{
    "_id" : ObjectId("5e746c378fc1e7a977e6be06"),
    "farm_id" : "123",
    "farm_name" : "测试",
    "fold_id" : "123",
    "fold_name" : "测试",
    "device_number" : "123",
    "equipment_number" : "123",
    "animal_number" : "123",
    "drink_quantity" : 100,
    "type" : "goat",
    "drink_time" : ISODate("2020-03-20T15:09:43.454Z")
}

/* 2 */
{
    "_id" : ObjectId("5e746c448fc1e7a977e6be07"),
    "farm_id" : "123",
    "farm_name" : "测试",
    "fold_id" : "123",
    "fold_name" : "测试",
    "device_number" : "123",
    "equipment_number" : "123",
    "animal_number" : "123",
    "drink_quantity" : 200,
    "type" : "goat",
    "drink_time" : ISODate("2020-03-20T15:09:56.139Z")
}

/* 3 */
{
    "_id" : ObjectId("5e746c488fc1e7a977e6be08"),
    "farm_id" : "123",
    "farm_name" : "测试",
    "fold_id" : "123",
    "fold_name" : "测试",
    "device_number" : "123",
    "equipment_number" : "123",
    "animal_number" : "123",
    "drink_quantity" : 300,
    "type" : "goat",
    "drink_time" : ISODate("2020-03-20T15:10:00.115Z")
}

/* 4 */
{
    "_id" : ObjectId("5e7474b1e47b4ffc8fbd4d3b"),
    "farm_id" : "123",
    "farm_name" : "测试",
    "fold_id" : "123",
    "fold_name" : "测试",
    "device_number" : "123",
    "equipment_number" : "124",
    "animal_number" : "124",
    "drink_quantity" : 100,
    "type" : "goat",
    "drink_time" : ISODate("2020-03-20T15:45:53.727Z")
}

/* 5 */
{
    "_id" : ObjectId("5e7474b7e47b4ffc8fbd4d3c"),
    "farm_id" : "123",
    "farm_name" : "测试",
    "fold_id" : "123",
    "fold_name" : "测试",
    "device_number" : "123",
    "equipment_number" : "124",
    "animal_number" : "124",
    "drink_quantity" : 200,
    "type" : "goat",
    "drink_time" : ISODate("2020-03-20T15:45:59.674Z")
}

/* 6 */
{
    "_id" : ObjectId("5e7474c0e47b4ffc8fbd4d3d"),
    "farm_id" : "123",
    "farm_name" : "测试",
    "fold_id" : "123",
    "fold_name" : "测试",
    "device_number" : "123",
    "equipment_number" : "125",
    "animal_number" : "125",
    "drink_quantity" : 100,
    "type" : "goat",
    "drink_time" : ISODate("2020-03-20T15:46:08.953Z")
}

/* 7 */
{
    "_id" : ObjectId("5e748632217a21f9adb48c12"),
    "farm_id" : "125",
    "farm_name" : "测试",
    "fold_id" : "125",
    "fold_name" : "测试",
    "device_number" : "125",
    "equipment_number" : "125",
    "animal_number" : "125",
    "drink_quantity" : 100,
    "type" : "goat",
    "drink_time" : ISODate("2020-03-20T17:00:34.398Z")
}

查询 123 125牧场下，饮水次数小于2的equipment_mumber，饮水次数就是有一条数据，就是饮水一次

    def aggregate_many(self):
        # 获取所有牧场下，饮水次数小于2的羊的equipment_number
        match_dict = {"$match":{"farm_id":{"$in":["123","125"]}}}
        project_dict = {"$project":{"_id":0}}
        group_dict = {"$group":{"_id":{"equipment_number":"$equipment_number","farm_id":"$farm_id"},"total_count":{"$sum":1}}}
        # match_dict_1 = {"$match":{"total_count":{"$lt":2}}}
        result = self.db["sheep_water_intake"].aggregate([match_dict,project_dict,group_dict])
        for i in result:
            print(i)

{‘_id‘: {‘equipment_number‘: ‘125‘, ‘farm_id‘: ‘125‘}, ‘total_count‘: 1}
{‘_id‘: {‘equipment_number‘: ‘125‘, ‘farm_id‘: ‘123‘}, ‘total_count‘: 1}
{‘_id‘: {‘equipment_number‘: ‘124‘, ‘farm_id‘: ‘123‘}, ‘total_count‘: 2}
{‘_id‘: {‘equipment_number‘: ‘123‘, ‘farm_id‘: ‘123‘}, ‘total_count‘: 3}

首先考虑去重的问题，group_dict = {"$group":{"_id":{"equipment_number":"

group应该是先分组，分完组之后，进行累加，先看看不分组的数据

    def aggregate_many(self):
        # 获取所有牧场下，饮水次数小于2的羊的equipment_number
        match_dict = {"$match":{"farm_id":{"$in":["123","125"]}}}
        project_dict = {"$project":{"_id":0}}
        # group_dict = {"$group":{"_id":{"equipment_number":"$equipment_number","farm_id":"$farm_id"},"total_count":{"$sum":1}}}
        match_dict_1 = {"$match":{"total_count":{"$lt":2}}}
        result = self.db["sheep_water_intake"].aggregate([match_dict,project_dict])
        for i in result:
            print(i)



{‘farm_id‘: ‘123‘, ‘farm_name‘: ‘测试‘, ‘fold_id‘: ‘123‘, ‘fold_name‘: ‘测试‘, ‘device_number‘: ‘123‘, ‘equipment_number‘: ‘123‘, ‘animal_number‘: ‘123‘, ‘drink_quantity‘: 100, ‘type‘: ‘goat‘, ‘drink_time‘: datetime.datetime(2020, 3, 20, 15, 9, 43, 454000)}
{‘farm_id‘: ‘123‘, ‘farm_name‘: ‘测试‘, ‘fold_id‘: ‘123‘, ‘fold_name‘: ‘测试‘, ‘device_number‘: ‘123‘, ‘equipment_number‘: ‘123‘, ‘animal_number‘: ‘123‘, ‘drink_quantity‘: 200, ‘type‘: ‘goat‘, ‘drink_time‘: datetime.datetime(2020, 3, 20, 15, 9, 56, 139000)}
{‘farm_id‘: ‘123‘, ‘farm_name‘: ‘测试‘, ‘fold_id‘: ‘123‘, ‘fold_name‘: ‘测试‘, ‘device_number‘: ‘123‘, ‘equipment_number‘: ‘123‘, ‘animal_number‘: ‘123‘, ‘drink_quantity‘: 300, ‘type‘: ‘goat‘, ‘drink_time‘: datetime.datetime(2020, 3, 20, 15, 10, 0, 115000)}
{‘farm_id‘: ‘123‘, ‘farm_name‘: ‘测试‘, ‘fold_id‘: ‘123‘, ‘fold_name‘: ‘测试‘, ‘device_number‘: ‘123‘, ‘equipment_number‘: ‘124‘, ‘animal_number‘: ‘124‘, ‘drink_quantity‘: 100, ‘type‘: ‘goat‘, ‘drink_time‘: datetime.datetime(2020, 3, 20, 15, 45, 53, 727000)}
{‘farm_id‘: ‘123‘, ‘farm_name‘: ‘测试‘, ‘fold_id‘: ‘123‘, ‘fold_name‘: ‘测试‘, ‘device_number‘: ‘123‘, ‘equipment_number‘: ‘124‘, ‘animal_number‘: ‘124‘, ‘drink_quantity‘: 200, ‘type‘: ‘goat‘, ‘drink_time‘: datetime.datetime(2020, 3, 20, 15, 45, 59, 674000)}
{‘farm_id‘: ‘123‘, ‘farm_name‘: ‘测试‘, ‘fold_id‘: ‘123‘, ‘fold_name‘: ‘测试‘, ‘device_number‘: ‘123‘, ‘equipment_number‘: ‘125‘, ‘animal_number‘: ‘125‘, ‘drink_quantity‘: 100, ‘type‘: ‘goat‘, ‘drink_time‘: datetime.datetime(2020, 3, 20, 15, 46, 8, 953000)}
{‘farm_id‘: ‘125‘, ‘farm_name‘: ‘测试‘, ‘fold_id‘: ‘125‘, ‘fold_name‘: ‘测试‘, ‘device_number‘: ‘125‘, ‘equipment_number‘: ‘125‘, ‘animal_number‘: ‘125‘, ‘drink_quantity‘: 100, ‘type‘: ‘goat‘, ‘drink_time‘: datetime.datetime(2020, 3, 20, 17, 0, 34, 398000)}

最终的结果

    def aggregate_many(self):
        # 获取所有牧场下，饮水次数小于2的羊的equipment_number
        match_dict = {"$match":{"farm_id":{"$in":["123","125"]}}}
        project_dict = {"$project":{"_id":0}}
        group_dict = {"$group":{"_id":{"equipment_number":"$equipment_number","farm_id":"$farm_id"},"total_count":{"$sum":1}}}
        match_dict_1 = {"$match":{"total_count":{"$lt":2}}}
        result = self.db["sheep_water_intake"].aggregate([match_dict,project_dict,group_dict,match_dict_1])
        for i in result:
            print(i)

{‘_id‘: {‘equipment_number‘: ‘125‘, ‘farm_id‘: ‘125‘}, ‘total_count‘: 1}
{‘_id‘: {‘equipment_number‘: ‘125‘, ‘farm_id‘: ‘123‘}, ‘total_count‘: 1}

$lookup 多表联查

test2

{
    "_id" : ObjectId("5e7c756b2a89d7c2fc178f57"),
    "brand" : "惠普公司",
    "address" : "美国"
}

test1

{
    "_id" : ObjectId("5e7c753b2a89d7c2fc178f38"),
    "name" : "暗夜精灵笔记本电脑",
    "brand_id" : "5e7c756b2a89d7c2fc178f57",


}


{
    "_id" : ObjectId("5e7c75d02a89d7c2fc178fb0"),
    "name" : "暗夜精灵2",
    "brand_id" : "5e7c756b2a89d7c2fc178f57",
    "price" : 5600
}

通过test2的_id获取所有brand_id为_id的电脑名称和价格

from pymongo import MongoClient
class PyMongoTest(object):

    def __init__(self):
        self.host = "xx"
        self.port = xx
        self.username = "xx"
        self.password = "xx"
        self.database = "xx"
        self.client = MongoClient(host=self.host,port=self.port)
        self.db = self.client[self.database]
        self.connect_result = False
        if self.username and self.password:
            self.connect_result = self.db.authenticate(self.username,self.password)


    def aggregate_two_collection(self):
        collection_one = "test2"
        collection_two = "test1"
        lookup_dict = {"$lookup":{"from":collection_two,"localField":"_id","foreignField":"brand_id","as":"brand_product"}}
        result = self.db[collection_one].aggregate([lookup_dict])
        for r in result:
            print(r)

p = PyMongoTest()
p.aggregate_two_collection()

# 结果
{‘_id‘: ObjectId(‘5e7c756b2a89d7c2fc178f57‘), ‘brand‘: ‘惠普公司‘, ‘address‘: ‘美国‘, ‘brand_product‘: []}

将test2改为

{
    "_id" : ObjectId("5e7c756b2a89d7c2fc178f57"),
    "brand" : "惠普公司",
    "address" : "美国",
    "oid" : "5e7c756b2a89d7c2fc178f57"
}

    def aggregate_two_collection(self):
        collection_one = "test2"
        collection_two = "test1"
        lookup_dict = {"$lookup":{"from":collection_two,"localField":"oid","foreignField":"brand_id","as":"brand_product"}}
        result = self.db[collection_one].aggregate([lookup_dict])
        for r in result:
            print(r)

# 结果
{‘_id‘: ObjectId(‘5e7c756b2a89d7c2fc178f57‘), ‘brand‘: ‘惠普公司‘, ‘address‘: ‘美国‘, ‘oid‘: ‘5e7c756b2a89d7c2fc178f57‘, ‘brand_product‘: [{‘_id‘: ObjectId(‘5e7c753b2a89d7c2fc178f38‘), ‘name‘: ‘暗夜精灵笔记本电脑‘, ‘brand_id‘: ‘5e7c756b2a89d7c2fc178f57‘}, {‘_id‘: ObjectId(‘5e7c75d02a89d7c2fc178fb0‘), ‘name‘: ‘暗夜精灵2‘, ‘brand_id‘: ‘5e7c756b2a89d7c2fc178f57‘, ‘price‘: 5600}]}

可以看出：from是要关联的集合名，localField是关联的字段，foreignField也是关联的字段，但是必须注意，这两个字段的类型必须相同，要不就拿不出数据，as就是关联后，列表的名称

修改test1

/* 1 */
{
    "_id" : ObjectId("5e7c753b2a89d7c2fc178f38"),
    "name" : "暗夜精灵笔记本电脑",
    "brand_id" : "5e7c756b2a89d7c2fc178f57F",
　　 

}

/* 2 */
{
    "_id" : ObjectId("5e7c75d02a89d7c2fc178fb0"),
    "name" : "暗夜精灵2",
    "brand_id" : "5e7c756b2a89d7c2fc178f57D",
    "price" : 5600
}

{‘_id‘: ObjectId(‘5e7c756b2a89d7c2fc178f57‘), ‘brand‘: ‘惠普公司‘, ‘address‘: ‘美国‘, ‘oid‘: ‘5e7c756b2a89d7c2fc178f57‘, ‘brand_product‘: []}

说明 localField是关联的字段，foreignField也是关联的字段，值也必须相同。

将test1两个数据的brand_id修改成和test1的oid值一样，做下面测试

关联后，只想输出部分字段，怎么办？

    def aggregate_two_collection(self):
        collection_one = "test2"
        collection_two = "test1"
        lookup_dict = {"$lookup":{"from":collection_two,"localField":"oid","foreignField":"brand_id","as":"brand_product"}}
        project_dict = {"$project":{"_id":0,"oid":0}}
        result = self.db[collection_one].aggregate([lookup_dict,project_dict])
        for r in result:
            print(r)

# 结果
{‘brand‘: ‘惠普公司‘, ‘address‘: ‘美国‘, ‘brand_product‘: [{‘_id‘: ObjectId(‘5e7c753b2a89d7c2fc178f38‘), ‘name‘: ‘暗夜精灵笔记本电脑‘, ‘brand_id‘: ‘5e7c756b2a89d7c2fc178f57‘}, {‘_id‘: ObjectId(‘5e7c75d02a89d7c2fc178fb0‘), ‘name‘: ‘暗夜精灵2‘, ‘brand_id‘: ‘5e7c756b2a89d7c2fc178f57‘, ‘price‘: 5600}]}

project只影响原表的字段输出，不影响要关联表的字段，如果需要影响要关联表的字段输出呢？

更改test1数据为

/* 1 */
{
    "_id" : ObjectId("5e7c753b2a89d7c2fc178f38"),
    "name" : "暗夜精灵笔记本电脑",
    "brand_id" : "5e7c756b2a89d7c2fc178f57",
    "price" : 5000
}

/* 2 */
{
    "_id" : ObjectId("5e7c75d02a89d7c2fc178fb0"),
    "name" : "暗夜精灵2",
    "brand_id" : "5e7c756b2a89d7c2fc178f57",
    "price" : 5600
}

    def aggregate_two_collection(self):
        collection_one = "test2"
        collection_two = "test1"
        lookup_dict = {"$lookup":{"from":collection_two,"localField":"oid","foreignField":"brand_id","as":"brand_product"}}
        project_dict = {"$project":{"_id":0,"brand_product._id":0}}
        result = self.db[collection_one].aggregate([lookup_dict,project_dict])
        for r in result:
            print(r)


#结果
{‘brand‘: ‘惠普公司‘, ‘address‘: ‘美国‘, ‘oid‘: ‘5e7c756b2a89d7c2fc178f57‘, ‘brand_product‘: [{‘name‘: ‘暗夜精灵笔记本电脑‘, ‘brand_id‘: ‘5e7c756b2a89d7c2fc178f57‘, ‘price‘: 5000}, {‘name‘: ‘暗夜精灵2‘, ‘brand_id‘: ‘5e7c756b2a89d7c2fc178f57‘, ‘price‘: 5600}]}

计算两款电脑的平均值？

 def aggregate_two_collection(self):
        collection_one = "test2"
        collection_two = "test1"
        lookup_dict = {"$lookup":{"from":collection_two,"localField":"oid","foreignField":"brand_id","as":"brand_product"}}
        project_dict = {"$project":{"_id":0,"brand_product._id":0}}
        unwind_dict = {"$unwind":"$brand_product"}
        group_dict = {"$group":{"_id":{"oid":"$oid"},"avg_price":{"$avg":"$brand_product.price"}}}
        result = self.db[collection_one].aggregate([lookup_dict,project_dict,unwind_dict,group_dict])
        for r in result:
            print(r)


# 结果
{‘_id‘: {‘oid‘: ‘5e7c756b2a89d7c2fc178f57‘}, ‘avg_price‘: 5300.0}

$substr 切割字符串操作

/* 1 */
{
    "_id" : ObjectId("5e7dc3322a89d7c2fc18605d"),
    "animal_number" : "1001",
    "status" : "0",
    "time" : ISODate("2020-03-01T23:00:00.000Z")
}

/* 2 */
{
    "_id" : ObjectId("5e7dc3462a89d7c2fc18606e"),
    "animal_number" : "1001",
    "status" : "1",
    "time" : ISODate("2020-03-01T12:00:00.000Z")
}

/* 3 */
{
    "_id" : ObjectId("5e7dc35d2a89d7c2fc186093"),
    "animal_number" : "1001",
    "status" : "0",
    "time" : ISODate("2020-03-02T15:00:00.000Z")
}

/* 4 */
{
    "_id" : ObjectId("5e7dc3702a89d7c2fc1860a4"),
    "animal_number" : "1001",
    "status" : "1",
    "time" : ISODate("2020-03-02T22:33:00.000Z")
}

/* 5 */
{
    "_id" : ObjectId("5e7dc3912a89d7c2fc1860c3"),
    "animal_number" : "1001",
    "status" : "0",
    "time" : ISODate("2020-03-03T21:39:00.000Z")
}

/* 6 */
{
    "_id" : ObjectId("5e7dc39e2a89d7c2fc1860ce"),
    "animal_number" : "1001",
    "status" : "1",
    "time" : ISODate("2020-03-04T23:00:00.000Z")
}

获取每天status为0的次数，和status为1的次数

    def aggregate(self):
        match_dict = {"$match":{"animal_number":"1001"}}
        project = {"$project":{"_id":0,"animal_number":"$animal_number","status":"$status","time":{"$substr":["$time",0,10]}}}
        result = self.db["test2"].aggregate([match_dict,project])
        for info in result:
            print(info)

# 结果
{‘animal_number‘: ‘1001‘, ‘status‘: ‘0‘, ‘time‘: ‘2020-03-01‘}
{‘animal_number‘: ‘1001‘, ‘status‘: ‘1‘, ‘time‘: ‘2020-03-01‘}
{‘animal_number‘: ‘1001‘, ‘status‘: ‘0‘, ‘time‘: ‘2020-03-02‘}
{‘animal_number‘: ‘1001‘, ‘status‘: ‘1‘, ‘time‘: ‘2020-03-02‘}
{‘animal_number‘: ‘1001‘, ‘status‘: ‘0‘, ‘time‘: ‘2020-03-03‘}
{‘animal_number‘: ‘1001‘, ‘status‘: ‘1‘, ‘time‘: ‘2020-03-04‘}

project里面使用 "status":1和"status":"$status"表示的含义一样，均表示需要展示
$substr:["$需要切割字段的名字"，起始位置，终止位置]

 def aggregate(self):
        match_dict = {"$match":{"animal_number":"1001"}}
        project = {"$project":{"_id":0,"animal_number":"$animal_number","status":"$status","time":{"$substr":["$time",0,10]}}}
        group_dict = {"$group":{"_id":{"time":"$time","status":"$status"},"every_status_every_day_count":{"$sum":1}}}
        result = self.db["test2"].aggregate([match_dict,project,group_dict])
        for info in result:
            print(info)

# 结果
{‘_id‘: {‘time‘: ‘2020-03-03‘, ‘status‘: ‘0‘}, ‘every_status_every_day_count‘: 1}
{‘_id‘: {‘time‘: ‘2020-03-04‘, ‘status‘: ‘1‘}, ‘every_status_every_day_count‘: 1}
{‘_id‘: {‘time‘: ‘2020-03-01‘, ‘status‘: ‘1‘}, ‘every_status_every_day_count‘: 1}
{‘_id‘: {‘time‘: ‘2020-03-01‘, ‘status‘: ‘0‘}, ‘every_status_every_day_count‘: 1}
{‘_id‘: {‘time‘: ‘2020-03-02‘, ‘status‘: ‘0‘}, ‘every_status_every_day_count‘: 1}
{‘_id‘: {‘time‘: ‘2020-03-02‘, ‘status‘: ‘1‘}, ‘every_status_every_day_count‘: 1}

mongodb（五)：聚合操作（python）

码农公寓

相关文章