Oracle 矩阵乘法查询

嗨，汤姆，

为了证明Oracle SQL对于解决数学问题或好奇心来说足够强大，您能否将查询共享给我们以生成矩阵乘法结果。我认为它可以用“仅模型”子句来实现。

是否可以使用分析函数Lag和Lead函数来编写？

      
  
      __   __          __      __ 
     | 6 7 -4|        | 5 -1 6 -3| 
M1=  | 0 4  1|   M2=  | 0  4 1  7|   
     | 2 -2 3|        | 8  2 9  3|  
     |_     _|        |_        _|  


 M1 x M2 =  
 __                                                                      __
| 6×5 + 7×0 + -4×8  6×-1 + 7×4 + -4×2  6×6 + 7×1 + -4×9  6×-3 + 7×7 + -4×3 |
| 0×5 + 4×0 +  1×8  0×-1 + 4×4 + 1×2   0×6 + 4×1 + 1×9   0×-3 + 4×7 + 1×3  |
| 2×5 + -2×0 + 3×8  2×-1 + -2×4 + 3×2  2×6 + -2×1 + 3×9  2×-3 + -2×7 + 3×3 |
|__                                                                      __|  

 Result = 
 __                __
|-2    14     7    19|     
|8     18    13    31|
|34    -4    37   -11|
|__                __|

CREATE TABLE m1 (c1 number, c2 number, c3 number)
/
CREATE TABLE m2 (col1 number, col2 number, col3 number,col4 number)
/
INSERT INTO m1 VALUES (6,7,-4)
/
INSERT INTO m1 VALUES (0,4,1)
/
INSERT INTO m1 VALUES (2,-2,3)
/
COMMIT;

SELECT * FROM m1
/
INSERT INTO m2 VALUES (5,-1,6,-3)
/
INSERT INTO m2 VALUES (0,4,1,7)
/
INSERT INTO m2 VALUES (8,2,9,3)
/

COMMIT;

SELECT * FROM m2
/

谢谢，
博拉利

我不得不对你的表做一点改动- -大家都知道，插入到表中的顺序并不能预测检索的顺序。因此，我们需要在那里添加一个行号- -某种单调递增的值- -以正确的顺序检索矩阵。

ops$tkyte%ORA11GR2> CREATE TABLE m1 (rn number, c1 number, c2 number, c3 number)
  2  /

Table created.

ops$tkyte%ORA11GR2> CREATE TABLE m2 (rn number, col1 number, col2 number, col3 number,col4 number)
  2  /

Table created.

ops$tkyte%ORA11GR2> INSERT INTO m1 VALUES (1, 6,7,-4)
  2  /

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO m1 VALUES (2, 0,4,1)
  2  /

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO m1 VALUES (3, 2,-2,3)
  2  /

1 row created.

ops$tkyte%ORA11GR2> COMMIT;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> SELECT * FROM m1
  2  /

        RN         C1         C2         C3
---------- ---------- ---------- ----------
         1          6          7         -4
         2          0          4          1
         3          2         -2          3

ops$tkyte%ORA11GR2> INSERT INTO m2 VALUES (1, 5,-1,6,-3)
  2  /

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO m2 VALUES (2, 0,4,1,7)
  2  /

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO m2 VALUES (3, 8,2,9,3)
  2  /

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> COMMIT;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> SELECT * FROM m2
  2  /

        RN       COL1       COL2       COL3       COL4
---------- ---------- ---------- ---------- ----------
         1          5         -1          6         -3
         2          0          4          1          7
         3          8          2          9          3

现在，我们需要做的是转置矩阵M2 ，使其设置友好。我们希望将M1中的第一行与M2中的第一列和M2中的第二列连接起来，依此类推。与m1中的第2行相同。所以，如果我们把M2转到它的一边，我们就可以用笛卡尔连接来获得我们所有的积分：

ops$tkyte%ORA11GR2> with d1
  2  as
  3  ( select rn, cname, val
  4      from m2
  5   unpivot ( val for cname in (COL1, COL2, COL3, COL4) )
  6  )
  7  select * from d1;

        RN CNAM        VAL
---------- ---- ----------
         1 COL1          5
         1 COL2         -1
         1 COL3          6
         1 COL4         -3
         2 COL1          0
         2 COL2          4
         2 COL3          1
         2 COL4          7
         3 COL1          8
         3 COL2          2
         3 COL3          9
         3 COL4          3

12 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with d1
  2  as
  3  ( select rn, cname, val
  4      from m2
  5   unpivot ( val for cname in (COL1, COL2, COL3, COL4) )
  6  ),
  7  d2 as
  8  (
  9  select cname, x1, x2, x3
 10    from d1
 11   pivot (max(val) for rn in ( 1 as x1, 2 as x2, 3 as x3 ))
 12  )
 13  select * from d2;

CNAM         X1         X2         X3
---- ---------- ---------- ----------
COL1          5          0          8
COL2         -1          4          2
COL3          6          1          9
COL4         -3          7          3

旋转/取消旋转组合转换了我们的结果集-所以我们有两个集合，每个集合都有三个列（现在很容易相乘）。...

ops$tkyte%ORA11GR2> with d1
  2  as
  3  ( select rn, cname, val
  4      from m2
  5   unpivot ( val for cname in (COL1, COL2, COL3, COL4) )
  6  ),
  7  d2 as
  8  (
  9  select cname, x1, x2, x3
 10    from d1
 11   pivot (max(val) for rn in ( 1 as x1, 2 as x2, 3 as x3 ))
 12  ),
 13  d3 as
 14  (
 15  select c1*x1+c2*x2+c3*x3 val, rn, cname
 16    from m1, d2
 17  )
 18  select * from d3;

       VAL         RN CNAM
---------- ---------- ----
        -2          1 COL1
         8          2 COL1
        34          3 COL1
        14          1 COL2
        18          2 COL2
        -4          3 COL2
         7          1 COL3
        13          2 COL3
        37          3 COL3
        19          1 COL4
        31          2 COL4
       -11          3 COL4

12 rows selected.

我们所要做的就是将CNAME列转换为列-再次透视：

ops$tkyte%ORA11GR2> with d1
  2  as
  3  ( select rn, cname, val
  4      from m2
  5   unpivot ( val for cname in (COL1, COL2, COL3, COL4) )
  6  ),
  7  d2 as
  8  (
  9  select cname, x1, x2, x3
 10    from d1
 11   pivot (max(val) for rn in ( 1 as x1, 2 as x2, 3 as x3 ))
 12  ),
 13  d3 as
 14  (
 15  select c1*x1+c2*x2+c3*x3 val, rn, cname
 16    from m1, d2
 17  )
 18  select column1, column2, column3, column4
 19    from d3
 20   pivot (max(val) for cname in ( 'COL1' as column1, 'COL2' as column2, 'COL3' as column3, 'COL4' as column4 ))
 21   order by rn
 22  /

   COLUMN1    COLUMN2    COLUMN3    COLUMN4
---------- ---------- ---------- ----------
        -2         14          7         19
         8         18         13         31
        34         -4         37        -11

qed :)

我很期待看到所有其他的技术，其他人提出：)