今天要学习的是《08 | 聚合查询》,这一节主要是理论的内容,后面的一节是演练。
MongoDB的聚合框架,可以处理进行类似于SQL的group,left outer join之类的运算,只不过可以来控制执行的顺序。
适应的范围目前看优势还是在分析(OLAP),当然OLTP也支持
常规的用法是生成一个数组(Pipeline),然后在Pipeline里写处理的步骤(Stage),最后在表(Collection)上调用arrgegate来进行运算。
## 这个是伪代码pipeline = [$stage1, $stage2, ... $stageN];db.<COLLECTION>.aggregate( pipline, { options });
步骤 | 作用 | SQL |
---|---|---|
$match | 过滤 | where |
$project | 投影 | as |
$sort | 排序 | order by |
$group | 分组 | group by |
$skip/$limit | 结果限制 | skip/limit |
$lookup | 左外连接 | left outer join |
$eq/$gt/$lt/$gte/$lte
$and/$or/$not/$in
$geoWithin/$intersect
$map/$reduce/$filter
$range
$multiply/$divide/$substract/$add
$year/$month/$dayOfMonth/$hour/$minute/$second
$sum/$avg
$push/$addToSet
$first/$last/$max/$min
步骤 | 作用 |
---|---|
$unwind | 展开数组 |
$graphLookup | 图搜索 |
$facet/$bucket | 分面搜索 |
SQL
SELECT first_name as '名', last_name as '姓'FROM usersWHERE gender = '男'SKIP 100LIMIT 20
MQL
db.users.aggregate([ { $match: { gender: '男' } }, { $skip: 100 }, { $limit: 20 }, { $project: { '名': '$first_name', '姓': '$last_name' } }]);
SQL
select department, count(null) as emp_qtyfrom userswhere gender = '女'group by departmenthaving count(*) < 10
MQL
db.users.aggregate([ { $match: { 'gender' : '女' }}, { $group: { _id: '$department' emp_qty: { $sum: 1 } }}, { $match: { emp_qty: { $lt: 10 }}}])
说白了就是将文档的树型结构展开成行的结构
> db.students.findOne(){ name: '张三', score: [ { subject: '语文', score: 84 }, { subject: '数学', score: 90 }, { subject: '外语', score: 69 } ]}
db.students.aggregate([$unwind: '$score'}]){ name: '张三', score: {subject: '语文', score: 84 }}{ name: '张三', score: {subject: '数学', score: 90 }}{ name: '张三', score: {subject: '外语', score: 69 }}
db.products.aggregate([{ $bucket: { groupBy: '$price', boundaries: [0, 10, 20, 30, 40], default: 'Other', output: { 'count': { $sum: 1 }} }}])
db.products.aggregate([{ $facet: { price: { $bucket: {...} }, year: { $bucket: {...} } }}])
今天的状态不太好哈,简单过一个基础知识,明天操练起来
联系客服