一组使用JavaScript的数组元素中的GroupBy(基于标头的动态输入进行分组的简单方法)

Question

我正在尝试为数组创建GroubBy，如下所示：

const sqlData = [
['a','b','c','d','e','f','g'],
['aa','10/11/2020','##',2,3,4,5],
['bb','10/12/2020','$$',23,13,14,95],
['aa','10/12/2020','!!',21,23,24,58],
['aa','10/12/2020','##',22,13,44,55],
['aa','10/11/2020','$$',12,33,45,52],
['bb','10/11/2020','!!',52,32,49,52],
['aa','10/11/2020','!!',72,53,44,51],
['bb','10/11/2020','##',12,3,43,65],
['bb','10/13/2020','##',112,63,4,85],
['bb','10/13/2020','$$',22,63,24,95],
['bb','10/12/2020','$$',32,73,44,50],
['bb','10/13/2020','$$',52,388,24,50],
['bb','10/13/2020','$$',72,233,41,52],
['bb','10/13/2020','##',82,123,46,57],
['bb','10/13/2020','!!',92,763,47,58],

];

因此，我将sqlData[0]作为Table Header，其余数据作为Table Body。我有兴趣根据第三个输入groupby headers进行分组，如下所示：

groupbyheaders = [a,b,c]

在函数CCD_ 5中。在哪里我可以选择，如总和，平均值，最大值，最小值等

我如何解决这个问题是通过这个：

• 第一个创建头数组All Headers - Non Numeric Headers (where mathematical Op is not possible) + GroupByHeaders (that are Non Numeric)

所以在这种情况下，它给了我：[a,b,c (which are groupby headers + non Numeric), d, e, f, g]

现在，在循环整个初始数据之后，我正在执行一个串联操作，如下所示：aa<some unique token>10/11/2020<some unique token>##

现在，根据所有这些值，我生成唯一的条目，然后根据这些条目进行求和。虽然我已经完成了构思，但脚本实际上越来越大，所以我希望你所有的输入都能以简化和计算友好的方式完成这项任务。谢谢

Answer 1

考虑以下内容。它使用ES6语法，但如果您愿意，可以很容易地进行修改。

const sqlData = [
['a', 'b', 'c', 'd', 'e', 'f', 'g'],
['aa', '10/11/2020', '##', 2, 3, 4, 5],
['bb', '10/12/2020', '$$', 23, 13, 14, 95],
['aa', '10/12/2020', '!!', 21, 23, 24, 58],
['aa', '10/12/2020', '##', 22, 13, 44, 55],
['aa', '10/11/2020', '$$', 12, 33, 45, 52],
['bb', '10/11/2020', '!!', 52, 32, 49, 52],
['aa', '10/11/2020', '!!', 72, 53, 44, 51],
['bb', '10/11/2020', '##', 12, 3, 43, 65],
['bb', '10/13/2020', '##', 112, 63, 4, 85],
['bb', '10/13/2020', '$$', 22, 63, 24, 95],
['bb', '10/12/2020', '$$', 32, 73, 44, 50],
['bb', '10/13/2020', '$$', 52, 388, 24, 50],
['bb', '10/13/2020', '$$', 72, 233, 41, 52],
['bb', '10/13/2020', '##', 82, 123, 46, 57],
['bb', '10/13/2020', '!!', 92, 763, 47, 58],
];
// First, change the data to be an array of objects [{ column: value }]
const allColumns = sqlData.splice(0, 1)[0];
const sqlDataObjects = sqlData.map(row => {
const result = {};
allColumns.forEach(c => result[c] = row[allColumns.indexOf(c)]);
return result;
});
// Dynamically create a mapping of method names to functions.
// All deal with an array and should return a single number.
const methods = {
sum: (arr) => arr.reduce((total, v) => total + v, 0),
avg: (arr) => methods.sum(arr) / arr.length,
};
function groupBy(columns, methodName) {
const keyFn = (obj) => columns.map(c => obj[c]).join('<some unique token>');
const result = {};
// Create an object that just stores all raw values, grouped in arrays
// (like ARRAY_AGG in PostgreSQL)
const groups = sqlDataObjects.reduce((obj, row) => {
// Construct a key and select all columns that we need to store in the result
const key = keyFn(row);
const aggregatedColumnKeys = allColumns.filter(k => !columns.includes(k));
if (!(key in obj)) {
obj[key] = {};
aggregatedColumnKeys.forEach(c => obj[key][c] = []);
}
aggregatedColumnKeys.forEach(c => obj[key][c].push(row[c]));
return obj;
}, {});
// Choose the method to apply
const method = methods[methodName];
// Apply the method to each column in each group
Object.keys(groups).forEach(groupName => {
const group = groups[groupName];
result[groupName] = {};
Object.keys(group).forEach(c => {
result[groupName][c] = method(group[c]);
});
});
return result;
}
console.log(groupBy(['a', 'b', 'c'], 'sum'));
console.log(groupBy(['a', 'b', 'c'], 'avg'));