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

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 Cost\n";
  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

我试过使用减值函数，但无法让它工作。当我在日志下面写的时候-

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时，必须将其放入一个例如rows数组中。然后，可以将该数组编辑为一个对象，该对象包含按同一个报表月份分组的聚合列组。这样一个groups对象的values就可以被map和join编辑成最终的结果。

​

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; }

​