Ver Requiere lógica de interdependencia: ¿posible sin MODELO?

Question

Feb 12, 2015, 06:03 PM

Ver Requiere lógica de interdependencia: ¿posible sin MODELO?

Estoy tratando de escribir un poco de Oracle 11g SQL, pero me encuentro con un pequeño problema de huevo y gallina. Estoy buscando un comportamiento similar a una hoja de cálculo. He encontrado una solución que usa OracleMODEL cláusula, pero el rendimiento no es excelente. Así que me pregunto si un "no-MODEL"La solución es incluso técnicamente factible.

Aquí hay un ejemplo de juguete que demuestra lo que estoy tratando de hacer. Dada esta tabla:

CREATE TABLE t (id NUMBER PRIMARY KEY, n NUMBER);
INSERT INTO t (id, n) VALUES (2, 0);
INSERT INTO t (id, n) VALUES (3, 1);
INSERT INTO t (id, n) VALUES (5, 1);
INSERT INTO t (id, n) VALUES (7, 2);
INSERT INTO t (id, n) VALUES (11, 3);
INSERT INTO t (id, n) VALUES (13, 5);
INSERT INTO t (id, n) VALUES (17, 8);
INSERT INTO t (id, n) VALUES (19, 13);

Quiero calcular dos columnas derivadas adicionales, llamarlasX yY.

Aquí están las reglas de cómoX yY deben ser calculados:

X: para la primera fila, definida por el valor mínimo de ID, establezcaX aN. Para todas las filas posteriores, el valor deX debe ser uno menos que el valor del anteriorY, según lo ordenado porID.

Y: dos vecesN másX.

Los siguientes pasos muestran cómo llenaría mi vi deseado, ew si tuviera que hacer esto a mano. Primero, las primeras filas de los datos dados:

 ID    N    X    Y
---  ---  ---  ---
  2    0
  3    1
  5    1
  7    2
....

Como estamos en la primera fila,X debe establecerse enNo0. Y debiera ser2 * N + Xo0.

 ID    N    X    Y
---  ---  ---  ---
  2    0    0    0
  3    1
  5    1
  7    2
....

Ahora, como ya no estamos en la primera fila,X siempre debe ser uno menos que la fila anteriorY de aqui en adelante. Aquí en la segunda fila, eso significaX = (anteriorY) -1 = 0 - 1 = -1. Y la segunda filaY estarán2 * N + Xo2 * (1) + (-1) = 1.

 ID    N    X    Y
---  ---  ---  ---
  2    0    0    0
  3    1   -1    1
  5    1
  7    2
....

Si continúa con las matemáticas, este es el resultado deseado:

 ID    N    X    Y
---  ---  ---  ---
  2    0    0    0
  3    1   -1    1
  5    1    0    2
  7    2    1    5
 11    3    4   10
 13    5    9   19
 17    8   18   34
 19   13   33   59

Dadas las reglas de cómoX yY se calculan, ¿es posible obtener este resultado sin tener que recurrir a laMODEL ¿cláusula?

No estoy buscando una simplificación matemática basada en este ejemplo particular; Este es solo un ejemplo de juguete que se me ocurrió que demuestra el tipo de interdependencia que estoy enfrentando en mi problema real.

PD: Aquí hay unMODEL ejemplo pude improvisar juntos que genera esta salida; Tal vez hay modificaciones posibles para mejorar el rendimiento?

SQL> WITH u AS (
  2      SELECT ROW_NUMBER() OVER (ORDER BY t.id) r
  3      ,      t.id
  4      ,      t.n
  5      FROM   t
  6  )
  7  SELECT r
  8  ,      id
  9  ,      n
 10  ,      x
 11  ,      y
 12  FROM   u
 13  MODEL
 14      DIMENSION BY (r)
 15      MEASURES (id
 16      ,         n
 17      ,         CAST(NULL AS NUMBER) x
 18      ,         CAST(NULL AS NUMBER) y) RULES AUTOMATIC ORDER
 19    ( x[1] = n[cv()]
 20    , y[r] = 2 * n[cv()] + x[cv()]
 21    , x[r > 1] ORDER BY r = y[cv() - 1] - 1
 22      )
 23  ;

         R         ID          N          X          Y
---------- ---------- ---------- ---------- ----------
         1          2          0          0          0
         2          3          1         -1          1
         3          5          1          0          2
         4          7          2          1          5
         5         11          3          4         10
         6         13          5          9         19
         7         17          8         18         34
         8         19         13         33         59

8 rows selected.

SQL>

Gracias.