39 lines
848 B
Text
39 lines
848 B
Text
create or replace table A (i integer, j integer, value float );
|
|
insert into A values
|
|
(1, 1, 1),
|
|
(1, 2, 2),
|
|
(1, 3, 3),
|
|
(2, 1, 2),
|
|
(2, 2, 5),
|
|
(2, 3, 7);
|
|
|
|
create or replace table B (i integer, j integer, value float );
|
|
insert into B values
|
|
(1, 1, 2),
|
|
(1, 2, 4),
|
|
(1, 3, 8),
|
|
(2, 1, 1),
|
|
(2, 2, 5),
|
|
(2, 3, -10),
|
|
(3, 1, 3),
|
|
(3, 2, 6),
|
|
(3, 3, 9);
|
|
|
|
.print Display A in a matrix format including the row id
|
|
pivot A on j using max(value) order by i;
|
|
|
|
.print Display B likewise
|
|
pivot B on j using max(value) order by i;
|
|
|
|
. print Compute C = A * B
|
|
CREATE OR REPLACE table C as
|
|
SELECT A.i, B.j,
|
|
SUM(A.value * B.value) AS value
|
|
FROM A INNER JOIN B
|
|
ON A.j = B.i
|
|
GROUP BY A.i, B.j;
|
|
|
|
.print Display C in matrix format excluding the row id
|
|
select columns(* exclude (i))
|
|
from (pivot C on j using max(value))
|
|
order by i;
|