Oracle 函数统计大全
本文系统梳理 Oracle 数据库全量函数体系，涵盖从基础到高级的所有函数类别，提供详细语法、示例和实战场景。
一、聚合函数（Aggregate Functions）
对多行数据进行汇总计算，常与GROUP BY子句配合使用。
1.1 基础聚合函数
| 函数 | 功能 | 示例 |
|---|
| COUNT | 统计行数或非 NULL 值个数 | SELECT COUNT(*) FROM employees; |
| SUM | 计算数值总和 | SELECT SUM(salary) FROM employees; |
| AVG | 计算平均值 | SELECT AVG(salary) FROM employees; |
| MAX | 返回最大值 | SELECT MAX(salary) FROM employees; |
| MIN | 返回最小值 | SELECT MIN(salary) FROM employees; |
性能差异：
COUNT(*)：统计所有行，需要扫描表，性能一般COUNT(1)：统计行数，不需要扫描具体列，性能较快COUNT(column)：统计非 NULL 值个数，扫描非 NULL 列COUNT(DISTINCT column)：计算不同值数量，使用排序，性能较慢
-- 部门薪资统计SELECTdepartment_id,COUNT(*)AS员工数,SUM(salary)AS总薪资,AVG(salary)AS平均薪资,MAX(salary)AS最高薪资,MIN(salary)AS最低薪资FROMemployeesGROUPBYdepartment_id;
1.2 高级聚合函数
| 函数 | 功能 |
|---|
| GROUPING SETS | 多维度分组汇总 |
| ROLLUP | 分层汇总，生成小计和总计 |
| CUBE | 多维立方体汇总 |
| LISTAGG | 字符串聚合（11gR2+） |
| WM_CONCAT | 字符串聚合（10g-12c，已废弃） |
-- ROLLUP 分层汇总SELECTdepartment_id,manager_id,SUM(salary)FROMemployeesGROUPBYROLLUP(department_id,manager_id);-- 结果：先按 department+manager 汇总，再按 department 汇总，最后总计-- CUBE 多维汇总SELECTdepartment_id,job_id,AVG(salary)FROMemployeesGROUPBYCUBE(department_id,job_id);-- 结果：所有维度组合（dept+job, dept, job, 总计）-- LISTAGG 字符串聚合SELECTdepartment_id,LISTAGG(first_name,',')WITHINGROUP(ORDERBYsalaryDESC)ASemployeesFROMemployeesGROUPBYdepartment_id;
二、字符串函数
处理和操作字符数据，是数据处理中最常用的函数类别。
2.1 大小写转换
| 函数 | 功能 | 示例 |
|---|
| UPPER | 转大写 | UPPER('oracle') → 'ORACLE' |
| LOWER | 转小写 | LOWER('SQL') → 'sql' |
| INITCAP | 首字母大写 | INITCAP('hello world') → 'Hello World' |
2.2 字符串连接与截取
| 函数 | 功能 | 示例 |
|---|
| CONCAT | 连接两个字符串 | CONCAT('A', 'B') → 'AB' |
| || | 连接操作符 | 'A' || 'B' → 'AB' |
| SUBSTR | 截取子串 | SUBSTR('Oracle', 1, 3) → 'Ora' |
| LENGTH | 返回字符串长度 | LENGTH('Oracle') → 6 |
2.3 查找与替换
| 函数 | 功能 | 示例 |
|---|
| INSTR | 查找子串位置 | INSTR('Oracle SQL', 'SQL') → 8 |
| REPLACE | 替换子串 | REPLACE('SQL*Plus', '*', ' ') → 'SQL Plus' |
| TRANSLATE | 字符替换 | TRANSLATE('ABC', 'BC', 'XY') → 'AXY' |
2.4 填充与修剪
| 函数 | 功能 | 示例 |
|---|
| LPAD | 左填充 | LPAD('123', 5, '0') → '00123' |
| RPAD | 右填充 | RPAD('456', 5, '*') → '456**' |
| LTRIM | 左修剪 | LTRIM(' ABC') → 'ABC' |
| RTRIM | 右修剪 | RTRIM('ABC ') → 'ABC' |
| TRIM | 两端修剪 | TRIM(' ABC ') → 'ABC' |
2.5 其他字符串函数
-- ASCII/CHR：字符与ASCII码互转SELECTASCII('A')FROMdual;-- 65SELECTCHR(65)FROMdual;-- 'A'-- REGEXP_LIKE：正则表达式匹配SELECT*FROMemployeesWHEREREGEXP_LIKE(email,'^[a-z]+@[a-z]+\.com$');-- REGEXP_SUBSTR：正则提取SELECTREGEXP_SUBSTR('John,Smith,25','[^,]+',1,2)FROMdual;-- 'Smith'
三、数值函数
对数字进行计算和处理，支持数学运算和统计分析。
3.1 四舍五入与截断
| 函数 | 功能 | 示例 |
|---|
| ROUND | 四舍五入 | ROUND(123.456, 2) → 123.46 |
| TRUNC | 截断 | TRUNC(123.456, 2) → 123.45 |
| CEIL | 向上取整 | CEIL(123.1) → 124 |
| FLOOR | 向下取整 | FLOOR(123.9) → 123 |
3.2 数学运算
| 函数 | 功能 | 示例 |
|---|
| MOD | 取模 | MOD(10, 3) → 1 |
| POWER | 幂运算 | POWER(2, 3) → 8 |
| ABS | 绝对值 | ABS(-123) → 123 |
| SIGN | 符号函数 | SIGN(-123) → -1 |
3.3 三角函数与对数
-- 三角函数SELECTSIN(3.14159),COS(3.14159),TAN(3.14159)FROMdual;-- 对数SELECTLOG(10,100)FROMdual;-- 以10为底，100的对数 → 2-- 平方根SELECTSQRT(16)FROMdual;-- 4
3.4 统计分析函数
-- 标准差与方差SELECTSTDDEV(salary),VARIANCE(salary)FROMemployees;-- 中位数SELECTMEDIAN(salary)FROMemployees;-- 百分位数SELECTPERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYsalary)FROMemployees;
四、日期时间函数
Oracle 日期处理功能极其强大，支持复杂的时间计算。
4.1 获取当前时间
| 函数 | 功能 | 返回类型 |
|---|
| SYSDATE | 当前日期时间 | DATE |
| SYSTIMESTAMP | 当前时间戳 | TIMESTAMP |
| CURRENT_DATE | 当前会话日期 | DATE |
| CURRENT_TIMESTAMP | 当前会话时间戳 | TIMESTAMP |
SELECTSYSDATE,SYSTIMESTAMPFROMdual;
4.2 日期加减
| 函数 | 功能 | 示例 |
|---|
| +/- | 加减天数 | SYSDATE + 1（明天） |
| ADD_MONTHS | 加月份 | ADD_MONTHS(SYSDATE, 3) |
| MONTHS_BETWEEN | 月份差 | MONTHS_BETWEEN(SYSDATE, hire_date) |
-- 计算明天、一小时前SELECTSYSDATE+1AStomorrowFROMdual;SELECTSYSDATE-1/24ASone_hour_agoFROMdual;-- 加3个月SELECTADD_MONTHS(SYSDATE,3)FROMdual;-- 计算工龄（月数）SELECTMONTHS_BETWEEN(SYSDATE,hire_date)FROMemployees;
4.3 日期提取与格式化
| 函数 | 功能 | 示例 |
|---|
| EXTRACT | 提取年月日 | EXTRACT(YEAR FROM SYSDATE) |
| TO_CHAR | 格式化日期 | TO_CHAR(SYSDATE, 'YYYY-MM-DD') |
| NEXT_DAY | 下个星期几 | NEXT_DAY(SYSDATE, 'MONDAY') |
| LAST_DAY | 当月最后一天 | LAST_DAY(SYSDATE) |
-- 提取年月日SELECTEXTRACT(YEARFROMSYSDATE)ASyear,EXTRACT(MONTHFROMSYSDATE)ASmonth,EXTRACT(DAYFROMSYSDATE)ASdayFROMdual;-- 格式化日期SELECTTO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')FROMdual;-- 下周一SELECTNEXT_DAY(SYSDATE,'MONDAY')FROMdual;-- 本月最后一天SELECTLAST_DAY(SYSDATE)FROMdual;
4.4 日期截断与舍入
-- TRUNC 截断到指定单位SELECTTRUNC(SYSDATE,'MONTH')FROMdual;-- 当月第一天SELECTTRUNC(SYSDATE,'YEAR')FROMdual;-- 当年第一天-- ROUND 舍入到指定单位SELECTROUND(SYSDATE,'MONTH')FROMdual;
五、转换函数
实现不同数据类型间的转换，是数据清洗的关键工具。
5.1 字符串转换
| 函数 | 功能 | 示例 |
|---|
| TO_CHAR | 转字符串 | TO_CHAR(12345.67, 'L99,999.99') |
| TO_NUMBER | 转数字 | TO_NUMBER('123.45') |
| TO_DATE | 转日期 | TO_DATE('2023-12-25', 'YYYY-MM-DD') |
| CAST | 通用转换 | CAST('123' AS NUMBER) |
-- 数字格式化（货币）SELECTTO_CHAR(salary,'L99,999.99')FROMemployees;-- ￥12,345.67-- 日期格式化SELECTTO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')FROMdual;-- 字符串转数字SELECTTO_NUMBER('123.45')+100FROMdual;-- 223.45-- 字符串转日期SELECTTO_DATE('2023-12-25 10:30:00','YYYY-MM-DD HH24:MI:SS')FROMdual;
5.2 空值处理
| 函数 | 功能 | 示例 |
|---|
| NVL | 空值替换 | NVL(commission_pct, 0) |
| NVL2 | 空值判断 | NVL2(expr1, expr2, expr3) |
| COALESCE | 返回首个非NULL | COALESCE(expr1, expr2, ...) |
| NULLIF | 相等返回NULL | NULLIF(expr1, expr2) |
-- NVL 处理空值SELECTfirst_name,NVL(commission_pct,0)FROMemployees;-- COALESCE 多值判断SELECTCOALESCE(phone_mobile,phone_home,phone_work,'N/A')FROMcontacts;-- NULLIF 避免除零SELECTNULLIF(salary,0)FROMemployees;
六、条件判断函数
6.1 DECODE 函数
Oracle 特有的条件判断函数，类似 switch-case。
-- 语法：DECODE(expr, search1, result1, search2, result2, ..., default)SELECTemployee_id,DECODE(department_id,10,'Admin',20,'Marketing',30,'Purchasing','Other')ASdept_nameFROMemployees;
6.2 CASE 表达式
标准 SQL 条件判断，更灵活。
-- 简单 CASESELECTemployee_id,CASEdepartment_idWHEN10THEN'Admin'WHEN20THEN'Marketing'ELSE'Other'ENDASdept_nameFROMemployees;-- 搜索 CASE（支持复杂条件）SELECTemployee_id,salary,CASEWHENsalary>10000THEN'High'WHENsalary>5000THEN'Medium'ELSE'Low'ENDASsalary_levelFROMemployees;
6.3 NULL 判断函数
-- NULLIFSELECTNULLIF(first_name,last_name)FROMemployees;-- 相同返回NULL-- LNNVL（否定条件）SELECT*FROMemployeesWHERELNNVL(salary>5000);-- 等价于 salary <= 5000 或 salary IS NULL
七、分析函数（窗口函数）
分析函数是 Oracle 高级特性，用于在结果集上执行计算，不改变行数。
7.1 排序函数
| 函数 | 功能 | 区别 |
|---|
| RANK | 排名，相同值并列，有间隔 | 1,1,3 |
| DENSE_RANK | 密集排名，相同值并列，无间隔 | 1,1,2 |
| ROW_NUMBER | 唯一序号，相同值也区分 | 1,2,3 |
-- 员工薪水排名SELECTemployee_id,salary,RANK()OVER(ORDERBYsalaryDESC)ASrank,DENSE_RANK()OVER(ORDERBYsalaryDESC)ASdense_rank,ROW_NUMBER()OVER(ORDERBYsalaryDESC)ASrow_numFROMemployees;
7.2 聚合分析函数
-- 部门内薪水占比SELECTemployee_id,department_id,salary,SUM(salary)OVER(PARTITIONBYdepartment_id)ASdept_total,salary/SUM(salary)OVER(PARTITIONBYdepartment_id)ASratioFROMemployees;-- 累计求和SELECTemployee_id,hire_date,salary,SUM(salary)OVER(ORDERBYhire_date)ASrunning_totalFROMemployees;
7.3 取值函数
-- LAG/LEAD：前后行取值SELECTemployee_id,hire_date,salary,LAG(salary,1)OVER(ORDERBYhire_date)ASprev_salary,LEAD(salary,1)OVER(ORDERBYhire_date)ASnext_salaryFROMemployees;-- FIRST_VALUE/LAST_VALUE：窗口首尾值SELECTemployee_id,department_id,salary,FIRST_VALUE(salary)OVER(PARTITIONBYdepartment_idORDERBYsalaryDESC)AShighest_salaryFROMemployees;
7.4 窗口定义
-- ROWS 窗口（物理行）SELECTemployee_id,salary,AVG(salary)OVER(ORDERBYemployee_idROWSBETWEEN2PRECEDINGAND2FOLLOWING)ASmoving_avgFROMemployees;-- RANGE 窗口（逻辑范围）SELECTemployee_id,salary,SUM(salary)OVER(ORDERBYsalary RANGEBETWEEN1000PRECEDINGAND1000FOLLOWING)ASrange_sumFROMemployees;
八、高级函数
8.1 随机数生成
-- DBMS_RANDOM 包SELECTDBMS_RANDOM.VALUE(0,100)FROMdual;-- 0-100随机数SELECTDBMS_RANDOM.STRING('U',10)FROMdual;-- 10位大写随机字符串
8.2 层次查询函数
-- CONNECT_BY_ROOT：根节点值SELECTemployee_id,last_name,CONNECT_BY_ROOT last_nameASroot_nameFROMemployeesSTARTWITHmanager_idISNULLCONNECTBYPRIOR employee_id=manager_id;-- SYS_CONNECT_BY_PATH：路径字符串SELECTemployee_id,SYS_CONNECT_BY_PATH(last_name,'/')ASpathFROMemployeesSTARTWITHmanager_idISNULLCONNECTBYPRIOR employee_id=manager_id;
8.3 其他实用函数
-- UID：当前用户IDSELECTUIDFROMdual;-- USER：当前用户名SELECTUSERFROMdual;-- GREATEST/LEAST：多值比较SELECTGREATEST(10,20,30),LEAST(10,20,30)FROMdual;-- 30, 10-- BIN_TO_NUM：二进制转数字SELECTBIN_TO_NUM(1,0,1)FROMdual;-- 5
九、实战场景与性能提示
9.1 场景示例
-- 场景1：员工绩效评级SELECTemployee_id,salary,CASEWHENsalary>PERCENTILE_CONT(0.9)WITHINGROUP(ORDERBYsalary)OVER()THEN'S'WHENsalary>PERCENTILE_CONT(0.7)WITHINGROUP(ORDERBYsalary)OVER()THEN'A'WHENsalary>PERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYsalary)OVER()THEN'B'ELSE'C'ENDASperformance_gradeFROMemployees;-- 场景2：新员工留存率SELECTEXTRACT(YEARFROMhire_date)AShire_year,COUNT(*)AShired,COUNT(CASEWHENtermination_dateISNULLTHEN1END)ASretained,ROUND(COUNT(CASEWHENtermination_dateISNULLTHEN1END)/COUNT(*)*100,2)ASretention_rateFROMemployeesGROUPBYEXTRACT(YEARFROMhire_date);
9.2 性能优化建议
- 聚合函数：在 WHERE 子句中过滤数据后再聚合，减少计算量
- 分析函数：避免在大数据集上滥用
ORDER BY导致排序溢出，可配合PARTITION BY缩小窗口 - 字符串函数：在 WHERE 子句中对列使用函数会导致索引失效，可考虑函数索引
- 日期函数：优先使用
EXTRACT而非TO_CHAR进行日期判断，性能更好 - 空值处理：
NVL和COALESCE在大量数据上有性能差异，COALESCE 更优
十、总结
函数分类速查表
| 类别 | 核心函数 | 使用频率 | 难度 |
|---|
| 聚合函数 | COUNT, SUM, AVG, MAX, MIN | ⭐⭐⭐⭐⭐ | 低 |
| 字符串函数 | SUBSTR, LENGTH, INSTR, TO_CHAR | ⭐⭐⭐⭐⭐ | 中 |
| 数值函数 | ROUND, TRUNC, MOD, ABS | ⭐⭐⭐⭐ | 中 |
| 日期函数 | SYSDATE, ADD_MONTHS, MONTHS_BETWEEN | ⭐⭐⭐⭐⭐ | 中 |
| 转换函数 | TO_CHAR, TO_DATE, TO_NUMBER, NVL | ⭐⭐⭐⭐⭐ | 中 |
| 条件函数 | CASE, DECODE, COALESCE | ⭐⭐⭐⭐ | 中 |
| 分析函数 | RANK, DENSE_RANK, ROW_NUMBER, SUM(…) OVER | ⭐⭐⭐ | 高 |
| 高级函数 | DBMS_RANDOM, CONNECT_BY_ROOT, LISTAGG | ⭐⭐ | 高 |
学习建议：
- 新手：掌握聚合、字符串、日期、转换函数，覆盖 80% 日常需求
- 进阶：熟练使用 CASE 和窗口函数，解决复杂业务场景
- 专家：深入理解分析函数执行原理，优化大数据量查询性能
Oracle 函数体系庞大且功能强大，建议结合实际业务场景反复练习，方能融会贯通。
