JSON 数据的 JavaScript SUM 和 GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11199653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JavaScript SUM and GROUP BY of JSON data
提问by LordChariot
This is my first attempt at doing JavaScript with some JSON data objects and need some advice on the proper way to attain my goal.
这是我第一次尝试使用一些 JSON 数据对象执行 JavaScript，需要一些有关实现目标的正确方法的建议。
Some server-side code actually generates a JSON formatted string that I have to work with and assign it to a string:
一些服务器端代码实际上生成了一个 JSON 格式的字符串，我必须使用它并将其分配给一个字符串：
var dataString='$DATASTRING$';
But the end-result I have to work with after the server substitutes its data (without the \r\n, of course):
但是在服务器替换其数据后我必须处理的最终结果（当然没有 \r\n）：
var dataString='[
{ "category" : "Search Engines", "hits" : 5, "bytes" : 50189 },
{ "category" : "Content Server", "hits" : 1, "bytes" : 17308 },
{ "category" : "Content Server", "hits" : 1, "bytes" : 47412 },
{ "category" : "Search Engines", "hits" : 1, "bytes" : 7601 },
{ "category" : "Business", "hits" : 1, "bytes" : 2847 },
{ "category" : "Content Server", "hits" : 1, "bytes" : 24210 },
{ "category" : "Internet Services", "hits" : 1, "bytes" : 3690 },
{ "category" : "Search Engines", "hits" : 6, "bytes" : 613036 },
{ "category" : "Search Engines", "hits" : 1, "bytes" : 2858 }
]';
And then I can change it to an object to work with.
然后我可以将其更改为要使用的对象。
var dataObject=eval("("+dataString+")");
This allows me to access the data individual rows of data, but I need to sum, group by, and order the values.
这允许我访问数据的各个数据行，但我需要对值进行求和、分组和排序。
I need to the equivalent of an SQL statement like this:
我需要等效于这样的 SQL 语句：
SELECT category, sum(hits), sum(bytes)
FROM dataObject
GROUP BY category
ORDER BY sum(bytes) DESC
My desired output would be an object like this that I can further process:
我想要的输出是这样的对象，我可以进一步处理：
var aggregatedObject='[
{ "category" : "Search Engines", "hits" : 13, "bytes" : 673684 },
{ "category" : "Content Server", "hits" : 3, "bytes" : 88930 },
{ "category" : "Internet Services", "hits" : 1, "bytes" : 3690 },
{ "category" : "Business", "hits" : 1, "bytes" : 2847 }
]';
...but i don't know where to start.
……但我不知道从哪里开始。
I could loop through all the category values and find the unique categories first, then loop again and sum the hits and bytes, then again to sort, but it seems there has got to be an easier way.
我可以遍历所有类别值并首先找到唯一的类别，然后再次循环并对命中和字节求和，然后再次进行排序，但似乎必须有更简单的方法。
prototype.js (1.7) is already included on the client page, but I could add Underscore, jQuery, or some other small library if I had to.
prototype.js (1.7) 已经包含在客户端页面中，但如果需要，我可以添加 Underscore、jQuery 或其他一些小型库。
I just don't know what would be best, easiest, smallest with the least amount of code to process the query.
我只是不知道什么是最好的，最简单的，最小的，用最少的代码来处理查询。
Any suggestions?
有什么建议？
回答by
You can use the native functions .reduce()to aggregrate the data, and then .sort()to sort by bytes.
您可以使用本机函数.reduce()聚合数据，然后按.sort()进行排序bytes。
var result = dataObject.reduce(function(res, obj) {
if (!(obj.category in res))
res.__array.push(res[obj.category] = obj);
else {
res[obj.category].hits += obj.hits;
res[obj.category].bytes += obj.bytes;
}
return res;
}, {__array:[]}).__array
.sort(function(a,b) { return b.bytes - a.bytes; });
If you're supporting older implementations, you'll need to use a shim for .reduce().
如果您支持较旧的实现，则需要对.reduce().
回答by KyleMit
If you go the LINQ.jsroute, you can do it like this:
如果你走LINQ.js路线，你可以这样做：
var aggregatedObject = Enumerable.From(dataArray)
.GroupBy("$.category", null,
function (key, g) {
return {
category: key,
hits: g.Sum("$.hits"),
bytes: g.Sum("$.bytes")
}
})
.ToArray();
Working demo with Stack Snippets:
使用 Stack Snippets 的工作演示：
var dataArray = [
{ category: "Search Engines", hits: 5, bytes: 50189 },
{ category: "Content Server", hits: 1, bytes: 17308 },
{ category: "Content Server", hits: 1, bytes: 47412 },
{ category: "Search Engines", hits: 1, bytes: 7601 },
{ category: "Business", hits: 1, bytes: 2847 },
{ category: "Content Server", hits: 1, bytes: 24210 },
{ category: "Internet ", hits: 1, bytes: 3690 },
{ category: "Search Engines", hits: 6, bytes: 613036 },
{ category: "Search Engines", hits: 1, bytes: 2858 }
];
var aggregatedObject = Enumerable.From(dataArray)
.GroupBy("$.category", null,
function (key, g) {
return {
category: key,
hits: g.Sum("$.hits"),
bytes: g.Sum("$.bytes")
}
})
.ToArray();
console.log(aggregatedObject);<script src="//cdnjs.cloudflare.com/ajax/libs/linq.js/2.2.0.2/linq.min.js"></script>Also, you can find more info on linqjs group by with a sum
回答by Naim Sulejmani
Hi here is one solution written by me Visit: aggregate_groupby_js on npmor in aggregate_groupby_js on github
嗨，这是我写的一个解决方案访问：npm上的aggregate_groupby_js或github 上的aggregate_groupby_js
The javascript library for using aggregate functions on array of objects. Basic functions like SUM, MIN, MAX, AVG, DISTINCT_COUNT for entire javascript objects
用于在对象数组上使用聚合函数的 javascript 库。整个 javascript 对象的基本函数，如 SUM、MIN、MAX、AVG、DISTINCT_COUNT
Example:
例子：
var arr = [{`"shape"`:`"square"`,`"color"`:`"red"`,`"used"`:1,`"instances"`:1},
{`"shape"`:`"square"`,`"color"`:`"red"`,`"used"`:2,`"instances"`:1},
{`"shape"`:`"circle"`,`"color"`:`"blue"`,`"used"`:0,`"instances"`:0},
{`"shape"`:`"square"`,`"color"`:`"blue"`,`"used"`:4,`"instances"`:4},
{`"shape"`:`"circle"`,`"color"`:`"red"`,"`used"`:1,`"instances"`:1},
{`"shape"`:`"circle"`,`"color"`:`"red"`,`"used"`:1,`"instances"`:0},
{`"shape"`:`"square"`,`"color"`:`"blue"`,`"used"`:4,`"instances"`:5},
{`"shape"`:`"square"`,`"color"`:`"red"`,`"used"`:2,`"instances"`:1}];
// Specify columns
var columns =[`"used"`, `"instances"`];
// Initialize object
var gb = new GroupBy(arr,columns);
// or
var gb = new GroupBy(arr,[`"used"`, `"instances"`]);
// Call the aggregate functions
gb.sum();
gb.min();
gb.max();
gb.avg();
gb.distinctCount();
回答by Hymanwanders
Given the dataString above, the below code seems to work. It goes through each object; if the category exists in the groupedObjectsarray, its hits and bytes are added to the existing object. Otherwise, it is considered new and added to the groupedObjects array.
鉴于上面的数据字符串，下面的代码似乎有效。它遍历每个对象；如果该类别存在于groupedObjects数组中，则将其命中和字节添加到现有对象中。否则，它被认为是新的并添加到 groupedObjects 数组中。
This solution makes use of underscore.js and jQuery
此解决方案使用 underscore.js 和 jQuery
Here's a jsfiddle demo: http://jsfiddle.net/R3p4c/2/
这是一个 jsfiddle 演示：http: //jsfiddle.net/R3p4c/2/
var objects = $.parseJSON(dataString);
var categories = new Array();
var groupedObjects = new Array();
var i = 0;
_.each(objects,function(obj){
var existingObj;
if($.inArray(obj.category,categories) >= 0) {
existingObj = _.find(objects,function(o){return o.category === obj.category; });
existingObj.hits += obj.hits;
existingObj.bytes += obj.bytes;
} else {
groupedObjects[i] = obj;
categories[i] = obj.category;
i++;
}
});
groupedObjects = _.sortBy(groupedObjects,function(obj){ return obj.bytes; }).reverse();
回答by AhbapAldirmaz
var obj = [{Poz:'F1',Cap:10},{Poz:'F1',Cap:5},{Poz:'F1',Cap:5},{Poz:'F2',Cap:20},{Poz:'F1',Cap:5},{Poz:'F1',Cap:15},{Poz:'F2',Cap:5},{Poz:'F3',Cap:5},{Poz:'F4',Cap:5},{Poz:'F1',Cap:5}];
Array.prototype.sumUnic = function(name, sumName){
var returnArr = [];
var obj = this;
for(var x = 0; x<obj.length; x++){
if((function(source){
if(returnArr.length == 0){
return true;
}else{
for(var y = 0; y<returnArr.length; y++){
var isThere = [];
if(returnArr[y][name] == source[name]){
returnArr[y][sumName] = parseInt(returnArr[y][sumName]) + parseInt(source[sumName]);
return false;
}else{
isThere.push(source);
}
}
if(isThere.length>0)returnArr.push(source);
return false;
}
})(obj[x])){
returnArr.push(obj[x]);
}
}
return returnArr;
}
obj.sumUnic('Poz','Cap');
// return "[{"Poz":"F1","Cap":45},{"Poz":"F2","Cap":25},{"Poz":"F3","Cap":5},{"Poz":"F4","Cap":5}]"
