如何模拟PouchDB上的聚合函数avg,sum,max,min和count?

时间:2021-11-30 01:28:46

Does anyone know how to create aggregate functions, for example avg, sum, max and min on PouchDB database. I created a simple application to test the PouchDB. I'm still not figured out how to run these commands. Thanks in advance.

有谁知道如何在PouchDB数据库上创建聚合函数,例如avg,sum,max和min。我创建了一个简单的应用程序来测试PouchDB。我还没弄明白如何运行这些命令。提前致谢。

For example. How do you get the highest, lowest or average for the "number" field?

例如。您如何获得“数字”字段的最高,最低或平均值?

My main Ionic 2 component

我的主要Ionic 2组件

import {Component} from '@angular/core';
import {Platform, ionicBootstrap} from 'ionic-angular';
import {StatusBar} from 'ionic-native';
import {HomePage} from './pages/home/home';
declare var require: any;
var pouch = require('pouchdb');
var pouchFind = require('pouchdb-find');
@Component({
    template: '<ion-nav [root]="rootPage"></ion-nav>'
})
export class MyApp {
    rootPage: any = HomePage;
    db: any;
    value: any;
    constructor(platform: Platform) {
        platform.ready().then(() => {
            StatusBar.styleDefault();
        });
        pouch.plugin(pouchFind);
        this.db = new pouch('friendsdb');
        let 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'
            }
        ];
        this.db.bulkDocs(docs).then(function (result) {
            console.log(result);
        }).catch(function (err) {
            console.log(err);
        });
    }
}
ionicBootstrap(MyApp);

3 个解决方案

#1


2  

You can use the map/reduce functions of the db.query() method from PouchDB to get the average, sum, largest or any other kind of aggregation of the docs.

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

I have created a demo JSBin fiddle with a running example. I added the explanation of the functions directly into the code (below) as comments, as I thought it'd be simpler.

我用一个运行的例子创建了一个演示JSBin小提琴。我将函数的解释直接添加到代码(下面)作为注释,因为我认为它更简单。

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://*.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);
  });
}

Note: This is just one way to do it. There are several other possibilities (not emitting IDs as keys, using groups and different reduce functions, using built-in reduce functions, such as _sum, ...), I just thought this was the simpler alternative generally speaking.

注意:这只是一种方法。还有其他几种可能性(不使用ID作为键,使用组和不同的reduce函数,使用内置的reduce函数,例如_sum,...),我只是认为这是一般说来的更简单的替代方法。

#2


3  

The highest and lowest values of the numbers fields are retrievable using the built-in _stats reduce function.

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

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
});

The result looks similar to this:

结果看起来类似于:

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

The highest value is in the "max"-field, the lowest in the "min"-field. Now you just have to calculate your desired average, for example the mean average:

最高值在“最大”字段中,“最小”字段中最低。现在你只需要计算你想要的平均值,例如平均值:

var meanAverage = result.sum / result.count;

Other built-in reduce functions in PouchDB are _count and _sum.

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

The PouchDB documentation says the following about reduce functions:

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

Tip: if you’re not using a built-in, you’re probably doing it wrong.

提示:如果您没有使用内置功能,那么您可能做错了。

#3


0  

I'm a fan of views in PouchDB for problems like this.

我喜欢PouchDB中的观点,因为这样的问题。

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

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

It is possible to create a stored view that allows you to requery the same index multiple times: meaning while the first time through is slow (full scan), later queries will be much faster as the data has already been indexed.

可以创建一个存储视图,允许您多次重新查询相同的索引:这意味着第一次通过时速度很慢(完全扫描),后面的查询将会更快,因为数据已被索引。

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);
});

Note the addition of the view to the data you load into your database, as well as the fact that the map and reduce are requried to be converted to strings (the .toString() at the end of the function)

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

#1


2  

You can use the map/reduce functions of the db.query() method from PouchDB to get the average, sum, largest or any other kind of aggregation of the docs.

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

I have created a demo JSBin fiddle with a running example. I added the explanation of the functions directly into the code (below) as comments, as I thought it'd be simpler.

我用一个运行的例子创建了一个演示JSBin小提琴。我将函数的解释直接添加到代码(下面)作为注释,因为我认为它更简单。

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://*.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);
  });
}

Note: This is just one way to do it. There are several other possibilities (not emitting IDs as keys, using groups and different reduce functions, using built-in reduce functions, such as _sum, ...), I just thought this was the simpler alternative generally speaking.

注意:这只是一种方法。还有其他几种可能性(不使用ID作为键,使用组和不同的reduce函数,使用内置的reduce函数,例如_sum,...),我只是认为这是一般说来的更简单的替代方法。

#2


3  

The highest and lowest values of the numbers fields are retrievable using the built-in _stats reduce function.

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

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
});

The result looks similar to this:

结果看起来类似于:

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

The highest value is in the "max"-field, the lowest in the "min"-field. Now you just have to calculate your desired average, for example the mean average:

最高值在“最大”字段中,“最小”字段中最低。现在你只需要计算你想要的平均值,例如平均值:

var meanAverage = result.sum / result.count;

Other built-in reduce functions in PouchDB are _count and _sum.

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

The PouchDB documentation says the following about reduce functions:

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

Tip: if you’re not using a built-in, you’re probably doing it wrong.

提示:如果您没有使用内置功能,那么您可能做错了。

#3


0  

I'm a fan of views in PouchDB for problems like this.

我喜欢PouchDB中的观点,因为这样的问题。

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

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

It is possible to create a stored view that allows you to requery the same index multiple times: meaning while the first time through is slow (full scan), later queries will be much faster as the data has already been indexed.

可以创建一个存储视图,允许您多次重新查询相同的索引:这意味着第一次通过时速度很慢(完全扫描),后面的查询将会更快,因为数据已被索引。

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);
});

Note the addition of the view to the data you load into your database, as well as the fact that the map and reduce are requried to be converted to strings (the .toString() at the end of the function)

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