45

Matrix Multiplication Calculated with T-SQL

 6 years ago
source link: https://www.tuicool.com/articles/hit/RbuYFzu
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

By:Eli Leiba |   Last Updated: 2018-11-23   |  |   Related Tips:More > T-SQL

Problem

I need to perform matrix multiplication in T-SQL for one of my applications. Is this possible with T-SQL code in SQL Server?

Solution

The term "matrix multiplication" or sometimes called "matrix product" is a binary operation (an operation between two matrices) that produces a matrix from two matrices in the following manner:

3AnyqaY.png!web

Here is the definition (from the Wikipedia): https://en.wikipedia.org/wiki/Matrix_multiplication .

Matrix Multiplication with T-SQL

The stored procedure in this tip is a suggested T-SQL solution to this operation and does the mathematical operations by using the T-SQL equivalent methods. My solution involves creating a rather simple T-SQL stored procedure in a SQL Server application database, called dbo.multMatrixes that will get the following two parameters of type matrixType: @matA and @matB. These are two tables valued parameters (TVP) that represent the two matrices in T-SQL.

Create New Type

We need to create a new type in SQL Server and the matrix type is defined as follows:

CREATE TYPE matrixType AS TABLE (i INT,j INT, val REAL)
GO

This type represents a matrix where V [i,j] equals the value of the cell in row 'i' and column 'j'.

i = 1,2,3,...n and j = 1,2,3,…m for the first matrix and 1,2,3,…m and 1,2,3,...p for the second matrix respectively.

The procedure does a join on these 2 tables matching j (matrix A) to i (matrix B) grouped by i (in matrix A) and j (in matrix B) and sums the product of the values giving the multiplied matrix as the result set.

SQL Server Stored Procedure to Calculate Matrix Multiplication

-- ================================================================================================= 
-- Author: Eli Leiba 
-- Create date: 11-2018 
-- Procedure Name: dbo.multMatrixes
-- Description: 
--      The procedure gets 2 parameters of type matrixType: @matA and @matB 
--      The procedure multiplies the two matrixes by doing the mathematical operation. 
--      It joins the tables matching j (matrix A) to i (matrix B), grouped by i (matrix A) and j (matrix B) and 
--      sums the product of the values. 
-- ================================================================================================= 
CREATE PROCEDURE dbo.multMatrixes @matA matrixType READONLY, 
@matB matrixType READONLY 
AS 
SELECT A.I, B.J, SUM (A.val * B.val) AS val 
FROM @matA A,@matB B 
WHERE A.J = B.I 
GROUP BY A.I, 
B.J; 
GO

Example Execution

Let's say we have two matrixes as follows:

RJBNBbB.png!web

Here is how the final matrix is calculated.

For each row in Matrix A we multiply this against each column in Matrix B.  The values shaded in gray are the final values for the matrix which we will see below.

A73mqey.png!web

This is what Matrix C would look like using the numbers from above.

amm6FnM.png!web

Below is the T-SQL script for populating the matrices and completing the multiplication. For the values, the first position is the row, the second is the column and the third is the value.

  • For Matrix A, for row 1, column 1 the value is -1, this would be represented as (1, 1, -1)
  • For Matrix A, for row 1, column 2 the value is -0, this would be represented as (1, 2, 0)
  • etc...
DECLARE @matrixA matrixType;
DECLARE @matrixB matrixType;
 
-- filling first matrix
INSERT INTO @matrixA
VALUES (1, 1, -1), (1, 2, 0), (1, 3, 6), 
       (2, 1,  2), (2, 2, 5), (2, 3, 1);
 
-- filling second matrix
INSERT INTO @matrixB
VALUES (1, 1, 2), (1, 2, -4) ,(1, 3,  9), 
       (2, 1, 0), (2, 2,  5) ,(2, 3,  1), 
       (3, 1, 3), (3, 2,  6) ,(3, 3, -1);
 
-- now doing the matrixes multiplication
EXEC dbo.multMatrixes @matrixA, @matrixB

This returns 6 rows as follows. Column I is the row and column J is the column and val is the computed value.

rqmeIv3.png!web

If we pivot the data back to the matrix, we would have the following:

amm6FnM.png!web

Next Steps

  • You can create and compile this simple procedure (and type) in your application database and use it as a simple T-SQL tool for multiplying matrices.
  • The procedure should work for SQL Server version 2008 and above (2012, 2014, 2016 and 2017) since table valued parameters for stored procedure had been introduced in SQL Server 2008.

Last Updated: 2018-11-23

J7JRjaZ.png!web

q2qQNb.png!web

About the author

bEJZVz.jpg!web Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK