数据如下
表名:demo
{
"date":ISODate("2016-07-21")
"name":"userno"
"items":{
"00:01":{
"press":"1111",
},
"00:02":{
"press":"1121",
},
"00:03":{
"press":"2111",
},
....
"23:58":{
"press":"1311",
},
"23:59":{
"press":"1511",
}
}
},
{
"date":ISODate("2016-07-20")
"name":"userno"
"items":{
"00:01":{
"press":"4111",
},
"00:02":{
"press":"1621",
},
"00:03":{
"press":"2177",
},
....
"23:58":{
"press":"1311",
},
"23:59":{
"press":"1519",
}
}
},
{
"date":ISODate("2016-07-20")
"name":"userno222222"
"items":{
"00:01":{
"press":"4111",
},
"00:02":{
"press":"1621",
},
"00:03":{
"press":"2177",
},
....
"23:58":{
"press":"1311",
},
"23:59":{
"press":"1519",
}
}
},
现在要求name为userno,date是2016-07-21的数据中的press的最大值和最小值
请问mongo的sql应该怎么写
1.查询文档,之后对文档做极值
var doc = db.test.find({userno:"userno",date:"2016-07-21"})
getMaxAndMin = function(doc){
items = doc.items;
var max = 0;
var min = 10000;
for (var key in items) {
var value = items[key]['press'];
if (value > max) {
max = value;
}
if (value < min) {
min = value;
}
}
return {"max": max, "min": min};
}
doc.apply(getMaxAndMin )
2.使用group
db.test.group({key:{"name":true},condition:{"name":"userno"},initial:{max:0,min:10000},$reduce:reduce})
reduce = function (doc,prev){
var items = doc.items;
var max = prev.max;
var min = prev.min;
for(var key in items){
var value = items[key]['press'];
if(value > max){
max = value;
}
if(value < min){
min = value;
}
}
prev.max = max;
prev.min = min;
}
你的文档结构没有办法支持aggregation
的$group
/$max
操作。另外items
下面的子文档也不好建索引。
建议这样的结构:
{
"date": ISODate("2016-07-21"),
"name": "userno",
"items": [{
"time": "00:01",
"press": "1111",
}, {
"time": "00:02",
"press": "1121",
}, {
"time": "00:03",
"press": "2111",
}, {
"time": "23:58",
"press": "1311",
}, {
"time": "23:59",
"press": "1511",
}]
}
这样修改之后可以使用aggregation
方便地取得最小和最大press
:
db.demo.aggregate([
{$match: {date: ISODate("2016-07-21"), name: "userno"}},
{$unwind: "$items"},
{$group: {_id: null, min: {$min: "$items.press"}, max: {$max: "$items.press"}}}
])
这建立在对aggreation framework的正确理解上。相关知识请查阅文档。