· 7 years ago · Oct 09, 2018, 01:40 AM
1DROP TABLE IF EXISTS matrices;
2CREATE TABLE matrices (
3 matrix_id int NOT NULL, -- unique identifier for each matrix
4 i int NOT NULL, -- row number
5 j int NOT NULL, -- column number
6 val decimal NOT NULL, -- the value in this cell
7 PRIMARY KEY (matrix_id, i, j)
8);
9
10-- insert sparse representation of
11/* [[0, 4, 5],
12 [-1, 1, 2],
13 [0, 0, -3]] */
14INSERT INTO matrices
15VALUES
16 (1, 0, 1, 4 ),
17 (1, 0, 2, 5 ),
18 (1, 1, 0, -1),
19 (1, 1, 1, 1 ),
20 (1, 1, 2, 2 ),
21 (1, 2, 2, -3);
22
23-- insert sparse representation of
24/* [[0, 1, 0],
25 [-2, 1, 0],
26 [0, 2, -1]] */
27INSERT INTO matrices
28VALUES
29 (2, 0, 1, 1 ),
30 (2, 1, 0, -2),
31 (2, 1, 1, 1 ),
32 (2, 2, 1, 2 ),
33 (2, 2, 2, -1);
34
35-- query for the multiplication product
36SELECT
37 matrix_1.i,
38 matrix_2.j,
39 sum(matrix_1.val * matrix_2.val) AS val
40FROM (SELECT * FROM matrices WHERE matrix_id = 1) matrix_1
41JOIN (SELECT * FROM matrices WHERE matrix_id = 2) matrix_2
42 ON matrix_1.j = matrix_2.i
43GROUP BY matrix_1.i, matrix_2.j;
44
45/*
46 i | j | val
47---+---+-----
48 0 | 0 | -8
49 0 | 1 | 14
50 0 | 2 | -5
51 1 | 0 | -2
52 1 | 1 | 4
53 1 | 2 | -2
54 2 | 1 | -6
55 2 | 2 | 3
56(8 rows)
57*/