BigQuery:将矩阵乘法的直接表格式转换为更传统的矩阵乘法格式? [英] BigQuery: straight table format of matrix multiplication into more traditional Matrix multiplication format?

问题描述

此处的问题显示了如何要将矩阵乘法转换为直线表格式，例如给定(6x1)(路径，值)矩阵，您将获得(36,1)直线表.现在，我想获得传统的矩阵乘法格式，在示例中将是(6x6)矩阵.

This question here shows how to get matrix multiplication into straight table format, for example given (6x1) (Path, value) matrix, you will get (36,1) straight table. Now I want to get the traditional matrix multiplication format, in the example it would be (6x6) matrix.

如何将矩阵乘法的直线表塑造为更传统的矩阵乘法格式?

--standardSQL
WITH MatrixA AS (
  SELECT 1 AS p, 2 AS val UNION ALL
  SELECT 2, -3 UNION ALL
  SELECT 3, 4 UNION ALL
  SELECT 4, -1 UNION ALL
  SELECT 5, 0 UNION ALL
  SELECT 6, 2 
), MatrixB AS (
  SELECT 1 AS p, -1 AS val UNION ALL
  SELECT 2, 2 UNION ALL
  SELECT 3, 3 UNION ALL
  SELECT 4, 3 UNION ALL
  SELECT 5, 0 UNION ALL
  SELECT 6, 1
),
matrixMultiplication AS
(
SELECT a.p AS ap, b.p as bp, SUM(a.val * b.val) val
FROM MatrixA AS a
CROSS JOIN MatrixB AS b
GROUP BY a.p, b.p
ORDER BY a.p, b.p
)

--36 elements for the 6x6 PATHS Matrix
--TODO: how to shape it to 6x6 matrix?
SELECT * FROM matrixMultiplication

推荐答案

如何将其成形为6x6矩阵?

how to shape it to 6x6 matrix?

以下是BigQuery标准SQL的内容.几个简单的选项

Below is for BigQuery Standard SQL. Few simple options

选项1

#standardSQL
SELECT ap AS row, STRING_AGG(CAST(val AS STRING), ' ' ORDER BY bp) AS cols
FROM matrixMultiplication
GROUP BY row
-- ORDER BY row   

应用于问题中的伪数据时-结果为

when applied to dummy data from your question - result is

Row     row     cols     
1       1       -2 4 6 6 0 2     
2       2       3 -6 -9 -9 0 -3  
3       3       -4 8 12 12 0 4   
4       4       1 -2 -3 -3 0 -1  
5       5       0 0 0 0 0 0  
6       6       -2 4 6 6 0 2     

选项#2

Option #2

#standardSQL
SELECT row, 
  cols[OFFSET(0)] AS col1,
  cols[OFFSET(1)] AS col2,
  cols[OFFSET(2)] AS col3,
  cols[OFFSET(3)] AS col4,
  cols[OFFSET(4)] AS col5,
  cols[OFFSET(5)] AS col6
FROM (  
  SELECT ap AS row, ARRAY_AGG(val ORDER BY bp) AS cols
  FROM matrixMultiplication
  GROUP BY ap
)
-- ORDER BY row    

应用于问题中的伪数据时-结果为

when applied to dummy data from your question - result is

Row row col1    col2    col3    col4    col5    col6     
1   1   -2      4       6       6       0       2    
2   2   3       -6      -9      -9      0       -3   
3   3   -4      8       12      12      0       4    
4   4   1       -2      -3      -3      0       -1   
5   5   0       0       0       0       0       0    
6   6   -2      4       6       6       0       2    

这篇关于BigQuery:将矩阵乘法的直接表格式转换为更传统的矩阵乘法格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！