oracle中over() 函数用法

本文深入探讨了Oracle数据库中分析函数的使用方法,特别是over()函数的功能及其与sum()和rank()函数的组合应用。通过实际案例,展示了如何根据不同条件进行薪酬总额计算及职位薪资排名。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

Oracle分析函数是如此的强大,其中over() 函数的用法又尤为的特别,所以将自己的研究结果记录一下。

  个人理解:over() 函数 是对 分析函数的一种条件解释,直接点就是 给分析函数加条件吧。

  在网上看见比较常用的就是 与 sum()、rank() 函数使用。接下来就用分析下两种函数结合over的用法。

  以下测试使用的orace默认的 scott用户下的emp表数据。

  1.sum()结合over()

  Sql代码

  select a.empno as 员工编号

  ,a.ename as 员工姓名

  ,a.deptno as 部门编号

  ,a.sal as 薪酬

  ,sum(sal) over (partition by deptno) 按部门求薪酬总和

  from scott.emp a;

  此段sql 执行的结果为:

  部门编号 员工姓名 员工编码 薪酬 按部门求薪酬总和

  7934 MILLER 10 1300 8750

  7782 CLARK 10 2450 8750

  7839 KING 10 5000 8750

  7369 SMITH 20 800 10875

  7876 ADAMS 20 1100 10875

  7566 JONES 20 2975 10875

  7788 SCOTT 20 3000 10875

  7902 FORD 20 3000 10875

  7900 JAMES 30 950 9400

  7654 MARTIN 30 1250 9400

  7521 WARD 30 1250 9400

  7844 TURNER 30 1500 9400

  7499 ALLEN 30 1600 9400

  7698 BLAKE 30 2850 9400

  可以从结果上看到sum()函数对部门区分进行了求和统计。其中“partition by”官方点的说法叫做"分区",其实就是统计的范围条件。

  下面在给把上面的sql语句改造下 给 over() 函数加上 “order by sal” 会看到一个更过瘾的效果:

  Sql代码

  select a.empno as 员工编号

  ,a.ename as 员工姓名

  ,a.deptno as 部门编号

  ,a.sal as 薪酬

  ,sum(sal) over (partition by deptno) 按部门求薪酬总和

  ,sum(sal) over (partition by deptno order by sal) 按部门累计薪酬

  from scott.emp a;

  结果为:

  部门编号 员工姓名 员工编码 薪酬 按部门求薪酬总和 按部门累计薪酬

  7934 MILLER 10 1300 8750 1300

  7782 CLARK 10 2450 8750 3750

  7839 KING 10 5000 8750 8750

  7369 SMITH 20 800 10875 800

  7876 ADAMS 20 1100 10875 1900

  7566 JONES 20 2975 10875 4875

  7788 SCOTT 20 3000 10875 10875

  7902 FORD 20 3000 10875 10875

  7900 JAMES 30 950 9400 950

  7654 MARTIN 30 1250 9400 3450

  7521 WARD 30 1250 9400 3450

  7844 TURNER 30 1500 9400 4950

  7499 ALLEN 30 1600 9400 6550

  7698 BLAKE 30 2850 9400 9400

  从结果中可以看的 加了 “order by”后对 统计进行一个累加,这里个人理解为对统计范围规定了个统计顺利,一步一步的统计。

  注:此sql语句结尾处不要加“order by”,应为使用的分析函数的(partition by deptno order by sal)

  里已经有排序的语句了,如果再在句尾添加排序子句,一致倒罢了,不一致,结果就令人费解了。

  2.rank()结合over()

  rank函数是分级函数,这个函数必须与 over 函数使用,否则会报一个"缺少窗口函数的错"。我测试sql如下:

  Sql代码

  select a.empno as 员工编号,

  a.sal as 薪资,

  a.job as 岗位,

  rank() OVER(partition by a.job ORDER BY a.sal desc) as 岗位薪资等级

  from scott.emp a;

  查询结果为:

  员工编号 薪资 岗位 等级

  7902 3000 ANALYST 1

  7788 3000 ANALYST 1

  7934 1300 CLERK 1

  7876 1100 CLERK 2

  7900 950 CLERK 3

  7369 800 CLERK 4

  7566 2975 MANAGER 1

  7698 2850 MANAGER 2

  7782 2450 MANAGER 3

  7839 5000 PRESIDENT 1

  7499 1600 SALESMAN 1

  7844 1500 SALESMAN 2

  7654 1250 SALESMAN 3

  7521 1250 SALESMAN 3

 

 

说明:阅读代码时看到:SELECT ROWID, ROW_NUMBER() OVER (ORDER BY DB_BKUP ASC, TIME DESC) TEMP FROM t_pm_history WHERE DB_BKUP < 2 ORDER BY DB_BKUP ASC, TIME DESC,由此查到此文

<think>好的,我现在要处理用户关于Oracle数据库中MAX函数的使用方法和示例的查询。首先,我需要回忆一下MAX函数的基本知识。MAX函数是聚合函数,用于返回一组值中的最大值。它可以应用于数值、日期和字符串类型的数据。用户可能需要了解不同数据类型下的用法,以及一些常见的使用场景。 接下来,我应该考虑用户可能的实际需求。他们可能想知道如何在单列中使用MAX,或者结合GROUP BY进行分组统计。另外,用户可能对如何在复杂查询中使用MAX函数感兴趣,比如子查询或者与其他函数结合使用。还有,是否需要处理NULL值的情况?虽然MAX函数本身会忽略NULL,但用户可能有特殊情况需要处理。 然后,我需要参考用户提供的引用内容,但看起来引用主要关于Oracle管道函数,与MAX函数没有直接关系。不过,用户提到要参考之前的回答,所以可能需要确保回答结构清晰,步骤明确,符合用户之前的要求。比如,用户希望回答中有示例代码,分点说明,以及相关问题。 现在,我需要组织回答的结构。首先介绍MAX函数的基本用法,然后分点说明不同情况下的应用,每个点都给出示例SQL语句。可能包括:基本用法、分组统计、多列比较、结合子查询、与分析函数结合使用。同时,注意使用中文,确保LaTeX格式正确,行内公式用$...$,独立公式用$$...$$,但在这可能更多是SQL示例,所以需要正确使用代码块。 另外,用户提到生成相关问题,我需要考虑用户可能进一步想知道的内容,比如MAX与其他聚合函数的区别,性能优化,处理NULL的技巧,或者在复杂查询中的高级应用。这样可以帮助用户深入理解MAX函数的不同方面。 最后,检查是否有遗漏,比如是否覆盖了所有数据类型,是否提供了足够的示例,以及是否符合用户提到的格式要求。确保引用标识正确添加,尽管当前引用可能与问题无关,但按照用户要求,如果有引用内容需要标注,这可能不需要,但需要确认用户提供的引用是否有关联。根据用户给出的引用,主要是关于管道函数,但用户的问题是关于MAX函数,所以可能不需要引用,但需要遵循回答结构,末尾添加相关问题。</think>### Oracle MAX函数详解 #### 一、基本语法 MAX函数用于返回指定列的最大值,支持数值、日期和字符类型数据: $$ \text{MAX}([DISTINCT|ALL] \ expression) $$ #### 二、使用场景及示例 1. **单列最大值查询** ```sql -- 查询员工表最高工资 SELECT MAX(salary) AS max_salary FROM employees; ``` 2. **分组统计最大值** ```sql -- 查询每个部门的最高工资 SELECT department_id, MAX(salary) FROM employees GROUP BY department_id; ``` 3. **多列比较最大值** ```sql -- 使用GREATEST函数比较多列 SELECT GREATEST(hire_date, start_date) AS latest_date FROM job_history; ``` 4. **结合子查询使用** ```sql -- 查询工资等于最高工资的员工 SELECT employee_name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); ``` 5. **分析函数用法** ```sql -- 显示每个员工及其所在部门的最高工资 SELECT employee_name, salary, MAX(salary) OVER (PARTITION BY department_id) AS dept_max FROM employees; ``` #### 三、特殊处理技巧 - **日期处理**:MAX函数可获取最新日期 ```sql SELECT MAX(hire_date) AS latest_hire FROM employees; ``` - **字符串处理**:按字母顺序返回最大值 ```sql SELECT MAX(last_name) FROM employees; -- 返回Z字母开头的姓氏 ``` #### 四、性能建议 1. 在$WHERE$子句中避免重复计算 2. 对大数据量列建立索引优化查询 3. 使用分析函数替代关联子查询可提升效率
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值