SQL code:
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，则是在整个公司内进行排名。
SQL code:
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
相关推荐
#### 四、OVER函数示例 由于题目提供的部分内容中没有具体示例，以下是一些典型的使用场景示例。 ##### 示例1：使用ROW_NUMBER()计算每组内的行编号 假设我们有一个员工表`employees`，包含字段`employee_id`, `...
### Oracle开发中的OVER函数详解 #### 一、Oracle分析函数简介 在Oracle数据库开发中，分析函数是一类非常强大的工具，主要用于实现复杂的查询需求，尤其是在处理大量数据时，能够提供高级的数据汇总、排序和筛选...
Oracle数据库中的RATIO_TO_REPORT()函数是一个非常有用的分析函数，尤其在进行数据比例分析和比较时。这个函数能够计算一个值相对于所有值总和的比例，返回的结果是一个百分比。配合OVER()子句，它可以用于全局或者...
- 当`OVER`子句中使用`PARTITION BY NULL`时，意味着所有的行都被视为同一个分区，即整个结果集被视为一个整体来进行计算。 #### 六、总结 通过本文的学习，我们可以看到Oracle开窗函数的强大之处。无论是进行复杂...
通过以上示例可以看出，Oracle中的`OVER`子句和开窗函数是非常强大的工具，可以用来解决许多复杂的数据分析问题。正确理解和运用这些函数，能够极大地提高数据处理效率和灵活性。在实际应用中，可以根据具体需求灵活...
通过以上介绍和示例，我们可以看到Oracle数据库中的OVER函数提供了极其灵活和强大的数据处理功能，能够帮助我们轻松地完成复杂的数据分析任务。熟练掌握OVER函数的应用技巧，对于提升SQL技能和数据分析效率具有重要...
Oracle 分析函数（用法+实例） Oracle 分析函数是 Oracle 8.1.6 版本中引入的高级应用，属于 Oracle 的一大亮点。分析函数可以分为四大类：排名函数、聚合函数、行比较函数和统计函数。下面将对分析函数的原理、...
根据提供的文件信息，我们可以深入探讨Oracle分析函数的相关知识点，特别是`SUM()`函数配合`OVER`子句的不同用法，以及`RANK()`, `DENSE_RANK()`, 和 `ROW_NUMBER()` 这三个窗口函数的应用场景。 ### Oracle分析...
分析函数通过使用OVER子句，可以对数据进行分区、排序以及定义窗口大小等功能。它们在数据仓库、报告和在线事务处理等多种场合中非常有用。 以下是对给定文件内容中提到的Oracle分析函数相关知识点的详细介绍： 1....
在Oracle 9i 分析函数参考手册中，你可能会找到详细的函数用法、示例和最佳实践，帮助你更有效地利用这些功能。手册通常会涵盖函数语法、参数、返回类型以及如何解决常见问题。通过深入学习和实践，你可以掌握如何在...
通过本文的学习，我们了解了Oracle中的分析函数及其基本用法，包括常见的分析函数分类、基本语法以及实际应用场景。这些函数的强大之处在于它们能够轻松地处理复杂的数据计算需求，为数据分析提供了极大的便利。掌握...
本文将详细介绍每种类型的函数，并提供具体的使用场景和示例。 #### 二、分析函数 分析函数主要用于执行复杂的窗口运算，如计算移动平均、累计总和等。这类函数通常会结合`OVER`子句一起使用，以便在不同的数据集...
#### 四、Oracle分析函数示例 1. **使用`RANK()`函数** ```sql SELECT employee_id, salary, department_id, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; ``` ...
要计算每个员工的工资在其所在部门工资总额中的占比，可以使用`SUM`函数配合`OVER`子句： ```sql SELECT NAME, DEPT, SAL, SAL * 100 / SUM(SAL) OVER (PARTITION BY DEPT) AS PERCENT FROM salary; ``` 结果如下...
Oracle的分析函数`ROW_NUMBER() OVER()`是一种强大的工具，用于在查询结果集中为每一行分配一个唯一的序列号。这个函数通常用于数据分页、排名或者为特定条件的记录分配顺序。下面我们将详细讨论`ROW_NUMBER() OVER...
这个示例中，我们使用 row_number()over 函数来生成一个部门内的薪水排名列，然后在结果中取出排名在前 2 位的员工。 row_number()over 函数是一个非常有用的分析函数，可以帮助我们快速地生成各种形式的序号和排名...
Oracle分析函数是数据库管理系统Oracle中的一个强大特性，它允许用户在SQL查询中执行复杂的分析操作。分析函数在处理报表和数据迁移任务时尤其有用，因为它们可以基于分组计算聚合值，并为每个分组返回多行，而不...
在提供的内容中，提到了几个重要的分析函数示例： 1. AVG：此函数计算指定窗口内的平均值。例如，在员工表中，如果我们想要计算每个经理的员工平均工资，但要包括当前员工以及相邻的两个员工（如果存在的话），我们...
Oracle分析函数是数据库管理系统Oracle中一组强大的工具，用于处理集合数据，特别是在复杂的报表和数据分析场景中。它们允许用户在单个SQL查询中执行聚合操作，同时保持行的原始顺序，这是传统的GROUP BY函数无法...
`OVER (PARTITION BY ...)` 和开窗函数在Oracle数据库中提供了极其强大的数据处理能力，特别是在需要进行复杂的数据分析或报表制作时。掌握这些技术对于提高数据库查询效率和数据处理能力至关重要。