如何像MATLAB中的pdist一样在Google BigQuery中创建相似矩阵

Question

在MATLAB和Python（scipy）中，有一个函数（pdist）将返回给定矩阵的每一行之间的成对距离。

因此，对于BigQuery中的表：

A = user1 | 0 0 |
    user2 | 0 3 |
    user3 | 4 0 |

应返回

              user1 user2 user3
dist = user1 |  0      3     4   | 
       user2 |  3      0     5   | 
       user3 |  4      5     0   | 

或者一些变体（可能没有对角线，只有矩阵的上半部分或下半部分，因为它们是冗余的。）

作为列的对也是可以接受的（方法（我到目前为止的猜测）是使用自联接，但我不确定如何在所有列上迭代-例如，我有大约3000列）。这个解决方案看起来像：

dist = |user1  user2  3 | 
       |user1  user3  4 | 
       |user2  user3  5 |

同样作为用户之间的距离度量，我不一定只是希望欧几里得距离作为这里的例子，而是任何一般的距离。就是这样一个距离

sum(min(user1_d, user2_d) / diff(user1_d - user2_d)) for all d dimensions between the two users.

有人为此找到Google BigQuery解决方案吗？

Answer 1

有两个答案：

1. 您可以通过交叉连接来实现这一点，并通过编程建立一个查询来计算距离函数，或者使用当前处于封闭alpha版本的用户定义函数功能

2. 请谨慎使用大型数据集的交叉连接。交叉联接完成N^2个工作，并且可以生成N^2个输出。两个只有一百万行的小表的交叉连接会产生一万亿行的输出。交叉连接20亿个行表会生成数倍行的输出。

   如果确实对大型数据集进行交叉联接，则应尝试预聚合或筛选，以减少实际需要联接的行数。