Geek asks and answers

CouchDB - Map Hide a similar SQL group

Consider the following examples of documents stored in CouchDB

 {
"_id":....,
"rev":....,
"type":"orders",
"Period":"2013-01",
"Region":"East",
"Category":"Stationary",
"Product":"Pen",
"Rate":1,
"Qty":10,
"Amount":10
}

{
"_id":....,
"rev":....,
"type":"orders",
"Period":"2013-02",
"Region":"South",
"Category":"Food",
"Product":"Biscuit",
"Rate":7,
"Qty":5,
"Amount":35
}

Consider the following SQL query

SELECT Period, Region,Category, Product, Min(Rate),Max(Rate),Count(Rate), Sum(Qty),Sum(Amount)
FROM Sales
GROUP BY Period,Region,Category, Product;

Is it possible to create map / reduce views in couchdb equivalent to the above SQL query and create output like

[
    {
        "Period":"2013-01",
        "Region":"East",
        "Category":"Stationary",
        "Product":"Pen",
        "MinRate":1,
        "MaxRate":2,
        "OrdersCount":20,
        "TotQty":1000,
        "Amount":1750
    },
    {
    ... 
    }

]
+5
source share
2 answers

Upstairs, I find that @benedolph's answer is best practice and best scenario. Each abbreviation should ideally return 1 scalar value so that the code is as simple as possible.

However, the truth is, you will need to issue a few queries to get the full set of results described in your question. If you do not have the ability to run queries in parallel or it is really important to keep the number of requests, you can do it all at once.

:

function (doc) {
    emit([ doc.Period, doc.Region, doc.Category, doc.Product ], doc);
}

- , :

function (key, values, rereduce) {
    // helper function to sum all the values of a specified field in an array of objects
    function sumField(arr, field) {
        return arr.reduce(function (prev, cur) {
            return prev + cur[field];
        }, 0);
    }

    // helper function to create an array of just a single property from an array of objects
    // (this function came from underscore.js, at least it name and concept)
    function pluck(arr, field) {
        return arr.map(function (item) {
            return item[field];
        });
    }

    // rereduce made this more challenging, and I could not thoroughly test this right now
    // see the CouchDB wiki for more information
    if (rereduce) {
        // a rereduce handles transitionary values
        // (so the "values" below are the results of previous reduce functions, not the map function)
        return {
            OrdersCount: sumField(values, "OrdersCount"),
            MinRate: Math.min.apply(Math, pluck(values, "MinRate")),
            MaxRate: Math.max.apply(Math, pluck(values, "MaxRate")),
            TotQty: sumField(values, "TotQty"),
            Amount: sumField(values, "Amount")
        };
    } else {
        var rates = pluck(values, "Rate");

        // This takes a group of documents and gives you the stats you were asking for
        return {
            OrdersCount: values.length,
            MinRate: Math.min.apply(Math, rates),
            MaxRate: Math.max.apply(Math, rates),
            TotQty: sumField(values, "Qty"),
            Amount: sumField(values, "Amount")
        };
    }
}

"rereduce" , . ( ). wiki vs reereduce.

, , , Underscore.js. CommonJS , .

, , map/reduce, , , .

+3

, , "select". , , , .

:

{
    "_id": "_design/ddoc",
    "_rev": "...",
    "language": "javascript",
    "views": {
        "rates": {
            "map": "function(doc) {\n  emit([doc.Period, doc.Region, doc.Category, doc.Product], doc.Rate);\n}",
            "reduce": "_stats"
        },
        "qty": {
            "map": "function(doc) {\n  emit([doc.Period, doc.Region, doc.Category, doc.Product], doc.Qty);\n}",
            "reduce": "_stats"
        }
    }
}

<couchdb>/<database>/_design/ddoc/_view/rates?group_level=4, "Rate". :

{"rows":[
{"key":["2013-01","East","Stationary","Pen"],"value":{"sum":4,"count":3,"min":1,"max":2,"sumsqr":6}},
{"key":["2013-01","North","Stationary","Pen"],"value":{"sum":1,"count":1,"min":1,"max":1,"sumsqr":1}},
{"key":["2013-01","South","Stationary","Pen"],"value":{"sum":0.5,"count":1,"min":0.5,"max":0.5,"sumsqr":0.25}},
{"key":["2013-02","South","Food","Biscuit"],"value":{"sum":7,"count":1,"min":7,"max":7,"sumsqr":49}}
]}

"Qty" <couchdb>/<database>/_design/ddoc/_view/qty?group_level=4.

group_level , . , group_level=2 "" "".

+2

More articles:


All Articles