2022MongoDB6-聚合查询参数的用法和删除数据的方法
聚合条件筛选$match用法:
对订单金额大于120的数据做分组:
#对订单金额大于120的进行分组 #select sum(payment) from table where payment>120 group by title 在mongoDB中 {"$match":{"payment":{"$gt":120}}}等价于mysql的where payment>120
#select * from order where "payment">120 group by title > db.order.aggregate([{"$match":{"payment":{"$gt":120}}},{"$group":{"_id":"$title"}}]) { "_id" : "o5" } #查询条件是payment》120,以title进行分组并对payment的值进行累加 > db.order.aggregate([{"$match":{"payment":{"$gt":120}}},{"$group":{"_id":"$title", "sum_payment":{"$sum":"$payment"}}}]); { "_id" : "o5", "sum_payment" : 999 } #不分组求和 > db.order.aggregate([{"$group":{"_id":null, "sum_payment":{"$sum":"$payment"}}}]); { "_id" : null, "sum_payment" : 1448 } #不分组加条件进行求和 > db.order.aggregate([{"$match":{"payment":{"$gt":120}}},{"$group":{"_id":null, "sum_payment":{"$sum":"$payment"}}}]); { "_id" : null, "sum_payment" : 999 } >
在mysql钟having只能做聚合的条件筛选,例如:
select sum(payment) as sp from table where payment>120 group by title having sp >999
#查询字段payment大于120的数据 > db.order.aggregate([{"$match":{"payment":{"$gt":120}}}]); { "_id" : ObjectId("61dbe29ca3c2932347187cdc"), "title" : "o5", "payment" : 999 } { "_id" : ObjectId("61dc069aa3c2932347187cdd"), "title" : "o6", "payment" : 10086 } #查询payment大于120的数据,并对payment的值进行累加求和 > db.order.aggregate([{"$match":{"payment":{"$gt":120}}},{"$group":{"_id":null, "sum_payment":{"$sum":"$payment"}}}]); { "_id" : null, "sum_payment" : 11085 } #查询条件大于120的数据并对payment的值进行累加求和,最后对求和后的值进行having聚合条件筛选998 结论:$match放到group之前相当于where,放到group之后相当于having > db.order.aggregate([{"$match":{"payment":{"$gt":120}}},{"$group":{"_id":null, "sum_payment":{"$sum":"$payment"}}},{"$match":{"sum_payment":{"$gt":998}}}]); { "_id" : null, "sum_payment" : 11085 }
聚合函数最大$max最小$min平均值$avg的用法:
1找出payment最大的值:
> db.order.aggregate([{"$group":{"_id":null, "max_payment":{"$max":"$payment"}}}]); { "_id" : null, "max_payment" : 10086 } > #找出payment字段最小值 > db.order.aggregate([{"$group":{"_id":null, "min_payment":{"$min":"$payment"}}}]); { "_id" : null, "min_payment" : 100 } #求payment值的平均数: > db.order.aggregate([{"$group":{"_id":null,"avg_payment":{"$avg":"$payment"}}}]); { "_id" : null, "avg_payment" : 1922.3333333333333 } >
统计结果返回数组用$push:
使用名字进行分组,把重名的年龄放到数组里
> db.users.aggregate([{"$group":{"_id":"$name", "all_age":{"$push":"$age"}}}]); { "_id" : "test", "all_age" : [ 20 ] } { "_id" : "multi", "all_age" : [ 1, 1 ] } { "_id" : "admin", "all_age" : [ 31, 32 ] } { "_id" : null, "all_age" : [ 30 ] } >
例子:
#给行添加字段 > db.order.update({"title":"o1"},{"$set":{"item":["a","b","c"]}}); WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 }) > db.order.find(); { "_id" : ObjectId("61dbe29ca3c2932347187cd8"), "title" : "o1", "payment" : 100, "item" : [ "a", "b", "c" ] } { "_id" : ObjectId("61dbe29ca3c2932347187cd9"), "title" : "o2", "payment" : 120 } { "_id" : ObjectId("61dbe29ca3c2932347187cda"), "title" : "o3", "payment" : 119 } { "_id" : ObjectId("61dbe29ca3c2932347187cdb"), "title" : "o4", "payment" : 110 } { "_id" : ObjectId("61dbe29ca3c2932347187cdc"), "title" : "o5", "payment" : 999 } { "_id" : ObjectId("61dc069aa3c2932347187cdd"), "title" : "o6", "payment" : 10086 } > db.order.drop(); true #插入点新的的数据 > db.order.drop(); true > db.order.find(); > db.order.insert([ ... {"title":"o1","payment":10086,"items":["手机","手表","手机壳"]}, ... {"title":"o2","payment":10086,"items":["电脑","笔记本","手机"]}, ... {"title":"o3","payment":10086,"items":["电脑","手表","SIA"]}, ... {"title":"o4","payment":10086,"items":["手机","SIA","笔记本"]}, ... {"title":"o5","payment":10086,"items":["电脑","手机","手机"]}, ... ]); BulkWriteResult({ "writeErrors" : [ ], "writeConcernErrors" : [ ], "nInserted" : 5, "nUpserted" : 0, "nMatched" : 0, "nModified" : 0, "nRemoved" : 0, "upserted" : [ ] }) > db.order.find(); { "_id" : ObjectId("61dc1836a3c2932347187ce3"), "title" : "o1", "payment" : 10086, "items" : [ "手机", "手表", "手机壳" ] } { "_id" : ObjectId("61dc1836a3c2932347187ce4"), "title" : "o2", "payment" : 10086, "items" : [ "电脑", "笔记本", "手机" ] } { "_id" : ObjectId("61dc1836a3c2932347187ce5"), "title" : "o3", "payment" : 10086, "items" : [ "电脑", "手表", "SIA" ] } { "_id" : ObjectId("61dc1836a3c2932347187ce6"), "title" : "o4", "payment" : 10086, "items" : [ "手机", "SIA", "笔记本" ] } { "_id" : ObjectId("61dc1836a3c2932347187ce7"), "title" : "o5", "payment" : 10086, "items" : [ "电脑", "手机", "手机" ] } > #把列表钟的元素进行拆分: > db.order.aggregate([{"$unwind":"$items"}]); { "_id" : ObjectId("61dc1836a3c2932347187ce3"), "title" : "o1", "payment" : 10086, "items" : "手机" } { "_id" : ObjectId("61dc1836a3c2932347187ce3"), "title" : "o1", "payment" : 10086, "items" : "手表" } { "_id" : ObjectId("61dc1836a3c2932347187ce3"), "title" : "o1", "payment" : 10086, "items" : "手机壳" } { "_id" : ObjectId("61dc1836a3c2932347187ce4"), "title" : "o2", "payment" : 10086, "items" : "电脑" } { "_id" : ObjectId("61dc1836a3c2932347187ce4"), "title" : "o2", "payment" : 10086, "items" : "笔记本" } { "_id" : ObjectId("61dc1836a3c2932347187ce4"), "title" : "o2", "payment" : 10086, "items" : "手机" } { "_id" : ObjectId("61dc1836a3c2932347187ce5"), "title" : "o3", "payment" : 10086, "items" : "电脑" } { "_id" : ObjectId("61dc1836a3c2932347187ce5"), "title" : "o3", "payment" : 10086, "items" : "手表" } { "_id" : ObjectId("61dc1836a3c2932347187ce5"), "title" : "o3", "payment" : 10086, "items" : "SIA" } { "_id" : ObjectId("61dc1836a3c2932347187ce6"), "title" : "o4", "payment" : 10086, "items" : "手机" } { "_id" : ObjectId("61dc1836a3c2932347187ce6"), "title" : "o4", "payment" : 10086, "items" : "SIA" } { "_id" : ObjectId("61dc1836a3c2932347187ce6"), "title" : "o4", "payment" : 10086, "items" : "笔记本" } { "_id" : ObjectId("61dc1836a3c2932347187ce7"), "title" : "o5", "payment" : 10086, "items" : "电脑" } { "_id" : ObjectId("61dc1836a3c2932347187ce7"), "title" : "o5", "payment" : 10086, "items" : "手机" } { "_id" : ObjectId("61dc1836a3c2932347187ce7"), "title" : "o5", "payment" : 10086, "items" : "手机" } > #拆分之后再用iteams字段作为分组对象,就可以实现对每个元素数量进行统计 > db.order.aggregate([{"$unwind":"$items"}, {"$group":{"_id":"$items", "all_title":{"$push":"$title"}}}]); { "_id" : "手机", "all_title" : [ "o1", "o2", "o4", "o5", "o5" ] } { "_id" : "手表", "all_title" : [ "o1", "o3" ] } { "_id" : "手机壳", "all_title" : [ "o1" ] } { "_id" : "笔记本", "all_title" : [ "o2", "o4" ] } { "_id" : "SIA", "all_title" : [ "o3", "o4" ] } { "_id" : "电脑", "all_title" : [ "o2", "o3", "o5" ] } > #只看买了手表的订单title: > db.order.aggregate([{"$unwind":"$items"}, {"$match":{"items":"手表"}}, {"$group":{"_id":"$items", "all_title":{"$push":"$title"}}}]); { "_id" : "手表", "all_title" : [ "o1", "o3" ] } > 总结:$push:把结果拼接再数组里 $unwind:把数组拆分成一个一个的元素 $match:做where条件查询
删除数据的方法:
#删除id为xxx的数据 db.users.remove({"_id" : ObjectId("61d8fa98aa30ebeedffca109")}); #删除字段为hobbies的数据 db.users.remove({"hobbies":["sleep"]}); #3.2之后版本推荐用法 db.users.deleteOne({"name":"test"}); #删掉名字为admin的多行数据 db.users.deleteMany({"name":"admin"}); #删掉所有数据 db.users.remove({});