3.19 Multiplying Matrices

3.19.1 Problem

You want to implement matrix multiplication in SQL.

3.19.2 Solution

There are three ways that you can multiply a matrix:

By a scalar value
By a vector of values
By another matrix

When multiplying by a vector, the length of the vector must correspond to the maximum X index. When you multiply two matrices, the matrices must be equal.

3.19.2.1 Multiplying a matrix by a scalar value

To multiply matrix A by scalar 5, just multiply all rows of that matrix by 5:

SELECT DISTINCT X, Y ,Value*5 Value
FROM Matrices
WHERE Matrix='A'

X           Y           Value       
----------- ----------- ----------- 
1           1           30
1           2           15
2           1           20
2           2           35

3.19.2.2 Multiplying a matrix with a vector

To multiply matrix A by scalar S, use the following query:

SELECT m1.X, SUM(m1.Value*v.Value) VALUE
FROM Matrices m1, Matrices v
WHERE m1.Matrix='A' AND v.Matrix='S' AND m1.Y=v.X 
GROUP BY m1.X

X           Value       
----------- ----------- 
1           48
2           62

3.19.2.3 Multiplying two matrices

To multiply matrix A by matrix B, use the following code:

SELECT m1.X, m2.Y, SUM(m1.Value*m2.Value) Value
FROM Matrices m1, Matrices m2
WHERE m1.Matrix='A' AND m2.Matrix='B' AND m1.Y=m2.X
GROUP BY m1.X, m2.Y

X           Y           Value       
----------- ----------- ----------- 
1           1           51
2           1           59
1           2           24
2           2           26

3.19.3 Discussion

The biggest danger while working with matrices is in the confusion of indices. The SQL statements in this recipe can be used only if matrices or vectors are represented exactly as in our example. In any case, it is probably a good idea to check the indices carefully in the query.

Another issue to be concerned about is that you must ensure that you are multiplying data with the appropriate dimensions. When multiplying two matrices, their dimensions must match. When multiplying a matrix by a vector, the dimension of the vector must match the X dimension of the matrix. While it's possible to extend these queries to check for dimensional equality, this significantly increases the cost. If you can, it's best to build such checking mechanisms somewhere else.

3.19.3.1 Multiplying by a scalar

The easiest of multiplications uses SQL features to extract all elements of a matrix easily and just multiply them with a scalar. The SELECT list of such a query simply uses multiplication, in our case Value*5, to return the specified results.

3.19.3.2 Multiplying by a vector

Multiplication of a matrix by a vector is a bit more difficult. In our example, if we write down the matrix A and the vector S together, we will get the following:

Matrix A:   6   3
            4   7

Vector S:   5   6

Algebraic rules state that the first vector element multiplies values in the first matrix column, the second vector element multiplies values in the second matrix column, and so forth. This gives us the following matrix of values:

6x5   3x6
4x5   7x6

The final step is to sum all the values in each row of this matrix, so the result is a vector:

6x5 + 3x6 = 30 + 18 = 48
4x5 + 7x6 = 20 + 42 = 62

As you can see, the result of multiplying a matrix by a vector is another vector. In our case, the result vector is as follows:

48   62

3.19.3.3 Multiplying by a matrix

The query to multiply two matrices together uses the same principle as the query for multiplying a matrix by a vector. The query cross-matches the elements according to their position, performs multiplications, and sums the results of those multiplications so that the result is a vector. In our example, the following two matrices are multiplied together:

Matrix A      Matrix B
   6   3         6   3
   4   7         5   2

When we say that in matrix multiplication you "cross-match" elements, we mean that that X,Y values from one matrix are multiplied by the corresponding Y,X values from the other. For example, element 1,2 from matrix A must be multiplied by element 2,1 from matrix B. In our example, this cross-matching yields the following multiplications:

6*6   3*5
4*6   7*5
6*3   3*2
4*3   7*2

The results must then be summed into a vector:

6*6 + 3*5 = 36 + 15 = 51
4*6 + 7*5 = 24 + 35 = 59
6*3 + 3*2 = 18 +  6 = 24
4*3 + 7*2 = 12 + 14 = 26

3.19.3.4 Squaring a matrix

The matrix multiplication query can easily be modified to square a matrix. To square a matrix is to multiply it by itself. The only thing that has to be changed is that both m1 and m2 must be restricted to the same matrix. In the following example, m1 and m2 both represent matrix A:

SELECT m1.X, m2.Y, SUM(m1.Value*m2.Value) Value
FROM Matrices m1, Matrices m2
WHERE m1.Matrix='A' AND m2.Matrix='A' AND m1.Y=m2.X
GROUP BY m1.X, m2.Y

The results are then the square of A:

X           Y           Value       
----------- ----------- ----------- 
1           1           48
2           1           52
1           2           39
2           2           61