google-sheets - Google表格查询-两个范围之间的笛卡尔联接

Question

我们如何使用查询功能在Google Spreadsheet上的两个范围之间实现以下联接？

范围1

Model   Style
Nissan  Coupe
Nissan  Truck
Honda   Sedan
Honda   Hatchback
Toyata  Truck
Honda   Coupe

范围2

Style       Engine
Coupe       1200 cc
Coupe       1500 cc
Sedan       1000 cc
Truck       2000 cc
Sedan       1800 cc
Hatchback   800 cc

输出

Model   Style       Engine
Nissan  Coupe       1200 cc
Nissan  Coupe       1500 cc
Honda   Sedan       1000 cc
Nissan  Truck       2000 cc
Honda   Sedan       1800 cc
Honda   Hatchback   800 cc
Honda   Coupe       1200 cc
Honda   Coupe       1500 cc
Toyata  Truck       2000 cc

Answer 1

我们通过query命令使用的数据可视化语言不支持任何类型的联接。可以使用custom function获得所需的结果。

它的格式为=cartesianJoin(arr1, arr2, col1, col2)，其中前两个参数是要连接的范围，其他两个是要进行连接的列号(相对于范围)。例如，在您的情况下，第一个数组可以在A2:B8中，第二个数组在D2:E8中。那么公式将是

=cartesianJoin(A2:B8, D2:E8, 2, 1)

表示我们通过第一数组(B)的第二列等于第二数组(D)的第一列进行连接。

function cartesianJoin(arr1, arr2, col1, col2) {
  var output = [];
  for (var i = 0; i < arr1.length; i++) {
    var r1 = arr1[i];
    var matching = arr2.filter(function(r2) {
      return r1[col1 - 1] === r2[col2 - 1];
    }).map(function(r2) {
      var copyr2 = r2.slice(0);
      copyr2.splice(col2 - 1, 1);
      return r1.concat(copyr2);
    });
    output = output.concat(matching);
  }
  return output;                     
}

逻辑:对于第一个数组(r1)的每一行，按照相等性要求过滤第二个数组，然后将每个匹配的行连接到r1，首先删除匹配的列，这样它就不会出现两次。

截屏: