如何在PouchDB上模拟聚合函数avg、sum、max、min和count

使用内置的_statsreduce函数可以检索数字字段的最高值和最低值。

var myMapReduceFun = {
  map: function (doc) {
    emit(doc._id, doc.number);
  },
  reduce: '_stats'
};
db.query(myMapReduceFun, {reduce: true}).then(function (result) {
  // handle result
}).catch(function (err) {
  // handle errors
});

结果看起来类似于此：

{"sum":35,"count":3,"min":10,"max":13,"sumsqr":214}

最大值在"max"字段中，最小值在"min"字段中。现在你只需要计算你想要的平均值，例如平均值：

var meanAverage = result.sum / result.count;

PouchDB中其他内置的reduce函数有_count和_sum。

PouchDB文档说明了以下关于reduce函数的内容：

提示：如果你没有使用内置的，你可能做错了。

您可以使用PouchDB中db.query()方法的map/reduce函数来获得文档的平均值、总和、最大值或任何其他类型的聚合。

我创建了一个演示JSBin fiddle和一个运行示例。我将函数的解释直接添加到代码中(如下(作为注释，因为我认为这会更简单。

var db = new PouchDB('friendsdb');
var docs = [
      {'_id': '1', 'number': 10, 'values': '1, 2, 3', 'loto': 'fooloto'},
      {'_id': '2', 'number': 12, 'values': '4, 7, 9', 'loto': 'barloto'},
      {'_id': '3', 'number': 13, 'values': '9, 4, 5', 'loto': 'fooloto'}
];
db.bulkDocs(docs).then(function(result) {
  querySum();
  queryLargest();
  querySmallest();
  queryAverage();
}).catch(function(err) {
  console.log(err);
});
function querySum() {
  function map(doc) {
    // the function emit(key, value) takes two arguments
    // the key (first) arguments will be sent as an array to the reduce() function as KEYS
    // the value (second) arguments will be sent as an array to the reduce() function as VALUES
    emit(doc._id, doc.number);
  }
  function reduce(keys, values, rereduce) {
    // keys:
    //   here the keys arg will be an array containing everything that was emitted as key in the map function...
    //   ...plus the ID of each doc (that is included automatically by PouchDB/CouchDB).
    //   So each element of the keys array will be an array of [keySentToTheEmitFunction, _idOfTheDoc]
    //
    // values
    //   will be an array of the values emitted as value
    console.info('keys ', JSON.stringify(keys));
    console.info('values ', JSON.stringify(values));
    // check for more info: http://couchdb.readthedocs.io/en/latest/couchapp/views/intro.html

    // So, since we want the sum, we can just sum all items of the values array
    // (there are several ways to sum an array, I'm just using vanilla for to keep it simple)
    var i = 0, totalSum = 0;
    for(; i < values.length; i++){
        totalSum += values[i];
    }
    return totalSum;
  }
  db.query({map: map, reduce: reduce}, function(err, response) {
    console.log('sum is ' + response.rows[0].value);
  });
}
function queryLargest() {
  function map(doc) {
    emit(doc._id, doc.number);
  }
  function reduce(keys, values, rereduce) {
    // everything same as before (see querySum() above)
    // so, this time we want the larger element of the values array
    // http://stackoverflow.com/a/1379560/1850609
    return Math.max.apply(Math, values);
  }
  db.query({map: map, reduce: reduce}, function(err, response) {
    console.log('largest is ' + response.rows[0].value);
  });
}
function querySmallest() {
  function map(doc) {
    emit(doc._id, doc.number);
  }
  function reduce(keys, values, rereduce) {
    // all the same... now the looking for the min
    return Math.min.apply(Math, values);
  }
  db.query({map: map, reduce: reduce}, function(err, response) {
    console.log('smallest is ' + response.rows[0].value);
  });
}
function queryAverage() {
  function map(doc) {
    emit(doc._id, doc.number);
  }
  function reduce(keys, values, rereduce) {
    // now simply calculating the average
    var i = 0, totalSum = 0;
    for(; i < values.length; i++){
        totalSum += values[i];
    }
    return totalSum/values.length;
  }
  db.query({map: map, reduce: reduce}, function(err, response) {
    console.log('average is ' + response.rows[0].value);
  });
}

注意：这只是一种方法。还有其他几种可能性(不将ID作为密钥发送，使用组和不同的reduce函数，使用内置的reduce函数，如_sum，…(，我只是认为这是一般来说更简单的替代方案。

我是PouchDB中views的粉丝。

https://pouchdb.com/2014/05/01/secondary-indexes-have-landed-in-pouchdb.html

可以创建一个存储视图，允许您多次重新查询同一索引：这意味着虽然第一次查询很慢(完全扫描(，但由于数据已经被索引，以后的查询会快得多。

var db = new PouchDB('friendsdb');
var view = {
  '_id':'_design/metrics',
  'views':{
    'metrics':{
      'map':function(doc){
        // collect up all the data we are looking for
        emit(doc._id, doc.number);
      }.toString(),
      'reduce':function(keys, values, rereduce){
        var metrics = {
          sum:0,
          max:Number.MIN_VALUE,
          min:Number.MAX_VALUE,
          count:0
        };
        // aggregate up the values
        for(var i=values.length-1; i>=0; i--){
          var v = values[i];
          metrics.sum += v;
          metrics.max = (metrics.max < v) ? v : metrics.max;
          metrics.min = (metrics.min < v) ? metrics.min : v;
          metrics.count += v.count || 1;
        }
        metrics.avg = metrics.sum/metrics.count;
        return metrics;
      }.toString()
    }
  }
};
// alternately you could use a built in reduce
// if one already exists for the aggregation 
// you are looking for
//view.reduce = '_stats';
// note the addition of the view
var docs = [view
  ,{'_id':'1','number':10,'values':[1,2,3],'loto':'fooloto'}
  ,{'_id':'2','number':12,'values':[4,7,9],'loto':'barloto'}
  ,{'_id':'3','number':13,'values':[9,4,5],'loto':'fooloto'}
];
db.bulkDocs(docs).then(function(result) {
  db.query('metrics',{reduce:true},function(err, response) {
    var m = response.rows[0].value;
    console.log('smallest.: ' + m.min);
    console.log('largest..: ' + m.max);
    console.log('average..: ' + m.avg);
    console.log('count....: ' + m.count);
    console.log('Total ...: ' + m.sum);
  });
}).catch(function(err) {
  console.log(err);
});

请注意，将视图添加到加载到数据库中的数据中，并且需要将map和reduce转换为字符串(函数末尾的.toString()(