oracle over()函数,从oracle 8i开始支持,后面的版本支持的比较好. 通常在做统计分析时我们都想尽可能多滴选择出原始列和统计值列,但是这样group by后面就必须跟随更多的列,使用分析函数可以避免使用group by时选择出来的列名必须出现在group by列表中的痛苦. sql over的作用及用法 RANK ( ) OVER ( [query_partition_clause] order_by_clause ) DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 可实现按指定的字段分组排序,对于相同分组字段的结果集进行排序, 其中PARTITION BY 为分组字段,ORDER BY 指定排序字段 over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。 其参数:over(partition by columnname1 order by columnname2) 含义:按columname1指定的字段进行分组排序,或者说按字段columnname1的值进行分组排序。 例如:employees表中,有两个部门的记录:department_id =10和20 select department_id,rank() over(partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名,在部门20中进行薪水排名。如果是partition by org_id,则是在整个公司内进行排名。 --1、over() 注(9i下over括号内必须有内容,不允许为空,本文所有示例均在10g下运行的) --所有人的总工资 select a.empno, a.ename, sum(a.sal) over() total from emp a; EMPNO ENAME TOTAL 7369 SMITH 29025 7499 ALLEN 29025 7521 WARD 29025 7566 JONES 29025 7654 MARTIN 29025 7698 BLAKE 29025 7782 CLARK 29025 7788 SCOTT 29025 7839 KING 29025 7844 TURNER 29025 7876 ADAMS 29025 7900 JAMES 29025 7902 FORD 29025 7934 MILLER 29025 --2、over(partition by ...) 分组统计 --统计部门的平均工资 select a.empno, a.ename, b.dname, to_char(round(avg(a.sal) over(partition by b.dname), 2),'$999,999,999.99') dept_avg from emp a, dept b where a.deptno = b.deptno; EMPNO ENAME DNAME DEPT_AVG 7934 MILLER ACCOUNTING $2,916.67 7839 KING ACCOUNTING $2,916.67 7782 CLARK ACCOUNTING $2,916.67 7876 ADAMS RESEARCH $2,175.00 7902 FORD RESEARCH $2,175.00 7566 JONES RESEARCH $2,175.00 7369 SMITH RESEARCH $2,175.00 7788 SCOTT RESEARCH $2,175.00 7521 WARD SALES $1,566.67 7844 TURNER SALES $1,566.67 7499 ALLEN SALES $1,566.67 7900 JAMES SALES $1,566.67 7698 BLAKE SALES $1,566.67 7654 MARTIN SALES $1,566.67 --查询出管理员工人数最多的人的名字和他管理的人的名字 select b.ename, t.ename, t.mgr, t.cnt from (select a.empno, a.ename, a.mgr, count(1) over(partition by a.mgr) cnt from emp a) t, emp b where t.mgr = b.empno; ENAME ENAME MGR CNT JONES SCOTT 7566 2 JONES FORD 7566 2 BLAKE WARD 7698 5 BLAKE TURNER 7698 5 BLAKE ALLEN 7698 5 BLAKE JAMES 7698 5 BLAKE MARTIN 7698 5 CLARK MILLER 7782 1 SCOTT ADAMS 7788 1 KING BLAKE 7839 3 KING JONES 7839 3 KING CLARK 7839 3 FORD SMITH 7902 1 --3、over(order by ...) 排序统计 select a.empno, a.deptno, a.ename, a.sal, sum(a.sal) over(order by a.ename) sum from emp a; EMPNO DEPTNO ENAME SAL SUM 7876 20 ADAMS 1100.00 1100 7499 30 ALLEN 1600.00 2700 7698 30 BLAKE 2850.00 5550 7782 10 CLARK 2450.00 8000 7902 20 FORD 3000.00 11000 7900 30 JAMES 950.00 11950 7566 20 JONES 2975.00 14925 7839 10 KING 5000.00 19925 7654 30 MARTIN 1250.00 21175 7934 10 MILLER 1300.00 22475 7788 20 SCOTT 3000.00 25475 7369 20 SMITH 800.00 26275 7844 30 TURNER 1500.00 27775 7521 30 WARD 1250.00 29025 --4、over(partition by ... order by ...) 分组排序统计 --统计各部门薪水前三名的人员 select t.* from (select rank() over(partition by b.dname order by a.sal desc) rk, a.empno, a.ename, b.dname, a.sal from emp a, dept b where a.deptno = b.deptno) t where t.rk <= 3; RK EMPNO ENAME DNAME SAL 1 7839 KING ACCOUNTING 5000.00 2 7782 CLARK ACCOUNTING 2450.00 3 7934 MILLER ACCOUNTING 1300.00 1 7902 FORD RESEARCH 3000.00 1 7788 SCOTT RESEARCH 3000.00 3 7566 JONES RESEARCH 2975.00 1 7698 BLAKE SALES 2850.00 2 7499 ALLEN SALES 1600.00 3 7844 TURNER SALES 1500.00
相关推荐
ORACLE的OVER函数使用技巧ORACLE的OVER函数使用技巧ORACLE的OVER函数使用技巧
### over函数概述 在数据库查询语言SQL中,`over`函数是处理复杂数据分析的一个强大工具。它主要用于定义一个数据窗口,使得可以在该窗口内执行各种聚合操作,如排名、累计和等。`over`函数通常与`row_number()`、`...
### Oracle中OVER函数详解 #### 一、概述 在Oracle数据库中,OVER函数是一个非常强大的工具,主要用于实现复杂的窗口函数操作。它可以帮助我们对数据进行分组、排序、累计等多种处理,大大增强了SQL语言的数据分析...
### Oracle开发中的OVER函数详解 #### 一、Oracle分析函数简介 在Oracle数据库开发中,分析函数是一类非常强大的工具,主要用于实现复杂的查询需求,尤其是在处理大量数据时,能够提供高级的数据汇总、排序和筛选...
OVER函数通常与窗口函数(如RANK(), DENSE_RANK(), ROW_NUMBER(), SUM(), AVG()等)一起使用。其基本语法形式如下: ```sql <window_function> OVER ( [window_spec] ) ``` 其中`<window_function>`代表具体的窗口...
2. 自动添加字段编号:可以使用 ROW_NUMBER() OVER 函数来自动添加字段编号。 3. 数据分析:可以使用 ROW_NUMBER() OVER 函数来对数据进行分析和排名。 ROW_NUMBER() OVER 函数是一个非常有用的函数,可以帮助我们...
### 使用 OVER 函数实现递归汇总计算 #### Oracle 分析函数概述 自 Oracle 8.1.6 版本起,Oracle 数据库引入了分析函数(Analytic Functions),这是一种非常强大的工具,主要用于处理复杂的查询需求,尤其是在...
### Oracle分析函数OVER及开窗函数详解 #### 一、概述 在Oracle数据库中,分析函数(Analytic Functions)是一种非常强大的工具,用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算,并且每行返回一个...
本文将详细讲解如何使用 MINUS 函数和 OVER 函数,直接通过视图实现两个记录集的比较。 MINUS 函数 MINUS 函数是 Oracle 中的一个集合操作符,用于从一个查询结果中删除另一个查询结果中的记录。MINUS 函数可以...
标题:Informix SQL函数使用说明大全 描述:本文旨在提供Informix数据库中内置函数的全面指南,涵盖了统计、日期时间、数学、指数与对数、三角函数、统计分析、字符串处理以及二进制数据处理等多个方面的函数应用。...
总结来说,Oracle的RATIO_TO_REPORT()函数提供了计算数据占比的能力,而OVER()子句则允许我们灵活地指定计算范围。在实际业务场景中,这个功能可以帮助我们进行深入的数据分析,发现隐藏的模式和趋势,进而优化业务...
首先,我们来看一个简单的例子,如何使用row_number() over()来消除重复的记录。假设我们有一个名为`dict_depts_source`的表,其中包含可能重复的`outer_code`字段。第一步是通过`order by outer_code`查看重复的...
在聚合函数中,它们通常与GROUP BY结合使用;在分析函数中,它们可以对每个分组的排序结果获取第一或最后一个值。 5. FIRST_VALUE (col) OVER ( analytic_clause ) 此函数返回根据analytic_clause定义的范围内的第...
要统计每一天到目前为止的总销售额,可以使用`SUM`函数配合`OVER`子句: ```sql SELECT DATE, SALE, SUM(SALE) OVER (ORDER BY DATE) AS CUMULATIVE_SALE FROM sales; ``` 结果如下: | DATE | SALE | CUMULATIVE...
ORACLE中OVER函数的用法.doc
"SYBASE iq与ORACLE使用分析函数计算同期值及常用计算函数使用" 本资源摘要信息主要介绍了SYBASE iq和ORACLE中使用分析函数计算同期值和常用计算函数的使用方法。文章涵盖了同期值的计算、累计及同期累计值的计算、...
分析函数的语法为:FUNCTION_NAME(<参数>,…) OVER (表达式,…> 表达式 <ASC DESC> <NULLS FIRST NULLS LAST>> 子句>)。 1. 值域窗(RANGE WINDOW) 值域窗是一种基于值域的窗口函数,可以对数据进行聚合和分析。...
NX二次开发UF_DRPOS_create_stepover 函数介绍,Ufun提供了一系列丰富的 API 函数,可以帮助用户实现自动化、定制化和扩展 NX 软件的功能。无论您是从事机械设计、制造、模具设计、逆向工程、CAE 分析等领域的专业...
这个示例中,我们使用 row_number()over 函数来生成一个部门内的薪水累计列,并将其与部门总和和每个人的薪水占部门总额的比例进行比较。 3. 部门从大到小排列,部门里各员工的薪水从高到低排列: ``` select ...