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

问题描述

我正在使用 Google Analytics 并使用 Bigquery 处理数据，我需要做一个

解决方案

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

i，k，值

和 MatrixB - 模式为

k, j, 值

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

这将模仿以下矩阵:

矩阵A2 -3 4-1 0 2矩阵B-1 2 30 1 71 1 -2

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

#standardSQLWITH MatrixA AS (SELECT 1 AS i, 1 AS k, 2 AS val UNION ALL选择 1, 2, -3 联合所有选择 1、3、4 联合所有选择 2, 1, -1 联合所有选择 2, 2, 0 联合所有选择 2、3、2), MatrixB AS (SELECT 1 AS k, 1 AS j, -1 AS val UNION ALL选择 1, 2, 2 联合所有选择 1、3、3 联合所有选择 2, 1, 0 联合所有选择 2, 2, 1 联合所有选择 2、3、7 联合所有选择 3, 1, 1 联合所有选择 3, 2, 1 联合所有选择 3、3、-2)SELECT i, j, SUM(a.val * b.val) val从 MatrixA 作为 a交叉连接矩阵B AS b哪里 a.k = b.k按 i, j 分组按 i, j 排序

结果如下

行 i j val1 1 1 22 1 2 53 1 3 -234 2 1 35 2 2 06 2 3 -7

代表MatrixA * MatrixB

2 5 -233 0 -7

注意:您可以使用

From MatrixA AS a加入矩阵B AS bON a.k = b.k

代替

From MatrixA AS a交叉连接矩阵B AS b哪里 a.k = b.k

看你的喜好

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

What is the most feasible way of implementing matrix multiplication in Google Cloud? Can it be done directly in Bigquery?

解决方案

Assuming MatrixA is a table with below columns:

i, k, value   

and MatrixB - has schema as

k, j, value    

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

This would mimic below matrices :

Matrix A
 2 -3  4
-1  0  2

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

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   

result will be as below

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     

which represents MatrixA * MatrixB

2   5  -23
3   0   -7

as a note: you can use

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

instead of

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

just matter of your preferences

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