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({});

 

posted @ 2022-01-10 21:46  linuxTang  阅读(225)  评论(0)    收藏  举报