Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Raghavendra.

Asked: December 03, 2014 - 1:20 pm UTC

Last updated: December 03, 2014 - 10:50 pm UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Just to prove that Oracle SQL is powerful enough for solving mathematical problems OR for curiosity, can you share us the query to generate the matrix multiplication result. I think that it can be achieved using ONLY MODEL clause?.

Is it possible to write using analytical functions Lag and Lead functions?
      
  
      __   __          __      __ 
     | 6 7 -4|        | 5 -1 6 -3| 
M1=  | 0 4  1|   M2=  | 0  4 1  7|   
     | 2 -2 3|        | 8  2 9  3|  
     |_     _|        |_        _|  


 M1 x M2 =  
 __                                                                      __
| 6×5 + 7×0 + -4×8  6×-1 + 7×4 + -4×2  6×6 + 7×1 + -4×9  6×-3 + 7×7 + -4×3 |
| 0×5 + 4×0 +  1×8  0×-1 + 4×4 + 1×2   0×6 + 4×1 + 1×9   0×-3 + 4×7 + 1×3  |
| 2×5 + -2×0 + 3×8  2×-1 + -2×4 + 3×2  2×6 + -2×1 + 3×9  2×-3 + -2×7 + 3×3 |
|__                                                                      __|  

 Result = 
 __                __
|-2    14     7    19|     
|8     18    13    31|
|34    -4    37   -11|
|__                __|

CREATE TABLE m1 (c1 number, c2 number, c3 number)
/
CREATE TABLE m2 (col1 number, col2 number, col3 number,col4 number)
/
INSERT INTO m1 VALUES (6,7,-4)
/
INSERT INTO m1 VALUES (0,4,1)
/
INSERT INTO m1 VALUES (2,-2,3)
/
COMMIT;

SELECT * FROM m1
/
INSERT INTO m2 VALUES (5,-1,6,-3)
/
INSERT INTO m2 VALUES (0,4,1,7)
/
INSERT INTO m2 VALUES (8,2,9,3)
/

COMMIT;

SELECT * FROM m2
/


Thanks,
Boralli

and Tom said...

I had to change your tables a bit - as everyone knows, the order of insertion into a table does not predict the order of retrieval. So, we need to add a row number in there - some sort of monotonically increasing value - to retrieve the matrix in the right order.

ops$tkyte%ORA11GR2> CREATE TABLE m1 (rn number, c1 number, c2 number, c3 number)
  2  /

Table created.

ops$tkyte%ORA11GR2> CREATE TABLE m2 (rn number, col1 number, col2 number, col3 number,col4 number)
  2  /

Table created.

ops$tkyte%ORA11GR2> INSERT INTO m1 VALUES (1, 6,7,-4)
  2  /

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO m1 VALUES (2, 0,4,1)
  2  /

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO m1 VALUES (3, 2,-2,3)
  2  /

1 row created.

ops$tkyte%ORA11GR2> COMMIT;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> SELECT * FROM m1
  2  /

        RN         C1         C2         C3
---------- ---------- ---------- ----------
         1          6          7         -4
         2          0          4          1
         3          2         -2          3

ops$tkyte%ORA11GR2> INSERT INTO m2 VALUES (1, 5,-1,6,-3)
  2  /

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO m2 VALUES (2, 0,4,1,7)
  2  /

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO m2 VALUES (3, 8,2,9,3)
  2  /

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> COMMIT;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> SELECT * FROM m2
  2  /

        RN       COL1       COL2       COL3       COL4
---------- ---------- ---------- ---------- ----------
         1          5         -1          6         -3
         2          0          4          1          7
         3          8          2          9          3


Now, what we need to do is transpose matrix M2 to make it set friendly. We want to join the first row in M1 with the first column in M2 and the 2nd column in M2 and so on. Same with row 2 in m1. So, it we turn M2 on it's side we can just cartesian join to get all of our points:


ops$tkyte%ORA11GR2> with d1
  2  as
  3  ( select rn, cname, val
  4      from m2
  5   unpivot ( val for cname in (COL1, COL2, COL3, COL4) )
  6  )
  7  select * from d1;

        RN CNAM        VAL
---------- ---- ----------
         1 COL1          5
         1 COL2         -1
         1 COL3          6
         1 COL4         -3
         2 COL1          0
         2 COL2          4
         2 COL3          1
         2 COL4          7
         3 COL1          8
         3 COL2          2
         3 COL3          9
         3 COL4          3

12 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with d1
  2  as
  3  ( select rn, cname, val
  4      from m2
  5   unpivot ( val for cname in (COL1, COL2, COL3, COL4) )
  6  ),
  7  d2 as
  8  (
  9  select cname, x1, x2, x3
 10    from d1
 11   pivot (max(val) for rn in ( 1 as x1, 2 as x2, 3 as x3 ))
 12  )
 13  select * from d2;

CNAM         X1         X2         X3
---- ---------- ---------- ----------
COL1          5          0          8
COL2         -1          4          2
COL3          6          1          9
COL4         -3          7          3


a combination pivot/unpivot transposed our result set - so we have two sets each with three columns (easy to multiply now)....

ops$tkyte%ORA11GR2> with d1
  2  as
  3  ( select rn, cname, val
  4      from m2
  5   unpivot ( val for cname in (COL1, COL2, COL3, COL4) )
  6  ),
  7  d2 as
  8  (
  9  select cname, x1, x2, x3
 10    from d1
 11   pivot (max(val) for rn in ( 1 as x1, 2 as x2, 3 as x3 ))
 12  ),
 13  d3 as
 14  (
 15  select c1*x1+c2*x2+c3*x3 val, rn, cname
 16    from m1, d2
 17  )
 18  select * from d3;

       VAL         RN CNAM
---------- ---------- ----
        -2          1 COL1
         8          2 COL1
        34          3 COL1
        14          1 COL2
        18          2 COL2
        -4          3 COL2
         7          1 COL3
        13          2 COL3
        37          3 COL3
        19          1 COL4
        31          2 COL4
       -11          3 COL4

12 rows selected.


and all we have to do is turn the CNAME column into columns - pivoting again:

ops$tkyte%ORA11GR2> with d1
  2  as
  3  ( select rn, cname, val
  4      from m2
  5   unpivot ( val for cname in (COL1, COL2, COL3, COL4) )
  6  ),
  7  d2 as
  8  (
  9  select cname, x1, x2, x3
 10    from d1
 11   pivot (max(val) for rn in ( 1 as x1, 2 as x2, 3 as x3 ))
 12  ),
 13  d3 as
 14  (
 15  select c1*x1+c2*x2+c3*x3 val, rn, cname
 16    from m1, d2
 17  )
 18  select column1, column2, column3, column4
 19    from d3
 20   pivot (max(val) for cname in ( 'COL1' as column1, 'COL2' as column2, 'COL3' as column3, 'COL4' as column4 ))
 21   order by rn
 22  /

   COLUMN1    COLUMN2    COLUMN3    COLUMN4
---------- ---------- ---------- ----------
        -2         14          7         19
         8         18         13         31
        34         -4         37        -11



qed :)

I am looking forward to seeing all of the other techniques others come up with :)

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Similar to Tom's, but pivot only once

Stew Ashton, December 03, 2014 - 9:43 pm UTC


I am a firm believer in storing matrices one cell per row: row number, column number and cell value(s). This makes every kind of manipulation easier. When the data isn't stored that way, UNPIVOT can convert it to that format.

In my variant, I start from Tom's corrected tables with row numbers added.
- first I convert both tables to a row + column + value format,
- then I multiply each cell, and use PIVOT to sum the cell values and put them in the right output column.

with matrixa as (
  select rn, to_number(substr(col,2)) cn, cellval
  from m1
  unpivot(cellval for col in(c1,c2,c3))
)
, matrixb as (
  select rn, to_number(substr(col,4)) cn, cellval
  from m2
  unpivot(cellval for col in(col1,col2,col3,col4))
)
select * from (
  select a.rn, b.cn, a.cellval*b.cellval cellval
  from matrixa a join matrixb b on a.cn = b.rn
)
pivot(sum(cellval) for cn in(1 col1, 2 col2, 3 col3, 4 col4))
order by rn;

        RN       COL1       COL2       COL3       COL4
---------- ---------- ---------- ---------- ----------
         1         -2         14          7         19 
         2          8         18         13         31 
         3         34         -4         37        -11
If the matrices were stored one cell value per table row and the output was a new table, the code would be simply:
select a.rn, b.cn,
sum(a.cellval*b.cellval) cellval
from matrixa a
join matrixb b on a.cn = b.rn
group by a.rn, b.cn;

Tom Kyte
December 03, 2014 - 10:49 pm UTC

see, my old math background got in the way. I learned to multiply matrices in college by the transformation approach (at least in my head I "saw" it that way). So for me it was natural to transpose, multiply and pivot :)

Your approach is very nice - i like it. but it is unpivot - unpivot - pivot, mine is unpivot - pivot - pivot, sort of six one way half dozen the other :)

Maybe see you here: http://www.meetup.com/parisoracle/events/218559162/ next week...

Using UNPIVOT & PIVOT together

Stew Ashton, December 03, 2014 - 9:52 pm UTC


If you want, you can do UNPIVOT and PIVOT in the same SELECT:
select * from m2
unpivot (val for cname in (COL1, COL2, COL3, COL4))
pivot(max(val) for rn in(1 x1, 2 x2, 3 x3));

CNAME         X1         X2         X3
----- ---------- ---------- ----------
COL1           5          0          8 
COL2          -1          4          2 
COL3           6          1          9 
COL4          -3          7          3

Tom Kyte
December 03, 2014 - 10:50 pm UTC

agreed, I find that very hard to read - sort of like winning an obfuscated C coding contest :) I usually inline view or with subquery factor the bits to build it piece by piece.

I let the optimizer merge it all back together for me

Paris meetup

Stew Ashton, December 04, 2014 - 12:19 pm UTC


Nice of you to mention the Paris meetup. I signed up for it, then realized I had a conflict :( I will be at UKOUG Tech 14 though!

The MODEL Clause

Rajeshwaran, Jeyabal, December 07, 2014 - 12:07 pm UTC 

rajesh@ORA11G> select a1,a2,a3,a4
  2  from m1
  3    model
  4      reference m2 on ( select col1,col2,
  5            col3,col4,
  6            row_number() over(order by r) r
  7            from m2)
  8        dimension by(r)
  9        measures(col1,col2,col3,col4)
 10      main conversions
 11      partition by(c1 x1,c2 x2,c3 x3)
 12      dimension by (1 x)
 13      measures(c1,c2,c3,0 a1,0 a2,0 a3,0 a4,r rn)
 14      rules
 15      (
 16        a1[1] = ( c1[cv()] * m2.col1[cv(x)] ) +
 17                ( c2[cv()] * m2.col1[cv(x)+1] ) +
 18                ( c3[cv()] * m2.col1[cv(x)+2] ) ,
 19        a2[1] = ( c1[cv()] * m2.col2[cv(x)] ) +
 20                ( c2[cv()] * m2.col2[cv(x)+1] ) +
 21                ( c3[cv()] * m2.col2[cv(x)+2] ) ,
 22        a3[1] = ( c1[cv()] * m2.col3[cv(x)] ) +
 23                ( c2[cv()] * m2.col3[cv(x)+1] ) +
 24                ( c3[cv()] * m2.col3[cv(x)+2] ) ,
 25        a4[1] = ( c1[cv()] * m2.col4[cv(x)] ) +
 26                ( c2[cv()] * m2.col4[cv(x)+1] ) +
 27                ( c3[cv()] * m2.col4[cv(x)+2] )
 28      )
 29  order by rn
 30  /

        A1         A2         A3         A4
---------- ---------- ---------- ----------
        -2         14          7         19
         8         18         13         31
        34         -4         37        -11

3 rows selected.

rajesh@ORA11G>

Use the Oracle provided solution

Dan, December 10, 2014 - 3:45 pm UTC

Depends on if you are trying to see what you can do with Oracle can do (see above) or have a real problem you need to address...

Look at the Oracle supplied package UTL_NLA. Does Vectors/Matrices and Linear Algebra. You just have to figure out how to use it..

another approach

Amol Patel, April 26, 2017 - 5:06 pm UTC 

select rownum, 
regexp_substr(val, '[^,]+', 1, 1) * regexp_substr(col1, '[^,]+', 1, 1) + 
regexp_substr(val, '[^,]+', 1, 2) * regexp_substr(col1, '[^,]+', 1, 2) + 
regexp_substr(val, '[^,]+', 1, 3) * regexp_substr(col1, '[^,]+', 1, 3) as f1,
regexp_substr(val, '[^,]+', 1, 1) * regexp_substr(col2, '[^,]+', 1, 1) + 
regexp_substr(val, '[^,]+', 1, 2) * regexp_substr(col2, '[^,]+', 1, 2) + 
regexp_substr(val, '[^,]+', 1, 3) * regexp_substr(col2, '[^,]+', 1, 3) as f2,
regexp_substr(val, '[^,]+', 1, 1) * regexp_substr(col3, '[^,]+', 1, 1) + 
regexp_substr(val, '[^,]+', 1, 2) * regexp_substr(col3, '[^,]+', 1, 2) + 
regexp_substr(val, '[^,]+', 1, 3) * regexp_substr(col3, '[^,]+', 1, 3) as f3,
regexp_substr(val, '[^,]+', 1, 1) * regexp_substr(col4, '[^,]+', 1, 1) + 
regexp_substr(val, '[^,]+', 1, 2) * regexp_substr(col4, '[^,]+', 1, 2) + 
regexp_substr(val, '[^,]+', 1, 3) * regexp_substr(col4, '[^,]+', 1, 3) as f4
from (
select * from (select 
listagg (col1, ',') within group (order by rn) as col1,
listagg (col2, ',') within group (order by rn) as col2,
listagg (col3, ',') within group (order by rn) as col3,
listagg (col4, ',') within group (order by rn) as col4
from m2 order by rn) m3, (select c1||','||c2||','||c3 as val from m1 order by rn)
);

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.