CODEWITHSUNDEEP
postgresqlmatrix-multiplication
Taisia Sharapova
Taisia Sharapova

Reputation: 19

Postresql matrix multiplication of a table (multiply a table by itself)

I have a square table similar to this:

  | c | d |
  | - | - |
a | 1 | 2 |
b | 3 | 4 |

I want to calculate matrix multiplication result where this table is multiplied by itself, i.e., this:

  | c  | d |
  | -- | - |
a | 7  | 10 |
b | 15 | 22 |

While I understand that SQL should not be my language of choice for this task, I need to do this in that language. How do I do this?

Upvotes: 0

Views: 327

Answers (1)

Bob
Bob

Reputation: 14654

It will make your life easier if you represent your matrix elements as (i,j,a[i,j]).

WITH matrix AS (SELECT * FROM
  (VALUES ('a','a',1), ('a','b',1), ('b','a',2), ('b','b',3)) AS t(i,j,a))
SELECT m1.i as i, m2.j as j, sum(m1.a * m2.a) FROM matrix m1, matrix m2
GROUP BY  m1.i, m2.j
ORDER BY i,j

This will handle sparse matrices nicely as well

Here a dbfiddle that you might be able to visualize.

Upvotes: 1

Related Questions