如何按特定项目/列的值对基于数组或列的数据进行分组，同时汇总另一个项目/列的值？

Question

我们正在SAAS产品中构建集成，该产品从表中导出数据并将其写入CSV文件。该工具允许编写JavaScript(我不太熟悉)代码，可以从表中读取数据并将其写入CSV文件，并将文件传输到S3存储桶。

到目前为止，我还能继续工作。示例JavaScript-

// Manually invoke this method via 'Test connection'
function testConnection(context) {
return true;
}
// Manually invoke this method via 'Run manually'
function exportData(context) {
// Write the message to the log.
ai.log.logInfo("Script Execution Starting...");
var dataSource = context.getDataSource();
var bucketName = dataSource.getSetting("Bucket Name").getValue();
var awsAccessKey = dataSource.getSetting("Access Key").getValue();
var awsSecretKey = dataSource.getSetting("Access Secret Key").getValue();
var region = dataSource.getSetting("Region").getValue();
var date = new Date();
var currentDate = date.toLocaleDateString().replaceAll("/","");
//Append unique number and current date to the file name.
var key = dataSource.getSetting("Output File Name").getValue() + "_" + Date.now() + "_" + currentDate + ".csv";
ai.log.logInfo("Preparing Output File : " + key);
// Step 1: Build an array with the data that needs to be written to the csv file.
var reader = context.createTableReader(['SqlProjectCodeOnly', 'ResCodeCategory', 'Period', 'SqlAWSAccountID', 'SqlAWSAccountName', 'SqlAWSAccountRegion', 'Value']);
// Step 2: Write the header of the output file.
var result = "Project|Product Name|Report Month|AWS Account ID|AWS Account Name|AWS Region|Adjusted Costn";
var row = null;
// If reader.readRow() returns null, then there are no more records.
while ((row = reader.readRow()) !== null) {
//Separate each field by pipe.
result += row.join("|") + 'n';
}
ai.awss3.putFile(bucketName, key, result, region, awsAccessKey, awsSecretKey);
ai.log.logInfo("Script Execution Completed.");
}

它以以下格式输出数据-

Project|Product Name|Report Month|AWS Account ID|AWS Account Name|AWS Region|Adjusted Cost
XXXXXXX|AWS Elastic Compute Cloud|01/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|2588.598833
XXXXXXX|AWS Elastic Compute Cloud|01/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|1553.1593004
XXXXXXX|AWS Elastic Compute Cloud|02/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|4141.7581328
XXXXXXX|AWS Elastic Compute Cloud|02/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|1553.1593004

现在，它需要按前六列对数据进行分组，并将最后一列的值相加，以获得以下格式的数据-

Project|Product Name|Report Month|AWS Account ID|AWS Account Name|AWS Region|Adjusted Cost
XXXXXXX|AWS Elastic Compute Cloud|01/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|4,141.7581334
XXXXXXX|AWS Elastic Compute Cloud|02/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|5,694.9174338

我试着使用reduce函数，但没能使它起作用。当我在下面写日志-

ai.log.logVerbose( " row: " + row);

它写入的数据如下-

row: XXXXXXX|AWS Elastic Compute Cloud|01/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|2588.598833
row: XXXXXXX|AWS Elastic Compute Cloud|01/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|1553.1593004
row: XXXXXXX|AWS Elastic Compute Cloud|02/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|4141.7581328
row: XXXXXXX|AWS Elastic Compute Cloud|02/2022|AWS Account ID (Uncategorized)|AWS Account Name (Uncategorized)|AWS Region (Uncategorized)|1553.1593004.

如果能有专家帮忙，我将不胜感激。

Answer 1

在读取每个row时，必须将其push放入例如rows数组中。然后，可以将此数组reduce转换为一个对象，该对象包含按同一报表月份分组的聚合列组。这样的groups对象的values然后可以被maped和joined到最终结果中。

function collectAndAggregateSameReportMonthAdjustedCost(groups, recordColumns) {
const reportMonth = recordColumns[2];
const adjustedCost = parseFloat(recordColumns[6]);
let groupedColumns = groups[reportMonth];
if (!groupedColumns) {
groupedColumns = groups[reportMonth] = [...recordColumns];
groupedColumns[6] = adjustedCost;
} else {
groupedColumns[6] = groupedColumns[6] + adjustedCost;
}
return groups;
}
// const rows = [];
let row;
// // If reader.readRow() returns null, then there are no more records.
// while ((row = reader.readRow()) !== null) {
//   rows.push(row);
// }
// `rows` after being aggregated by the above commented code
// will loke like the next provided demonstrator ...
const rows = [[
'XXXXXXX',
'AWS Elastic Compute Cloud',
'01/2022',
'AWS Account ID (Uncategorized)',
'AWS Account Name (Uncategorized)',
'AWS Region (Uncategorized)',
'2588.598833',
], [
'XXXXXXX',
'AWS Elastic Compute Cloud',
'01/2022',
'AWS Account ID (Uncategorized)',
'AWS Account Name (Uncategorized)',
'AWS Region (Uncategorized)',
'1553.1593004',
], [
'XXXXXXX',
'AWS Elastic Compute Cloud',
'02/2022',
'AWS Account ID (Uncategorized)',
'AWS Account Name (Uncategorized)',
'AWS Region (Uncategorized)',
'4141.7581328',
], [
'XXXXXXX',
'AWS Elastic Compute Cloud',
'02/2022',
'AWS Account ID (Uncategorized)',
'AWS Account Name (Uncategorized)',
'AWS Region (Uncategorized)',
'1553.1593004',
]];
const aggregatedRows = Object
.values(
rows
.reduce(collectAndAggregateSameReportMonthAdjustedCost, {})
);
console.log({ aggregatedRows });
const result = [
'Project|Product Name|Report Month|AWS Account ID|AWS Account Name|AWS Region|Adjusted Cost',
...aggregatedRows
.map(columns => {
columns[6] = columns[6]
.toLocaleString('en', {
useGrouping: 'always',
maximumFractionDigits: 7,
});
return columns.join('|');
}),
].join('n');
console.log(result);

.as-console-wrapper { min-height: 100%!important; top: 0; }