Instalacion de Oracle Database en GOOGLE CLOUD

Saludos,

Vamos a realizar una instalación del motor de base de datos Oracle 12c sobre una plataforma Google Gloud Platafoform.

Primero como requisitos necesitamos:

Maquina virtual creada en el GCloud. Previo a esto debes crear tu proyecto en el GCloud y crear la maquina con las características mínimas. GCloud ofrece $300 dolares de crédito gratis para tus pruebas en un año de vigencia. Conforme vayas usando recursos en tus proyectos esos $300 se van consumiendo. Aqui la medida de consumo es por hora y para que tengas una idea la maquina virtual basica que vamos a crear tiene un costo de $30 mensuales mas  o menos $1 dolar, ahora si apagamos la maquina el saldo no se consume.

Para acceder a estos $300 necesita tener una tarjeta de crédito o debito , te cobraran un $1 al registrarte pero luego te lo devuelven. No te preocupes, si en tus pruebas te consumes los $300 Google no te cobrará nada sin tu consentimiento.

Creación de la maquina virtual en GCloud
Tareas Previa a la instalación

Creación de la maquina virtual
-SO Centos 6
-1Cpu
-4Gb Ram
-20Gb de disco

• Crea tu proyecto GCP.

• Crea tu maquina virtual en tu GCP . Configura los recursos necesario y SO Centos 7. Considerar la configuración de las IP'S interna y externa.

Recursos SO

Definir la Red Interna y Externa

Hacer click en crear

Una vez creado la maquina, podemos ver nuestras maquinas en GCP\Compute Engine\ Instancias VM.

Instancias VM

Conexion SSH con Putty

• Con el Putty Key Generator crearemos claves privadas y publicas para la conexión. En la seccio comentario escribiremos el nombre de usuario con el que se va establecer la conexión.

Hay que guardar el private key y public key localmente para usarlo luego en la conexión

• Copiamos el texto Public Key generado , vamos a GCP\Compute Engine\ Metadatos en la seccion de Claves SSH damos click en eidtar y agregamos una.

• En nuetro Putty configuramos la conexion para hacer uso de nuestro privete key guardado y usamos la dirección de la ip externa que se le asigno a la maquina srvoradbdesa01 35.190.191.250

Ya configurado la conexión SSH, ahora si empezamos proceder con la instalación de los pre-requisitos y motor de base de datos.

Instalación Base de Datos 

1 Instalación de pre-requisitos del SO.
Instalar los paquetes necesarios en el SO  Centos previo a la instalacion del motor de base de datos Oracle

yum install -y binutils.x86_64 compat-libcap1.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 \
glibc-devel.i686 glibc-devel.x86_64 ksh compat-libstdc++-33 libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 \
libgcc.i686 libgcc.x86_64 libstdc++.i686 libstdc++.x86_64 libstdc++-devel.i686 libstdc++-devel.x86_64 libXi.i686 libXi.x86_64 \
libXtst.i686 libXtst.x86_64 make.x86_64 sysstat.x86_64

2 Configuración del sistema operativo y usuario.
Se establecerán los parámetros necesarios, configurar usuarios y grupos de usuarios necesarios para realizar la instalación del motor de base de datos.

Crear los grupos de usuarios oinstall y dba, luego crear el usuario oracle para asociarlos a los grupos en el SO.

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle
<<escribe una clave>>

Luego de la creacion del usuario y grupos se agregaran las configuración necesarias en el sistemas. Editar el "sysctl.conf"

vim /etc/sysctl.conf

Copiar el texto y pegarlo.

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

Guardar los cambios del archivo sysctl.conf y aplica los cambios.

sysctl -p
sysctl -a

Agrega los limites para el usuario oracle en el archivo "limits.conf"

vim /etc/security/limits.conf

Copia lo siguiente y pegalo. en el archivo limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Guarda los cambios del archivo limits.conf

3 Configuración del sistema operativo y usuario.
Ahora si, realizaremos la instalación, pero para ello tenemos que instalar un paquete "X Windows System", el cual nos permitirá ejecutar las ventanas de instalación del oracle de forma gráfica ejecutando lo desde el SSH.
También puedes revisar el siguiente link para la instlación del xam en windows si vas a usar el Putty como cliente para la instalación. http://www.geo.mtu.edu/geoschem/docs/putty_install.html

yum groupinstall -y "X Window System"

Vía SSH para realizar la instalación grafica debe de ejecutar la siguiente linea.

ssh -X oracle@35.190.191.250

4 Configuracion de directorios y descarga el archivo instlador de Oracle 

Con el comando wget descarga lo instladores o puede descargarlo desde tu pc y via ftp subirlos al servidor.

mkdir /orainstall

ll *.zip*
-rw-r--r--. 1 oracle oinstall 1673544724 Jul 11  2014 linuxamd64_12102_database_1of2.zip
-rw-r--r--. 1 oracle oinstall 1014530602 Jul 11  2014 linuxamd64_12102_database_2of2.zip

Para descomprimir instala el zip y unzip

yum -y install zip unzip

Extra los archivos dentro de la carpeta /orainstall/stage/

unzip linuxamd64_12102_database_1of2.zip -d /stage/
unzip linuxamd64_12102_database_2of2.zip -d /stage/

Crea las carpetas de instalación de binarios y datos.

 mkdir -p /u01 /u02

Cambia de propietario y permisos a las carpetas con los instaladores

chown -R oracle:oinstall /u01 /u02
chmod -R 775 /u01 /u02
chmod g+s /u01 /u02

chown -R oracle:oinstall /stage/

Instalador de Oracle 12c para Linux

• https://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-linux-download-2240591.html

5 Instalar la base de datos Oracle 12c

Ejecuta una nueva instancia de ssh con el usuario oracle con el parametro -X para ejecutarlo en modo gráfico.

Para usar el modos interactivo grafico debe de instalar el Xming para windows y configurara el putty. Les dejo el link de como deben de hacerlo
http://www.geo.mtu.edu/geoschem/docs/putty_install.html

ssh -X oracle@35.190.191.250

Anda a la carpeta en donde descomprimiste los instaladores y ejecuta el instalador

cd /orainstall/stage/database/
./runInstaller

5.01 Pantalla de inicio de la instalación, si tienes cuenta del OracleSupport ingresarla sino omitir. Clic en next.

5.02 Selecciona la opción de instalación, para nuestro caso instalaremos el software y crearemos la base de datos.

5.03 Para nuestro caso de practica seleccionaremos ella clase Descktop

5.04 Cambiamos la ubicación en donde se almacenaran los archivos de datos en /u02/, indicamos el nombre de la base de datos, establecemos la clave de los usuarios sys/system y desmarcamos "Create as Container database"

5.05 Dejamos tal como esta y damos click en next.

5.06 Verificacion de adveretencia y errores, aplicar las correcciones y volver a verificar hasta no tener advertencias. Click en next.

5.07 Vista preliminar de los parámetros de la instancia antes de la instalación. Click en nexte iniciará la instalación.

5.08 Durante la instalación se presentará mensajes de ejecutar script con el root, para ello abriremos una nueva ventana del ssh y ejecutaremos los scripts en pantalla.

5.09 Se muestra los datos de la instanacia ya creada con su nombre y datos de acceso a EM.

5.10 Mensaje de instalación finalizada

5.11 Comprobar que la base de datos este activa SQLPLUS.

Abrimos una ventana ssh root@35.190.191.250

ssh root@35.190.191.250
//escribe tu contraseña

Cambiamos al usuario oracle 

su - oracle

Seteamos variables de ambiente para inicar el sqlplus

export ORACLE_SID=orcldb01
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1/
export PATH=$PATH:$ORACLE_HOME/bin

No conectamos al sqlplus como sysdba

sqlplus / as sysdba

Export de variables

Sqlplus

5.11 Probar el acceso al EM de Oracle desde la dirección publica 35.190.191.250


Conectarte desde el SQLPLUS desde la IP Publica, para conectarte desde la ip publica que te da el GCLOUD deberas

1. Agregar en la interfaz de red la ip publica 35.190.191.250
2. Configurar el listener para que escuche desde la ip publica

1 Agregar en la interfaz de red la ip publica 35.190.191.250

• Inicie sesión en su servidor con SSH como root.
• Vaya al directorio /etc/sysconfig/network-scripts.

 

A partir de la ifcfg-eth0 crearemos un nuevo archivo y editaremos para insertar las lineas de la IP publica.

• cp ifcfg-eth0 ifcfg-eth0:0
•  vi ifcfg-eth0:0

#Agregar el archivo ifcfg-eth0:0
DEVICE="eth0:0"
BOOTPROTO="static"
IPADDR=35.190.191.250
NETMASK=255.255.255.0
ONBOOT="yes"

• Reiniciamos el servicio: $/etc/init.d/network restart
• Verificamos la configuracion con un ifconfig 

2 Configurar el listener para que escuche desde la ip publica

Ingresaremos al ssh con el usuario oracle y setereamos el home
https://kb.iweb.com/hc/es/articles/230241888-Agregar-y-ver-las-direcciones-IP-en-servidores-CentOS

Vamos a la ruta del archivos LISTENER.ORA y editamos para agregar la linea que permitira al listener escuchar desde la interfaz publica.

• $cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin
• vi listener.ora
• Agregamos la linea del la ippublica

Reiniciamos el listener y la base de datos y verificamos la escucha

• lsnrctl stop
• lsnrctl start
• sqlplus / as sysdba
• sql>shutdown immediate;
• sql>startup;

Ejecutamos un lsnrctl status

No se puede cargar el archivo o ensamblado 'Oracle.DataAccess' ni una de sus dependencias. Se ha intentado cargar un programa con un formato incorrecto.

Todo iba bien cuando vivíamos en un mundo donde solo existía en el desarrollo 32bits, pero una vez apareció 64bits nos dió algunos dolores de cabeza sobre todo en temas de conexión a base de datos Oracle.

Tenia una aplicación realizada en ASP la cual funcionaba con VS 2008 luego la migre a 2010 hasta que la pase 2012 y hasta ahí la deje. No me presentaba problemas con el cliente de Oracle 11.1.0.2G para 32bits pero cuando quise actualizar a 12.2.0.1 C para 64 bits se volvió un dolor de cabeza que corriera con VS ya que siempre se presentaba el error:

No se puede cargar el archivo o ensamblado 'Oracle.DataAccess' ni una de sus dependencias. Se ha intentado cargar un programa con un formato incorrecto.

Seguí los paso recomendados en el Oracle Suport para la instalación y configuración del ODAC64 en VS  ASP agregarlos en el proyecto como referencia y establecer el proyecto de AnyCpu a x64.

Aunque se siguió todos los pasos recomendados, esto no funcionaba y seguía dando el mismo error.

Después de indagar, investigar y probar mucho me percate de una particularidad del VS2012 y es que el IIS Express funciona a 32bits, esto quería decir que jamas de los jamases me iba a funcionar la conexión con el Oracle.DataAccess.dll de 64bits. Y entonces, de que me sirve establecer el AnyCpu o x64? bueno eso solo sirve ara la compilación y generacion de los archivos binarios y empaquetados Dll por ello en la compilación no da error sino en la ejecución por que el IIS Express al ejecutar en 32bits SOLO va a ejecutar referencias que funcionen a 32bits!!!.

Y ahora que hacemos ? Instalamos un Visual Studio para 64 bits? bueno, pues eso sería lo lógico pero no existe un VS para 64bits.  Y ahora? bueno para este tipo de inconvenientes de plataformas web de 64bits Microsoft incluyó desde la versión VS2013 la opción de ejecutar los proyectos web en plataforma IIS de 64bits.  En Herramientas\Opciones\Proyectos y Soluciones\Proyectos Web activa el casillero Usa version de 64bits para IIS Express.

Bueno, esta solucionado para los que tiene VS2013 y superior.  Y los que tenemos VS2012 que hacemos?
Bueno, también lo podemos hacer que ejecute el IIS Express a 64bits pero es un poco mas complicado ya que tenemos que tenemos que hacerlo desde el regedit.

Ejecutamos el regedit, vamos a la ruta HKEY_CU\SOFTWARE\Microsotf\VisualStudio\11.0\WebProjects, aqui agregamos un nuevo valor DWORD Use64BitIISExpress con valor 1, esto hará que por defectos el IIS Express del VS2012 funcione a 64bits.

Y listo, reiniciamos el VS2012 para que cargue con la nueva configuración y ejecute el IIS Express ejecute a 64bits y asi funcione nuestra conexión Oracle 12c con Oracle.DataAccess.dll de 64bits.

Nota: Una vez configurado el IIS para 64bits los proyectos web de 32bits no funcionaran. Tendran que dejar la configuración original.

LogMiner Ver el contenido Logfile y archivelog.

LogMiner es una  utilidad de Oracle para examinar el contenido de los archivos redolog y archivelogs, como sabemos en los redologs son almacenan todos los cambios realizados en la base de datos y sirven para la recuperación de la base de datos en caso de un fallo del sistema manejador de base de datos RDBMS, los archevelogs son los redologs almacenados de forma histórica y sirven para recuperaciones de base de datos en conjunto con el backup RMAN.

Para que nos sirve esta utilidad:

• Determinar cuando pudo ocurrir  una corrupción lógica en una base de datos, como errores cometidos en el nivel de la aplicación.
• Determinar qué acciones tendría que realizar para realizar una recuperación detallada en el nivel de transacción.
• Ajuste del rendimiento y planificación de la capacidad a través del análisis de tendencias.
• Realice un seguimiento de cualquier DML(Insert, update, delete) y  (Create, alter, drop) ejecutados en la base de datos, el orden en que se ejecutaron y quién los ejecutó.

Como podemos examinarlos?

Bueno para esto es mejor un ejemplo examinando los redologs, para esto vamos a realizar unas operaciones DDL(Create table) y DML (Insert).

create table prueba_log_miner (     campo1  number,     campo2 varchar2(30),     campo3 date ); insert into prueba_log_miner values(1,'PRUEBA LOGS',sysdate); commit;

Luego de ejecutar las sentencias ahora hay que determinar cual es el grupo de redolog activo donde se almaceno el registro de la modificación., luego ubicamos los archivos miembros del grupo y copiamos uno de ellos.

Ver el grupo activo

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

Vemos que el grupo 2 es el activo, ahora seleccionar unos de los archivos miembros del grupo numero 2

SELECT GROUP# "GROUP", STATUS, MEMBER , TYPE FROM SYS.V_$LOGFILE WHERE GROUP# =2;

Ahora si vamos a usar las utilidades DBMS_LOGMNR

--Se indica el archivo que se cargara los logs almacenados Begin   SYS.DBMS_LOGMNR.ADD_LOGFILE( 'E:\ORACLE\DATA\BASE\ONLINELOG\G2_REDO01.LOG',  sys.dbms_logmnr.New); end; --Se realiza la carga del archivo al diccionario. Begin   SYS.DBMS_LOGMNR.START_LOGMNR   (    Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG   ); end;

sys.dbms_logmnr.New Usamos para indicar que se realizara una carga nueva si es un solo archivo, pero si vamos a incluir más de uno el siguiente archivo se pondría el sys.dbms_logmnr.Addfile, con esto podemos ver mas de un archivo en el diccionario.

Ahora revisamos el contenido de los logs en la vista V$LOGMNR_CONTENTS

Select  timestamp , session# , operation, sql_redo From V$LOGMNR_CONTENTS where sql_redo like '%prueba_log_miner%' or sql_redo like '%PRUEBA_LOG_MINER%' Order by 1 desc

Listo, podemos ver el contenido del redo log. Ahora como podemos ver el contenido del archivelog? Bueno es el mismo procedimiento, pero debemos ubicar la ruta de los archivelogs.

Previo a esto debe de estar la base de datos en modo ARCHIVELOG, vamos a generar unos tres switch de log para forzar al archivado de los redologs.

ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE;

Ahora revisamos el archivelog generado

select STAMP, NAME, FIRST_TIME  from V$ARCHIVED_LOG where FIRST_TIME>=sysdate -1 order by 1 desc  ;

Tomamos los archivos  generados para incluirlos en el script

Begin   SYS.DBMS_LOGMNR.ADD_LOGFILE( 'E:\ORACLE\BACKUP\BASE\ARCHIVELOG\LOG_D7IPROD_1662_0955901488_0001.ARC',  sys.dbms_logmnr.New);   SYS.DBMS_LOGMNR.ADD_LOGFILE( 'E:\ORACLE\BACKUP\BASE\ARCHIVELOG\LOG_D7IPROD_1663_0955901488_0001.ARC',  sys.dbms_logmnr.Addfile);   SYS.DBMS_LOGMNR.ADD_LOGFILE( 'E:\ORACLE\BACKUP\D7ITEST\ARCHIVELOG\LOG_D7IPROD_1664_0955901488_0001.ARC',  sys.dbms_logmnr.Addfile); end; Begin   SYS.DBMS_LOGMNR.START_LOGMNR   (    Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG   ); end;

Realizamos el select

Select  timestamp ,  operation,  sql_redo From V$LOGMNR_CONTENTS where sql_redo like '%prueba_log_miner%' or sql_redo like '%PRUEBA_LOG_MINER%' Order by 1 desc;

Listo, podemos revisar las sentencias ejecutadas.

Referencias
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_logmnr.htm#CCHEGJCG

Funciones Analiticas de Oracle

A veces se nos ha presentado requerimientos de presentación de información que no teníamos ni idea hacerlo. Para los SQL FANS, les presento algunas funciones muy útiles en el ámbito analítico para la elaboración de consultas SQL.

Para el uso de esta funciones hay que conocer las clausulas OVER y PARTITION BY, favor lean “Consultas SQL con la utilidad OVER(PARTITION BY”, claro que en este articulo lo explicaremos brevemente.

OVER utilidad para utilizar funciones de grupo a nivel de fila sin GROUP BY el PARTITION BY especificamos como se establecerá la agrupación.

Script para ejemplos

CREATE TABLE emp (   empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,   ename    VARCHAR2(10),   job      VARCHAR2(9),   mgr      NUMBER(4),   hiredate DATE,   sal      NUMBER(7,2),   comm     NUMBER(7,2),   deptno   NUMBER(2) ); INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;

RANK.

Como su nombre lo indica es una función para ranquear las filas de datos de una consulta de acuerdo a un orden de los datos  y agrupación.

Para más claridad un ejemplo, tenemos la tabla emp y deseamos sacar los datos ranqueado por sueldo por cada departamento.

SELECT ENAME,        DEPTNO,        SAL,        RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL) AS RANKING FROM   EMP;

En la consulta se agrega fila con el RANK dentro del OVER especificamos como va agrupar los datos y a continuación el orden para el RANKING.

En el resultado se muestra los datos agrupados por DPTONO (PARTITION BY DEPTNO), mostrando los datos ordenados por el salario en forma ascendente y al final el RANKING del salario.

Noten que para departamento 20 tanto SCOTT y FORD tienen el mismo RANKING ya que poseen el mismo salario.

Ahora si en vez de hacer el ranking del salario lo queremos hacer del mayor a menor en vez de menor a mayor solo agregamos DESC en el ORDER BY .

SELECT ENAME,        DEPTNO,        SAL,        RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANKING FROM   EMP;

Listo ahora tenemos el rankin del mejor salario al menor salario agrupado por empresa.

DENSE_RANK

Es similar a la RANK sino que aquí el rankin se aplica de forma consecutiva.

SELECT ENAME,        DEPTNO,        SAL,        DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL) AS DENSE_RANKING FROM   EMP;

Noten que el rankin asignado se repite en fila con datos iguales para MARTIN y WARD y TURNER tiene la numeración siguiente del ranking.

Comparemos RANK y DENSE_RANK

SELECT ENAME,        DEPTNO,        SAL,        RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANKING,        DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS DENSE_RANKING FROM   EMP;

Si se fijan ambos dan el mismo ranking de datos pero el siguiente rankin RANK  da el valor 3  y DENSE_RANK da el consecutivo de 1 que el  2.

FIRST_VALUE y LAST_VALUE

Esta funcionalidad nos permite extraer el primero o último dato de una partición de datos en particular. Si bien es cierto a diferencia de  MAX y MIN  con FIRST_VALUE y LAST_VALUE podemor retornar cualquier campo del set de datos con cualquier criterio en el dordenamiento.

Como haríamos si deseamos obtener una consulta de datos de empleados que me muestre una columna del salario de la primera contratación y otra con el salario de la ultima contratación por grupo de departamento.

SELECT E.EMPNO,        E.DEPTNO,        E.HIREDATE,        E.SAL,        FIRST_VALUE(E.SAL)IGNORE NULLS OVER (                 PARTITION BY E.DEPTNO ORDER BY E.HIREDATE               ) AS PRIMER_SAL,        LAST_VALUE(E.SAL)IGNORE NULLS OVER (                 PARTITION BY E.DEPTNO ORDER BY E.HIREDATE                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ULTIMO_SAL FROM   EMP E ORDER BY E.DEPTNO, E.HIREDATE;

Vemos que el resultado tenemos por cada fila el primer salario y último salario según la fecha de ingreso por departamento. Como vemos en esta consulta evaluamos un campo pero retornamos otro.

LAG

Es una función analítica que proporciona acceso a más de una fila de una tabla al mismo tiempo Dada una serie de filas devueltas de una consulta y una posición del cursor, LAG proporciona acceso a una fila en un desplazamiento físico dado antes de esa posición.

Hagamos un ejemplo, realizaremos una consulta que extraiga el salario del empleado más otra columna con el salario previo.

SELECT EMPNO,        DEPTNO,        HIREDATE,        SAL,        LAG(SAL, 1, 0 ) OVER (ORDER BY HIREDATE) AS PREV_SAL FROM   EMP ORDER BY HIREDATE;

 Como vemos, la consulta esta ordenada por fecha de contratación y muestra los salarios. Adicional a esto presenta PREV_SAL que el salario anterior a los datos retornados.

LAG recibe 3 parámetros, el campo a retornar, la fila previa a retornar y el valor a retornar en caso de no existir.

Ahora hagamos el mismo ejemplo pero agrupado por departamento. Para que por orden de departamento presente la información.

SELECT EMPNO,        DEPTNO,        HIREDATE,        SAL,        LAG(SAL, 1, 0 ) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE) AS PREV_SAL FROM   EMP ORDER BY DEPTNO, HIREDATE;

Como vemos , al iniciar cada grupo nos muestra 0 ya que se estableció en el PARTITION BY DEPTNO.

LEAD
Es algo parecido a la LAG pero solo que retorna el siguiente valor del set de datos, usaremos los ejemplos anteriores pero cambiaremos para que traiga el siguiente

SELECT EMPNO,        DEPTNO,        HIREDATE,        SAL,        LEAD(SAL, 1, 0 ) OVER (ORDER BY HIREDATE) AS POS_SAL FROM   EMP ORDER BY HIREDATE;

ahora lo realizaremos por departamento

SELECT EMPNO,        DEPTNO,        HIREDATE,        SAL,        LEAD(SAL, 1, 0 ) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE) AS POS_SAL FROM   EMP ORDER BY DEPTNO, HIREDATE;

Funciones de estadistica AVG, STDDEV y VARIANCE

Podemos hace uso de las funciones estadisca estableciendo un orden calculo

SELECT EMPNO,        DEPTNO,        HIREDATE,        SAL,        LEAD(SAL, 1, 0 ) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE) AS POS_SAL FROM   EMP ORDER BY DEPTNO, HIREDATE;

Podemos agreagar particiones como el departamento para el calculo

SELECT EMPNO,        DEPTNO,        HIREDATE,        SAL,        STDDEV(SAL) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE) AS VAL_STDDEV,        VARIANCE(SAL) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE) AS VAL_VARIANCE,        AVG(SAL) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE) AS VAL_AVG FROM   EMP ORDER BY DEPTNO,HIREDATE;

Espero les sea útil como a mi estas funciones.