Members   Search   Help    Register    Login   Home
Home » SQL & PL/SQL » SQL & PL/SQL » matrix multiplication (Oracle 10g, Windows XP)
matrix multiplication [message #442485] Mon, 08 February 2010 07:55 Go to next message
joshua82
Messages: 31
Registered: December 2009
Member
Hi! I need to do a matrix multiplication in Oracle 10g (like this).

Consider a situation like this:
CREATE TABLE A(
 rows in NUMBER NOT NULL,
 A in NUMBER NOT NULL,
 B in NUMBER NOT NULL,
 C in NUMBER NOT NULL,
 D in NUMBER NOT NULL,
 E in NUMBER NOT NULL,
 F in NUMBER NOT NULL,
 G in NUMBER NOT NULL,
 H in NUMBER NOT NULL
)
INSERT INTO A VALUES(1, -3, 5, -2, 8, 6, 8, 9, 0);
INSERT INTO A VALUES(2, 3, 8, 6, 4, 9, 3, 6, -7);
INSERT INTO A VALUES(3, 2, 8, 9, 3, 4, 3, 7, 9);
INSERT INTO A VALUES(4, 1, -4, 4, 5, 7, 9, 9, 5);
INSERT INTO A VALUES(5, 7, 6, 2, -9, 7, 9, 9, 6);
INSERT INTO A VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8);
INSERT INTO A VALUES(7, 4, 0, 2, 5, 9, 5, 8, 3);
--==============================================

CREATE TABLE B(
 rows in NUMBER NOT NULL,
 A in NUMBER NOT NULL,
 B in NUMBER NOT NULL,
 C in NUMBER NOT NULL,
 D in NUMBER NOT NULL,
 E in NUMBER NOT NULL,
 F in NUMBER NOT NULL,
 G in NUMBER NOT NULL,
 H in NUMBER NOT NULL,
 I in NUMBER NOT NULL
)
INSERT INTO B VALUES(1, 18, 5, 12, 8, 0, 8, 9, -2, 1);
INSERT INTO B VALUES(2, 0, 8, -6, 4, -2, 3, 6, 5, 6);
INSERT INTO B VALUES(3, 2 , 8, 9, 3, 4, 3, 7, 4, 9);
INSERT INTO B VALUES(4, 1, 9, 0, 5, 7, 9, -9, 5, 3);
INSERT INTO B VALUES(5, 7, 6, 2, 9, 7, 9, 9, 6, 8);
INSERT INTO B VALUES(6, 5, 0, 9, 6, 5, 8, 6, 8, 9);
INSERT INTO B VALUES(7, 0, 4, 2, 5, 9, 0, 8, 6, 1);
INSERT INTO B VALUES(8, 5, 9, 5, 3, 7, -3, 0, 2, 6);



------------------- Matrix A -----------------------------

rows     1     2     3     4     5     6     7     8
--------------------------------------------------------
1       -3     5    -2     8     6     8     9     0
2        3     8     6     4     9     3     6    -7
3        2     8     9     3     4     3     7     9
4        1    -4     4     5     7     9     9     5
5        7     6     2    -9     7     9     9     6
6        5     2     9     6     5     8     6     8
7        4     0     2     5     9     5     8     3


---------------- Matrix B ----------------------------

rows     1     2     3     4     5     6     7     8     9
--------------------------------------------------------------
1       18     5    12     8     0     8     9    -2     1
2        0     8    -6     4    -2     3     6     5     6
3        2     8     9     3     4     3     7     4     9
4        1     9     0     5     7     9    -9     5     3
5        7     6     2     9     7     9     9     6     8
6        5     0     9     6     5     8     6     8     9
7        0     4     2     5     9     0     8     6     1
8        5     9     5     3     7    -3     0     2     6


I found a solution in SQL (here), but I need to do that in PL/SQL.
Thanks

[Updated on: Mon, 08 February 2010 07:56]

Report message to a moderator
Re: matrix multiplication [message #442497 is a reply to message #442485] Mon, 08 February 2010 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to first know how to order the rows in the tables.
Which one is the first one? and so on.
If you have no column to order the rows then it is not possible.

Regards
Michel
Report message to a moderator
Re: matrix multiplication [message #442499 is a reply to message #442497] Mon, 08 February 2010 10:11 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
Michel Cadot wrote on Mon, 08 February 2010 09:46
You have to first know how to order the rows in the tables.

Probably, I misunderstand your reply.
The incremental number of row isn't an order?
Report message to a moderator
Re: matrix multiplication [message #442500 is a reply to message #442485] Mon, 08 February 2010 10:14 Go to previous messageGo to next message
_jum
Messages: 197
Registered: February 2008
Senior Member
Here is the example in a version for ORACLE 11.
The code is very near to the original, guess there are more effective ways to solve the problem:
DROP TABLE TESTMAT_A;

DROP TABLE TESTMAT_B;
--=====================================

CREATE TABLE TESTMAT_A(
 nr INTEGER NOT NULL,
 v1 INTEGER NOT NULL,
 v2 INTEGER NOT NULL,
 v3 INTEGER NOT NULL,
 v4 INTEGER NOT NULL,
 v5 INTEGER NOT NULL
);

INSERT INTO TESTMAT_A VALUES(1, -3, 5, -2, 8, 6);
INSERT INTO TESTMAT_A VALUES(2, 3, 8, 6, 4, 9);
INSERT INTO TESTMAT_A VALUES(3, 2, 8, 9, 3, 4);
INSERT INTO TESTMAT_A VALUES(4, 1, -4, 4, 5, -1);
INSERT INTO TESTMAT_A VALUES(5, 7, 6, 2, -9, 0);

--=====================================

CREATE TABLE TESTMAT_B(
 nr INTEGER NOT NULL,
 v1 INTEGER NOT NULL,
 v2 INTEGER NOT NULL,
 v3 INTEGER NOT NULL,
 v4 INTEGER NOT NULL,
 v5 INTEGER NOT NULL
);

INSERT INTO TESTMAT_B VALUES(1, 18, 5, 12, 8, 0);
INSERT INTO TESTMAT_B VALUES(2, 0, 8, -6, 4, -2);
INSERT INTO TESTMAT_B VALUES(3, 2 , 8, 9, 3, 4);
INSERT INTO TESTMAT_B VALUES(4, 1, 9, 0, 5, 7);
INSERT INTO TESTMAT_B VALUES(5, 7, 6, 2, 9, 7);

--=====================================

SELECT * FROM TESTMAT_A;

SELECT * FROM TESTMAT_B;

/*
--=========== Matrice TESTMAT_A ==============
--        NR         V1         V2         V3         V4         V5
------------ ---------- ---------- ---------- ---------- ----------
--         1         -3          5         -2          8          6
--         2          3          8          6          4          9
--         3          2          8          9          3          4
--         4          1         -4          4          5         -1
--         5          7          6          2         -9          0


--============ Matrice TESTMAT_B =============
--        NR         V1         V2         V3         V4         V5
------------ ---------- ---------- ---------- ---------- ----------
--         1         18          5         12          8          0
--         2          0          8         -6          4         -2
--         3          2          8          9          3          4
--         4          1          9          0          5          7
--         5          7          6          2          9          7

*/

--ADDITION A+B

SELECT 
     A.v1 + B.v1 as v1,
     A.v2 + B.v2 as v2,
     A.v3 + B.v3 as v3,
     A.v4 + B.v4 as v4,
     A.v5 + B.v5 as v5
   FROM TESTMAT_A a INNER JOIN TESTMAT_B b ON A.nr = B.nr;

--        V1         V2         V3         V4         V5
------------ ---------- ---------- ---------- ----------
--        15         10         10         16          6
--         3         16          0          8          7
--         4         16         18          6          8
--         2          5          4         10          6
--        14         12          4          0          7
        
--=====================================

--SUBTRACTION A-B
SELECT 
     A.v1 -  B.v1 as v1,
     A.v2 - B.v2 as v2,
     A.v3 - B.v3 as v3,
     A.v4 - B.v4 as v4,
     A.v5 - B.v5 as v5
   FROM TESTMAT_A a INNER JOIN TESTMAT_B b ON A.nr = B.nr;

--        V1         V2         V3         V4         V5
------------ ---------- ---------- ---------- ----------
--       -21          0        -14          0          6
--         3          0         12          0         11
--         0          0          0          0          0
--         0        -13          4          0         -8
--         0          0          0        -18         -7

DROP TABLE TESTMAT_A;

--===============================================

CREATE TABLE TESTMAT_A(
 nr INTEGER NOT NULL,
 v1 INTEGER NOT NULL,
 v2 INTEGER NOT NULL,
 v3 INTEGER NOT NULL,
 v4 INTEGER NOT NULL,
 v5 INTEGER NOT NULL,
 v6 INTEGER NOT NULL,
 v7 INTEGER NOT NULL,
 v8 INTEGER NOT NULL
);


INSERT INTO TESTMAT_A VALUES(1, -3, 5, -2, 8, 6, 8, 9, 0);
INSERT INTO TESTMAT_A VALUES(2, 3, 8, 6, 4, 9, 3, 6, -7);
INSERT INTO TESTMAT_A VALUES(3, 2, 8, 9, 3, 4, 3, 7, 9);
INSERT INTO TESTMAT_A VALUES(4, 1, -4, 4, 5, 7, 9, 9, 5);
INSERT INTO TESTMAT_A VALUES(5, 7, 6, 2, -9, 7, 9, 9, 6);
INSERT INTO TESTMAT_A VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8);
INSERT INTO TESTMAT_A VALUES(7, 4, 0, 2, 5, 9, 5, 8, 3);

--==============================================

DROP TABLE TESTMAT_B;

CREATE TABLE TESTMAT_B(
 nr INTEGER NOT NULL,
 v1 INTEGER NOT NULL,
 v2 INTEGER NOT NULL,
 v3 INTEGER NOT NULL,
 v4 INTEGER NOT NULL,
 v5 INTEGER NOT NULL,
 v6 INTEGER NOT NULL,
 v7 INTEGER NOT NULL,
 v8 INTEGER NOT NULL,
 v9 INTEGER NOT NULL
);

INSERT INTO TESTMAT_B VALUES(1, 18, 5, 12, 8, 0, 8, 9, -2, 1);
INSERT INTO TESTMAT_B VALUES(2, 0, 8, -6, 4, -2, 3, 6, 5, 6);
INSERT INTO TESTMAT_B VALUES(3, 2 , 8, 9, 3, 4, 3, 7, 4, 9);
INSERT INTO TESTMAT_B VALUES(4, 1, 9, 0, 5, 7, 9, -9, 5, 3);
INSERT INTO TESTMAT_B VALUES(5, 7, 6, 2, 9, 7, 9, 9, 6, 8);
INSERT INTO TESTMAT_B VALUES(6, 5, 0, 9, 6, 5, 8, 6, 8, 9);
INSERT INTO TESTMAT_B VALUES(7, 0, 4, 2, 5, 9, 0, 8, 6, 1);
INSERT INTO TESTMAT_B VALUES(8, 5, 9, 5, 3, 7, -3, 0, 2, 6);

--===== Transform matrix A into array A ======
WITH arr_A AS
(SELECT nr, decode(mod(rownum,8),0,8,mod(rownum,8)) Col, Val FROM TESTMAT_A UNPIVOT (Val FOR Col IN (v1,v2,v3,v4,v5,v6,v7,v8)) unpvt ),
--===== Transform matrix B into array B ======
arr_B AS
(SELECT nr, decode(mod(rownum,9),0,9,mod(rownum,9)) Col, Val FROM TESTMAT_B UNPIVOT (Val FOR Col IN (v1,v2,v3,v4,v5,v6,v7,v8,v9)) unpvt),
--===== Calculate Product A*B =================
product AS (SELECT rowA as Rw, colB as Col, sum(product) Val FROM
(SELECT arr_A.nr rowA, arr_A.col colA, 
        arr_B.nr rowB, arr_B.col colB, 
        arr_A.val * arr_B.val as product 
     FROM arr_A INNER JOIN arr_B 
     ON arr_A.col = arr_B.nr) t1
GROUP BY colB, rowA
) 
--===== Back to matrix form (reverse transformation) =======
SELECT * FROM product
  PIVOT (max(Val) FOR Col IN (1,2,3,4,5,6,7,8,9)) piv  
ORDER BY rw;
 
--RW	1	2	3	4	5	6	7	8	9
-----------------------------------------
--1	32	153	18	177	201	175	91	217	162
--2	113	178	64	202	119	228	228	178	180
--3	145	306	151	206	210	127	212	195	260
--4	150	173	210	206	269	173	157	197	204
--5	245	150	209	221	150	116	383	159	216
--6	229	293	263	252	259	212	207	212	282
--7	184	194	160	223	224	195	180	173	180

[Updated on: Mon, 08 February 2010 10:16]

Report message to a moderator
Re: matrix multiplication [message #442501 is a reply to message #442499] Mon, 08 February 2010 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
joshua82 wrote on Mon, 08 February 2010 17:11
Michel Cadot wrote on Mon, 08 February 2010 09:46
You have to first know how to order the rows in the tables.

Probably, I misunderstand your reply.
The incremental number of row isn't an order?

Embarassed sorry, sorry, sorry, didn't see it, time to have a snap.

You have the row numbers and the column numbers and the formulas, so you have the query. The only difficuly is to carefully write each result column formula.

Regards
Michel
Report message to a moderator
Re: matrix multiplication [message #442502 is a reply to message #442500] Mon, 08 February 2010 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PIVOT/UNPIVOT does not exist in 10g.

Regards
Michel

[Updated on: Mon, 08 February 2010 10:47]

Report message to a moderator
Re: matrix multiplication [message #442504 is a reply to message #442485] Mon, 08 February 2010 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
CREATE TABLE A(
 r NUMBER NOT NULL,
 A NUMBER NOT NULL,
 B NUMBER NOT NULL,
 C NUMBER NOT NULL,
 D NUMBER NOT NULL,
 E NUMBER NOT NULL,
 F NUMBER NOT NULL,
 G NUMBER NOT NULL,
 H NUMBER NOT NULL
);
INSERT INTO A VALUES(1, -3, 5, -2, 8, 6, 8, 9, 0);
INSERT INTO A VALUES(2, 3, 8, 6, 4, 9, 3, 6, -7);
INSERT INTO A VALUES(3, 2, 8, 9, 3, 4, 3, 7, 9);
INSERT INTO A VALUES(4, 1, -4, 4, 5, 7, 9, 9, 5);
INSERT INTO A VALUES(5, 7, 6, 2, -9, 7, 9, 9, 6);
INSERT INTO A VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8);
INSERT INTO A VALUES(7, 4, 0, 2, 5, 9, 5, 8, 3);
--==============================================

CREATE TABLE B(
 r NUMBER NOT NULL,
 A NUMBER NOT NULL,
 B NUMBER NOT NULL,
 C NUMBER NOT NULL,
 D NUMBER NOT NULL,
 E NUMBER NOT NULL,
 F NUMBER NOT NULL,
 G NUMBER NOT NULL,
 H NUMBER NOT NULL,
 I NUMBER NOT NULL
);
INSERT INTO B VALUES(1, 18, 5, 12, 8, 0, 8, 9, -2, 1);
INSERT INTO B VALUES(2, 0, 8, -6, 4, -2, 3, 6, 5, 6);
INSERT INTO B VALUES(3, 2 , 8, 9, 3, 4, 3, 7, 4, 9);
INSERT INTO B VALUES(4, 1, 9, 0, 5, 7, 9, -9, 5, 3);
INSERT INTO B VALUES(5, 7, 6, 2, 9, 7, 9, 9, 6, 8);
INSERT INTO B VALUES(6, 5, 0, 9, 6, 5, 8, 6, 8, 9);
INSERT INTO B VALUES(7, 0, 4, 2, 5, 9, 0, 8, 6, 1);
INSERT INTO B VALUES(8, 5, 9, 5, 3, 7, -3, 0, 2, 6);


Next time, please post a WORKING test case.

SQL> with 
  2    a2 as (
  3      select r, col, decode(col, 1,a, 2,b, 3,c, 4,d, 5,e, 6,f, 7,g, 8,h) val
  4      from a, (select level col from dual connect by level <= 8)
  5    ),
  6    b2 as (
  7      select r, col, decode(col, 1,a, 2,b, 3,c, 4,d, 5,e, 6,f, 7,g, 8,h, 9,i) val
  8      from b, (select level col from dual connect by level <= 9)
  9    ),
 10    p as ( -- product
 11     select a2.r, b2.col, sum(a2.val*b2.val) val
 12     from a2, b2
 13     where a2.col = b2.r
 14     group by a2.r, b2.col
 15   )
 16  select r,
 17         sum(decode(col,1,val)) c1,
 18         sum(decode(col,2,val)) c2,
 19         sum(decode(col,3,val)) c3,
 20         sum(decode(col,4,val)) c4,
 21         sum(decode(col,5,val)) c5,
 22         sum(decode(col,6,val)) c6,
 23         sum(decode(col,7,val)) c7,
 24         sum(decode(col,8,val)) c8,
 25         sum(decode(col,9,val)) c9
 26  from p
 27  group by r
 28  order by r
 29  /
         R         C1         C2         C3         C4         C5         C6         C7         C8         C9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1         32        153         18        177        201        175         91        217        162
         2        113        178         64        202        119        228        228        178        180
         3        145        306        151        206        210        127        212        195        260
         4        150        173        210        206        269        173        157        197        204
         5        245        150        209        221        150        116        383        159        216
         6        229        293        263        252        259        212        207        212        282
         7        184        194        160        223        224        195        180        173        180

7 rows selected.

Regards
Michel

[Updated on: Mon, 08 February 2010 11:59]

Report message to a moderator
Re: matrix multiplication [message #442552 is a reply to message #442485] Mon, 08 February 2010 23:30 Go to previous messageGo to next message
rleishman
Messages: 3364
Registered: October 2005
Location: Melbourne, Australia
Senior Member
joshua82 wrote on Tue, 09 February 2010 00:55
I found a solution in SQL (here), but I need to do that in PL/SQL.

Sounds like a homework assignment.
Report message to a moderator
Re: matrix multiplication [message #442553 is a reply to message #442552] Mon, 08 February 2010 23:32 Go to previous messageGo to next message
BlackSwan
Messages: 13052
Registered: January 2009
Senior Member
>Sounds like a homework assignment.
REALLY?

who else would be attempting matrix math while (ab)using any RDBMS?
Report message to a moderator
Re: matrix multiplication [message #442584 is a reply to message #442553] Tue, 09 February 2010 03:58 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
BlackSwan wrote on Mon, 08 February 2010 23:32
who else would be attempting matrix math while (ab)using any RDBMS?

this sounds good..but how? (I'm a real newbie with Oracle.. Sad )


I solved with Michel solution (thanks!!) but I'm in trouble to create subquery like a2: in a real test I'm in a situation like this (a column vector represent a matrix):
CREATE TABLE example(
value NUMBER
);
INSERT INTO example VALUES(11);
INSERT INTO example VALUES(12);
INSERT INTO example VALUES(13);
INSERT INTO example VALUES(21);
INSERT INTO example VALUES(22);
INSERT INTO example VALUES(23);
INSERT INTO example VALUES(31);
INSERT INTO example VALUES(32);
INSERT INTO example VALUES(33);

-------which is like:-----------
VALUE      
  11
  12
  13
  21
  22
  23
  31
  32
  33

--- which represents a matrix:
              11         12      13
              21         22      23
              31         32      33


but, in your solution, I need to create a situation like this:
r     col   val
1	1	11
2	1	21
3	1	31
1	2	12
2	2	22
3	2	32
1	3	13
2	3	23
3	3	23


Report message to a moderator
Re: matrix multiplication [message #442598 is a reply to message #442502] Tue, 09 February 2010 05:38 Go to previous messageGo to next message
ramoradba
Messages: 1729
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Mon, 08 February 2010 22:16
PIVOT/UNPIVOT does not exist in 10g.

Regards
Michel


Quote:
Here is the example in a version for ORACLE 11.
The code is very near to the original, guess there are more effective ways to solve the problem:


sriram
Report message to a moderator
Re: matrix multiplication [message #442602 is a reply to message #442598] Tue, 09 February 2010 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OP is clear on his version, try to FIRST answer his question, otherwise there is no limit to what can be posted, you could also post a solution to the next topic.

Regards
Michel
Report message to a moderator
Re: matrix multiplication [message #442603 is a reply to message #442584] Tue, 09 February 2010 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but, in your solution, I need to create a situation like this:

You do not need, the query does it for you from the input table. You just have to put the correct numbers in "connect by" clause.

Regards
Michel
Report message to a moderator
Re: matrix multiplication [message #442613 is a reply to message #442603] Tue, 09 February 2010 07:33 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
Michel Cadot wrote on Tue, 09 February 2010 06:10
You just have to put the correct numbers in "connect by" clause.

This makes an error (a matrix of 27 rows, not a matrix of 9 rows Shocked )..

The problem is about "r" and "col" column which values is really important for your solution. I came from a vector matrix and I need to put in "r" and "col" the right values (as it can be considered as a matrix 3 x 3)
r     col   val
1	1	11
2	1	21
3	1	31
1	2	12
2	2	22
3	2	32
1	3	13
2	3	23
3	3	23

Thanks!

[Updated on: Tue, 09 February 2010 07:33]

Report message to a moderator
Re: matrix multiplication [message #442626 is a reply to message #442613] Tue, 09 February 2010 09:41 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to put in "r" and "col" the right values (as it can be considered as a matrix 3 x 3)

No, you don't need, you need... (what is in my previous post).
Just create A and B table for your data, update the query with correct numbers and execute the query.

Regards
Michel
Report message to a moderator
Re: matrix multiplication [message #442628 is a reply to message #442626] Tue, 09 February 2010 09:53 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
the problem is that I can't create A and B. Confused
I have a table (like example in my previous post) which is created in another procedure.

example is like this:
VALUE      
  11
  12
  13
  21
  22
  23
  31
  32
  33


I need to format this table like (and use it in your solution):
r     col    val
1	1	11
2	1	21
3	1	31
1	2	12
2	2	22
3	2	32
1	3	13
2	3	23
3	3	33

Thanks

[Updated on: Tue, 09 February 2010 09:56]

Report message to a moderator
Re: matrix multiplication [message #442630 is a reply to message #442628] Tue, 09 February 2010 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to format this table like (and use it in your solution):

You don't need it for my solution, my solution starts from the tables you provided.

Quote:
example is like this:

As I said in my first answer:
Quote:
You have to first know how to order the rows in the tables.
Which one is the first one? and so on.
If you have no column to order the rows then it is not possible.

Regards
Michel
Report message to a moderator
Re: matrix multiplication [message #442632 is a reply to message #442485] Tue, 09 February 2010 10:14 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
I understand.
So, I can pre-process my table example and I can create a matrix 3x3, like this:

row     A          B          C
  1      11         12       13
  2      21         22       23
  3      31         32       33


and then use your solution..

But my question is the same: how extract a 3x3 matrix from a vector matrix?

thanks
Report message to a moderator
Re: matrix multiplication [message #442636 is a reply to message #442632] Tue, 09 February 2010 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how extract a 3x3 matrix from a vector matrix?

I don't understand the question.
What should be the result from the matrix you gave?

Regards
Michel
Report message to a moderator
Re: matrix multiplication [message #442656 is a reply to message #442636] Tue, 09 February 2010 14:44 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
Michel Cadot wrote on Tue, 09 February 2010 10:59
Quote:
how extract a 3x3 matrix from a vector matrix?

I don't understand the question.

No, it's me. I'm not able to explain what I need.

The problem is that in input I've a matrix column (like example, not like A or B) which came from a previous procedure; this matrix column it's a table representation of a 3x3 matrix. Like this:

----the matrix column 'example' in input
VALUE      
  11
  12
  13
  21
  22
  23
  31
  32
  33


and 

---what the 'example' table represent:
 11         12       13
 21         22       23
 31         32       33


I need from example to replace values in this form:

r     col    val
1	1	11
2	1	21
3	1	31
1	2	12
2	2	22
3	2	32
1	3	13
2	3	23
3	3	33


and then use it in your solution.


Report message to a moderator
Re: matrix multiplication [message #442679 is a reply to message #442656] Tue, 09 February 2010 23:17 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again in the input table you must have a column that indicates the order.
And you need a rule to transform the subsequent rows into columns:
- is this, row1-col1, row1-col2, row1-col3, row2-col1...
- is this, row1-col1, row2-col1, row3-col1, row1-col2...
- other?

Regards
Michel
Report message to a moderator
Re: matrix multiplication [message #442701 is a reply to message #442679] Wed, 10 February 2010 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 35397
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please ALWAYS post a test case with your question.
create table t (id integer, val integer);
insert into t values (1,  11);
insert into t values (2,  12);
insert into t values (3,  13);
insert into t values (4,  21);
insert into t values (5,  22);
insert into t values (6,  23);
insert into t values (7,  31);
insert into t values (8,  32);
insert into t values (9,  33);
commit;

SQL> select * from t order by id;
        ID        VAL
---------- ----------
         1         11
         2         12
         3         13
         4         21
         5         22
         6         23
         7         31
         8         32
         9         33

9 rows selected.

For the first case of sharing out:
SQL> with
  2    data as (
  3      select trunc((id-1)/3)+1 line, mod(id-1,3)+1 col, val
  4      from t
  5    )
  6  select line, 
  7         max(decode(col,1,val)) col1,
  8         max(decode(col,2,val)) col2,
  9         max(decode(col,3,val)) col3
 10  from data
 11  group by line
 12  order by line
 13  /
      LINE       COL1       COL2       COL3
---------- ---------- ---------- ----------
         1         11         12         13
         2         21         22         23
         3         31         32         33

3 rows selected.

For the second case, just swap line and column:
SQL> with
  2    data as (
  3      select mod(id-1,3)+1 line, trunc((id-1)/3)+1 col, val
  4      from t
  5    )
  6  select line, 
  7         max(decode(col,1,val)) col1,
  8         max(decode(col,2,val)) col2,
  9         max(decode(col,3,val)) col3
 10  from data
 11  group by line
 12  order by line
 13  /
      LINE       COL1       COL2       COL3
---------- ---------- ---------- ----------
         1         11         21         31
         2         12         22         32
         3         13         23         33

3 rows selected.

Regards
Michel
Report message to a moderator
Re: matrix multiplication [message #442715 is a reply to message #442485] Wed, 10 February 2010 02:23 Go to previous message
joshua82
Messages: 31
Registered: December 2009
Member
Thanks, Michel!
Report message to a moderator
Previous Topic:Loading data from flat file using ORGANIZATION EXTERNAL
Next Topic:ORA-06508: PL/SQL: could not find program unit being called
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Sep 05 20:45:44 CDT 2010

Total time taken to generate the page: 0.18657 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.