一、字符型函数(只是记录一些容易忘记的基本函数)
1、大小写转换函数 INITCAP：将字符串中每一个单词的第一个字母转换为大写，其他均为小写 SELECT INITCAP(column) FROM DUAL 2、字符串处理函数 INSTR：从一个字符串中查找一个给定字符的数字位置 SELECT INSTR(String,'K') FROM DUAL LPAD：用给定的字符从左填充字符串到给定的长度 RPAD：用给定的字符从又填充字符串到给定的长度 3、字符串替代函数 REPLACE(string,'被替换字符串','替换的字符串') SELECT REPLACE(DATA_2,'6','G') FROM DUAL 4、数字型函数 ROUND(x,y)：四舍五入，保留小数点 SELECT ROUND(3.1247,2) FROM DUAL --->得到3.12 TRUNC(x,y)：截断x的值到y位小数点 SELECT TRUNC(3.5647,2) FROM DUAL --->得到3.56 MOD(x,y)：返回x除以y的余数 SELECT MOD(5,3) FROM DUAL --->得到3 5、日期时间函数(SYSDATE) MONTHS_BETWEEN(date1,date2)：计算两个日期的月数 SELECT MONTHS_BETWEEN(TO_DATE('2016-09-10','YYYY-MM-DD'),TO_DATE('2016-03-09','YYYY-MM-DD')) FROM DUAL; ADD_MINTHS(date,n)：添加n天到date，必须是整数，可以是负数 SELECT MONTHS_BETWEEN(TO_DATE('2016-09-10','YYYY-MM-DD'),TO_DATE('2016-03-09','YYYY-MM-DD')) FROM DUAL; NEXT_DAY(date,char)：计算date之后的下一个周(char)的指定天的日期 SELECT NEXT_DAY(SYSDATE,'星期五') FROM DUAL; LAST_DAY(date)：计算包含date的月的最后一天的日期 SELECT LAST_DAY(SYSDATE) FROM DUAL; ROUND(date,'frm')：返回格式化模式frm四舍五入到指定单位date SELECT ROUND(SYSDATE,'YEAR') FROM DUAL; TRUNC(date,'frm')：返回格式化模式截断到指定单位的带天的时间部分date SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL; 6、类型转换函数 CHARTOROWID：将包含外部格式的ROWID的CHAR或者VARCHAR2数值转换为ROWID CONVERT：将一个字符集转换到另一个字符集 HEXTORAW：将十六进制字符串转换为RAW ROWIDTOCHAR：将ROWID转换为字符串表示形式 TO_BLOB：将指定的值转换成BLOB类型值 TO_CHAR(date,日期格式)：将日期型或数字型的值转换为VARCHAR2的类型值 SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MM:SS') FROM DUAL; TO_CLOB：将指定的值转换成CLOB类型的值 TO_DATE：将CHAR或VARCHAR2字符串强制转换为日期值 SELECT TO_DATE('20160902','YYYY-MM-DD') FROM DUAL; TO_LABEL：将CHAR或VARCHAR2字符串强转为MLSLABLE TO_MULTI_BYTE：将任何单字节字符串转换为多字节字符串 TO_NUMBER：将CHAR或VARCHAR2字符串强制转为NUMBER SELECT TO_NUMBER('$20160902','$999999999') FROM DUAL; TO_SINGLE_BYTE：将任何多字节字符串转为单字节字符串 7、分组函数 AVG：返回列平均数 COUNT：返回查询行总数 SUM：返回列总和 MIN：返回列最小值 MAX：返回列最大值 8、其他函数 NVL：转换NULL值为一个实际值 *NVL函数可用的数据类型可以是日期、字符、数字，但是数据类型必须是匹配 公式：NVL(exp1,exp2) SELECT LOT_ID,NVL(LOT_DESC,'123') LOT_DESC,MAT_ID FROM MWIPLOTSTS WHERE LOT_ID='2VKF09BQUOY0001';--如果LOT_DESC为空就返回'123'，不是空就返回LOT_DESC NVL2：如果表达式1不为NULL，则NVL2返回表达式2的值；如果表达式1为NULL，则NVL2返回表达式3.表达式1可以是任意数据类型 *NVL2同样返回的数据类型必须是匹配的 公式：NVL2(exp1,exp2,exp3) SELECT LOT_ID,NVL(LOT_DESC,'123','kkkk') LOT_DESC,MAT_ID FROM MWIPLOTSTS WHERE LOT_ID='2VKF09BQUOY0001';--如果LOT_DESC为空就返回'123'，不是空就返回'kkkk' NULLIF：比较两个表达式，如果相等则返回NULL，如果不等则返回第1个表达式 公式：NULLIF(exp1,exp2) COALESCE：返回表达式列表中的第1个非NULL表达式 *COALESCE函数可以从多个表达式中返回第一个非空表达式 公式：COALESCE(exp1,exp2,...expn) SELECT COALESCE(mgr,deptno,empno) 员工 FROM DUAL;--查询mgr字段，如果mgr为空，就返回deptno，如果deptno还是空就返回empno，以此类推 DECODE：根据特定条件，实现IF-THEN-ELSE条件判断返回值 *DECODE函数比较表达式和每个查找值表达式，如果表达式与查找值相匹配，则返回结果值；如果省略默认值，当没有查找值与表达式相匹配时返回一个NULL值 公式：DECODE(columnName,value1,result1,value2,result2,value3,result3,...valuen,resultn) SELECT LOT_ID,OPER,DECODE(OPER,'1020','AAAA','1060','BBBB','1080','CCCC','1900','DDDD') OPER_DESC FROM MWIPLOTSTS;--当OPER列的值是1020，则返回AAAA，是1060，则返回BBBB...
二、Oracle分析型函数
1、什么是分析型函数 分析型函数用于解决复杂报表统计功能，它可以在数据中进行分组，然后计算基于某组的统计值，并且每一组每一行都返回一个统计值 2、分析函数应用的场景 ①、运行总计：比如逐行地显示一个部门的累计汇总工资，每行包含前面各行工资之和 ②、查找一组内的百分数：比如显示在某部门中付给个人的总工资的百分比，将他们与该部门的工资总和扣除 ③、前N个查询：查找指定条件的前N条记录 ④、移动平均值计算：将当前行的值与前N行的值加载一起求平均值 ⑤、执行等级查询：比如显示一个部门内某个员工工资相关等级 3、基本语法： FUNCTION_NAME(<argument>,<argument>...) OVER (<Partition-Clause><Order-by-Clause><Windowing Clause>) ①、FUNCTION_NAME：用于指定分析函数名，Oracle提供26个分析函数，如SUM、AVG、COUNT、MIN、MAX、ROW_NUMBER等 ②、<argument>用于指定分析函数的参数，每个函数可以有0~3个参数，参数可以是列明或者表达式 ③、OVER：是一个关键字，用于标识当前函数为分析函数，区分SUM、AVG、COUNT、MIN、MAX、ROW_NUMBER等函数为分析函数，而不是分组函数 ④、<Partition-Clause>：分区主句，是可选的分区子句，如果不存在任何分区子句，则全部的结果集可以看作一个单一的大区 ⑤、<Order-by-Clause>：可选的排序子句，用来根据结果集进行排序 ⑥、<Windowing Clause>：用于定义分析函数将在其上操作的行的集合，该子句给出了一个定义变化或固定的数据窗口的方法，分析函数将对这些数据进行操作 4、分析函数结构 4.1、分析函数名 Oracle提供26个分析函数，按功能分为5大类 ①、等级(RandKing)函数：用于寻找前N种查询，比如ROW_NUMBER函数、RANK、DENSE_RANK等 ②、开窗(Windowing)函数：用于计算行的累计值，这些函数与分组函数同名，比如SUM、AVG、COUNT、MIN、MAX SUM(column) OVER (ORDER BY column) SUM(column) OVER (PARTITION BY column ORDER BY column) ③、制表(Reporting)函数：与开窗函数相似，制表函数也允许对一个结果集执行多种聚合运算，比如SUM、AVG、COUNT、MIN、MAX，与窗口函数不同的是， 制表函数不能指定一个本地窗口，因此总是在整个分区或整个组上产生相同的结果 SUM(column) OVER () SUM(column) OVER (PARTITION BY column) 制表函数只是在开窗函数的基础上少了ORDER BY子句 ④、LAG和LEAD函数：允许在结果集中向前或向后检索值，如果要避免数据的自连接，他们是非常有用的 ⑤、其他统计函数：例如VAR_POP、VAR_SAMP和STDEV_POP及线性的衰减函数等，这些函数计算任何为排序分区统计值 4.2、分区子句 分区子句使用PARTITION BY关键字，将一个简单的结果集分为N组，分区与组的概念比较相似，在语法上与SQL查询的GROUP BY子句相似 公式：PARTITION BY expression <, expression> <,expression> ROW_NUMBER() OVER(PARTITION BY LAST_BOX_LEVEL ORDER BY UPDATE_TIME DESC) 4.3、排序子句 排序子句使用ORDER BY关键字，ORDER BY子句用于指定分组中数据的排序方式，排序方式会明显地影响任何分析函数的结果 统计中使用不排序与排序有很大的差别 SELECT ename,sal,AVG(sal) OVER() FROM emp SELECT ename,sal,AVG(sal) OVER(ORDER BY ename) FROM emp 没有使用ORDER BY，默认的窗口是所有的分区，因为统计将在全部组上计算平均值。 使用ORDER BY，每一行的平均值都是该行与前面所有行的平均值 4.4、开窗子句 开窗子句必须定义在ORDER BY子句后面，用来定义一个变化或固定的数据窗口方法，分析函数将对这些数据进行操作 开窗子句应用的场合： ①、从当前记录开始直至某个部分的最后一条结束记录 ②、在统计时可以统计分组以外的记录 ③、在当前的前几行或后几行进行滚动计算 SELECT LAST_BOX_LEVEL ,SUM(BOX_QTY) BOX_TOTAL, SUM(SUM(BOX_QTY)) OVER (ORDER BY LAST_BOX_LEVEL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ALL_TOTAL FROM CPCKBOXSTS WHERE WO_ID='SA16271422' GROUP BY LAST_BOX_LEVEL;ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING是Oracle提供的开窗子句，是指将第一条到最后一条的记录，也就是表中所有的记录 4.5分析函数列表 DISTINCT是用来统计唯一值，尽在分区子句中使用，不能再ORDER BY和开窗子句使用 ①、COUNT：用来统计分区中各组的行数 语法：COUNT({* | [DISTINCT | ALL] expr}) OVER (analytic_clause) SELECT empno,ename,COUNT(*) OVER (PRATITION BY deptno ORDER BY empno) FROM DUAL 统计某个范围(50~100)：COUNT(*) OVER (ORDER BY sal RANGE BETWEEN 50 PRECEDING AND 100 FOLLOWING) ②、SUM：用来汇总分区汇总的记录 语法：SUM({* | [DISTINCT | ALL] expr}) OVER (analytic_clause) SELECT deptno,sal,SUM(sal) OVER (PRATITION BY deptno ORDER BY deptno) FROM DUAL ③、AVG：用来计算分区中记录的平均值 语法：AVG({* | [DISTINCT | ALL] expr}) OVER (analytic_clause) SELECT deptno,sal,AVG(sal) OVER (PRATITION BY deptno ORDER BY deptno) FROM DUAL ④、MIN和MAX：用来区分 语法：MAX(column) OVER (analytic_clause) 语法：MIN(column) OVER (analytic_clause) SELECT detpno,empno,hiredate,sal,MIN(sal) OVER (PARTITION BY detpno ORDER BY hiredate RANGE UNBOUNDED PRECEDING) 最低薪水, OVER (PARTITION BY detpno ORDER BY hiredate RANGE UNBOUNDED PRECEDING) 最高薪水 FROM emp ⑤、RANK、DENSE_RANK和ROW_NUMBER： RANK、DENSE_RANK：都是用来记录编号，根据ORDER BY子句表达式的值，计算它们与其他行的相对位置，每一行赋一个数字序号，形成1开始的序列，将相同的值得到同样的数字序列 不同之处： RANK：将相同的行分配同样的序号之后，后面的行跳跃，比如两行序数为1，则没有序数2，直接跳到序数3 DENSE_RANK：没有跳跃值,数字存在重复行 ROW_NUMBER：没有跳跃值，不存在重复值 语法：RANK() OVER ([partition_clause] order_by_clause) 语法：DENSE_RANK() OVER ([partition_clause] order_by_clause) 语法：ROW_NUMBER() OVER ([partition_clause] order_by_clause) SELECT deptno,ename,sal, RANK () OVER (ORDER BY deptno) RANK 结果, DENSE_RANK () OVER (ORDER BY deptno) DENSE_RANK 结果, ROW_NUMBER () OVER (ORDER BY deptno) ROW_NUMBER 结果 FROM emp ORDER BY deptno
⑥、FIRST和LAST：FIRST函数从DENSE_RANK返回集合中取出排在第一行，LAST相反，取出最后一行。 如果分组中只有一行，无论是FIRST还是LAST都只会返回仅一行记录 SELECT depyno, MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY comm), MAN(sal) KEEP (DENSE_RANK LASTORDER BY comm) FROM emp GROUP BY deptno ⑦、FIRST_VALUE和LAST_VALUE：它们分别用于返回OVER子句中查询出来的第一条和最后一条记录 语法：FIRST_VALUE (column) OVER (anaytic_clause) 语法：LAST_VALUE (column) OVER (anaytic_clause) SELECT deptno,empno,sal, FIRST_VALUE(sal) OVER (PARTITION BY detpno ORDER BY empno), FIRST_VALUE(sal) OVER (PARTITION BY detpno ORDER BY empno), FROM emp
三、分析函数使用示例