企业官网建设流程全解析

注册一个私人网站,网站背景色代码,做网络传销网站犯法吗,泉州网站seo公司Oracle 函数统计大全 本文系统梳理 Oracle 数据库全量函数体系#xff0c;涵盖从基础到高级的所有函数类别#xff0c;提供详细语法、示例和实战场景。 一、聚合函数#xff08;Aggregate Functions#xff09; 对多行数据进行汇总计算#xff0c;常与 GROUP BY 子句配合…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字符串聚合11gR2WM_CONCAT字符串聚合10g-12c已废弃-- ROLLUP 分层汇总SELECTdepartment_id,manager_id,SUM(salary)FROMemployeesGROUPBYROLLUP(department_id,manager_id);-- 结果先按 departmentmanager 汇总再按 department 汇总最后总计-- CUBE 多维汇总SELECTdepartment_id,job_id,AVG(salary)FROMemployeesGROUPBYCUBE(department_id,job_id);-- 结果所有维度组合deptjob, dept, job, 总计-- LISTAGG 字符串聚合SELECTdepartment_id,LISTAGG(first_name,,)WITHINGROUP(ORDERBYsalaryDESC)ASemployeesFROMemployeesGROUPBYdepartment_id;二、字符串函数处理和操作字符数据是数据处理中最常用的函数类别。2.1 大小写转换函数功能示例UPPER转大写UPPER(oracle) → ORACLELOWER转小写LOWER(SQL) → sqlINITCAP首字母大写INITCAP(hello world) → Hello World2.2 字符串连接与截取函数功能示例CONCAT连接两个字符串CONCAT(A, B) → AB||连接操作符A || B → ABSUBSTR截取子串SUBSTR(Oracle, 1, 3) → OraLENGTH返回字符串长度LENGTH(Oracle) → 62.3 查找与替换函数功能示例INSTR查找子串位置INSTR(Oracle SQL, SQL) → 8REPLACE替换子串REPLACE(SQL*Plus, *, ) → SQL PlusTRANSLATE字符替换TRANSLATE(ABC, BC, XY) → AXY2.4 填充与修剪函数功能示例LPAD左填充LPAD(123, 5, 0) → 00123RPAD右填充RPAD(456, 5, *) → 456**LTRIM左修剪LTRIM( ABC) → ABCRTRIM右修剪RTRIM(ABC ) → ABCTRIM两端修剪TRIM( ABC ) → ABC2.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.46TRUNC截断TRUNC(123.456, 2) → 123.45CEIL向上取整CEIL(123.1) → 124FLOOR向下取整FLOOR(123.9) → 1233.2 数学运算函数功能示例MOD取模MOD(10, 3) → 1POWER幂运算POWER(2, 3) → 8ABS绝对值ABS(-123) → 123SIGN符号函数SIGN(-123) → -13.3 三角函数与对数-- 三角函数SELECTSIN(3.14159),COS(3.14159),TAN(3.14159)FROMdual;-- 对数SELECTLOG(10,100)FROMdual;-- 以10为底100的对数 → 2-- 平方根SELECTSQRT(16)FROMdual;-- 43.4 统计分析函数-- 标准差与方差SELECTSTDDEV(salary),VARIANCE(salary)FROMemployees;-- 中位数SELECTMEDIAN(salary)FROMemployees;-- 百分位数SELECTPERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYsalary)FROMemployees;四、日期时间函数Oracle 日期处理功能极其强大支持复杂的时间计算。4.1 获取当前时间函数功能返回类型SYSDATE当前日期时间DATESYSTIMESTAMP当前时间戳TIMESTAMPCURRENT_DATE当前会话日期DATECURRENT_TIMESTAMP当前会话时间戳TIMESTAMPSELECTSYSDATE,SYSTIMESTAMPFROMdual;4.2 日期加减函数功能示例/-加减天数SYSDATE 1明天ADD_MONTHS加月份ADD_MONTHS(SYSDATE, 3)MONTHS_BETWEEN月份差MONTHS_BETWEEN(SYSDATE, hire_date)-- 计算明天、一小时前SELECTSYSDATE1AStomorrowFROMdual;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返回首个非NULLCOALESCE(expr1, expr2, ...)NULLIF相等返回NULLNULLIF(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_idWHEN10THENAdminWHEN20THENMarketingELSEOtherENDASdept_nameFROMemployees;-- 搜索 CASE支持复杂条件SELECTemployee_id,salary,CASEWHENsalary10000THENHighWHENsalary5000THENMediumELSELowENDASsalary_levelFROMemployees;6.3 NULL 判断函数-- NULLIFSELECTNULLIF(first_name,last_name)FROMemployees;-- 相同返回NULL-- LNNVL否定条件SELECT*FROMemployeesWHERELNNVL(salary5000);-- 等价于 salary 5000 或 salary IS NULL七、分析函数窗口函数分析函数是 Oracle 高级特性用于在结果集上执行计算不改变行数。7.1 排序函数函数功能区别RANK排名相同值并列有间隔1,1,3DENSE_RANK密集排名相同值并列无间隔1,1,2ROW_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_idmanager_id;-- SYS_CONNECT_BY_PATH路径字符串SELECTemployee_id,SYS_CONNECT_BY_PATH(last_name,/)ASpathFROMemployeesSTARTWITHmanager_idISNULLCONNECTBYPRIOR employee_idmanager_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,CASEWHENsalaryPERCENTILE_CONT(0.9)WITHINGROUP(ORDERBYsalary)OVER()THENSWHENsalaryPERCENTILE_CONT(0.7)WITHINGROUP(ORDERBYsalary)OVER()THENAWHENsalaryPERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYsalary)OVER()THENBELSECENDASperformance_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 函数体系庞大且功能强大建议结合实际业务场景反复练习方能融会贯通。