测试数据:
db.table.drop()
db.table.find()
db.table.insertMany([
{year:"2018",unit:"北大",p1num:100,p2num:200},
{year:"2018",unit:"清华",p1num:100,p2num:200},
{year:"2018",unit:"人大",p1num:100,p2num:200},
{year:"2017",unit:"北大",p1num:100,p2num:200},
{year:"2017",unit:"清华",p1num:100,p2num:200},
{year:"2017",unit:"南开",p1num:59,p2num:20},
{year:"2017",unit:"复旦",p1num:159,p2num:0},
{year:"2017",unit:"人民",p1num:259},
{year:"2017",unit:"中山",p1num:39,p2num:null}
])
1. 基本总量查询
-- sql
select count(1) from table
-- mongo:
db.table.find().count()
-- 结果
9
2. 聚合后的总量查询
-- sql
select year, count(1) as total from table group by year
-- mongo:
db.table.aggregate([
{
$group:{_id:"$year",total:{$sum:1}}
}
])
-- 结果:
{
"_id": "2017",
"total": 6
}
{
"_id": "2018",
"total": 3
}
3.多聚合的总量查询
-- sql:
select year, unit, count(1) as total from table group by year, unit
-- mongo:
db.table.aggregate([
{
$group:{_id:{f1:"$year",f2:"$unit"}, total:{$sum:1}}
}
])
-- 结果:
// 1
{
"_id": {
"f1": "2017",
"f2": "人民"
},
"total": 1
}
// 2
{
"_id": {
"f1": "2017",
"f2": "中山"
},
"total": 1
}
// 3
{
"_id": {
"f1": "2017",
"f2": "复旦"
},
"total": 1
}
...
4.聚合的多列总量查询
-- sql
select year,count(p1num),count(p2num) from table group by year
--mongo:
db.table.group({
key: { year:2},
initial: {"ct1ct":0,"ct2ct":0},
reduce: function Reduce(doc, out) {
out.ct1ct += (doc.p1num > 0 ? 1: 0)
out.ct2ct += (doc.p2num > 0 ? 1: 0)
}
})
--结果:
[
{
"year": "2018",
"ct1ct": 3,
"ct2ct": 3
},
{
"year": "2017",
"ct1ct": 6,
"ct2ct": 3
}
]
网友评论