CODEWITHSUNDEEP
matrixgoogle-bigquery
hhh
hhh

Reputation: 52850

BigQuery: straight table format of matrix multiplication into more traditional Matrix multiplication format?

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.

How to shape a straight table of matrix multiplication into more traditional matrix multiplication format?

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

Upvotes: 0

Views: 286

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

how to shape it to 6x6 matrix?

Below is for BigQuery Standard SQL. Few simple options

Option #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

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

Upvotes: 1

Related Questions