排列分析函数与ORACLE取整及四舍五入.docxVIP

排列分析函数与ORACLE取整及四舍五入所述重点排列分析函数某年级期末考试成绩如下所示：成绩表 student_score班级 class姓名 name成绩 score1小白951小邓1001小刘951小吴901小张942小丁982小马912小王95计算排名：排名规则一：按照不同班级分组排名，名次不允许重复，若成绩相同，则按照姓名进行排序排名规则二：按照不同班级分组排名，若出现相同名次，后续的名字跳号进行排序排名规则三：按照不同班级分组排名，若出现相同名次，后续名次连续排序班级 class姓名 name成绩 score排名1排名2排名31小邓1001111小白952221小刘953221小张944431小吴905542小丁1001112小王982222小马91333SELECT class,name,score, ROW_NUMBER() OVER(PARTITION BY class ORDER BY score,name) RANK() OVER(PARTITION BY class ORDER BY score) DENSE_RANK() OVER(PARITION BY class ORDER BY score) FROM student_score知识点：ROW_NUMBER()函数除用于排序之外，也可以用于批次生成项次批次生成单号时，应该如何解决，现提供一解法，通过建立序列SEQUENCE语法：CREATE SEQUENCE seq_name INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 1000SELECT seq_name.NEXTVAL FROM pmm_fileFIRST_VALUE()与LAST_VALUE()针对上述的student_score表，需要在表最后增加2列，分别计算每班的最高分与最低分，如何实现？SELECT class,name,score, FIRST_VALUE(score) OVER(PARTITION BY class ORDER BY score DESC) high_score,FIRST_VALUE(score) OVER(PARTITION BY class ORDER BY score)low_score,LAST_VALUE(score) OVER(PARTITION BY class ORDER BY score)last_scoreFROM student_score班级 class姓名name成绩 score最高分high_score最低分low_score最后一笔last_score1小白9510090951小邓100100901001小刘9510090951小吴9010090901小张9410090942小丁989891982小马919891912小意点：分析函数计算的时候是在一个分析窗口中进行的，默认的窗口是当前组的第一行到当前行，因此，当使用LAST_VALUES()来计算时，会得到如上的结果，即当前行的数据，而不是分组内的最后一行的数据，如果窗口是整个组，需要指定窗口子句。取整与四舍五入取整在现有程序中经常会看到如下的程序段：DEFINE l_xx NUMBER(15,3)DEFINE l_xy NUMBER(10)LET l_xy = l_ta_pml040/l_ta_ima020IF l_xy l_xx THEN LET l_xy = l_xy+1END IF这段程序实质上是说明如果l_xy为小数，则返回大于该小数的最小整数，如果l_xy为整数，则直接取该整数，在ORACLE中有相同功能的函数 CEIL()，同时也有相反功能的函数FLOOR()，返回小于等于某数值的最大整数，例如：SELECT CEIL(12.2),FLOOR(12.2) FROM DUAL那么结果为13，12四舍五入经常会看到如下的程序段：DEFINE l_qry1 NUMBER(15,3)DEFINE l_qry2 NUMBER(15,3)LET l_qry2 = cl_digcut(l_qry1,0)该程序段的作用是对l_qry1进行四舍五入，小数取0，在ORACLE中有相同功能的函数ROUND()语法：ROUND(number,decimal_places)number：需四舍五入处理的数值decimal_places：四舍五入，小数取几位（预设为0）举例：SELECT ROUND(123.45,1) FROM DUAL;结果：123.5引申：TRUNC(number,decimal_places)，按照指定的精度截取