GROUP BY和使用下划线JS聚集的JSON数组 [英] Group by and aggregation on JSON array using Underscore JS
问题描述
我有一个JSON数组对象。我想组基于列FlexCategoryName数据和执行矩阵列聚集如AVG点击等成本,总和。下面是JSON对象数组
数组[100]
0:对象
1:对象
2:对象
3:对象
0:对象
点击次数:1067
成本:4094.2
日期:2014年2月8日
FlexCategoryName:CAT1
林pressions:65943
信息:20
VENUE_ID:1
1:对象
点击次数:106
费用:409
日期:2014年2月8日
FlexCategoryName:CAT2
林pressions:65941
信息:21
VENUE_ID:1
2:对象
点击次数:106
费用:409
日期:2014年2月8日
FlexCategoryName:CAT2
林pressions:10
信息:21
VENUE_ID:2
3:对象
点击次数:106
费用:409
日期:2014年2月8日
FlexCategoryName:3类
林pressions:10
信息:21
VENUE_ID:2
4:对象
点击次数:20
费用:10
日期:2014年2月8日
FlexCategoryName:五类
林pressions:10
信息:21
VENUE_ID:3
如何使用下划线JS通过和聚集矩阵列执行组。
预计输出
0:对象
成本:基于FlexCategoryName CAT1一切代价的总和
FlexCategoryName:CAT1
林pressions:基于FlexCategoryName CAT1所有IM pressions总和1:对象
成本:基于FlexCategoryName CAT2一切代价的总和
FlexCategoryName:CAT2
林pressions:基于FlexCategoryName CAT2所有IM pressions总和
VAR _ =要求(下划线);
函数sum(数字){
返回_.reduce(数字功能(结果,电流){
返回结果+ parseFloat(电流);
},0);
}
VAR的结果= _.chain(数据)
.groupBy(FlexCategoryName)
.MAP(功能(值,键){
返回{
FlexCategoryName:键,
费用:金额(_采摘(价值,成本)),
林pressions:SUM(_采摘(价值,我现在pressions)。)
}
})
。值();的console.log(结果);
输出
[{FlexCategoryName:CAT1',成本:4094.2,林pressions:65943},
{FlexCategoryName:CAT2',费用:818,林pressions:65951},
{FlexCategoryName:'3类',费用:409,林pressions:10},
{FlexCategoryName:五类,成本:10,林pressions:10}]
如果你想找到的平均值,而不是总和,则只需更改功能。
编辑:如果您希望将基于多个领域,你可以嵌套这样的分组
VAR的结果= _.chain(数据)
.groupBy(FlexCategoryName)
.MAP(功能(值,键){
返回_.chain(值)
.groupBy(VENUE_ID)
.MAP(函数(值,venue_id){
返回{
FlexCategoryName:键,
费用:金额(_采摘(值,成本)),
林pressions:SUM(_采摘(值,我现在pressions)。)
VENUE_ID:venue_id
}
})
。值();
})
。值();
I have a json array object . I want to group by data based on column "FlexCategoryName" and perform aggregation on matrix columns such as avg of "cost" ,sum of "clicks" etc . Below are JSON array Object
Array[100]
0: Object
1: Object
2: Object
3: Object
0: Object
Clicks: 1067
Cost: 4094.2
Date: "2/8/2014"
FlexCategoryName: "Cat1"
Impressions: 65943
Leads: 20
VENUE_ID:1
1: Object
Clicks: 106
Cost: 409
Date: "2/8/2014"
FlexCategoryName: "Cat2"
Impressions: 65941
Leads: 21
VENUE_ID:1
2: Object
Clicks: 106
Cost: 409
Date: "2/8/2014"
FlexCategoryName: "Cat2"
Impressions: 10
Leads: 21
VENUE_ID:2
3: Object
Clicks: 106
Cost: 409
Date: "2/8/2014"
FlexCategoryName: "Cat3"
Impressions: 10
Leads: 21
VENUE_ID:2
4: Object
Clicks: 20
Cost: 10
Date: "2/8/2014"
FlexCategoryName: "Cat5"
Impressions: 10
Leads: 21
VENUE_ID:3
How to use Underscore JS to perform group by and aggregation on matrix columns .
Expected Output
0: Object
Cost: sum of all cost based on FlexCategoryName Cat1
FlexCategoryName: "Cat1"
Impressions: sum of all Impressions based on FlexCategoryName Cat1
1: Object
Cost: sum of all cost based on FlexCategoryName Cat2
FlexCategoryName: "Cat2"
Impressions: sum of all Impressions based on FlexCategoryName Cat2
var _ = require("underscore");
function sum(numbers) {
return _.reduce(numbers, function(result, current) {
return result + parseFloat(current);
}, 0);
}
var result = _.chain(data)
.groupBy("FlexCategoryName")
.map(function(value, key) {
return {
FlexCategoryName: key,
Cost: sum(_.pluck(value, "Cost")),
Impressions: sum(_.pluck(value, "Impressions"))
}
})
.value();
console.log(result);
Output
[ { FlexCategoryName: 'Cat1', Cost: 4094.2, Impressions: 65943 },
{ FlexCategoryName: 'Cat2', Cost: 818, Impressions: 65951 },
{ FlexCategoryName: 'Cat3', Cost: 409, Impressions: 10 },
{ FlexCategoryName: 'Cat5', Cost: 10, Impressions: 10 } ]
If you want to find average values instead of sum, then just change the function.
Edit: If you want to group based on more than one fields, you can nest the grouping like this
var result = _.chain(data)
.groupBy("FlexCategoryName")
.map(function(value, key) {
return _.chain(value)
.groupBy("VENUE_ID")
.map(function(value1, venue_id) {
return {
FlexCategoryName: key,
Cost: sum(_.pluck(value1, "Cost")),
Impressions: sum(_.pluck(value1, "Impressions")),
VENUE_ID: venue_id
}
})
.value();
})
.value();
这篇关于GROUP BY和使用下划线JS聚集的JSON数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!