LAG/LEAD en Teradata

Soy un fan de las funciones analíticas (‘Analytical Functions‘). Permiten algo que el SQL no podía hacer hasta que aparecieron: relacionar entre sí filas de un mismo ‘resultset‘. Así podemos relacionar una fila con un total de partición, saber su rango (‘rank‘) dentro de una partición, relacionarla con su fila anterior, con su fila siguiente…

Esto en realidad rompe la teoría relacional: estamos asignando un ‘orden’ a un conjunto -y no sólo por el motivo de hacer un ‘resultset‘ más legible por el ojo humano, como en un simple listado en un informe-.

No obstante, en BI (‘Business Intelligence‘) es muy útil disponer de este tipo de funciones, ya que añaden al SQL una gran potencia suplementaria: particionar, asignar rangos, hacer sumas acumulativas en una sola sentencia SQL tiene a veces un valor incalculable.

Oracle implementa dos funciones, LAG y LEAD, para relacionar una fila con su inmediatamente anterior o posterior.

Su funcionamiento es sencillo:

SQL*Plus: Release 10.2.0.1.0 - Production on Lun Mar 2 13:08:23 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Introduzca el nombre de usuario: carlos@xe.localhost
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

CARLOS@xe.localhost> INSERT INTO PRUEBA01
 2 SELECT OBJECT_ID
 3 FROM DBA_OBJECTS WHERE OBJECT_ID  SELECT * FROM PRUEBA01;

      ID_N
----------
         2
         3
         4
         5
         6
         7
         8
         9
        10

9 filas seleccionadas.

CARLOS@xe.localhost> SELECT ID_N,
  2         LAG(ID_N) OVER (ORDER BY ID_N) ID_N_PREV,
  3         LEAD(ID_N) OVER (ORDER BY ID_N) ID_N_POST
  4    FROM PRUEBA01;

      ID_N  ID_N_PREV  ID_N_POST
---------- ---------- ----------
         2                     3
         3          2          4
         4          3          5
         5          4          6
         6          5          7
         7          6          8
         8          7          9
         9          8         10
        10          9

9 filas seleccionadas.

Pero, si hacemos esto en Teradata sólo obtendríamos un error:

 Teradata BTEQ 08.02.03.03 for WIN32.
 Copyright 1984-2006, NCR Corporation. ALL RIGHTS RESERVED.
 Enter your logon or BTEQ command:
.LOGON MY_TD/my_db

.LOGON MY_TD/my_db
Password:

 *** Logon successfully completed.
 *** Teradata Database Release is V2R.06.02.01.18
 *** Teradata Database Version is 06.02.01.17
 *** Transaction Semantics are BTET.
 *** Character Set Name is 'ASCII'.

 *** Total elapsed time was 2 seconds.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE TABLE MY_DB.PRUEBA01(ID_N INTEGER NOT NULL);

CREATE TABLE MY_DB.PRUEBA01(ID_N INTEGER NOT NULL);

 *** Table has been created.
 *** Total elapsed time was 1 second.

SELECT * FROM MY_DB.PRUEBA01 ORDER BY ID_N;

 *** Query completed. 9 rows found. One column returned.
 *** Total elapsed time was 1 second.

       ID_N
-----------
          2
          3
          4
          5
          6
          7
          8
          9
         10

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

SELECT ID_N,
       LAG(ID_N) OVER (ORDER BY ID_N) ID_N_PREV,
       LEAD(ID_N) OVER (ORDER BY ID_N) ID_N_POST
  FROM MY_DB.PRUEBA01;

SELECT ID_N,
       LAG(ID_N) OVER (ORDER BY ID_N) ID_N_PREV,
       LEAD(ID_N) OVER (ORDER BY ID_N) ID_N_POST
  FROM MY_DB.PRUEBA01;

       LAG(ID_N) OVER (ORDER BY ID_N) ID_N_PREV,
               $
 *** Failure 3706 Syntax error: Data Type "ID_N" does not match a Defined Type name.
                Statement# 1, Info =31
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

En efecto: en Teradata no existen LAG ni LEAD. Pero eso no significa que no podamos obtener los mismos resultados sin renunciar a las ventajas de las funciones analíticas.

Nos vamos a apoyar en la ‘windowing clause‘ de las funciones analíticas, que es la cláusula que indica que ‘ventana’ de filas va a ver dicha función analítica. Simplemente necesitamos referenciar UNA SOLA FILA y utilizar cualquier función que nos valga para pintarla: un MAX() o un MIN() será suficiente.

Así:

SELECT ID_N,
       MAX(ID_N) OVER (ORDER BY ID_N
                       ROWS BETWEEN 1 PRECEDING
                       AND 1 PRECEDING) ID_N_PREV,
       MAX(ID_N) OVER (ORDER BY ID_N
                       ROWS BETWEEN 1 FOLLOWING
                       AND 1 FOLLOWING) ID_N_POST
  FROM MY_DB.PRUEBA01
;

 *** Query completed. 9 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

       ID_N    ID_N_PREV    ID_N_POST
-----------  -----------  -----------
          2       (null)            3
          3            2            4
          4            3            5
          5            4            6
          6            5            7
          7            6            8
          8            7            9
          9            8           10
         10            9       (null)

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Una vez más: ¿mejor? ¿peor? No. Distinto.

Saludos.

Carlos.

2 respuestas a LAG/LEAD en Teradata

  1. […] vimos algo parecido con LAG/LEAD en Teradata. Aquí vamos a ver cómo podemos hacer que en Teradata -que carece de función RPAD- no echemos en […]

  2. Leonardo Aravena dice:

    Se obtendría lo mismo utilizando preceding o following?

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: