網(wǎng)站 各種 備案,wordpress 關(guān)閉頭像,wordpress_主題教程,新東方英語培訓(xùn)機構(gòu)官網(wǎng)Oracle 函數(shù)統(tǒng)計大全 本文系統(tǒng)梳理 Oracle 數(shù)據(jù)庫全量函數(shù)體系#xff0c;涵蓋從基礎(chǔ)到高級的所有函數(shù)類別#xff0c;提供詳細語法、示例和實戰(zhàn)場景。 一、聚合函數(shù)#xff08;Aggregate Functions#xff09; 對多行數(shù)據(jù)進行匯總計算#xff0c;常與 GROUP BY 子句配合…Oracle 函數(shù)統(tǒng)計大全本文系統(tǒng)梳理 Oracle 數(shù)據(jù)庫全量函數(shù)體系涵蓋從基礎(chǔ)到高級的所有函數(shù)類別提供詳細語法、示例和實戰(zhàn)場景。一、聚合函數(shù)Aggregate Functions對多行數(shù)據(jù)進行匯總計算常與GROUP BY子句配合使用。1.1 基礎(chǔ)聚合函數(shù)函數(shù)功能示例COUNT統(tǒng)計行數(shù)或非 NULL 值個數(shù)SELECT COUNT(*) FROM employees;SUM計算數(shù)值總和SELECT SUM(salary) FROM employees;AVG計算平均值SELECT AVG(salary) FROM employees;MAX返回最大值SELECT MAX(salary) FROM employees;MIN返回最小值SELECT MIN(salary) FROM employees;性能差異COUNT(*)統(tǒng)計所有行需要掃描表性能一般COUNT(1)統(tǒng)計行數(shù)不需要掃描具體列性能較快COUNT(column)統(tǒng)計非 NULL 值個數(shù)掃描非 NULL 列COUNT(DISTINCT column)計算不同值數(shù)量使用排序性能較慢-- 部門薪資統(tǒng)計SELECTdepartment_id,COUNT(*)AS員工數(shù),SUM(salary)AS總薪資,AVG(salary)AS平均薪資,MAX(salary)AS最高薪資,MIN(salary)AS最低薪資FROMemployeesGROUPBYdepartment_id;1.2 高級聚合函數(shù)函數(shù)功能GROUPING SETS多維度分組匯總ROLLUP分層匯總生成小計和總計CUBE多維立方體匯總LISTAGG字符串聚合11gR2WM_CONCAT字符串聚合10g-12c已廢棄-- ROLLUP 分層匯總SELECTdepartment_id,manager_id,SUM(salary)FROMemployeesGROUPBYROLLUP(department_id,manager_id);-- 結(jié)果先按 departmentmanager 匯總再按 department 匯總最后總計-- CUBE 多維匯總SELECTdepartment_id,job_id,AVG(salary)FROMemployeesGROUPBYCUBE(department_id,job_id);-- 結(jié)果所有維度組合deptjob, dept, job, 總計-- LISTAGG 字符串聚合SELECTdepartment_id,LISTAGG(first_name,,)WITHINGROUP(ORDERBYsalaryDESC)ASemployeesFROMemployeesGROUPBYdepartment_id;二、字符串函數(shù)處理和操作字符數(shù)據(jù)是數(shù)據(jù)處理中最常用的函數(shù)類別。2.1 大小寫轉(zhuǎn)換函數(shù)功能示例UPPER轉(zhuǎn)大寫UPPER(oracle) → ORACLELOWER轉(zhuǎn)小寫LOWER(SQL) → sqlINITCAP首字母大寫INITCAP(hello world) → Hello World2.2 字符串連接與截取函數(shù)功能示例CONCAT連接兩個字符串CONCAT(A, B) → AB||連接操作符A || B → ABSUBSTR截取子串SUBSTR(Oracle, 1, 3) → OraLENGTH返回字符串長度LENGTH(Oracle) → 62.3 查找與替換函數(shù)功能示例INSTR查找子串位置INSTR(Oracle SQL, SQL) → 8REPLACE替換子串REPLACE(SQL*Plus, *, ) → SQL PlusTRANSLATE字符替換TRANSLATE(ABC, BC, XY) → AXY2.4 填充與修剪函數(shù)功能示例LPAD左填充LPAD(123, 5, 0) → 00123RPAD右填充RPAD(456, 5, *) → 456**LTRIM左修剪LTRIM( ABC) → ABCRTRIM右修剪RTRIM(ABC ) → ABCTRIM兩端修剪TRIM( ABC ) → ABC2.5 其他字符串函數(shù)-- ASCII/CHR字符與ASCII碼互轉(zhuǎn)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三、數(shù)值函數(shù)對數(shù)字進行計算和處理支持數(shù)學(xué)運算和統(tǒng)計分析。3.1 四舍五入與截斷函數(shù)功能示例ROUND四舍五入ROUND(123.456, 2) → 123.46TRUNC截斷TRUNC(123.456, 2) → 123.45CEIL向上取整CEIL(123.1) → 124FLOOR向下取整FLOOR(123.9) → 1233.2 數(shù)學(xué)運算函數(shù)功能示例MOD取模MOD(10, 3) → 1POWER冪運算POWER(2, 3) → 8ABS絕對值A(chǔ)BS(-123) → 123SIGN符號函數(shù)SIGN(-123) → -13.3 三角函數(shù)與對數(shù)-- 三角函數(shù)SELECTSIN(3.14159),COS(3.14159),TAN(3.14159)FROMdual;-- 對數(shù)SELECTLOG(10,100)FROMdual;-- 以10為底100的對數(shù) → 2-- 平方根SELECTSQRT(16)FROMdual;-- 43.4 統(tǒng)計分析函數(shù)-- 標準差與方差SELECTSTDDEV(salary),VARIANCE(salary)FROMemployees;-- 中位數(shù)SELECTMEDIAN(salary)FROMemployees;-- 百分位數(shù)SELECTPERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYsalary)FROMemployees;四、日期時間函數(shù)Oracle 日期處理功能極其強大支持復(fù)雜的時間計算。4.1 獲取當前時間函數(shù)功能返回類型SYSDATE當前日期時間DATESYSTIMESTAMP當前時間戳TIMESTAMPCURRENT_DATE當前會話日期DATECURRENT_TIMESTAMP當前會話時間戳TIMESTAMPSELECTSYSDATE,SYSTIMESTAMPFROMdual;4.2 日期加減函數(shù)功能示例/-加減天數(shù)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;-- 計算工齡月數(shù)SELECTMONTHS_BETWEEN(SYSDATE,hire_date)FROMemployees;4.3 日期提取與格式化函數(shù)功能示例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;五、轉(zhuǎn)換函數(shù)實現(xiàn)不同數(shù)據(jù)類型間的轉(zhuǎn)換是數(shù)據(jù)清洗的關(guān)鍵工具。5.1 字符串轉(zhuǎn)換函數(shù)功能示例TO_CHAR轉(zhuǎn)字符串TO_CHAR(12345.67, L99,999.99)TO_NUMBER轉(zhuǎn)數(shù)字TO_NUMBER(123.45)TO_DATE轉(zhuǎn)日期TO_DATE(2023-12-25, YYYY-MM-DD)CAST通用轉(zhuǎn)換CAST(123 AS NUMBER)-- 數(shù)字格式化貨幣SELECTTO_CHAR(salary,L99,999.99)FROMemployees;-- 12,345.67-- 日期格式化SELECTTO_CHAR(SYSDATE,YYYY-MM-DD HH24:MI:SS)FROMdual;-- 字符串轉(zhuǎn)數(shù)字SELECTTO_NUMBER(123.45)100FROMdual;-- 223.45-- 字符串轉(zhuǎn)日期SELECTTO_DATE(2023-12-25 10:30:00,YYYY-MM-DD HH24:MI:SS)FROMdual;5.2 空值處理函數(shù)功能示例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;六、條件判斷函數(shù)6.1 DECODE 函數(shù)Oracle 特有的條件判斷函數(shù)類似 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支持復(fù)雜條件SELECTemployee_id,salary,CASEWHENsalary10000THENHighWHENsalary5000THENMediumELSELowENDASsalary_levelFROMemployees;6.3 NULL 判斷函數(shù)-- NULLIFSELECTNULLIF(first_name,last_name)FROMemployees;-- 相同返回NULL-- LNNVL否定條件SELECT*FROMemployeesWHERELNNVL(salary5000);-- 等價于 salary 5000 或 salary IS NULL七、分析函數(shù)窗口函數(shù)分析函數(shù)是 Oracle 高級特性用于在結(jié)果集上執(zhí)行計算不改變行數(shù)。7.1 排序函數(shù)函數(shù)功能區(qū)別RANK排名相同值并列有間隔1,1,3DENSE_RANK密集排名相同值并列無間隔1,1,2ROW_NUMBER唯一序號相同值也區(qū)分1,2,3-- 員工薪水排名SELECTemployee_id,salary,RANK()OVER(ORDERBYsalaryDESC)ASrank,DENSE_RANK()OVER(ORDERBYsalaryDESC)ASdense_rank,ROW_NUMBER()OVER(ORDERBYsalaryDESC)ASrow_numFROMemployees;7.2 聚合分析函數(shù)-- 部門內(nèi)薪水占比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 取值函數(shù)-- 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;八、高級函數(shù)8.1 隨機數(shù)生成-- DBMS_RANDOM 包SELECTDBMS_RANDOM.VALUE(0,100)FROMdual;-- 0-100隨機數(shù)SELECTDBMS_RANDOM.STRING(U,10)FROMdual;-- 10位大寫隨機字符串8.2 層次查詢函數(shù)-- CONNECT_BY_ROOT根節(jié)點值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 其他實用函數(shù)-- UID當前用戶IDSELECTUIDFROMdual;-- USER當前用戶名SELECTUSERFROMdual;-- GREATEST/LEAST多值比較SELECTGREATEST(10,20,30),LEAST(10,20,30)FROMdual;-- 30, 10-- BIN_TO_NUM二進制轉(zhuǎn)數(shù)字SELECTBIN_TO_NUM(1,0,1)FROMdual;-- 5九、實戰(zhàn)場景與性能提示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 性能優(yōu)化建議聚合函數(shù)在 WHERE 子句中過濾數(shù)據(jù)后再聚合減少計算量分析函數(shù)避免在大數(shù)據(jù)集上濫用ORDER BY導(dǎo)致排序溢出可配合PARTITION BY縮小窗口字符串函數(shù)在 WHERE 子句中對列使用函數(shù)會導(dǎo)致索引失效可考慮函數(shù)索引日期函數(shù)優(yōu)先使用EXTRACT而非TO_CHAR進行日期判斷性能更好空值處理NVL和COALESCE在大量數(shù)據(jù)上有性能差異COALESCE 更優(yōu)十、總結(jié)函數(shù)分類速查表類別核心函數(shù)使用頻率難度聚合函數(shù)COUNT, SUM, AVG, MAX, MIN?????低字符串函數(shù)SUBSTR, LENGTH, INSTR, TO_CHAR?????中數(shù)值函數(shù)ROUND, TRUNC, MOD, ABS????中日期函數(shù)SYSDATE, ADD_MONTHS, MONTHS_BETWEEN?????中轉(zhuǎn)換函數(shù)TO_CHAR, TO_DATE, TO_NUMBER, NVL?????中條件函數(shù)CASE, DECODE, COALESCE????中分析函數(shù)RANK, DENSE_RANK, ROW_NUMBER, SUM(…) OVER???高高級函數(shù)DBMS_RANDOM, CONNECT_BY_ROOT, LISTAGG??高學(xué)習(xí)建議新手掌握聚合、字符串、日期、轉(zhuǎn)換函數(shù)覆蓋 80% 日常需求進階熟練使用 CASE 和窗口函數(shù)解決復(fù)雜業(yè)務(wù)場景專家深入理解分析函數(shù)執(zhí)行原理優(yōu)化大數(shù)據(jù)量查詢性能Oracle 函數(shù)體系龐大且功能強大建議結(jié)合實際業(yè)務(wù)場景反復(fù)練習(xí)方能融會貫通。