Expresiones de Tablas Comunes Recursivas al Rescate

Traducción del post original publicado en Noviembre 27 2013.

Las expresiones de tablas comunes recursivas han estado disponibles en Oracle desde la versión 11g R2 que fue lanzada por allá en el año 2009. Siempre las había considerado como una alternativa a la cláusula connect by para poder escribir consultas jerárquicas que siguieran el estándar ANSI, así que nunca me había tomado la molestia de revisar su funcionalidad en detalle.

Durante Kscope13, tuve la oportunidad de asistir a una sesión de Dominic Delmolino donde él mostró que las expresiones de tablas comunes recursivas pueden ser utilizadas para resolver otros tipos de problemas. Uno de los casos justamente es cuando necesitamos «generar un valor» y luego queremos hacer referencia a dicho valor en el siguiente registro de nuestra consulta.

No hace mucho tuve que trabajar en una consulta con un requerimiento similar. La idea básica es que tenemos un número variable de registros por cada cliente y para cada registro debemos establecer o calcular un nuevo valor (a la cual vamos a llamar VAL). La parte interesante está en que para algunos registros, VAL debe ser determinado utilizando el VAL que se encuentra en el registro anterior.

En primer lugar voy a crear una tabla y voy a insertar unos registros que ayuden a visualizar el problema.

SQL> CREATE TABLE RSF_TEST
 2 (
 3 ID_CLIENT NUMBER NOT NULL,
 4 DT_ELIG DATE NOT NULL,
 5 TP NUMBER NOT NULL,
 6 SRV_GRP NUMBER NOT NULL,
 7 SRV_CODE NUMBER NOT NULL,
 8 DT_ADMIT DATE NOT NULL,
 9 DT_DISCHARGE DATE NOT NULL
 10 );

Table created

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-11-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-11-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-10-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-10-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-09-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-09-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-08-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-08-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-07-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-07-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-06-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-06-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-05-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('01-05-2013', 'dd-mm-yyyy'), to_date('31-12-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-05-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-04-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-04-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (615086545, to_date('01-03-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('07-03-2013', 'dd-mm-yyyy'), to_date('31-03-2014', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-03-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-02-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-01-2013', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-12-2012', 'dd-mm-yyyy'), 13, 19, 12, to_date('12-12-2012', 'dd-mm-yyyy'), to_date('30-04-2013', 'dd-mm-yyyy'));
1 row inserted

SQL> insert into RSF_TEST (ID_CLIENT, DT_ELIG, TP, SRV_GRP, SRV_CODE, DT_ADMIT, DT_DISCHARGE)
 2 values (525459313, to_date('01-03-2012', 'dd-mm-yyyy'), 13, 1, 1, to_date('18-01-2012', 'dd-mm-yyyy'), to_date('29-03-2012', 'dd-mm-yyyy'));
1 row inserted

SQL> commit;

Commit complete

SQL>

A continuación tenemos una versión simplificada del problema. La consulta original contiene más predicados y las sentencias CASE son un poco más complejas ya que deben cubrir una serie de posibles combinaciones de las columnas srv_grp, srv_code. Traté de limitar el código de forma que únicamente incluya lo necesario para mostrar cómo determinar VAL para los registros que existen en mi tabla.

WITH
clients AS
(
 SELECT
   id_client,
   dt_elig,
   tp,
   srv_grp,
   srv_code,
   dt_admit,
   dt_discharge,
   row_number() OVER (PARTITION BY id_client ORDER BY id_client, dt_elig) rn
 FROM rsf_test
),
results (id_client, dt_elig, tp, srv_grp, srv_code, dt_admit, dt_discharge, rn, val) AS
(
 SELECT
   id_client,
   dt_elig,
   tp,
   srv_grp,
   srv_code,
   dt_admit,
   dt_discharge,
   rn,
   CASE
     WHEN tp <> 14 AND srv_grp = 19 AND srv_code = 12 AND dt_elig BETWEEN dt_admit AND dt_discharge THEN 3
     WHEN tp <> 14 AND srv_grp = 19 AND srv_code = 12 AND dt_elig NOT BETWEEN dt_admit AND dt_discharge THEN 0
     WHEN tp <> 14 AND srv_grp IN (1,10) AND srv_code IN (1,3) THEN 0
   END val
 FROM clients
 WHERE
   rn = 1
 UNION ALL
 SELECT
   c.id_client,
   c.dt_elig,
   c.tp,
   c.srv_grp,
   c.srv_code,
   c.dt_admit,
   c.dt_discharge,
   c.rn,
   CASE
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val = 3 AND c.dt_elig BETWEEN c.dt_admit AND c.dt_discharge THEN 3
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val < 3 AND c.dt_elig BETWEEN c.dt_admit AND c.dt_discharge THEN p.val + 1
     WHEN c.tp <> 14 AND c.srv_grp IN (1,10) AND c.srv_code IN (1,3) AND p.val = 3 THEN 3
     WHEN c.tp <> 14 AND c.srv_grp IN (1,10) AND c.srv_code IN (1,3) AND p.val < 3 THEN p.val
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val = 3 AND c.dt_elig NOT BETWEEN c.dt_admit AND c.dt_discharge THEN 3
     WHEN c.tp <> 14 AND c.srv_grp = 19 AND c.srv_code = 12 AND p.val < 3 AND c.dt_elig NOT BETWEEN c.dt_admit AND c.dt_discharge THEN p.val
   END val
 FROM
   clients c,
   results p
 WHERE
   c.id_client = p.id_client
   AND c.rn = p.rn + 1
)
SELECT
  r.id_client,
  r.dt_elig,
  r.tp,
  r.srv_grp,
  r.srv_code,
  r.dt_admit,
  r.dt_discharge,
  --r.rn,
  r.val
FROM results r
ORDER BY
  r.id_client,
  r.dt_elig;

Como pueden ver, las expresiones de tablas comunes recursivas contienen dos consultas: La consulta anchor que sirve para generar el primer registro que luego es procesado por la consulta recursiva. Las dos consultas son combinadas mediante el operador UNION ALL.

Ahora veamos el resultado una vez que se ejecuta la consulta:

 ID_CLIENT DT_ELIG           TP    SRV_GRP   SRV_CODE DT_ADMIT  DT_DISCHA        VAL
---------- --------- ---------- ---------- ---------- --------- --------- ----------
 525459313 01-MAR-12         13          1          1 18-JAN-12 29-MAR-12          0
 525459313 01-DEC-12         13         19         12 12-DEC-12 30-APR-13          0
 525459313 01-JAN-13         13         19         12 12-DEC-12 30-APR-13          1
 525459313 01-FEB-13         13         19         12 12-DEC-12 30-APR-13          2
 525459313 01-MAR-13         13         19         12 12-DEC-12 30-APR-13          3
 525459313 01-APR-13         13         19         12 12-DEC-12 30-APR-13          3
 525459313 01-MAY-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-JUN-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-JUL-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-AUG-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-SEP-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-OCT-13         13         19         12 01-MAY-13 31-DEC-13          3
 525459313 01-NOV-13         13         19         12 01-MAY-13 31-DEC-13          3
 615086545 01-MAR-13         13         19         12 07-MAR-13 31-MAR-14          0
 615086545 01-APR-13         13         19         12 07-MAR-13 31-MAR-14          1
 615086545 01-MAY-13         13         19         12 07-MAR-13 31-MAR-14          2
 615086545 01-JUN-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-JUL-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-AUG-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-SEP-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-OCT-13         13         19         12 07-MAR-13 31-MAR-14          3
 615086545 01-NOV-13         13         19         12 07-MAR-13 31-MAR-14          3

22 rows selected.

En éste ejemplo en particular, la consulta anchor asigna ya sea un 0 o un 3 para VAL en el primer registro y de ahí la consulta recursiva evalúa si VAL debería incrementarse o permanecer estático en cada uno de los siguientes registros. La idea es tener acceso a VAL de la iteración previa para poder determinar el nuevo valor en la iteración actual.