SQL Query for Matrix Multiplication in Oracle

oracle

I am facing a problem in writing a sql query for multiplication of two matrices.
Consider that i have a 3×3 matrices A and B, having the following schema:

A(i,j,val) and B(i,j,val) where

i int: Row index

j int: Column Index

val int: Cell value

The product of matrix A and matrix B is matrix C having cell at row i and column j. I want a single sql query to get the matrix product of A and B.

Best Answer

Input (A, then B):

set pages 12

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
),
B as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, 12 - rownum as val from dual connect by level <= 9
)
select A.i, A.j, A.val from A union all
select B.i, B.j, B.val from B
;

         I          J        VAL
---------- ---------- ----------
         1          1          1
         1          2          2
         1          3          3
         2          1          4
         2          2          5
         2          3          6
         3          1          7
         3          2          8
         3          3          9

         I          J        VAL
---------- ---------- ----------
         1          1         11
         1          2         10
         1          3          9
         2          1          8
         2          2          7
         2          3          6
         3          1          5
         3          2          4
         3          3          3

And their product (AB):

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
),
B as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, 12 - rownum as val from dual connect by level <= 9
)
----------------------------------------------------------------
select
  A.i as i,
  B.j as j,
  sum (A.val * B.val) as val
from
  A, B
where A.j = B.i  
group by A.i, B.j
order by
  1, 2
;

         I          J        VAL
---------- ---------- ----------
         1          1         42
         1          2         36
         1          3         30
         2          1        114
         2          2         99
         2          3         84
         3          1        186
         3          2        162
         3          3        138
Related Question