05-mongodb进阶习题

最新推荐文章于 2023-01-08 10:23:23 发布

步道师就是我

最新推荐文章于 2023-01-08 10:23:23 发布

阅读量486

点赞数

分类专栏： mongodb 文章标签： mongodb

本文链接：https://blog.csdn.net/q287573145/article/details/121627193

版权

mongodb 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

这篇博客展示了MongoDB数据库中如何进行各种查询操作，包括查询特定ID的商品、排除特定分类的商品、价格条件筛选、分类统计及价格统计等。还探讨了mapReduce在大数据环境下的优势，用于计算库存总量和平均价格。最后，通过mapReduce计算了每个栏目下商品的平均价格。

摘要由CSDN通过智能技术生成

一、数据准备

use testdb

db.ecs_category.insert([{"cat_id":1,"cat_name":"\u624b\u673a\u7c7b\u578b"},{"cat_id":2,"cat_name":"CDMA\u624b\u673a"},{"cat_id":3,"cat_name":"GSM\u624b\u673a"},{"cat_id":4,"cat_name":"3G\u624b\u673a"},{"cat_id":5,"cat_name":"\u53cc\u6a21\u624b\u673a"},{"cat_id":6,"cat_name":"\u624b\u673a\u914d\u4ef6"},{"cat_id":7,"cat_name":"\u5145\u7535\u5668"},{"cat_id":8,"cat_name":"\u8033\u673a"},{"cat_id":9,"cat_name":"\u7535\u6c60"},{"cat_id":11,"cat_name":"\u8bfb\u5361\u5668\u548c\u5185\u5b58\u5361"},{"cat_id":12,"cat_name":"\u5145\u503c\u5361"},{"cat_id":13,"cat_name":"\u5c0f\u7075\u901a\/\u56fa\u8bdd\u5145\u503c\u5361"},{"cat_id":14,"cat_name":"\u79fb\u52a8\u624b\u673a\u5145\u503c\u5361"},{"cat_id":15,"cat_name":"\u8054\u901a\u624b\u673a\u5145\u503c\u5361"}])

db.ecs_goods.insert([{"goods_id":1,"cat_id":4,"goods_name":"KD876","goods_number":1,"click_count":7,"shop_price":1388.00,"add_time":1240902890},{"goods_id":4,"cat_id":8,"goods_name":"\u8bfa\u57fa\u4e9aN85\u539f\u88c5\u5145\u7535\u5668","goods_number":17,"click_count":0,"shop_price":58.00,"add_time":1241422402},{"goods_id":3,"cat_id":8,"goods_name":"\u8bfa\u57fa\u4e9a\u539f\u88c55800\u8033\u673a","goods_number":24,"click_count":3,"shop_price":68.00,"add_time":1241422082},{"goods_id":5,"cat_id":11,"goods_name":"\u7d22\u7231\u539f\u88c5M2\u5361\u8bfb\u5361\u5668","goods_number":8,"click_count":3,"shop_price":20.00,"add_time":1241422518},{"goods_id":6,"cat_id":11,"goods_name":"\u80dc\u521bKINGMAX\u5185\u5b58\u5361","goods_number":15,"click_count":0,"shop_price":42.00,"add_time":1241422573},{"goods_id":7,"cat_id":8,"goods_name":"\u8bfa\u57fa\u4e9aN85\u539f\u88c5\u7acb\u4f53\u58f0\u8033\u673aHS-82","goods_number":20,"click_count":0,"shop_price":100.00,"add_time":1241422785},{"goods_id":8,"cat_id":3,"goods_name":"\u98de\u5229\u6d669@9v","goods_number":1,"click_count":9,"shop_price":399.00,"add_time":1241425512},{"goods_id":9,"cat_id":3,"goods_name":"\u8bfa\u57fa\u4e9aE66","goods_number":4,"click_count":20,"shop_price":2298.00,"add_time":1241511871},{"goods_id":10,"cat_id":3,"goods_name":"\u7d22\u7231C702c","goods_number":7,"click_count":11,"shop_price":1328.00,"add_time":1241965622},{"goods_id":11,"cat_id":3,"goods_name":"\u7d22\u7231C702c","goods_number":1,"click_count":0,"shop_price":1300.00,"add_time":1241966951},{"goods_id":12,"cat_id":3,"goods_name":"\u6469\u6258\u7f57\u62c9A810","goods_number":8,"click_count":13,"shop_price":983.00,"add_time":1245297652},
{"goods_id":13,"cat_id":3,"goods_name":"\u8bfa\u57fa\u4e9a5320 XpressMusic","goods_number":8,"click_count":13,"shop_price":1311.00,"add_time":1241967762},{"goods_id":14,"cat_id":4,"goods_name":"\u8bfa\u57fa\u4e9a5800XM","goods_number":1,"click_count":6,"shop_price":2625.00,"add_time":1241968492},{"goods_id":15,"cat_id":3,"goods_name":"\u6469\u6258\u7f57\u62c9A810","goods_number":3,"click_count":8,"shop_price":788.00,"add_time":1241968703},{"goods_id":16,"cat_id":2,"goods_name":"\u6052\u57fa\u4f1f\u4e1aG101","goods_number":0,"click_count":3,"shop_price":823.33,"add_time":1241968949},{"goods_id":17,"cat_id":3,"goods_name":"\u590f\u65b0N7","goods_number":1,"click_count":2,"shop_price":2300.00,"add_time":1241969394},{"goods_id":18,"cat_id":4,"goods_name":"\u590f\u65b0T5","goods_number":1,"click_count":0,"shop_price":2878.00,"add_time":1241969533},{"goods_id":19,"cat_id":3,"goods_name":"\u4e09\u661fSGH-F258","goods_number":12,"click_count":7,"shop_price":858.00,"add_time":1241970139},{"goods_id":20,"cat_id":3,"goods_name":"\u4e09\u661fBC01","goods_number":12,"click_count":14,"shop_price":280.00,"add_time":1241970417},{"goods_id":21,"cat_id":3,"goods_name":"\u91d1\u7acb A30","goods_number":40,"click_count":4,"shop_price":2000.00,"add_time":1241970634},{"goods_id":22,"cat_id":3,"goods_name":"\u591a\u666e\u8fbeTouch HD","goods_number":1,"click_count":15,"shop_price":5999.00,"add_time":1241971076},
{"goods_id":23,"cat_id":5,"goods_name":"\u8bfa\u57fa\u4e9aN96","goods_number":8,"click_count":17,"shop_price":3700.00,"add_time":1241971488},{"goods_id":24,"cat_id":3,"goods_name":"P806","goods_number":100,"click_count":35,"shop_price":2000.00,"add_time":1241971981},{"goods_id":25,"cat_id":13,"goods_name":"\u5c0f\u7075\u901a\/\u56fa\u8bdd50\u5143\u5145\u503c\u5361","goods_number":2,"click_count":0,"shop_price":48.00,"add_time":1241972709},{"goods_id":26,"cat_id":13,"goods_name":"\u5c0f\u7075\u901a\/\u56fa\u8bdd20\u5143\u5145\u503c\u5361","goods_number":2,"click_count":0,"shop_price":19.00,"add_time":1241972789},{"goods_id":27,"cat_id":15,"goods_name":"\u8054\u901a100\u5143\u5145\u503c\u5361","goods_number":2,"click_count":0,"shop_price":95.00,"add_time":1241972894},{"goods_id":28,"cat_id":15,"goods_name":"\u8054\u901a50\u5143\u5145\u503c\u5361","goods_number":0,"click_count":0,"shop_price":45.00,"add_time":1241972976},{"goods_id":29,"cat_id":14,"goods_name":"\u79fb\u52a8100\u5143\u5145\u503c\u5361","goods_number":0,"click_count":0,"shop_price":90.00,"add_time":1241973022},{"goods_id":30,"cat_id":14,"goods_name":"\u79fb\u52a820\u5143\u5145\u503c\u5361","goods_number":9,"click_count":1,"shop_price":18.00,"add_time":1241973114},{"goods_id":31,"cat_id":3,"goods_name":"\u6469\u6258\u7f57\u62c9E8 ","goods_number":1,"click_count":5,"shop_price":1337.00,"add_time":1242110412},{"goods_id":32,"cat_id":3,"goods_name":"\u8bfa\u57fa\u4e9aN85","goods_number":4,"click_count":9,"shop_price":3010.00,"add_time":1242110760}])

二、基础练习

查出满足以下条件的商品
1.1:主键为32的商品
 db.goods.find({goods_id:32});

1.2:不属第3栏目的所有商品($ne)
 db.goods.find({cat_id:{$ne:3}},{goods_id:1,cat_id:1,goods_name:1});

1.3:本店价格高于3000元的商品{$gt}
 db.goods.find({shop_price:{$gt:3000}},{goods_name:1,shop_price:1});

1.4:本店价格低于或等于100元的商品($lte)
 db.goods.find({shop_price:{$lte:100}},{goods_name:1,shop_price:1});

1.5:取出第4栏目或第11栏目的商品($in)
 db.goods.find({cat_id:{$in:[4,11]}},{goods_name:1,shop_price:1});

1.6:取出100<=价格<=500的商品($and)
db.goods.find({$and:[{price:{$gt:100},{$price:{$lt:500}}}]);

1.7:取出不属于第3栏目且不属于第11栏目的商品($and $nin和$nor分别实现)
 db.goods.find({$and:[{cat_id:{$ne:3}},{cat_id:{$ne:11}}]},{goods_name:1,cat_id:1})
 db.goods.find({cat_id:{$nin:[3,11]}},{goods_name:1,cat_id:1});
 db.goods.find({$nor:[{cat_id:3},{cat_id:11}]},{goods_name:1,cat_id:1});

1.8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
db.goods.find({$or:[{$and:[{shop_price:{$gt:100}},{shop_price:{$lt:300}}]},{$and:[{shop_price:{$gt:4000}},{shop_price:{$lt:5000}}]}]},{goods_name:1,shop_price:1});

1.9:取出goods_id%5 == 1, 即,1,6,11,..这样的商品
db.goods.find({goods_id:{$mod:[5,1]}});

1.10:取出有age属性的文档
db.stu.find({age:{$exists:1}});
含有age属性的文档将会被查出

三、进阶练习

1、#查询每个栏目下的商品数量
{
    key:{cat_id:1},
    cond:{},
    reduce:function(curr,result) {
        result.cnt += 1;
    },
    initial:{cnt:0}
}

2、#查询每个栏目下价格高于50元的商品数量
{
    key:{cat_id:1},
    cond:{shop_price:{$gt:50}},
    reduce:function(curr,result) {
        result.cnt += 1;
    },
    initial:{cnt:0}
}

3、#每个栏目下的商品库存量 sum()操作
{
    key:{cat_id:1},
    cond:{},
    reduce:function(curr,result) {
        result.num += curr.goods_number;
    },
    initial:{num:0}
}

4、#查询每个栏目最贵的商品价格, max()操作
{
    key:{cat_id:1},
    cond:{},
    reduce:function(curr , result) {
        if(curr.shop_price > result.max) {
            result.max = curr.shop_price;
        }
    },
    initial:{max:0}
}

5、#同上,查询每个栏目下最便宜的商品价格,同学们自行完成

6、#查询每个栏目下商品的平均价格
{
    key:{cat_id:1},
    cond:{},
    reduce:function(curr , result) {
        result.cnt += 1;
        result.sum += curr.shop_price;
    },
    initial:{sum:0,cnt:0},
    finalize:function(result) {
        result.avg = result.sum/result.cnt;
    }
}

7、#查询每个栏目下的商品数量
db.collection.aggregate();
[
    {$group:{_id:"$cat_id",total:{$sum:1}}}
]

8、#查询goods下有多少条商品,select count(*) from goods
[
    {$group:{_id:null,total:{$sum:1}}}
]

9、#查询每个栏目下 价格大于50元的商品个数
[
{$match:{shop_price:{$gt:50}}},
{$group:{_id:"$cat_id",total:{$sum:1}}}
]

10、#查询每个栏目下 价格大于50元的商品个数，并筛选出"满足条件的商品个数" 大于等于3的栏目 
[
    {$match:{shop_price:{$gt:50}}},
    {$group:{_id:"$cat_id",total:{$sum:1}}},
    {$match:{total:{$gte:3}}}
]

11、#查询每个栏目下的库存量
[
    {$group:{_id:"$cat_id" , total:{$sum:"$goods_number"}}},
]

12、#查询每个栏目下的库存量,并按库存量排序
[
    {$group:{_id:"$cat_id" , total:{$sum:"$goods_number"}}},
    {$sort:{total:1}}
]

13、#查询每个栏目下的库存量,并按库存量排序
[
    {$group:{_id:"$cat_id" , total:{$sum:"$goods_number"}}},
    {$sort:{total:1}},
    {$limit:3}
]

14、#查询每个栏目的商品平均价格,并按平均价格由高到低排序
[
    {$group:{_id:"$cat_id" , avg:{$avg:"$shop_price"}}},
    {$sort:{avg:-1}}
]

mapReduce 随着"大数据"概念而流行.其实mapReduce的概念非常简单,从功能上说,相当于RDBMS的 group 操作
mapReduce的真正强项在哪?
答:在于分布式,当数据非常大时,像google,有N多数据中心,数据都不在地球的一端,用group力所不及.

group既然不支持分布式,单台服务器的运算能力必然是有限的.而mapRecuce支持分布式,支持大量的服务器同时工作,
用蛮力来统计.

mapRecuce的工作过程:
map-->映射
reduce->归约

map: 先是把属于同一个组的数据,映射到一个数组上.cat_id-3 [23,2,6,7]
reduce: 把数组(同一组)的数据,进行运算.
用mapReduce计算每个栏目的库存总量

map函数
var map = function() {
    emit(this.cat_id,this.goods_number);
}
var reduce = function(cat_id,numbers) {
    return Array.sum(numbers);
}
db.goods.mapReduce(map,reduce,{out:'res'});

15、#用mapReduce计算每个栏目下商品的平均价格
var map = function() {
    emit(this.cat_id,this.shop_price);
}
var reduce = function(cat_id,values) {
    return Array.avg(values);
}
db.goods.mapReduce(map,reduce,{out:'res'});