google-cloud-platform - 谷歌云 : matrix multiplication with Bigquery or some other service?

Question

我正在使用 Google Analytics 并使用 Bigquery 处理数据，我需要执行 matrix multiplication .

在 Google Cloud 中实现矩阵乘法的最可行方法是什么？可以直接在 Bigquery 中完成吗？

Answer 1

假设 MatrixA 是一个包含以下列的表格:

i, k, value   

和 MatrixB - 架构为

k, j, value    

并假设两个表中的 k 值范围相同:

这将模仿以下矩阵:

Matrix A
 2 -3  4
-1  0  2

Matrix B
-1  2  3
 0  1  7
 1  1 -2

下面的乘法代码适用于 BigQuery 标准 SQL

#standardSQL
WITH MatrixA AS (
  SELECT 1 AS i, 1 AS k, 2 AS val UNION ALL
  SELECT 1, 2, -3 UNION ALL
  SELECT 1, 3, 4 UNION ALL
  SELECT 2, 1, -1 UNION ALL
  SELECT 2, 2, 0 UNION ALL
  SELECT 2, 3, 2 
), MatrixB AS (
  SELECT 1 AS k, 1 AS j, -1 AS val UNION ALL
  SELECT 1, 2, 2 UNION ALL
  SELECT 1, 3, 3 UNION ALL
  SELECT 2, 1, 0 UNION ALL
  SELECT 2, 2, 1 UNION ALL
  SELECT 2, 3, 7 UNION ALL
  SELECT 3, 1, 1 UNION ALL
  SELECT 3, 2, 1 UNION ALL
  SELECT 3, 3, -2 
)
SELECT i, j, SUM(a.val * b.val) val
FROM MatrixA AS a
CROSS JOIN MatrixB AS b
WHERE a.k = b.k
GROUP BY i, j
ORDER BY i, j   

结果如下

Row i   j   val  
1   1   1   2    
2   1   2   5    
3   1   3   -23  
4   2   1   3    
5   2   2   0    
6   2   3   -7     

代表MatrixA * MatrixB

2   5  -23
3   0   -7

注意:你可以使用

FROM MatrixA AS a
JOIN MatrixB AS b
ON a.k = b.k  

代替

FROM MatrixA AS a
CROSS JOIN MatrixB AS b
WHERE a.k = b.k   

只看你的喜好