数据库表
表一: 数据记录表
var mongoose = require('mongoose'), Schema = mongoose.Schema;var DocViewSchema = new Schema({ realName: String, //实在姓名 userName: String, //OA账号 count: Number, //文档统计 docName: String, //文档项目名称 docType: String, //文档我的项目类型 orgPath: String, //所属部门的组织门路 orgGroup: String, //所属事业群 orgName: String //所属小组 time: String,});module.exports = mongoose.model('Doc_view', DocViewSchema);
表二:文档主表
var mongoose = require('mongoose'), Schema = mongoose.Schema;var docsSchema = new Schema({ docType:String, //文档类型 user_name: String, //零碎用户 realName: String, //用户中文姓名 description: String, // 文档形容 gitUrl:String,//操作的文档源近程地址 displayName: String, //文档展现名 name:String,//文档名称 logo:String, owner:String,//文档创建人 developer:Array, //开发者 realName: String,//实在姓名 toTop:Date,//是否置顶 dest: String, //vuepress类型文档的输入目录 docKey: String, //束缚文档为public(公开)、private(公有)类型的关键字 privateMember: Array, //存储公有文档可查看的人员范畴 createTime: { type: Date, default: Date.now }, opendFiles:Array, orgName: String, //所属部门 orgPath: String, //所属部门的组织门路 orgGroup: String, //所属事业群 docCollection: Array, newGit: Boolean //标识是否是在零碎上构建的新我的项目});module.exports = mongoose.model('docs', docsSchema);
表数据
表一: 展现数据
{ "_id" : ObjectId("603678000f6ec514b4b8d02f"), "__v" : 0, "time" : "2021-02-24", "docName" : "abgFeedback", "userName" : "zhangsan", "orgPath" : "******", "orgGroup" : "*****", "orgName" : "****", "realName" : "张三", "count" : 10}/* 42 */{ "_id" : ObjectId("603678000f6ec514b4b8d030"), "__v" : 0, "time" : "2021-02-24", "docName" : "bookgit", "userName" : "liuniu", "orgPath" : "******", "orgGroup" : "*****", "orgName" : "****", "realName" : "刘牛", "count" : 1}/* 43 */{ "_id" : ObjectId("603678000f6ec514b4b8d031"), "__v" : 0, "time" : "2021-02-24", "docName" : "testtestdocs", "userName" : "lisi", "orgPath" : "******", "orgGroup" : "*****", "orgName" : "****", "realName" : "李四", "count" : 1}/* 44 */{ "_id" : ObjectId("6037c9800f6ec514b4b8d03c"), "__v" : 0, "time" : "2021-02-25", "docName" : "abgFeedback", "userName" : "zhangsan", "orgPath" : "******", "orgGroup" : "*****", "orgName" : "****", "realName" : "张三", "count" : 10}
表二:文档数据
/* 45 */{ "_id" : ObjectId("5f6da851a18a783210da7a16"), "docType" : "vuepress", "name" : "abgFeedback", "gitUrl" : "*****/abgFeedback.git", "description" : "问题反馈收集问题", "owner" : "zhangsan", "orgGroup" : "*****", "orgName" : "*****", "orgPath" : "****", "logo" : "https://img.58cdn.com.cn/escstatic/fecar/pmuse/58doc/58logo.jpeg", "newGit" : false, "toTop" : ISODate("2020-11-20T02:41:33.742Z"), "realName" : "龚成辉", "dest" : "abgFeedback", "opendFiles" : [], "createTime" : ISODate("2020-09-25T08:20:33.373Z"), "developer" : [], "__v" : 0, "docCollection" : [], "docKey" : "private", "privateMember" : ["lisi"]}
联表查问
db.getCollection('doc_views').aggregate([ { $match: { time: { $gte: "2020-03-29", $lte: "2021-03-29" } } }, { $group: { _id: { "name": '$docName' }, total: { $sum: "$count" } } }, { $lookup: { from: "docs", localField: "_id.name", foreignField: "name", as: "child" } }, { $project: { "child._id": false, "child.gitUrl": false, "child.description": false, "child.orgGroup": false, "child.orgPath": false, "child.newGit": false, "child.toTop": false, "child.docCollection": false, "child.opendFiles": false, "child.createTime": false, "child.logo": false, "child.orgName": false, "child.owner": false, "child.realName": false, "child.__v": false, "child.privateMember": false, "child.developer": false, "child.name": false } },])
- $match 聚合匹配条件 这块是以doc_views表time字段匹配前端传入的起始工夫和截止时间段的所有数据
$group 按某些指定的表达式对文档进行分组,而后将每个不同的分组的文档输入到下一阶段。输入文档蕴含一个
_id
字段,其中蕴含按关键字辨别的组- 这里以文档名称docName为关键字进行分组统计
- 同时给docName定义了一个name的别名 并包装到_id的对象上面
- 把所有以后时间段的同名文档进行合并并累计count字段输入total总数
$lookup 关联查问
参数 赋值 阐明 from "docs" 须要关联查问的表 此处查问的事主表docs localField "_id.name" 匹配查问数据的表的文档名称字段传参“_id.name”的起因是:aggregate管道模式数组上一个参数是下一个参数的入参 以后表文档名称关键字曾经被定义到_id对象外面了 foreignField "name" 匹配要联表的文档名称关键字 即docs表文档 名称关键字 as "child" 把以后匹配到的数据输入到as定义的别名 child外面 child是一个数组 留神:以摸一个关键字 如:文档名称 来聚合数据是 首先聚合进去的数据每个关键字只能有一条数据 否则联表时无奈匹配出关键字对应的主表数据
- $project 可指定显示或者疏忽联表外面的某些字段 如上所述 false示意不显示 true示意显示
返回后果
//疏忽字段的查问后果[ /* 22 */ { "_id" : { "name" : "ces1234" }, "total" : 5, "child" : [ { "docType" : "gitbook", "docKey" : "private" } ] }, /* 23 */ { "_id" : { "name" : "abgFeedback" }, "total" : 43, "child" : [ { "docType" : "vuepress", "dest" : "abgFeedback", "docKey" : "private" } ] }]//不疏忽字段的返回后果[ /* 22 */ { "_id" : { "name" : "ces1234" }, "total" : 5, "child" : [ { "_id" : ObjectId("5da19272f76a861008c468ff"), "docType" : "gitbook", "name" : "ces1234", "gitUrl" : "***/docs/ces1234.git", "description" : "APP前端组业务文档", "owner" : "zhangsan", "opendFiles" : [], "createTime" : ISODate("2019-10-12T08:44:34.986Z"), "developer" : [ "lisi" ], "__v" : 0, "orgPath" : "****", "orgName" : "****", "orgGroup" : "****", "toTop" : ISODate("2020-06-12T02:09:20.255Z"), "logo" : "https://img.58cdn.com.cn/escstatic/fecar/pmuse/58doc/58logo.jpeg", "docCollection" : [], "docKey" : "private", "privateMember" : [], "realName" : "龚成辉", "displayName" : "测试项目" } ] }, /* 23 */ { "_id" : { "name" : "abgFeedback" }, "total" : 43, "child" : [ { "_id" : ObjectId("5f6da851a18a783210da7a16"), "docType" : "vuepress", "name" : "abgFeedback", "gitUrl" : "****/docs/abgFeedback.git", "description" : "问题反馈收集问题", "owner" : "gongchenghui", "orgGroup" : "****", "orgName" : "****", "orgPath" : "****", "logo" : "https://img.58cdn.com.cn/escstatic/fecar/pmuse/58doc/58logo.jpeg", "newGit" : false, "toTop" : ISODate("2020-11-20T02:41:33.742Z"), "realName" : "龚成辉", "dest" : "abgFeedback", "opendFiles" : [], "createTime" : ISODate("2020-09-25T08:20:33.373Z"), "developer" : [], "__v" : 0, "docCollection" : [], "docKey" : "private", "privateMember" : [ "wangwu" ], "displayName" : "前端问题反馈" } ] }]