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，则是在整个公司内进行排名。
WITH
OBJ AS(
SELECT
name, type
目的：以oracle自带的scott模式为测试环境，主要通过试验体会分析函数的用法。
类似 sum(...) over ... 的使用
1.原表信息：
SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
2 from emp
3 order by deptno;
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
KING 5000
MILLER 1300
20 SMITH 800
ADAMS 1100
FORD 3000
SCOTT 3000
JONES 2975
30 ALLEN 1600
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
已选择14行。
2.先来一个简单的，注意over(...)条件的不同，
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意over (order by ename)如果没有order by 子句，求和就不是“连续”的，
放在一起，体会一下不同之处：
SQL> break on '' -- 取消数据分段显示
SQL> select deptno,ename,sal,
2 sum(sal) over (order by ename) 连续求和,
3 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)
4 100*round(sal/sum(sal) over (),4) "份额(%)"
5 from emp
6 /
DEPTNO ENAME SAL 连续求和 总和 份额(%)
---------- ---------- ---------- ---------- ---------- ----------
20 ADAMS 1100 1100 29025 3.79
30 ALLEN 1600 2700 29025 5.51
30 BLAKE 2850 5550 29025 9.82
10 CLARK 2450 8000 29025 8.44
20 FORD 3000 11000 29025 10.34
30 JAMES 950 11950 29025 3.27
20 JONES 2975 14925 29025 10.25
10 KING 5000 19925 29025 17.23
30 MARTIN 1250 21175 29025 4.31
10 MILLER 1300 22475 29025 4.48
20 SCOTT 3000 25475 29025 10.34
20 SMITH 800 26275 29025 2.76
30 TURNER 1500 27775 29025 5.17
30 WARD 1250 29025 29025 4.31
已选择14行。
3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同，
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。
SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
3 sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
4 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
5 sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
6 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
7 100*round(sal/sum(sal) over (),4) "总份额(%)"
8 from emp
9 /
DEPTNO ENAME SAL 部门连续求和 部门总和 部门份额(%) 连续求和 总和 总份额(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
10 CLARK 2450 2450 8750 28 2450 29025 8.44
KING 5000 7450 8750 57.14 7450 29025 17.23
MILLER 1300 8750 8750 14.86 8750 29025 4.48
20 ADAMS 1100 1100 10875 10.11 9850 29025 3.79
FORD 3000 4100 10875 27.59 12850 29025 10.34
JONES 2975 7075 10875 27.36 15825 29025 10.25
SCOTT 3000 10075 10875 27.59 18825 29025 10.34
SMITH 800 10875 10875 7.36 19625 29025 2.76
30 ALLEN 1600 1600 9400 17.02 21225 29025 5.51
BLAKE 2850 4450 9400 30.32 24075 29025 9.82
JAMES 950 5400 9400 10.11 25025 29025 3.27
MARTIN 1250 6650 9400 13.3 26275 29025 4.31
TURNER 1500 8150 9400 15.96 27775 29025 5.17
WARD 1250 9400 9400 13.3 29025 29025 4.31
已选择14行。
4.来一个综合的例子，求和规则有按部门分区的，有不分区的例子
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp;
DEPTNO ENAME SAL DEPT_SUM SUM
---------- ---------- ---------- ---------- ----------
10 MILLER 1300 1300 1300
CLARK 2450 3750 3750
KING 5000 8750 8750
20 SMITH 800 800 9550
ADAMS 1100 1900 10650
JONES 2975 4875 13625
SCOTT 3000 10875 19625
FORD 3000 10875 19625
30 JAMES 950 950 20575
WARD 1250 3450 23075
MARTIN 1250 3450 23075
TURNER 1500 4950 24575
ALLEN 1600 6550 26175
BLAKE 2850 9400 29025
已选择14行。
5.来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,
3 sum(sal) over (order by deptno desc,sal desc) sum
4 from emp;
DEPTNO ENAME SAL DEPT_SUM SUM
---------- ---------- ---------- ---------- ----------
30 BLAKE 2850 2850 2850
ALLEN 1600 4450 4450
TURNER 1500 5950 5950
WARD 1250 8450 8450
MARTIN 1250 8450 8450
JAMES 950 9400 9400
20 SCOTT 3000 6000 15400
FORD 3000 6000 15400
JONES 2975 8975 18375
ADAMS 1100 10075 19475
MITH 800 10875 20275
10 KING 5000 5000 25275
CLARK 2450 7450 27725
MILLER 1300 8750 29025
已选择14行。
6.体会：在"... from emp;"后面不要加order by 子句，使用的分析函数的(partition by deptno order by sal)
里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了。如：
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp
4 order by deptno desc;
DEPTNO ENAME SAL DEPT_SUM SUM
---------- ---------- ---------- ---------- ----------
30 JAMES 950 950 20575
WARD 1250 3450 23075
MARTIN 1250 3450 23075
TURNER 1500 4950 24575
ALLEN 1600 6550 26175
BLAKE 2850 9400 29025
20 SMITH 800 800 9550
ADAMS 1100 1900 10650
JONES 2975 4875 13625
SCOTT 3000 10875 19625
FORD 3000 10875 19625
10 MILLER 1300 1300 1300
CLARK 2450 3750 3750
KING 5000 8750 8750
已选择14行
==================================================================
利用over实现的分页功能：
--假设code1,code2为用来分页的KEY，每页显示5第数据
select code1,code2,code3,
ceil(count(*) over(partition by code1,code2 order by rownum)/5),
count(*) over(partition by code1,code2)
from ma_kbn order by code1,code2
==================================================================
相关推荐
### ORACLE OVER 函数详解 #### 一、概述 在Oracle数据库中，OVER函数是用于实现窗口函数（Window Functions）的重要组成部分。窗口函数允许我们执行更复杂的数据分析操作，如计算累计总和、排名等。这在开发报表...
ORACLE的OVER函数使用技巧ORACLE的OVER函数使用技巧ORACLE的OVER函数使用技巧
Oracle查询中的`OVER (PARTITION BY ..)`是一个窗口函数，它允许我们在数据集上执行计算，但不是在整个结果集上，而是针对每个分区。这部分功能非常强大，可以用于复杂的分析和排序任务，尤其是在处理分组数据时。在...
Oracle row_number()over Oracle 中的 row_number()over 函数是一个分析函数，用于生成一个连续的数字序列，可以根据某个列或多个列对数据进行分区和排序，然后生成一个唯一的序号。 row_number()over 函数的基本...
### Oracle开发中的OVER函数详解 #### 一、Oracle分析函数简介 在Oracle数据库开发中，分析函数是一类非常强大的工具，主要用于实现复杂的查询需求，尤其是在处理大量数据时，能够提供高级的数据汇总、排序和筛选...
### Oracle分析函数OVER及开窗函数详解 #### 一、概述 在Oracle数据库中，分析函数（Analytic Functions）是一种非常强大的工具，用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算，并且每行返回一个...
总结来说，Oracle的RATIO_TO_REPORT()函数提供了计算数据占比的能力，而OVER()子句则允许我们灵活地指定计算范围。在实际业务场景中，这个功能可以帮助我们进行深入的数据分析，发现隐藏的模式和趋势，进而优化业务...
Oracle的分析函数row_number() over()是数据库查询中一种强大的工具，它用于对数据集进行分组和排序，尤其在处理重复数据或者实现排名时非常有用。在Oracle SQL中，row_number()函数会为每一行生成一个唯一的数字，...
### Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数详解 #### 一、OVER (PARTITION BY ..) 概述 在Oracle数据库中，`OVER (PARTITION BY ...)` 是一种非常强大的功能，它允许用户在数据集上进行窗口操作。这在...
这份"Oracle Developer使用指南(16-19_over)"涵盖了从版本16到19的相关内容，旨在帮助用户深入理解和有效利用这个强大的开发环境。 在Oracle Developer中，主要包含了以下几个关键组件： 1. **Oracle Forms**: ...
ORACLE中OVER函数的用法.doc
Oracle分析函数在商业智能（BI）分析中扮演着至关重要的角色，它们允许用户对数据集进行复杂的计算和分析，而不必依赖多表联接或子查询。这些函数极大地提升了SQL查询的效率和灵活性，尤其在处理大数据量时。以下是...
根据提供的文件信息，我们可以深入探讨Oracle分析函数的相关知识点，特别是`SUM()`函数配合`OVER`子句的不同用法，以及`RANK()`, `DENSE_RANK()`, 和 `ROW_NUMBER()` 这三个窗口函数的应用场景。 ### Oracle分析...
Oracle函数在数据库查询和数据分析中扮演着至关重要的角色，尤其是一些著名的分析函数，它们能够帮助我们进行复杂的统计计算和数据处理。在本篇中，我们将深入探讨几个关键的分析函数，包括AVG、SUM、COUNT、FIRST_...
### GPFS 与 Oracle RAC 知识点详解 #### 一、GPFS与Oracle RAC简介 **GPFS（General Parallel File System）**是一种高性能的并行文件系统，最初由IBM开发，专为高性能计算（HPC）环境设计。GPFS支持多种硬件架构...
### Oracle实现阶梯式累加 在Oracle数据库中，实现阶梯式累加是一种常见的需求，尤其...希望本教程能帮助读者更好地理解和掌握Oracle中的窗口函数和`OVER`子句的应用技巧。在未来的工作中，这些技巧将会发挥重要作用。
此外，还可能涉及到了更高级的查询技巧，如联接（JOIN）、子查询、分组（GROUP BY）、排序（ORDER BY）以及窗口函数（OVER()）等。 另一方面，“www.pudn.com.txt”这个文件名看起来像是来源于一个网站地址，可能是...