oracle plsql 开窗函数over学习总结.docx

连续求和与求总和的区别D 为天 ，S 为销售业绩 为每天计算销售总额。SELECT SUM(s) OVER (ORDER BY d), SUM(s) OVER () FROM (SELECT AA,1 D, 20 S FROM DUAL UNION ALL SELECT AA,2 D, 15 S FROM DUAL UNION ALL SELECT AA,3 D, 14 S FROM DUAL UNION ALL SELECT AA,4 D, 18 S FROM DUAL UNION ALL SELECT AA,5 D, 30 S FROM DUAL)；各种求和举例CREATE TABLE TEST_ZHUXP(DEPTNO VARCHAR2(10), ENAME VARCHAR2(10), SAL VARCHAR2(10));--部门 姓名 薪水SELECT test_zhuxp.*, sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水连续求和 sum(sal) over (partition by deptno) 部门总和,-- 部门统计的总和，同一部门总和不变 100*round(sal/sum(sal) over (partition by deptno),4) 部门份额(%), sum(sal) over (order by deptno DESC,ename) 连续求和, --所有部门的薪水连续求和 sum(sal) over () 总和 -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和 100*round(sal/sum(sal) over (),4) 总份额(%) FROM test_ZHUXP注意求和后可以排序 不影响结果SELECT DEPTNO, ENAME, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO DESC, SAL DESC) 部门连续求和, SUM(SAL) OVER(ORDER BY DEPTNO DESC, SAL DESC) 公司连续求和 FROM TEST_ZHUXP排序1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果??2.rank()和dense_rank()的区别是：?rank()是跳跃排序，有两个第二名时接下来就是第四名?dense_rank()l是连续排序，有两个第二名时仍然跟着第三名SELECT t.*, RANK()OVER(PARTITION BY CLASS ORDER BY S DESC), dense_rank()OVER(PARTITION BY CLASS ORDER BY S DESC), ROW_NUMBER()OVER(PARTITION BY CLASS ORDER BY S DESC) FROM (SELECT a NAME,1 CLASS,80 SFROM DUAL UNION ALL SELECT b NAME,1 CLASS,89 S FROM DUAL UNION ALL SELECT c NAME,1 CLASS,89 S FROM DUAL UNION ALL SELECT e NAME,3 CLASS,100 S FROM DUAL UNION ALL SELECT f NAME,3 CLASS,100 S FROM DUAL UNION ALL SELECT g NAME,3 CLASS,79 S FROM DUAL) t统计和group by的区别是可以看到每一行数据的所有信息注意加NAME后的区别SELECT t.*, SUM(1)OVER(PARTITION BY CLASS ORDER BY CLASS/*NAME*/) FROM (SELECT a NAME,1 CLASS,80 SFROM DUAL UNION ALL SELECT b NAME,1 CLASS,89 S FROM DUAL UNION ALL SELECT c NAME,1 CLASS,89 S FROM DUAL UNION ALL SELECT e NAME,1 CLASS,100 S FROM DUAL UNION ALL SELECT f NAME,3 CLASS,100 S FROM DUAL UNION ALL SELECT g NAME,3 CLASS,79 S FROM DUAL) t开窗