递归SQL语句（Postgresql）-简化版 [英] Recursive SQL statement (Postgresql) - simplified version

问题描述

这是发布在此处的更复杂问题的简化问题：

This is simplified question for more complicated one posted here:

递归SQL语句（PostgreSQL 9.1.4）

简化的问题

鉴于您将上三角矩阵存储在3列（RowIndex，ColumnIndex，MatrixValue）中：

Given you have upper triangular matrix stored in 3 columns (RowIndex, ColumnIndex, MatrixValue):

   ColumnIndex       
    1   2   3   4   5
1   2   2   3   3   4
2   4   4   5   6   X
3   3   2   2   X   X
4   2   1   X   X   X
5   1   X   X   X   X

X值将使用以下算法计算： / p>

X values are to be calculated using the following algorithm:

M[i,j] = (M[i-1,j]+M[i,j-1])/2
(i= rows, j = columns, M=matrix)

Example:
M[3,4] = (M[2,4]+M[3,3])/2
M[3,5] = (m[2,5]+M[3,4])/2

所需的完整结果是：

   ColumnIndex       
    1   2   3    4      5
1   2   2   3    3      4
2   4   4   5    6      5
3   3   2   2    4      4.5
4   2   1   1.5  2.75   3.625
5   1   1   1.25 2.00   2.8125

样本数据：

create table matrix_data (
    RowIndex integer,
    ColumnIndex integer,
    MatrixValue numeric);

    insert into matrix_data values (1,1,2);
    insert into matrix_data values (1,2,2);
    insert into matrix_data values (1,3,3);
    insert into matrix_data values (1,4,3);
    insert into matrix_data values (1,5,4);
    insert into matrix_data values (2,1,4);
    insert into matrix_data values (2,2,4);
    insert into matrix_data values (2,3,5);
    insert into matrix_data values (2,4,6);
    insert into matrix_data values (3,1,3);
    insert into matrix_data values (3,2,2);
    insert into matrix_data values (3,3,2);
    insert into matrix_data values (4,1,2);
    insert into matrix_data values (4,2,1);
    insert into matrix_data values (5,1,1);

可以做到吗？

推荐答案

测试设置：

CREATE TEMP TABLE matrix (
    rowindex integer,
    columnindex integer,
    matrixvalue numeric);

INSERT INTO matrix VALUES
 (1,1,2),(1,2,2),(1,3,3),(1,4,3),(1,5,4)
,(2,1,4),(2,2,4),(2,3,5),(2,4,6)
,(3,1,3),(3,2,2),(3,3,2)
,(4,1,2),(4,2,1)
,(5,1,1);

使用 DO 在循环中运行INSERT：

Run INSERTs in a LOOP with DO:

DO $$
BEGIN

FOR i IN 2 .. 5 LOOP
   FOR j IN 7-i .. 5 LOOP
      INSERT INTO matrix
      VALUES (i,j, (
         SELECT sum(matrixvalue)/2
         FROM   matrix
         WHERE  (rowindex, columnindex) IN ((i-1, j),(i, j-1))
         ));
   END LOOP;
END LOOP;

END;
$$

查看结果：

SELECT * FROM matrix order BY 1,2;

这篇关于递归SQL语句（Postgresql）-简化版的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！