title: MongoDB 實(shí)現(xiàn)行級(jí)別的聚合函數(shù)（類(lèi)似于Oracle的開(kāi)窗函數(shù)）
tags: []
notebook: test note book

場(chǎng)景： 顯示每個(gè)班級(jí)每個(gè)人每科的分?jǐn)?shù)，班級(jí)的平均分，班級(jí)排名等聚合列

row data 為每個(gè)人的名字,年級(jí),科目,分?jǐn)?shù).要求統(tǒng)計(jì)出每個(gè)人的成績(jī)總分,單科成績(jī)排名,總分成績(jī)排名,班級(jí)成績(jī)排名.并且顯示在每一行上.

測(cè)試數(shù)據(jù)

db.score.insert({ "_id":"1", "name":"test1", "subject":"語(yǔ)文", "score":70, "class":"四年級(jí)"});
db.score.insert({ "_id":"2", "name":"test2", "subject":"語(yǔ)文", "score":80, "class":"四年級(jí)"});
db.score.insert({ "_id":"3", "name":"test3", "subject":"語(yǔ)文", "score":59, "class":"四年級(jí)"});
db.score.insert({ "_id":"4", "name":"test4", "subject":"語(yǔ)文", "score":78, "class":"五年級(jí)"});
db.score.insert({ "_id":"5", "name":"test5", "subject":"語(yǔ)文", "score":99, "class":"五年級(jí)"});
db.score.insert({ "_id":"6", "name":"test6", "subject":"語(yǔ)文", "score":67, "class":"五年級(jí)"});
db.score.insert({ "_id":"7", "name":"test1", "subject":"數(shù)學(xué)", "score":56, "class":"四年級(jí)"});
db.score.insert({ "_id":"8", "name":"test2", "subject":"數(shù)學(xué)", "score":78, "class":"四年級(jí)"});
db.score.insert({ "_id":"9", "name":"test3", "subject":"數(shù)學(xué)", "score":57, "class":"四年級(jí)"});
db.score.insert({ "_id":"10", "name":"test4", "subject":"數(shù)學(xué)", "score":88, "class":"五年級(jí)"});
db.score.insert({ "_id":"11", "name":"test5", "subject":"數(shù)學(xué)", "score":69, "class":"五年級(jí)"});
db.score.insert({ "_id":"12", "name":"test6", "subject":"數(shù)學(xué)", "score":47, "class":"五年級(jí)"});
db.score.insert({ "_id":"13", "name":"test1", "subject":"英語(yǔ)", "score":79, "class":"四年級(jí)"});
db.score.insert({ "_id":"14", "name":"test2", "subject":"英語(yǔ)", "score":66, "class":"四年級(jí)"});
db.score.insert({ "_id":"15", "name":"test3", "subject":"英語(yǔ)", "score":88, "class":"四年級(jí)"});
db.score.insert({ "_id":"16", "name":"test4", "subject":"英語(yǔ)", "score":97, "class":"五年級(jí)"});
db.score.insert({ "_id":"17", "name":"test5", "subject":"英語(yǔ)", "score":68, "class":"五年級(jí)"});
db.score.insert({ "_id":"18", "name":"test6", "subject":"英語(yǔ)", "score":77, "class":"五年級(jí)"});

先給出查詢(xún)語(yǔ)句,再一步步分析

db.score.aggregate([
    {
        $sort:{
            score:-1
        }
    },
    {
        $group:{
            _id:{
                subject:"$subject",
                class:"$class"
            },
            avgScore:{
                $avg:"$score"
            },
            sumScore:{
                $sum:"$score"
            },
            rowData:{
                $push:"$$ROOT"
            }
        }
    },
    {
        $unwind:{
            path:"$rowData",
            preserveNullAndEmptyArrays:true,
            includeArrayIndex:"rangIndex"
        }
    },  
    {
        $group:{
            _id:"$rowData.name",
            totalScore:{
                $sum:"$rowData.score"
            },
            rowData:{
                $push:"$$ROOT"
            }
        }
    },
    {
        $unwind:{
            path:"$rowData",
            preserveNullAndEmptyArrays:true,
        }
    },
    {
        $project:{
            _id:0,
            姓名:"$_id",
            年級(jí):"$rowData._id.class",
            科目:"$rowData._id.subject",
            成績(jī):"$rowData.rowData.score",
            總分:"$totalScore",
            年級(jí)科目成績(jī)排名:{$add:["$rowData.rangIndex",1]},
            年級(jí)科目平均分:"$rowData.avgScore",
            年級(jí)科目總分:"$rowData.sumScore"
        }
    }
]);

查詢(xún)結(jié)果

查詢(xún)語(yǔ)句分析

    {
        $sort:{
            score:-1
        }
    },

先按分?jǐn)?shù)從高到低講所有數(shù)據(jù)排序,目的是為了之后方便算出科目層級(jí)年級(jí)排名.

    {
        $group:{
            _id:{
                subject:"$subject",
                class:"$class"
            },
            avgScore:{
                $avg:"$score"
            },
            sumScore:{
                $sum:"$score"
            },
            rowData:{
                $push:"$$ROOT"
            }
        }
    },

• 按年級(jí)和科目進(jìn)行分組,目的是算出科目平均分和科目的總分.此處的rowData用了$group函數(shù)的子函數(shù)$push,$push的作用是進(jìn)行分組時(shí),將原始數(shù)據(jù)塞入到一個(gè)數(shù)組里面,有點(diǎn)類(lèi)似于$unwind的反過(guò)來(lái)的用法.因?yàn)槲覀冎筮€是要用到row level 的數(shù)據(jù).

• "$$ROOT" 的意思是將整個(gè)目錄push到數(shù)組里面, 這里也可以push自己想要的fields

    rowData:{
        $push:{
            name:"$name",
            score:"$score"
        }
    }

    {
        $unwind:{
            path:"$rowData",
            preserveNullAndEmptyArrays:true,
            includeArrayIndex:"rangIndex"
        }
    },  

unwind的作用是將數(shù)組解壓,那么我們即又可以得到row level 的數(shù)據(jù)了,此時(shí)每一行的數(shù)據(jù)也包含sumScore,avgScoure 屬性了.而這里的 includeArrayIndex屬性的意思是在unwind時(shí),給每條記錄加一個(gè)rangIndex屬性,屬性的內(nèi)容是行號(hào),有點(diǎn)類(lèi)似于oracle的rownumber.要注意的是這里的includeArrayIndex是每個(gè)組的行號(hào),也就是換一組就又會(huì)從0開(kāi)始,而且是默認(rèn)排序,到這里也就會(huì)明白為什么我會(huì)在最前面加一個(gè)$sort函數(shù)先排好序.

    {
        $group:{
            _id:"$rowData.name",
            totalScore:{
                $sum:"$rowData.score"
            },
            rowData:{
                $push:"$$ROOT"
            }
        }
    },
    {
        $unwind:{
            path:"$rowData",
            preserveNullAndEmptyArrays:true,
        }
    },

和上面的group類(lèi)似,此處作用是算出個(gè)人的總成績(jī),再u(mài)nwind 出row level data

    {
        $project:{
            _id:0,
            姓名:"$_id",
            年級(jí):"$rowData._id.class",
            科目:"$rowData._id.subject",
            成績(jī):"$rowData.rowData.score",
            總分:"$totalScore",
            年級(jí)科目成績(jī)排名:{$add:["$rowData.rangIndex",1]},
            年級(jí)科目平均分:"$rowData.avgScore",
            年級(jí)科目總分:"$rowData.sumScore"
        }
    }

顯示出需要的結(jié)果.這里的年級(jí)科目成績(jī)排名稍微處理了一下,每個(gè)結(jié)果都+1,因?yàn)閕ndex是從0開(kāi)始的

我們還可以計(jì)算

• 成績(jī)類(lèi)別 不及格/及格/良好/優(yōu)秀
• 班級(jí)人數(shù)統(tǒng)計(jì)
• 總成績(jī)排名
• 結(jié)果按年級(jí),科目,年級(jí)科目成績(jī)排名升序排名

有興趣的可以根據(jù)測(cè)試數(shù)據(jù)進(jìn)行查詢(xún)語(yǔ)句的改寫(xiě),算是鞏固練習(xí).