美文网首页
mongoDB 2 聚合查询

mongoDB 2 聚合查询

作者: 阿尔卡雷特 | 来源:发表于2018-12-27 13:56 被阅读0次

测试数据:

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
    }
]

相关文章

网友评论

      本文标题:mongoDB 2 聚合查询

      本文链接:https://www.haomeiwen.com/subject/pfcrlqtx.html