9. Funciones Oracle
Introducción a las funciones de Oracle
Section titled “Introducción a las funciones de Oracle”Las funciones en Oracle Database son bloques de código predefinidos que realizan operaciones específicas y devuelven un valor. Estas funciones son herramientas esenciales para manipular datos, realizar cálculos y transformar información dentro de consultas SQL. Oracle proporciona una amplia variedad de funciones integradas que facilitan el procesamiento de diferentes tipos de datos.
Categorías principales de funciones
Section titled “Categorías principales de funciones”- Funciones de cadena: Manipulan datos de tipo texto
- Funciones numéricas: Realizan operaciones matemáticas
- Funciones de fecha y hora: Manipulan y calculan valores temporales
- Funciones de conversión: Transforman datos de un tipo a otro
- Funciones de grupo: Realizan cálculos sobre conjuntos de filas
- Funciones analíticas: Realizan cálculos sobre particiones de datos
Funciones de cadenas
Section titled “Funciones de cadenas”Las funciones de cadena en Oracle permiten manipular datos de tipo VARCHAR2, CHAR, CLOB, NVARCHAR2, NCHAR y NCLOB.
UPPER, LOWER e INITCAP
Section titled “UPPER, LOWER e INITCAP”Estas funciones modifican la capitalización de las cadenas de texto.
-- UPPER: Convierte una cadena a mayúsculasSELECT UPPER('Oracle Database') FROM dual;-- Resultado: ORACLE DATABASE
-- LOWER: Convierte una cadena a minúsculasSELECT LOWER('Oracle DATABASE') FROM dual;-- Resultado: oracle database
-- INITCAP: Convierte la primera letra de cada palabra a mayúsculaSELECT INITCAP('oracle database') FROM dual;-- Resultado: Oracle DatabaseSUBSTR
Section titled “SUBSTR”Extrae una subcadena de una cadena dada, especificando la posición de inicio y opcionalmente la longitud.
-- Sintaxis: SUBSTR(cadena, posición_inicio [, longitud])-- Extraer desde la posición 8, 8 caracteresSELECT SUBSTR('Oracle Database', 8, 8) FROM dual;-- Resultado: Database
-- Extraer desde la posición 1, 6 caracteresSELECT SUBSTR('Oracle Database', 1, 6) FROM dual;-- Resultado: Oracle
-- Posición negativa (cuenta desde el final)SELECT SUBSTR('Oracle Database', -8) FROM dual;-- Resultado: DatabaseLENGTH
Section titled “LENGTH”Devuelve el número de caracteres en una cadena.
-- Contar caracteres en una cadenaSELECT LENGTH('Oracle Database') FROM dual;-- Resultado: 15
-- Uso en una consultaSELECT nombre, LENGTH(nombre) AS longitudFROM empleadosWHERE LENGTH(nombre) > 10;TRIM, LTRIM y RTRIM
Section titled “TRIM, LTRIM y RTRIM”Estas funciones eliminan caracteres específicos (por defecto espacios) del inicio, final o ambos extremos de una cadena.
-- Eliminar espacios de ambos extremosSELECT TRIM(' Oracle Database ') FROM dual;-- Resultado: "Oracle Database"
-- Eliminar un carácter específicoSELECT TRIM('O' FROM 'Oracle Database') FROM dual;-- Resultado: "racle Database"
-- Especificar posición (LEADING, TRAILING, BOTH)SELECT TRIM(LEADING 'O' FROM 'Oracle Database') FROM dual;-- Resultado: "racle Database"
SELECT TRIM(TRAILING 'e' FROM 'Oracle Database') FROM dual;-- Resultado: "Oracle Databas"-- LTRIM: Eliminar caracteres del inicio (izquierda)SELECT LTRIM(' Oracle Database') FROM dual;-- Resultado: "Oracle Database"
SELECT LTRIM('Oracle Database', 'Ora') FROM dual;-- Resultado: "cle Database"
-- RTRIM: Eliminar caracteres del final (derecha)SELECT RTRIM('Oracle Database ') FROM dual;-- Resultado: "Oracle Database"
SELECT RTRIM('Oracle Database', 'base') FROM dual;-- Resultado: "Oracle Data"CONCAT
Section titled “CONCAT”Concatena dos cadenas. Es equivalente al operador ||.
-- Concatenar dos cadenasSELECT CONCAT('Oracle ', 'Database') FROM dual;-- Resultado: "Oracle Database"
-- Equivalente usando el operador ||SELECT 'Oracle ' || 'Database' FROM dual;-- Resultado: "Oracle Database"
-- Concatenar múltiples cadenasSELECT 'Oracle ' || 'Database ' || 'Version ' || '19c' FROM dual;-- Resultado: "Oracle Database Version 19c"Busca la posición de una subcadena dentro de una cadena.
-- Sintaxis: INSTR(cadena, subcadena [, posición_inicio [, ocurrencia]])-- Buscar la primera ocurrenciaSELECT INSTR('Oracle Database Oracle', 'Oracle') FROM dual;-- Resultado: 1
-- Buscar desde una posición específicaSELECT INSTR('Oracle Database Oracle', 'Oracle', 2) FROM dual;-- Resultado: 17
-- Buscar la segunda ocurrenciaSELECT INSTR('Oracle Database Oracle', 'Oracle', 1, 2) FROM dual;-- Resultado: 17Otras funciones de cadena útiles
Section titled “Otras funciones de cadena útiles”| Función | Descripción | Ejemplo |
|---|---|---|
| REPLACE | Reemplaza todas las ocurrencias de una subcadena | REPLACE('Oracle DB', 'DB', 'Database') |
| LPAD | Rellena por la izquierda hasta una longitud | LPAD('Oracle', 10, '*') |
| RPAD | Rellena por la derecha hasta una longitud | RPAD('Oracle', 10, '*') |
| TRANSLATE | Reemplaza caracteres individuales | TRANSLATE('Oracle', 'Oa', '0@') |
| ASCII | Devuelve el código ASCII de un carácter | ASCII('A') |
| CHR | Devuelve el carácter de un código ASCII | CHR(65) |
Funciones numéricas
Section titled “Funciones numéricas”Las funciones numéricas en Oracle permiten realizar operaciones matemáticas sobre valores numéricos.
ROUND y TRUNC
Section titled “ROUND y TRUNC”Estas funciones redondean o truncan números a un número específico de decimales.
-- Redondear a 2 decimalesSELECT ROUND(123.456, 2) FROM dual;-- Resultado: 123.46
-- Redondear a 0 decimalesSELECT ROUND(123.567) FROM dual;-- Resultado: 124
-- Redondear a negativo (unidades superiores)SELECT ROUND(123.567, -1) FROM dual;-- Resultado: 120
SELECT ROUND(123.567, -2) FROM dual;-- Resultado: 100-- Truncar a 2 decimalesSELECT TRUNC(123.456, 2) FROM dual;-- Resultado: 123.45
-- Truncar a 0 decimalesSELECT TRUNC(123.567) FROM dual;-- Resultado: 123
-- Truncar a negativo (unidades superiores)SELECT TRUNC(123.567, -1) FROM dual;-- Resultado: 120
SELECT TRUNC(123.567, -2) FROM dual;-- Resultado: 100Devuelve el resto de una división.
-- Sintaxis: MOD(dividendo, divisor)SELECT MOD(10, 3) FROM dual;-- Resultado: 1
-- Verificar si un número es par o imparSELECT numero, CASE WHEN MOD(numero, 2) = 0 THEN 'Par' ELSE 'Impar' END AS tipoFROM numeros;POWER y SQRT
Section titled “POWER y SQRT”Calculan potencias y raíces cuadradas.
-- POWER: Eleva un número a una potenciaSELECT POWER(2, 3) FROM dual;-- Resultado: 8
-- SQRT: Calcula la raíz cuadradaSELECT SQRT(16) FROM dual;-- Resultado: 4Devuelve el valor absoluto de un número.
SELECT ABS(-15.5) FROM dual;-- Resultado: 15.5
SELECT ABS(15.5) FROM dual;-- Resultado: 15.5CEIL y FLOOR
Section titled “CEIL y FLOOR”Redondean hacia arriba o hacia abajo al entero más cercano.
-- CEIL: Redondea hacia arribaSELECT CEIL(15.1) FROM dual;-- Resultado: 16
SELECT CEIL(-15.1) FROM dual;-- Resultado: -15
-- FLOOR: Redondea hacia abajoSELECT FLOOR(15.9) FROM dual;-- Resultado: 15
SELECT FLOOR(-15.9) FROM dual;-- Resultado: -16Otras funciones numéricas útiles
Section titled “Otras funciones numéricas útiles”| Función | Descripción | Ejemplo |
|---|---|---|
| SIGN | Devuelve el signo de un número (-1, 0, 1) | SIGN(-10) |
| EXP | Devuelve e elevado a una potencia | EXP(1) |
| LN | Logaritmo natural | LN(10) |
| LOG | Logaritmo en una base específica | LOG(10, 100) |
| SIN, COS, TAN | Funciones trigonométricas | SIN(0) |
| ASIN, ACOS, ATAN | Funciones trigonométricas inversas | ASIN(0) |
Funciones de fecha y hora
Section titled “Funciones de fecha y hora”Las funciones de fecha y hora en Oracle permiten manipular valores temporales, realizar cálculos con fechas y extraer componentes específicos.
SYSDATE y CURRENT_DATE
Section titled “SYSDATE y CURRENT_DATE”Devuelven la fecha y hora actuales del sistema.
-- SYSDATE: Fecha y hora actuales del servidor de base de datosSELECT SYSDATE FROM dual;-- Resultado: 18-AGO-25 (formato depende de NLS_DATE_FORMAT)
-- CURRENT_DATE: Fecha y hora actuales en la zona horaria de la sesiónSELECT CURRENT_DATE FROM dual;
-- Mostrar con formatoSELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS fecha_actual FROM dual;ADD_MONTHS
Section titled “ADD_MONTHS”Añade un número específico de meses a una fecha.
-- Añadir 3 meses a la fecha actualSELECT ADD_MONTHS(SYSDATE, 3) FROM dual;
-- Restar 2 meses (usando número negativo)SELECT ADD_MONTHS(SYSDATE, -2) FROM dual;
-- Calcular la fecha de vencimiento de un contratoSELECT fecha_inicio, ADD_MONTHS(fecha_inicio, duracion_meses) AS fecha_vencimientoFROM contratos;MONTHS_BETWEEN
Section titled “MONTHS_BETWEEN”Calcula el número de meses entre dos fechas.
-- Calcular meses entre dos fechasSELECT MONTHS_BETWEEN( TO_DATE('15-08-2025', 'DD-MM-YYYY'), TO_DATE('15-02-2025', 'DD-MM-YYYY')) FROM dual;-- Resultado: 6
-- Calcular antigüedad de empleados en mesesSELECT nombre, fecha_contratacion, ROUND(MONTHS_BETWEEN(SYSDATE, fecha_contratacion), 0) AS meses_antiguedadFROM empleados;LAST_DAY
Section titled “LAST_DAY”Devuelve el último día del mes para una fecha dada.
-- Último día del mes actualSELECT LAST_DAY(SYSDATE) FROM dual;
-- Último día de febrero de 2024 (año bisiesto)SELECT LAST_DAY(TO_DATE('01-02-2024', 'DD-MM-YYYY')) FROM dual;-- Resultado: 29-FEB-24
-- Último día de febrero de 2025 (año no bisiesto)SELECT LAST_DAY(TO_DATE('01-02-2025', 'DD-MM-YYYY')) FROM dual;-- Resultado: 28-FEB-25NEXT_DAY
Section titled “NEXT_DAY”Devuelve la primera fecha posterior a una fecha dada que corresponde a un día de la semana específico.
-- Próximo lunes desde la fecha actualSELECT NEXT_DAY(SYSDATE, 'LUNES') FROM dual;
-- Próximo viernes desde una fecha específicaSELECT NEXT_DAY(TO_DATE('15-08-2025', 'DD-MM-YYYY'), 'VIERNES') FROM dual;
-- Nota: El nombre del día debe estar en el idioma configurado en NLS_DATE_LANGUAGEEXTRACT
Section titled “EXTRACT”Extrae componentes específicos (año, mes, día, etc.) de una fecha.
-- Extraer el año de la fecha actualSELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
-- Extraer el mesSELECT EXTRACT(MONTH FROM SYSDATE) FROM dual;
-- Extraer el díaSELECT EXTRACT(DAY FROM SYSDATE) FROM dual;
-- Extraer la horaSELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM dual;
-- Extraer el minutoSELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM dual;Operaciones aritméticas con fechas
Section titled “Operaciones aritméticas con fechas”Oracle permite realizar operaciones aritméticas directamente con fechas.
-- Sumar días a una fechaSELECT SYSDATE + 7 AS una_semana_despues FROM dual;
-- Restar días a una fechaSELECT SYSDATE - 7 AS una_semana_antes FROM dual;
-- Calcular la diferencia en días entre dos fechasSELECT TO_DATE('31-12-2025', 'DD-MM-YYYY') - TO_DATE('01-01-2025', 'DD-MM-YYYY') AS dias_en_2025FROM dual;-- Resultado: 364Funciones de intervalo
Section titled “Funciones de intervalo”Oracle proporciona funciones para trabajar con intervalos de tiempo.
-- Crear un intervalo de díasSELECT INTERVAL '30' DAY AS un_mes FROM dual;
-- Sumar un intervalo a una fechaSELECT SYSDATE + INTERVAL '12' HOUR AS doce_horas_despues FROM dual;
-- Intervalos complejosSELECT SYSDATE + INTERVAL '2 12:30:45' DAY TO SECOND AS futuro FROM dual;-- Crear un intervalo de añosSELECT INTERVAL '2' YEAR AS dos_anos FROM dual;
-- Sumar un intervalo a una fechaSELECT ADD_MONTHS(SYSDATE, 12*5) AS cinco_anos_despues FROM dual;-- O usando INTERVALSELECT SYSDATE + INTERVAL '5' YEAR AS cinco_anos_despues FROM dual;
-- Intervalos complejosSELECT SYSDATE + INTERVAL '2-6' YEAR TO MONTH AS futuro FROM dual;Otras funciones de fecha y hora útiles
Section titled “Otras funciones de fecha y hora útiles”| Función | Descripción | Ejemplo |
|---|---|---|
| TRUNC(date) | Trunca una fecha a medianoche | TRUNC(SYSDATE) |
| ROUND(date) | Redondea una fecha al día más cercano | ROUND(SYSDATE) |
| SYSTIMESTAMP | Fecha y hora con zona horaria y fracción de segundo | SYSTIMESTAMP |
| NEW_TIME | Convierte hora entre zonas horarias | NEW_TIME(SYSDATE, 'EST', 'PST') |
| TO_YMINTERVAL | Crea un intervalo de año a mes | TO_YMINTERVAL('01-06') |
| TO_DSINTERVAL | Crea un intervalo de día a segundo | TO_DSINTERVAL('2 12:30:00') |
Funciones de conversión
Section titled “Funciones de conversión”Las funciones de conversión en Oracle permiten transformar datos de un tipo a otro, facilitando operaciones entre diferentes tipos de datos.
TO_CHAR
Section titled “TO_CHAR”Convierte valores de fecha, numéricos o de intervalo a cadenas de caracteres con formato.
-- Convertir fecha a texto con formatoSELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') FROM dual;-- Resultado: 18-08-2025 20:44:23
-- Formato con nombres de mes y díaSELECT TO_CHAR(SYSDATE, 'Day, DD "de" Month "de" YYYY') FROM dual;-- Resultado: Lunes, 18 de Agosto de 2025
-- Formato con trimestre y semana del añoSELECT TO_CHAR(SYSDATE, 'Q "Trimestre", WW "Semana"') FROM dual;-- Convertir número a texto con formatoSELECT TO_CHAR(1234.56, '9,999.99') FROM dual;-- Resultado: 1,234.56
-- Formato con símbolo de monedaSELECT TO_CHAR(1234.56, '$9,999.99') FROM dual;-- Resultado: $1,234.56
-- Formato con textoSELECT TO_CHAR(1234.56, '9G999D99 "euros"') FROM dual;-- Resultado: 1,234.56 euros (G y D dependen de NLS_NUMERIC_CHARACTERS)TO_DATE
Section titled “TO_DATE”Convierte una cadena de caracteres a un valor de fecha.
-- Convertir texto a fechaSELECT TO_DATE('18-08-2025', 'DD-MM-YYYY') FROM dual;
-- Convertir texto con formato complejoSELECT TO_DATE('18 de Agosto, 2025', 'DD "de" Month, YYYY') FROM dual;
-- Convertir texto con horaSELECT TO_DATE('18-08-2025 14:30:00', 'DD-MM-YYYY HH24:MI:SS') FROM dual;
-- Uso en consultasSELECT *FROM pedidosWHERE fecha_pedido BETWEEN TO_DATE('01-01-2025', 'DD-MM-YYYY') AND TO_DATE('31-12-2025', 'DD-MM-YYYY');TO_NUMBER
Section titled “TO_NUMBER”Convierte una cadena de caracteres a un valor numérico.
-- Convertir texto a númeroSELECT TO_NUMBER('1234.56') FROM dual;
-- Convertir texto con formatoSELECT TO_NUMBER('1,234.56', '9,999.99') FROM dual;
-- Convertir texto con símbolo de monedaSELECT TO_NUMBER('$1,234.56', '$9,999.99') FROM dual;
-- Uso en consultasSELECT *FROM productosWHERE precio > TO_NUMBER('1,000.00', '9,999.99');Convierte un valor de un tipo de datos a otro.
-- Convertir número a cadenaSELECT CAST(1234.56 AS VARCHAR2(10)) FROM dual;
-- Convertir cadena a númeroSELECT CAST('1234.56' AS NUMBER(10,2)) FROM dual;
-- Convertir cadena a fechaSELECT CAST('18-AUG-25' AS DATE) FROM dual;
-- Convertir entre tipos de datos complejosSELECT CAST(fecha_nacimiento AS TIMESTAMP) FROM empleados;NVL, NULLIF y COALESCE
Section titled “NVL, NULLIF y COALESCE”Estas funciones ayudan a manejar valores NULL en las consultas.
-- Reemplazar NULL por un valor predeterminadoSELECT nombre, NVL(telefono, 'Sin teléfono') AS telefono_contactoFROM clientes;
-- Con valores numéricosSELECT producto, NVL(precio, 0) AS precio_finalFROM productos;-- Devuelve NULL si los dos valores son igualesSELECT NULLIF(valor1, valor2) FROM tabla;
-- Ejemplo: evitar división por ceroSELECT ventas_actuales, ventas_anteriores, ventas_actuales / NULLIF(ventas_anteriores, 0) AS ratioFROM informes_ventas;-- Devuelve el primer valor no NULL de la listaSELECT COALESCE(telefono_movil, telefono_fijo, email, 'Sin contacto') AS contactoFROM clientes;
-- Ejemplo con múltiples columnasSELECT producto, COALESCE(precio_oferta, precio_regular) AS precio_finalFROM productos;Otras funciones de conversión útiles
Section titled “Otras funciones de conversión útiles”| Función | Descripción | Ejemplo |
|---|---|---|
| TO_TIMESTAMP | Convierte texto a timestamp | TO_TIMESTAMP('18-08-2025 14:30:00.000', 'DD-MM-YYYY HH24:MI:SS.FF') |
| TO_CLOB | Convierte a tipo CLOB | TO_CLOB(texto_largo) |
| HEXTORAW | Convierte texto hexadecimal a RAW | HEXTORAW('7F') |
| RAWTOHEX | Convierte RAW a texto hexadecimal | RAWTOHEX(columna_raw) |
| BIN_TO_NUM | Convierte bits a número | BIN_TO_NUM(1,0,1) |
| CONVERT | Convierte entre conjuntos de caracteres | CONVERT(texto, 'UTF8', 'AL32UTF8') |
Funciones de grupo (agregación)
Section titled “Funciones de grupo (agregación)”Las funciones de grupo en Oracle realizan cálculos sobre conjuntos de filas y devuelven un único resultado por grupo. Se utilizan comúnmente con la cláusula GROUP BY.
SUM y AVG
Section titled “SUM y AVG”Calculan la suma y el promedio de valores numéricos.
-- Calcular la suma total de ventasSELECT SUM(monto_venta) AS total_ventas FROM ventas;
-- Calcular el promedio de ventasSELECT AVG(monto_venta) AS promedio_ventas FROM ventas;
-- Agrupar por departamentoSELECT departamento, SUM(salario) AS total_salarios, AVG(salario) AS salario_promedioFROM empleadosGROUP BY departamento;MIN y MAX
Section titled “MIN y MAX”Encuentran el valor mínimo y máximo en un conjunto de datos.
-- Encontrar el precio mínimo y máximoSELECT MIN(precio) AS precio_minimo, MAX(precio) AS precio_maximoFROM productos;
-- Agrupar por categoríaSELECT categoria, MIN(precio) AS precio_minimo, MAX(precio) AS precio_maximoFROM productosGROUP BY categoria;
-- También funcionan con fechas y textoSELECT MIN(fecha_contratacion) AS primera_contratacion, MAX(fecha_contratacion) AS ultima_contratacionFROM empleados;Cuenta el número de filas o valores no nulos.
-- Contar todas las filasSELECT COUNT(*) AS total_empleados FROM empleados;
-- Contar valores no nulos en una columnaSELECT COUNT(telefono) AS empleados_con_telefono FROM empleados;
-- Contar valores distintosSELECT COUNT(DISTINCT departamento) AS numero_departamentos FROM empleados;
-- Agrupar por departamentoSELECT departamento, COUNT(*) AS numero_empleadosFROM empleadosGROUP BY departamento;LISTAGG
Section titled “LISTAGG”Concatena valores de varias filas en una sola cadena.
-- Concatenar nombres de empleados por departamentoSELECT departamento, LISTAGG(nombre, ', ') WITHIN GROUP (ORDER BY nombre) AS empleadosFROM empleadosGROUP BY departamento;
-- Con límite y desbordamientoSELECT departamento, LISTAGG(nombre, ', ' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY nombre) AS empleadosFROM empleadosGROUP BY departamento;Cláusulas HAVING
Section titled “Cláusulas HAVING”La cláusula HAVING se utiliza para filtrar resultados basados en funciones de grupo.
-- Filtrar departamentos con más de 10 empleadosSELECT departamento, COUNT(*) AS numero_empleadosFROM empleadosGROUP BY departamentoHAVING COUNT(*) > 10;
-- Filtrar categorías con precio promedio superior a 100SELECT categoria, AVG(precio) AS precio_promedioFROM productosGROUP BY categoriaHAVING AVG(precio) > 100;Otras funciones de grupo útiles
Section titled “Otras funciones de grupo útiles”| Función | Descripción | Ejemplo |
|---|---|---|
| STDDEV | Desviación estándar | STDDEV(salario) |
| VARIANCE | Varianza | VARIANCE(salario) |
| MEDIAN | Mediana | MEDIAN(salario) |
| PERCENTILE_CONT | Percentil continuo | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salario) |
| PERCENTILE_DISC | Percentil discreto | PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salario) |
| GROUP_ID | Identifica filas duplicadas en agrupaciones | GROUP_ID() |
Funciones analíticas
Section titled “Funciones analíticas”Las funciones analíticas realizan cálculos sobre un conjunto de filas relacionadas con la fila actual, sin agrupar las filas en un único resultado como lo hacen las funciones de grupo.
RANK, DENSE_RANK y ROW_NUMBER
Section titled “RANK, DENSE_RANK y ROW_NUMBER”Estas funciones asignan números de clasificación a las filas.
-- Clasificar empleados por salarioSELECT nombre, departamento, salario, RANK() OVER (ORDER BY salario DESC) AS ranking_general, RANK() OVER (PARTITION BY departamento ORDER BY salario DESC) AS ranking_departamentoFROM empleados;
-- RANK deja huecos en la numeración cuando hay empates-- Ejemplo: 1, 2, 2, 4, 5, ...-- Clasificar empleados por salario sin huecosSELECT nombre, departamento, salario, DENSE_RANK() OVER (ORDER BY salario DESC) AS ranking_general, DENSE_RANK() OVER (PARTITION BY departamento ORDER BY salario DESC) AS ranking_departamentoFROM empleados;
-- DENSE_RANK no deja huecos en la numeración cuando hay empates-- Ejemplo: 1, 2, 2, 3, 4, ...-- Asignar números de fila únicosSELECT nombre, departamento, salario, ROW_NUMBER() OVER (ORDER BY salario DESC) AS numero_fila, ROW_NUMBER() OVER (PARTITION BY departamento ORDER BY salario DESC) AS numero_fila_departamentoFROM empleados;
-- ROW_NUMBER asigna números únicos incluso en empates-- Ejemplo: 1, 2, 3, 4, 5, ...ROWNUM vs. ROW_NUMBER
Section titled “ROWNUM vs. ROW_NUMBER”ROWNUM es una pseudocolumna que asigna números secuenciales a las filas devueltas por una consulta, mientras que ROW_NUMBER() es una función analítica más flexible.
-- ROWNUM: Asigna números antes de ordenarSELECT ROWNUM, nombre, salarioFROM empleadosWHERE ROWNUM <= 5;
-- ROW_NUMBER: Permite ordenar primero y luego asignar númerosSELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY salario DESC) AS rn, nombre, salario FROM empleados)WHERE rn <= 5;Funciones de ventana
Section titled “Funciones de ventana”Las funciones de ventana realizan cálculos sobre un conjunto de filas relacionadas con la fila actual.
-- Calcular suma acumuladaSELECT fecha, monto, SUM(monto) OVER (ORDER BY fecha) AS suma_acumuladaFROM ventas;
-- Calcular promedio móvil de 3 díasSELECT fecha, monto, AVG(monto) OVER (ORDER BY fecha ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS promedio_movilFROM ventas;
-- Calcular porcentaje del totalSELECT departamento, salario, salario / SUM(salario) OVER () * 100 AS porcentaje_total, salario / SUM(salario) OVER (PARTITION BY departamento) * 100 AS porcentaje_departamentoFROM empleados;Funciones LAG y LEAD
Section titled “Funciones LAG y LEAD”Acceden a datos de filas anteriores o posteriores sin necesidad de auto-uniones.
-- Comparar con el valor anteriorSELECT fecha, monto, LAG(monto) OVER (ORDER BY fecha) AS monto_anterior, monto - LAG(monto) OVER (ORDER BY fecha) AS diferenciaFROM ventas;
-- Comparar con el valor siguienteSELECT fecha, monto, LEAD(monto) OVER (ORDER BY fecha) AS monto_siguiente, monto - LEAD(monto) OVER (ORDER BY fecha) AS diferenciaFROM ventas;
-- Especificar un valor predeterminado para la primera/última filaSELECT fecha, monto, LAG(monto, 1, 0) OVER (ORDER BY fecha) AS monto_anteriorFROM ventas;Otras funciones analíticas útiles
Section titled “Otras funciones analíticas útiles”| Función | Descripción | Ejemplo |
|---|---|---|
| FIRST_VALUE | Primer valor de la ventana | FIRST_VALUE(salario) OVER (...) |
| LAST_VALUE | Último valor de la ventana | LAST_VALUE(salario) OVER (...) |
| NTH_VALUE | Enésimo valor de la ventana | NTH_VALUE(salario, 2) OVER (...) |
| NTILE | Divide las filas en grupos iguales | NTILE(4) OVER (ORDER BY salario) |
| CUME_DIST | Distribución acumulativa | CUME_DIST() OVER (ORDER BY salario) |
| PERCENT_RANK | Rango porcentual | PERCENT_RANK() OVER (ORDER BY salario) |
Conclusión
Section titled “Conclusión”Las funciones de Oracle Database son herramientas poderosas que permiten manipular y transformar datos de manera eficiente. Dominar estas funciones es esencial para escribir consultas SQL efectivas y expresivas.
Resumen de categorías de funciones
Section titled “Resumen de categorías de funciones”- Funciones de cadena: Manipulan y transforman datos de texto.
- Funciones numéricas: Realizan operaciones matemáticas y transformaciones.
- Funciones de fecha y hora: Manipulan valores temporales y realizan cálculos con fechas.
- Funciones de conversión: Transforman datos entre diferentes tipos.
- Funciones de grupo: Realizan cálculos sobre conjuntos de filas.
- Funciones analíticas: Realizan cálculos sobre conjuntos de filas relacionadas con la fila actual.
Recomendaciones para el uso de funciones
Section titled “Recomendaciones para el uso de funciones”- Elija la función adecuada: Asegúrese de seleccionar la función que mejor se adapte a su necesidad específica.
- Considere el rendimiento: Algunas funciones pueden ser más costosas en términos de rendimiento. Evalúe alternativas si es necesario.
- Pruebe con datos de ejemplo: Verifique el comportamiento de las funciones con datos de prueba antes de implementarlas en producción.
- Documente el uso de funciones complejas: Especialmente para funciones analíticas o combinaciones complejas de funciones.
- Mantenga la compatibilidad: Tenga en cuenta la versión de Oracle Database al utilizar funciones más recientes.