Google Cloud:与Bigquery或其他服务进行矩阵乘法? [英] Google Cloud: matrix multiplication with Bigquery or some other service?

问题描述

我正在使用Google Analytics(分析)并通过Bigquery处理数据，我需要进行矩阵乘法.

I am using Google Analytics and processing the data with Bigquery, I need to do a matrix multiplication.

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

推荐答案

假定MatrixA是具有以下各列的表:

Assuming MatrixA is a table with below columns:

i, k, value   

和MatrixB-模式为

and MatrixB - has schema as

k, j, value    

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

and also assuming that range of k-values is the same in both tables:

这将模仿下面的矩阵:

Matrix A
 2 -3  4
-1  0  2

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

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

Below code for multiplication is for BigQuery Standard 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

which represents 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   

随您的喜好

这篇关于Google Cloud:与Bigquery或其他服务进行矩阵乘法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！