Oracle 分析函数

最新推荐文章于 2022-04-22 17:38:55 发布
最新推荐文章于 2022-04-22 17:38:55 发布
阅读量101 收藏
点赞数
文章标签： 数据库
Oracle 分析函数练习
1 取出emp表汇总每个员工的部门号，姓名，员工工资已经部门的平均工资

方法一（分析函数）：
SQL> select deptno,ename,sal,avg(sal) over (partition by deptno) avg_sal from emp;


    DEPTNO ENAME     SAL    AVG_SAL
---------- ---------- ---------- ----------
  10 CLARK          2450 2916.66667
  10 KING           5000 2916.66667
  10 MILLER         1300 2916.66667
  20 JONES          2975       2175
  20 FORD           3000       2175
  20 ADAMS          1100       2175
  20 SMITH           800       2175
  20 SCOTT          3000       2175
  30 WARD           1250 1566.66667
  30 TURNER         1500 1566.66667
  30 ALLEN          1600 1566.66667
  30 JAMES           950 1566.66667
  30 BLAKE          2850 1566.66667
  30 MARTIN         1250 1566.66667

14 rows selected.


方法二(表连接)：
SQL> select a.deptno,a.ename,a.sal,b.avg_sal from emp a,(select deptno,avg(sal) avg_sal from emp group by deptno) b where a.deptno=b.deptno order by deptno;

    DEPTNO ENAME     SAL    AVG_SAL
---------- ---------- ---------- ----------
  10 CLARK          2450 2916.66667
  10 KING           5000 2916.66667
  10 MILLER         1300 2916.66667
  20 SMITH           800       2175
  20 JONES          2975       2175
  20 SCOTT          3000       2175
  20 ADAMS          1100       2175
  20 FORD           3000       2175
  30 ALLEN          1600 1566.66667
  30 WARD           1250 1566.66667
  30 MARTIN         1250 1566.66667
  30 BLAKE          2850 1566.66667
  30 TURNER         1500 1566.66667
  30 JAMES           950 1566.66667

14 rows selected.


2 显示各部门员工的工资，并附带显示该部分的最高工资。

方法一(分析函数)：
SQL> select deptno,ename,sal,max(sal) over (partition by deptno) max_sal from emp;

    DEPTNO ENAME             SAL    MAX_SAL
---------- ---------- ---------- ----------
        10 CLARK            2450       5000
        10 KING             5000       5000
        10 MILLER           1300       5000
        20 JONES            2975       3000
        20 FORD             3000       3000
        20 ADAMS            1100       3000
        20 SMITH             800       3000
        20 SCOTT            3000       3000
        30 WARD             1250       2850
        30 TURNER           1500       2850
        30 ALLEN            1600       2850
        30 JAMES             950       2850
        30 BLAKE            2850       2850
        30 MARTIN           1250       2850

14 rows selected.

当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row  --当前组的第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following  --整个组

SQL> select deptno,ename,sal,max(sal) over (partition by deptno order by ename) max_sal from emp;

    DEPTNO ENAME             SAL    MAX_SAL
---------- ---------- ---------- ----------
        10 CLARK            2450       2450
        10 KING             5000       5000
        10 MILLER           1300       5000
        20 ADAMS            1100       1100
        20 FORD             3000       3000
        20 JONES            2975       3000
        20 SCOTT            3000       3000
        20 SMITH             800       3000
        30 ALLEN            1600       1600
        30 BLAKE            2850       2850
        30 JAMES             950       2850
        30 MARTIN           1250       2850
        30 TURNER           1500       2850
        30 WARD             1250       2850

14 rows selected.

当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row  --当前组的第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following  --整个组

SQL> select deptno,ename,sal,max(sal) over (partition by deptno order by ename rows between unbounded preceding and unbounded following) max_sal from emp;

    DEPTNO ENAME             SAL    MAX_SAL
---------- ---------- ---------- ----------
        10 CLARK            2450       5000
        10 KING             5000       5000
        10 MILLER           1300       5000
        20 ADAMS            1100       3000
        20 FORD             3000       3000
        20 JONES            2975       3000
        20 SCOTT            3000       3000
        20 SMITH             800       3000
        30 ALLEN            1600       2850
        30 BLAKE            2850       2850
        30 JAMES             950       2850
        30 MARTIN           1250       2850
        30 TURNER           1500       2850
        30 WARD             1250       2850

14 rows selected.


方法二(表连接）
SQL> select a.deptno,a.ename,a.sal,b.max from emp a,(select deptno,max(sal) max from emp group by deptno) b where a.deptno=b.deptno order by deptno;

    DEPTNO ENAME             SAL        MAX
---------- ---------- ---------- ----------
        10 CLARK            2450       5000
        10 KING             5000       5000
        10 MILLER           1300       5000
        20 SMITH             800       3000
        20 JONES            2975       3000
        20 SCOTT            3000       3000
        20 ADAMS            1100       3000
        20 FORD             3000       3000
        30 ALLEN            1600       2850
        30 WARD             1250       2850
        30 MARTIN           1250       2850
        30 BLAKE            2850       2850
        30 TURNER           1500       2850
        30 JAMES             950       2850

14 rows selected.


3 对各部门进行分组，并附带显示第一行至当前行的汇总

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between unbounded preceding and current row) sum_sal from emp;

--注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总

    DEPTNO ENAME             SAL    SUM_SAL
---------- ---------- ---------- ----------
        10 CLARK            2450       2450
        10 KING             5000       7450
        10 MILLER           1300       8750
        20 ADAMS            1100       1100
        20 FORD             3000       4100
        20 JONES            2975       7075
        20 SCOTT            3000      10075
        20 SMITH             800      10875
        30 ALLEN            1600       1600
        30 BLAKE            2850       4450
        30 JAMES             950       5400
        30 MARTIN           1250       6650
        30 TURNER           1500       8150
        30 WARD             1250       9400

14 rows selected.

4 对各部门进行分组，并附带显示当前行至最后一行的汇总

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between current row and unbounded following) max from emp;

--注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总

    DEPTNO ENAME             SAL        MAX
---------- ---------- ---------- ----------
        10 CLARK            2450       8750
        10 KING             5000       6300
        10 MILLER           1300       1300
        20 ADAMS            1100      10875
        20 FORD             3000       9775
        20 JONES            2975       6775
        20 SCOTT            3000       3800
        20 SMITH             800        800
        30 ALLEN            1600       9400
        30 BLAKE            2850       7800
        30 JAMES             950       4950
        30 MARTIN           1250       4000
        30 TURNER           1500       2750
        30 WARD             1250       1250

14 rows selected.


5 对各部门进行分组，并附带显示当前行的上一行(rownum-1)到当前行的汇总

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between 1 preceding and current row) max from emp;

--注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总

    DEPTNO ENAME             SAL        MAX
---------- ---------- ---------- ----------
        10 CLARK            2450       2450
        10 KING             5000       7450
        10 MILLER           1300       6300
        20 ADAMS            1100       1100
        20 FORD             3000       4100
        20 JONES            2975       5975
        20 SCOTT            3000       5975
        20 SMITH             800       3800
        30 ALLEN            1600       1600
        30 BLAKE            2850       4450
        30 JAMES             950       3800
        30 MARTIN           1250       2200
        30 TURNER           1500       2750
        30 WARD             1250       2750

14 rows selected.

6 对各部门进行分组，并附带显示当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename rows between 1 preceding and 2 following) max from emp;

--注意ROWS BETWEEN 1 preceding AND 2 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

    DEPTNO ENAME             SAL        MAX
---------- ---------- ---------- ----------
        10 CLARK            2450       8750
        10 KING             5000       8750
        10 MILLER           1300       6300
        20 ADAMS            1100       7075
        20 FORD             3000      10075
        20 JONES            2975       9775
        20 SCOTT            3000       6775
        20 SMITH             800       3800
        30 ALLEN            1600       5400
        30 BLAKE            2850       6650
        30 JAMES             950       6550
        30 MARTIN           1250       4950
        30 TURNER           1500       4000
        30 WARD             1250       2750

14 rows selected.

SQL> select deptno,ename,sal,last_value(sal) over(partition by deptno) sum from emp;

    DEPTNO ENAME             SAL        SUM
---------- ---------- ---------- ----------
        10 CLARK            2450       1300
        10 KING             5000       1300
        10 MILLER           1300       1300
        20 JONES            2975       3000
        20 FORD             3000       3000
        20 ADAMS            1100       3000
        20 SMITH             800       3000
        20 SCOTT            3000       3000
        30 WARD             1250       1250
        30 TURNER           1500       1250
        30 ALLEN            1600       1250


    DEPTNO ENAME             SAL        SUM
---------- ---------- ---------- ----------
        30 JAMES             950       1250
        30 BLAKE            2850       1250
        30 MARTIN           1250       1250


14 rows selected.

SQL> select deptno,ename,sal,last_value(sal) over(partition by deptno order by sal desc) sum from emp;

    DEPTNO ENAME             SAL        SUM
---------- ---------- ---------- ----------
        10 KING             5000       5000
        10 CLARK            2450       2450
        10 MILLER           1300       1300
        20 SCOTT            3000       3000
        20 FORD             3000       3000
        20 JONES            2975       2975
        20 ADAMS            1100       1100
        20 SMITH             800        800
        30 BLAKE            2850       2850
        30 ALLEN            1600       1600
        30 TURNER           1500       1500


    DEPTNO ENAME             SAL        SUM
---------- ---------- ---------- ----------
        30 MARTIN           1250       1250
        30 WARD             1250       1250
        30 JAMES             950        950


14 rows selected.

SQL> select deptno,ename,sal,first_value(sal) over(partition by deptno) sum from emp;

    DEPTNO ENAME             SAL        SUM
---------- ---------- ---------- ----------
        10 CLARK            2450       2450
        10 KING             5000       2450
        10 MILLER           1300       2450
        20 JONES            2975       2975
        20 FORD             3000       2975
        20 ADAMS            1100       2975
        20 SMITH             800       2975
        20 SCOTT            3000       2975
        30 WARD             1250       1250
        30 TURNER           1500       1250
        30 ALLEN            1600       1250


    DEPTNO ENAME             SAL        SUM
---------- ---------- ---------- ----------
        30 JAMES             950       1250
        30 BLAKE            2850       1250
        30 MARTIN           1250       1250


14 rows selected.

=======
Rank()
=======

一 要求：同时取出语文成绩和数学成绩的前三名；

1：
SQL> select * from t1 order by 1,2 desc;

SUBJECT                             SCORE
------------------------------ ----------
chinese                               120
chinese                                88
chinese                                77
chinese                                70
chinese                                65
matchs                                150
matchs                                100
matchs                                 90
matchs                                 80
matchs                                 60

10 rows selected.

2：
SQL> select rank() over(partition by subject order by score desc) rank,t1.* from t1;


      RANK SUBJECT                             SCORE
---------- ------------------------------ ----------
         1 chinese                               120
         2 chinese                                88
         3 chinese                                77
         4 chinese                                70
         5 chinese                                65
         1 matchs                                150
         2 matchs                                100
         3 matchs                                 90
         4 matchs                                 80
         5 matchs                                 60

10 rows selected.

3：
SQL> select * from (select rank() over(partition by subject order by score desc) rank,t1.* from t1) a where a.rank<=3;

      RANK SUBJECT                             SCORE
---------- ------------------------------ ----------
         1 chinese                               120
         2 chinese                                88
         3 chinese                                77
         1 matchs                                150
         2 matchs                                100
         3 matchs                                 90

6 rows selected.

二 要求：取出分数最高的前三名

SQL> select * from (select rank() over(order by score desc) rank,t1.* from t1) a where a.rank<=3;

      RANK SUBJECT                             SCORE
---------- ------------------------------ ----------
         1 matchs                                150
         2 chinese                               120
         3 matchs                                100


三 要求：取出分数排在第五到第十名

SQL> select * from (select rank() over(order by score desc) rank,t1.* from t1) a where a.rank between 5 and 10;

      RANK SUBJECT                             SCORE
---------- ------------------------------ ----------
         5 chinese                                88
         6 matchs                                 80
         7 chinese                                77
         8 chinese                                70
         9 chinese                                65
        10 matchs                                 60


6 rows selected.

四 要求：每个部门工资前三名

SQL> select * from (select deptno,ename,sal,rank() over(partition by deptno order by sal desc) rk from emp) a where a.rk<=3;


    DEPTNO ENAME             SAL         RK
---------- ---------- ---------- ----------
        10 KING             5000          1
        10 CLARK            2450          2
        10 MILLER           1300          3
        20 SCOTT            3000          1
        20 FORD             3000          1
        20 JONES            2975          3
        30 BLAKE            2850          1
        30 ALLEN            1600          2
        30 TURNER           1500          3

9 rows selected.

=============
dense_rank()
=============

dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过

SQL> select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) rk from emp) a where a.rk<=3;

    DEPTNO ENAME             SAL         RK
---------- ---------- ---------- ----------
        10 KING             5000          1
        10 CLARK            2450          2
        10 MILLER           1300          3
        20 SCOTT            3000          1
        20 FORD             3000          1
        20 JONES            2975          2
        20 ADAMS            1100          3
        30 BLAKE            2850          1
        30 ALLEN            1600          2
        30 TURNER           1500          3

10 rows selected.

SQL> select * from (select deptno,ename,sal from emp order by sal desc) a where rownum<=5;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 SCOTT            3000
        20 FORD             3000
        20 JONES            2975
        30 BLAKE            2850

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1763699/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-1763699/

ckth47350
关注
Oracle 分析函数练习实例
JISHI412的博客
07-16 799
创建表 create table emp( deptno varchar2(20) , empno varchar2(20), ename varchar(20), sal number ); sql演示 –显示各部门员工的工资，并附带显示该部分的最高工资。 select e.deptno, e.empno, e.ename, e.sal, last_value(e.sal) over(partition by
oracle 分析函数测试学习
byebyegov5qq
10-09 99
CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES 12 7 9 2000 6 2 50658 2 5 1 2000 10 4 35580 2 5 4 2000 4 4 6454 2 5 4 2000 3 ...
Oracle开发者中级第4课（分析函数）实验
In-Memory Computing Technology
10-28 299
概述 本实验参考DevGym中的实验指南。 创建环境 首先创建表： create table bricks ( brick_id integer, colour varchar2(10), shape varchar2(10), weight integer ); insert into bricks values ( 1, 'blue', 'cube', 1 ); insert into bricks values ( 2, 'blue', 'pyramid', 2 );
oracle 分析函数_数据分析系列笔试题（6）
weixin_39657662的博客
11-13 315
途家名宿的数据分析笔试题，做的很轻松；总分100 时间60分钟选择题：30*2 ，代码题2*20其中代码题，只需要提交代码即可，不需要调试！选择题部分集中考察SQL基本语法，SQL函数，以及统计学，逻辑题选择题中，有这么一个，我还有映像：Excle中可以完成去除重复值的方法（多选题）：去除重复值、高级筛选、数据透视表具体参考：清白小生：excel中如何去除重复项问答题部分select count...
oracle分析函数（用法+实例）
09-13
oracle分析函数（用法+实例）,这属于oracle的高级应用。
ORACLE分析函数.pdf
09-30
Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性，它允许用户在单个查询中对一组行进行计算，而无需使用子查询或自连接。这些函数极大地增强了数据分析和报告的能力，提高了查询性能。以下是对Oracle分析...
常用Oracle分析函数大全
01-19
Oracle的分析函数功能非常强大，工作这些年来经常用到。这次将平时经常使用到的分析函数整理出来，以备日后查看。 我们拿案例来学习，这样理解起来更容易一些。 1、建表 create table earnings -- 打工赚钱表 ( ...
Oracle分析函数学习
KIN ORACLE
12-21 679
http://f.dataguru.cn/thread-41808-1-1.html row_number() over ([partition by col1] order by col2) ) 1. SQL> SELECT deptno 部门, ename 姓名, SAL 薪水, SUM(SAL) OVER (ORDER BY ENAME) 累加, SUM
ORACLE分析函数大全
06-14
文档详细介绍了oracle的分析函数，包括功能说明、sql示例等。分析函数功能强大，在报表或数据迁移的时候可能会使用到。分析函数用法看上去有点复杂，最好使用的时候，参考文档
oracle 函数 测试题,Oracle经典练习题
weixin_39699670的博客
04-02 583
Oracle经典练习题1、取得每个部门最高薪水的人员名称第一步：取得每个部门的最高薪水select deptno, max(sal) maxSal from emp group by deptno第二步：获取结果select ename, sal, e.deptno from emp e join ( select deptno, max(sal) maxSal from emp group by...
Oracle之分析函数 - 各种分析函数例子
qq_33879355的博客
11-05 1649
/*函数应用主要分为如下4类：  1 统计函数（MAX MIN AVG SUM COUNT等）  2.排序函数（ROW_NUMBER RANK DENSE_RNAK FIRST FIRST_VALUE LAST LAST_VALUE LAG LEAD 等）  3.数据分布函数（NTILE CUME_DIST PERCENT_RANK  PERCENTILE_CON  PERCENTI
oracle数据库之常用的函数练习
加油吧！ 少年~
06-28 3124
/*此文章可以作为sql脚本直接运行，某些函数限于oracle数据库！ 前面建表和数据插入可以不看，直接看后面红色部分的代码，前面的表只是提供一些数据供大家练习，不用自己再去建表了，真正的内容在后面*/ --人员表 create table person( personID     number , pname        varchar2(20) constraint N
oracle 每日一题-分析函数row_number
zhq200902的博客
03-08 566
转自http://www.itpub.net/thread-2084317-1-1.html 感谢newkid 原始出处： http://www.plsqlchallenge.com/ 作者：Kim Berg Hansen 运行环境：SQLPLUS, SERVEROUTPUT已打开 我有一张表保存着每个人的姓名，出生日期（DOB），以及父亲： create table
Oracle数据库常用函数应用习题
BingErer0124的博客
04-22 883
Oracle普通练习题
Oracle基础-实训存储过程
都随它大小便吧
12-15 4447
一、项目（实训）名称 存储过程的创建及调用 二、项目（实训）学时数 本实训项目预计实训学时数为2课时 三、项目（实训）目标 1.  了解数据表的创建方法 2.  数据操纵练习 3.  存储过程的创建 4.   存储过程的调用 四、项目（实训）中的具体任务 1.建数据表并完成数据插入 （1）学生表   （2）课程表   （3）成绩表
Oracle 常用函数练习题含答案
21号先锋者
03-08 3045
查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。 SELECT * FROM employees where upper(employees.first_name || employees.last_name) like '__A%'; 将员工的参加工作日期按如下格式显示：月份/年份 SELECT to_char(employees.hire_date,'mm/Y...
oracle 学习分组查询、分组函数、练习题答案
qq_34693104的博客
07-27 1814
oracle 学习笔记 –分组查询 MIN MAX AVG COUNT SUM NVL –NVL(参数一，参数二) 函数可以使分组函数强制包含含有空值的记录 –NVL2(参数一，参数二，参数三) SELECT nvl2(null,’男’,’女’) FROM DUAL;–女 SELECT nvl2(‘M’,’男’,’女’) FROM DUAL; –男 select * from emp...
Oracle分析函数详解与实战
1. Oracle分析函数简介： 分析函数与聚合函数（如SUM, COUNT, AVG等）的区别在于，聚合函数返回单个值，而分析函数可以返回多个结果行，每个结果行都包含计算值。分析函数的关键特性是它们可以在一个窗口（或称为...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值