`
evoleht
  • 浏览: 98505 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

[转]over()函数的使用

阅读更多
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函数使用技巧ORACLE的OVER函数使用技巧

    over函数的使用

    ### over函数概述 在数据库查询语言SQL中,`over`函数是处理复杂数据分析的一个强大工具。它主要用于定义一个数据窗口,使得可以在该窗口内执行各种聚合操作,如排名、累计和等。`over`函数通常与`row_number()`、`...

    Orcal中over函数.doc

    ### Oracle中OVER函数详解 #### 一、概述 在Oracle数据库中,OVER函数是一个非常强大的工具,主要用于实现复杂的窗口函数操作。它可以帮助我们对数据进行分组、排序、累计等多种处理,大大增强了SQL语言的数据分析...

    Oracle开发的over函数

    ### Oracle开发中的OVER函数详解 #### 一、Oracle分析函数简介 在Oracle数据库开发中,分析函数是一类非常强大的工具,主要用于实现复杂的查询需求,尤其是在处理大量数据时,能够提供高级的数据汇总、排序和筛选...

    ORACLE_OVER函数

    OVER函数通常与窗口函数(如RANK(), DENSE_RANK(), ROW_NUMBER(), SUM(), AVG()等)一起使用。其基本语法形式如下: ```sql &lt;window_function&gt; OVER ( [window_spec] ) ``` 其中`&lt;window_function&gt;`代表具体的窗口...

    ROW_NUMBER() OVER函数的基本用法

    2. 自动添加字段编号:可以使用 ROW_NUMBER() OVER 函数来自动添加字段编号。 3. 数据分析:可以使用 ROW_NUMBER() OVER 函数来对数据进行分析和排名。 ROW_NUMBER() OVER 函数是一个非常有用的函数,可以帮助我们...

    使用over函数实现递归汇总计算

    ### 使用 OVER 函数实现递归汇总计算 #### Oracle 分析函数概述 自 Oracle 8.1.6 版本起,Oracle 数据库引入了分析函数(Analytic Functions),这是一种非常强大的工具,主要用于处理复杂的查询需求,尤其是在...

    oracle分析函数over_及开窗函数.txt

    ### Oracle分析函数OVER及开窗函数详解 #### 一、概述 在Oracle数据库中,分析函数(Analytic Functions)是一种非常强大的工具,用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算,并且每行返回一个...

    MINUS函数和OVER函数[参考].pdf

    本文将详细讲解如何使用 MINUS 函数和 OVER 函数,直接通过视图实现两个记录集的比较。 MINUS 函数 MINUS 函数是 Oracle 中的一个集合操作符,用于从一个查询结果中删除另一个查询结果中的记录。MINUS 函数可以...

    informix sql函数使用说明大全

    标题:Informix SQL函数使用说明大全 描述:本文旨在提供Informix数据库中内置函数的全面指南,涵盖了统计、日期时间、数学、指数与对数、三角函数、统计分析、字符串处理以及二进制数据处理等多个方面的函数应用。...

    Oracle百分比分析函数RATIO_TO_REPORT() OVER()实例详解

    总结来说,Oracle的RATIO_TO_REPORT()函数提供了计算数据占比的能力,而OVER()子句则允许我们灵活地指定计算范围。在实际业务场景中,这个功能可以帮助我们进行深入的数据分析,发现隐藏的模式和趋势,进而优化业务...

    oracle分析函数row_number() over()使用

    首先,我们来看一个简单的例子,如何使用row_number() over()来消除重复的记录。假设我们有一个名为`dict_depts_source`的表,其中包含可能重复的`outer_code`字段。第一步是通过`order by outer_code`查看重复的...

    oracle函数介绍(6) 著名函数之分析函数.doc

    在聚合函数中,它们通常与GROUP BY结合使用;在分析函数中,它们可以对每个分组的排序结果获取第一或最后一个值。 5. FIRST_VALUE (col) OVER ( analytic_clause ) 此函数返回根据analytic_clause定义的范围内的第...

    oracle的分析函数over 及开窗函数

    要统计每一天到目前为止的总销售额,可以使用`SUM`函数配合`OVER`子句: ```sql SELECT DATE, SALE, SUM(SALE) OVER (ORDER BY DATE) AS CUMULATIVE_SALE FROM sales; ``` 结果如下: | DATE | SALE | CUMULATIVE...

    ORACLE中OVER函数的用法.doc

    ORACLE中OVER函数的用法.doc

    SYBASE iq与ORACLE使用分析函数计算同期值及常用计算函数使用

    "SYBASE iq与ORACLE使用分析函数计算同期值及常用计算函数使用" 本资源摘要信息主要介绍了SYBASE iq和ORACLE中使用分析函数计算同期值和常用计算函数的使用方法。文章涵盖了同期值的计算、累计及同期累计值的计算、...

    oracle函数大全分析函数,聚合函数,转换函数,日期型函数,字符型函数,数值型函数,其他函数.docx

    分析函数的语法为:FUNCTION_NAME(&lt;参数&gt;,…) OVER (表达式,…&gt; 表达式 &lt;ASC DESC&gt; &lt;NULLS FIRST NULLS LAST&gt;&gt; 子句&gt;)。 1. 值域窗(RANGE WINDOW) 值域窗是一种基于值域的窗口函数,可以对数据进行聚合和分析。...

    NX二次开发UF-DRPOS-create-stepover 函数介绍

    NX二次开发UF_DRPOS_create_stepover 函数介绍,Ufun提供了一系列丰富的 API 函数,可以帮助用户实现自动化、定制化和扩展 NX 软件的功能。无论您是从事机械设计、制造、模具设计、逆向工程、CAE 分析等领域的专业...

    Oracle row_number()over

    这个示例中,我们使用 row_number()over 函数来生成一个部门内的薪水累计列,并将其与部门总和和每个人的薪水占部门总额的比例进行比较。 3. 部门从大到小排列,部门里各员工的薪水从高到低排列: ``` select ...

Global site tag (gtag.js) - Google Analytics