从简单的数据表中学习oracle的窗口函数使用

最新推荐文章于 2023-10-25 17:43:38 发布

NeoInHeart

最新推荐文章于 2023-10-25 17:43:38 发布

阅读量502

点赞数 1

分类专栏： Oracle数据库文章标签： sql 数据库

本文链接：https://blog.csdn.net/NeoInHeart/article/details/107517221

版权

Oracle数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

oracle数据库创建及表的创建

oracle数据库创建数据库的基础语法
数据库命令的参数说明

1、创建表空间：

格式: create tablespace 表间名 datafile ‘数据文件名’ size 表空间大小

--在oracle数据库中创建一个表空间类似于mysql数据库中创建的database
create tablespace data_test datafile 'e:\oracle\oradata\test\data_1.dbf' size 2000M;

(*数据文件名包含全路径, 表空间大小 2000M 表是 2000兆)

2、建好表空间后, 就可以建用户

格式: create user 用户名 identified by 密码 default tablespace 表空间表;

-- 在oracle创建一个用户并赋给其表空间
create user study identified by study default tablespace data_test;

(*我们创建一个用户名为 study,密码为 study, 缺少表空间为 data_test -这是在第二步建好的.)

3、授权给新用户

GRANT connect, resource TO study;
--表示把 connect,resource角色授予study用户

grant create session to study;
--表示把 create session 权限授予给study用户

——————备注

权限包含：

create session 允许用户登录数据库权限
　　create table 允许用户创建表权限
　　unlimited tablespace 允许用户在其他表空间随意建表

角色包含：

connect
　　resource
　　dba

4.创建表

数据类型表-01 数据类型表-02

--创建 employees  表
create table EMPLOYEES
(
  employee_id    NUMBER(6) not null,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25),
  email          VARCHAR2(25),
  phone_number   VARCHAR2(20),
  hire_date      DATE,
  job_id         VARCHAR2(10),
  salary         NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id     NUMBER(6),
  department_id  NUMBER(4)
);

可以对创建表的列进行修改
修改的语法：

-- 增加一列
alter table EMPLOYEES
add USER_NAME VARCHAR(30);
-- 修改一列
alter table EMPLOYEES
modify first_name varchar(30) not null;
-- 删除一列
alter table EMPLOYEES
drop column USER_NAME;

对表重新命名

alter table EMPLOYEES
rename to EMPLOYEESS

5.在表中插入数据

insert into employees values (100,'Steven','King','SKING','515.123.4567','17-jun-03','AD_PRES',24000,NULL,NULL,90);
insert into employees values (101,'Neena','Kochhar','NKOCHHAR','515.123.4568','21-sep-05','AD_VP',17000,NULL,100,90);
insert into employees values (102,'Lex','De Haan','LDEHAAN','515.123.4569','13-jan-01','AD_VP',17000,NULL,100,90);
insert into employees values (103,'Alexander','Hunold','AHUNOLD','590.423.4567','03-jan-06','IT_PROG',9000,NULL,102,60);
insert into employees values (104,'Bruce','Ernst','BERNST','590.423.4568','21-may-07','IT_PROG',6000,NULL,103,60);
insert into employees values (105,'David','Austin','DAUSTIN','590.423.4569','25-jun-05','IT_PROG',4800,NULL,103,60);
insert into employees values (106,'Valli','Pataballa','VPATABAL','590.423.4560','05-feb-06','IT_PROG',4800,NULL,103,60);
insert into employees values (107,'Diana','Lorentz','DLORENTZ','590.423.5567','07-feb-07','IT_PROG',4200,NULL,103,60);
insert into employees values (108,'Nancy','Greenberg','NGREENBE','515.124.4569','17-aug-02','FI_MGR',12008,NULL,101,100);
insert into employees values (109,'Daniel','Faviet','DFAVIET','515.124.4169','16-aug-02','FI_ACCOUNT',9000,NULL,108,100);
insert into employees values (110,'John','Chen','JCHEN','515.124.4269','28-sep-05','FI_ACCOUNT',8200,NULL,108,100);
insert into employees values (111,'Ismael','Sciarra','ISCIARRA','515.124.4369','30-sep-05','FI_ACCOUNT',7700,NULL,108,100);
insert into employees values (112,'Jose Manuel','Urman','JMURMAN','515.124.4469','07-mar-06','FI_ACCOUNT',7800,NULL,108,100);
insert into employees values (113,'Luis','Popp','LPOPP','515.124.4567','07-dec-07','FI_ACCOUNT',6900,NULL,108,100);
insert into employees values (114,'Den','Raphaely','DRAPHEAL','515.127.4561','07-dec-02','PU_MAN',11000,NULL,100,30);
insert into employees values (115,'Alexander','Khoo','AKHOO','515.127.4562','18-may-03','PU_CLERK',3100,NULL,114,30);
insert into employees values (116,'Shelli','Baida','SBAIDA','515.127.4563','24-dec-05','PU_CLERK',2900,NULL,114,30);
insert into employees values (117,'Sigal','Tobias','STOBIAS','515.127.4564','24-jul-05','PU_CLERK',2800,NULL,114,30);
insert into employees values (118,'Guy','Himuro','GHIMURO','515.127.4565','15-nov-06','PU_CLERK',2600,NULL,114,30);
insert into employees values (119,'Karen','Colmenares','KCOLMENA','515.127.4566','10-aug-07','PU_CLERK',2500,NULL,114,30);
insert into employees values (120,'Matthew','Weiss','MWEISS','650.123.1234','18-jul-04','ST_MAN',8000,NULL,100,50);
insert into employees values (121,'Adam','Fripp','AFRIPP','650.123.2234','10-apr-05','ST_MAN',8200,NULL,100,50);
insert into employees values (122,'Payam','Kaufling','PKAUFLIN','650.123.3234','01-may-03','ST_MAN',7900,NULL,100,50);
insert into employees values (123,'Shanta','Vollman','SVOLLMAN','650.123.4234','10-oct-05','ST_MAN',6500,NULL,100,50);
insert into employees values (124,'Kevin','Mourgos','KMOURGOS','650.123.5234','16-nov-07','ST_MAN',5800,NULL,100,50);
insert into employees values (125,'Julia','Nayer','JNAYER','650.124.1214','16-jul-05','ST_CLERK',3200,NULL,120,50);
insert into employees values (126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','28-sep-06','ST_CLERK',2700,NULL,120,50);
insert into employees values (127,'James','Landry','JLANDRY','650.124.1334','14-jan-07','ST_CLERK',2400,NULL,120,50);
insert into employees values (128,'Steven','Markle','SMARKLE','650.124.1434','08-mar-08','ST_CLERK',2200,NULL,120,50);
insert into employees values (129,'Laura','Bissot','LBISSOT','650.124.5234','20-aug-05','ST_CLERK',3300,NULL,121,50);
insert into employees values (130,'Mozhe','Atkinson','MATKINSO','650.124.6234','30-oct-05','ST_CLERK',2800,NULL,121,50);
insert into employees values (131,'James','Marlow','JAMRLOW','650.124.7234','16-feb-05','ST_CLERK',2500,NULL,121,50);
insert into employees values (132,'TJ','Olson','TJOLSON','650.124.8234','10-apr-07','ST_CLERK',2100,NULL,121,50);
insert into employees values (133,'Jason','Mallin','JMALLIN','650.127.1934','14-jun-04','ST_CLERK',3300,NULL,122,50);
insert into employees values (134,'Michael','Rogers','MROGERS','650.127.1834','26-aug-06','ST_CLERK',2900,NULL,122,50);
insert into employees values (135,'Ki','Gee','KGEE','650.127.1734','12-dec-07','ST_CLERK',2400,NULL,122,50);
insert into employees values (136,'Hazel','Philtanker','HPHILTAN','650.127.1634','06-feb-08','ST_CLERK',2200,NULL,122,50);
insert into employees values (137,'Renske','Ladwig','RLADWIG','650.121.1234','14-jul-03','ST_CLERK',3600,NULL,123,50);
insert into employees values (138,'Stephen','Stiles','SSTILES','650.121.2034','26-oct-05','ST_CLERK',3200,NULL,123,50);
insert into employees values (139,'John','Seo','JSEO','650.121.2019','12-feb-06','ST_CLERK',2700,NULL,123,50);
insert into employees values (140,'Joshua','Patel','JPATEL','650.121.1834','06-apr-06','ST_CLERK',2500,NULL,123,50);
insert into employees values (141,'Trenna','Rajs','TRAJS','650.121.8009','17-oct-03','ST_CLERK',3500,NULL,124,50);
insert into employees values (142,'Curtis','Davies','CDAVIES','650.121.2994','29-jan-05','ST_CLERK',3100,NULL,124,50);
insert into employees values (143,'Randall','Matos','RMATOS','650.121.2874','15-mar-06','ST_CLERK',2600,NULL,124,50);
insert into employees values (144,'Peter','Vargas','PVARGAS','650.121.2004','09-jul-06','ST_CLERK',2500,NULL,124,50);
insert into employees values (145,'John','Russell','JRUSSEL','011.44.1344.429268','01-oct-04','SA_MAN',14000,.4,100,80);
insert into employees values (146,'Karen','Partners','KPARTNER','011.44.1344.467268','05-jan-05','SA_MAN',13500,.3,100,80);
insert into employees values (147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','10-mar-05','SA_MAN',12000,.3,100,80);
insert into employees values (148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','15-oct-07','SA_MAN',11000,.3,100,80);
insert into employees values (149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','29-jan-08','SA_MAN',10500,.2,100,80);
insert into employees values (150,'Peter','Tucker','PTUCKER','011.44.1344.129268','30-jan-05','SA_REP',10000,.3,145,80);
insert into employees values (151,'David','Bernstein','DBERNSTE','011.44.1344.345268','24-mar-05','SA_REP',9500,.25,145,80);
insert into employees values (152,'Peter','Hall','PHALL','011.44.1344.478968','20-aug-05','SA_REP',9000,.25,145,80);
insert into employees values (153,'Christopher','Olsen','COLSEN','011.44.1344.498718','30-mar-06','SA_REP',8000,.2,145,80);
insert into employees values (154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','09-dec-06','SA_REP',7500,.2,145,80);
insert into employees values (155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','23-nov-07','SA_REP',7000,.15,145,80);
insert into employees values (156,'Janette','King','JKING','011.44.1345.429268','30-jan-04','SA_REP',10000,.35,146,80);
insert into employees values (157,'Patrick','Sully','PSULLY','011.44.1345.929268','04-mar-04','SA_REP',9500,.35,146,80);
insert into employees values (158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','01-aug-04','SA_REP',9000,.35,146,80);
insert into employees values (159,'Lindsey','Smith','LSMITH','011.44.1345.729268','10-mar-05','SA_REP',8000,.3,146,80);
insert into employees values (160,'Louise','Doran','LDORAN','011.44.1345.629268','15-dec-05','SA_REP',7500,.3,146,80);
insert into employees values (161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','03-nov-06','SA_REP',7000,.25,146,80);
insert into employees values (162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','11-nov-05','SA_REP',10500,.25,147,80);
insert into employees values (163,'Danielle','Greene','DGREENE','011.44.1346.229268','19-mar-07','SA_REP',9500,.15,147,80);
insert into employees values (164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','24-jan-08','SA_REP',7200,.1,147,80);
insert into employees values (165,'David','Lee','DLEE','011.44.1346.529268','23-feb-08','SA_REP',6800,.1,147,80);
insert into employees values (166,'Sundar','Ande','SANDE','011.44.1346.629268','24-mar-08','SA_REP',6400,.1,147,80);
insert into employees values (167,'Amit','Banda','ABANDA','011.44.1346.729268','21-apr-08','SA_REP',6200,.1,147,80);
insert into employees values (168,'Lisa','Ozer','LOZER','011.44.1343.929268','11-mar-05','SA_REP',11500,.25,148,80);
insert into employees values (169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','23-mar-06','SA_REP',10000,.2,148,80);
insert into employees values (170,'Tayler','Fox','TFOX','011.44.1343.729268','24-jan-06','SA_REP',9600,.2,148,80);
insert into employees values (171,'William','Smith','WSMITH','011.44.1343.629268','23-feb-07','SA_REP',7400,.15,148,80);
insert into employees values (172,'Elizabeth','Bates','EBATES','011.44.1343.529268','24-mar-07','SA_REP',7300,.15,148,80);
insert into employees values (173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','21-apr-08','SA_REP',6100,.1,148,80);
insert into employees values (174,'Ellen','Abel','EABEL','011.44.1644.429267','11-may-04','SA_REP',11000,.3,149,80);
insert into employees values (175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','19-mar-05','SA_REP',8800,.25,149,80);
insert into employees values (176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','24-mar-06','SA_REP',8600,.2,149,80);
insert into employees values (177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','23-apr-06','SA_REP',8400,.2,149,80);
insert into employees values (178,'Kimberely','Grant','KGRANT','011.44.1644.429263','24-may-07','SA_REP',7000,.15,149,NULL);
insert into employees values (179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','04-jan-08','SA_REP',6200,.1,149,80);
insert into employees values (180,'Winston','Taylor','WTAYLOR','650.507.9876','24-jan-06','SH_CLERK',3200,NULL,120,50);
insert into employees values (181,'Jean','Fleaur','JFLEAUR','650.507.9877','23-feb-06','SH_CLERK',3100,NULL,120,50);
insert into employees values (182,'Martha','Sullivan','MSULLIVA','650.507.9878','21-jun-07','SH_CLERK',2500,NULL,120,50);
insert into employees values (183,'Girard','Geoni','GGEONI','650.507.9879','03-feb-08','SH_CLERK',2800,NULL,120,50);
insert into employees values (184,'Nandita','Sarchand','NSARCHAN','650.509.1876','27-jan-04','SH_CLERK',4200,NULL,121,50);
insert into employees values (185,'Alexis','Bull','ABULL','650.509.2876','20-feb-05','SH_CLERK',4100,NULL,121,50);
insert into employees values (186,'Julia','Dellinger','JDELLING','650.509.3876','24-jun-06','SH_CLERK',3400,NULL,121,50);
insert into employees values (187,'Anthony','Cabrio','ACABRIO','650.509.4876','07-feb-07','SH_CLERK',3000,NULL,121,50);
insert into employees values (188,'Kelly','Chung','KCHUNG','650.505.1876','14-jun-05','SH_CLERK',3800,NULL,122,50);
insert into employees values (189,'Jennifer','Dilly','JDILLY','650.505.2876','13-aug-05','SH_CLERK',3600,NULL,122,50);
insert into employees values (190,'Timothy','Gates','TGATES','650.505.3876','11-jul-06','SH_CLERK',2900,NULL,122,50);
insert into employees values (191,'Randall','Perkins','RPERKINS','650.505.4876','19-dec-07','SH_CLERK',2500,NULL,122,50);
insert into employees values (192,'Sarah','Bell','SBELL','650.501.1876','04-feb-04','SH_CLERK',4000,NULL,123,50);
insert into employees values (193,'Britney','Everett','BEVERETT','650.501.2876','03-mar-05','SH_CLERK',3900,NULL,123,50);
insert into employees values (194,'Samuel','McCain','SMCCAIN','650.501.3876','01-jul-06','SH_CLERK',3200,NULL,123,50);
insert into employees values (195,'Vance','Jones','VJONES','650.501.4876','17-mar-07','SH_CLERK',2800,NULL,123,50);
insert into employees values (196,'Alana','Walsh','AWALSH','650.507.9811','24-apr-06','SH_CLERK',3100,NULL,124,50);
insert into employees values (197,'Kevin','Feeney','KFEENEY','650.507.9822','23-may-06','SH_CLERK',3000,NULL,124,50);
insert into employees values (198,'Donald','OConnell','DOCONNEL','650.507.9833','21-jun-07','SH_CLERK',2600,NULL,124,50);
insert into employees values (199,'Douglas','Grant','DGRANT','650.507.9844','13-jan-08','SH_CLERK',2600,NULL,124,50);
insert into employees values (200,'Jennifer','Whalen','JWHALEN','515.123.4444','17-sep-03','AD_ASST',4400,NULL,101,10);
insert into employees values (201,'Michael','Hartstein','MHARTSTE','515.123.5555','17-feb-04','MK_MAN',13000,NULL,100,20);
insert into employees values (202,'Pat','Fay','PFAY','603.123.6666','17-aug-05','MK_REP',6000,NULL,201,20);
insert into employees values (203,'Susan','Mavris','SMAVRIS','515.123.7777','07-jun-02','HR_REP',6500,NULL,101,40);
insert into employees values (204,'Hermann','Baer','HBAER','515.123.8888','07-jun-02','PR_REP',10000,NULL,101,70);
insert into employees values (205,'Shelley','Higgins','SHIGGINS','515.123.8080','07-jun-02','AC_MGR',12008,NULL,101,110);
insert into employees values (206,'William','Gietz','WGIETZ','515.123.8181','07-jun-02','AC_ACCOUNT',8300,NULL,205,110);

oracle数据表创建之后就开始窗口函数的学习

1、分析函数的形式

分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，
他们的使用形式如下：分析函数名(参数) over (partition by 子句 order by 子句 rows/range… 子句)
(注：若窗口函数内和sql语句末尾共存在两个order by
a) order by 字段两者一致：即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容一样，
那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；
b) order by 字段两者不一致：即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容不一样，
那么sql语句中的排序将最后在分析函数分析结束后执行排序。)

注意Partition by可以有多个字段。

首先接个例子说明一下使用窗口函数的优点

例如：计算部门的平均工资和部门内部员工的差值

不运用窗口函数的传统写法为：

select t1.FIRST_NAME, (t1.SALARY - t3.avgs) as gaps
  from EMPLOYEES t1,
       (select t2.EMPLOYEE_ID, avg(t2.SALARY) as avgs
          from EMPLOYEES t2
         group by t2.EMPLOYEE_ID) t3
 where t1.EMPLOYEE_ID = t3.EMPLOYEE_ID;

使用窗口函数的写法为：

select FIRST_NAME,SALARY,
(e.SALARY-avg(e.SALARY) over(partition by e.JOB_ID )) as salary_gaps
FROM EMPLOYEES e

是不是觉得瞬间变得非常的简单。
平均工资和部门内部员工的差值

-- 累计收入求和的需要重新按 EMPLOYEE_ID 或者EMPLOYEE_ID、DEPARTMENT_ID排一下序,不然有些乱看的不是很清楚，基本常用的就是上面几种形式
select e.DEPARTMENT_ID,
       e.EMPLOYEE_ID,
       e.FIRST_NAME,
       e.SALARY,
       sum(e.SALARY)over()  总收入,
       sum(e.SALARY)over(partition by e.DEPARTMENT_ID)  部门总收入,--按部门分组求和
       sum(e.SALARY)over(order by e.EMPLOYEE_ID)  员工累计收入,--按照员工编号（empno）的排序取累计收入和
       sum(e.SALARY)over(partition by e.DEPARTMENT_ID order by e.EMPLOYEE_ID)  员工部门内累计收入,--按部门（deptno）分组，同时按员工编号（empno）排序取员工部门内累计收入和
       sum(e.SALARY)over(partition by e.DEPARTMENT_ID order by e.EMPLOYEE_ID rows between unbounded preceding and unbounded following)  部门总收入2 --可指定范围，结果同上
  from EMPLOYEES e;

结果展示

注：

– --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
– --unbounded：不受控制的，无限的
– --preceding：在…之前
– --following：在…之后
– rows between unbounded preceding and unbounded following 表中的所有记录
– rows between unbounded preceding and current row 是指第一行至当前行的汇总
– rows between current row and unbounded following 指当前行到最后一行的汇总
– rows between 1 preceding and current row 是指当前行的上一行(rownum-1)到当前行的汇总
– rows between 1 preceding and 2 following 是指当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总

2.oracle中的with as 的函数用法

With查询语句不是以select开始的，而是以“WITH”关键字开头
可认为在真正进行查询之前预先构造了一个临时表，之后便可多次使用它做进一步的分析和处理

WITH Clause方法的优点
增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标。

 第一种使用子查询的方法表被扫描了两次，而使用WITH Clause方法，表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。

 另外，观察WITH Clause方法执行计划，其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。

with as 的基础语法

-- with as的最基础的语法
with tempName as (select ....)
select ...

--针对一个别名
with tmp as (select * from tb_name)

--针对多个别名
with
   tmp as (select * from tb_name),
   tmp2 as (select * from tb_name2),
   tmp3 as (select * from tb_name3),
   …

with as 相当于虚拟视图。

with as短语，也叫做子查询部分(subquery factoring)，可以让你做很多事情，定义一个sql片断，该sql片断会被整个sql语句所用到。

有的时候，是为了让sql语句的可读性更高些，也有可能是在union all的不同部分，作为提供数据的部分。

--相当于建了个e临时表
with e as (select * from scott.emp e where e.empno=7499)
select * from e;
 
--相当于建了e、d临时表
with
     e as (select * from scott.emp),
     d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

with as优点

增加了sql的易读性，如果构造了多个子查询，结构会更清晰；
更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标

3.有关ROWS/RANGE窗口函数用法

-- 首先通过with as创建一个基础表，再通过对基础表的操作实现ROWS/RANGE的函数功能。
with t as
 (select (case
 when level in (1, 2) then
 1
 when level in (4, 5) then
 6
 else
 level
 end) id
 from dual
 connect by level < 10)
 select id,
 sum(id) over(order by id) default_sum,
 sum(id) over(order by id range between unbounded preceding and current row) range_unbound_sum,
 sum(id) over(order by id rows between unbounded preceding and current row) rows_unbound_sum,
 sum(id) over(order by id range between 1 preceding and 2 following) range_sum,
 sum(id) over(order by id rows between 1 preceding and 2 following) rows_sum
from t;

展示结果:
ROWS/RANG实现函数功能

注解：

1、窗口子句一般和order by 子句同时使用，且如果指定了order by 子句未指定窗口子句，则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW，如上例结果集中的defult_sum等于range_unbound_sum；
2、如果分析函数没有指定ORDER BY子句，也就不存在ROWS/RANGE窗口的计算；
3、range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内，如上例中range_sum（即range 1 preceing and 2 following）例的分析结果：
当id=1时，是sum为1-1<=id<=1+2 的和，即sum=1+1+3=5(取id为1,1,3);
当id=3时，是sum为3-1<=id<=3+2 的和，即sum=3（取id为3）；
当id=6时，是sum为6-1<=id<=6+2 的和，即sum=6+6+6+7+8=33（取id为6，6，6，7，8）;
以此类推下去，结果如上例中所示。
4、rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关），如上例中rows_sum例结果，是取前1行和后2行数据的求和，分析上例rows_sum的结果：
当id=1（第一个1时）时，前一行没数，后二行分别是1和3，sum=1+1+3=5；
当id=3时，前一行id=1,后二行id都为6，则sum=1+3+6+6=16；
以此类推下去，结果如上例所示。
注：行比较分析函数lead和lag无window(窗口)子句。

常用分析函数详细汇总

4.row_number() over：伪列（添加一个序号）；注：此分析函数必须要加order by排序

--按empno排序
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,row_number() over(order by EMPLOYEE_ID) rn from EMPLOYEES;

按empno排序

--（按部门分组，empno排序）添加一个伪列（和rownum类似）
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,row_number() over(partition by DEPARTMENT_ID order by EMPLOYEE_ID) rn from EMPLOYEES;
--此分析函数在对数据去重时用的比较多

（按部门分组，empno排序）添加一个伪列（和rownum类似）

--每个部门只取一条数据（当然 partition by 后面可以按需求跟多个字段，来达到你想要的筛选目的）
select * from(
  select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,row_number() over(partition by DEPARTMENT_ID order by EMPLOYEE_ID) rn from EMPLOYEES)
where rn = 1;

每个部门只取一条数据（当然 partition by 后面可以按需求跟多个字段，来达到你想要的筛选目的）

5、count() over()：计数

select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,count(*) over() from EMPLOYEES;--总计数

在这里插入图片描述

--按照empno累计计数
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,count(*) over(order by EMPLOYEE_ID) from EMPLOYEES;

在这里插入图片描述

--按照deptno分组计数
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,count(*) over(partition by DEPARTMENT_ID) from EMPLOYEES;

在这里插入图片描述

--按照deptno分组并累计计数
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,count(*) over(partition by DEPARTMENT_ID order by EMPLOYEE_ID) from EMPLOYEES;

在这里插入图片描述

6、sum() over()求和 / avg() over()求平均 /min() over()；max() over()：求最小最大

计算结果与count() over()相类似

-- 3、sum() over()：求和

select  EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,sum(SALARY) over() from EMPLOYEES;--总和
select  EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,sum(SALARY) over(order by EMPLOYEE_ID) from EMPLOYEES;--按empno累计求和
select  EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,sum(SALARY) over(partition by DEPARTMENT_ID) from EMPLOYEES;--按照deptno分组求和
select  EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,sum(SALARY) over(partition by DEPARTMENT_ID order by EMPLOYEE_ID) from EMPLOYEES;--按照deptno分组并按empno累计求和

-- 4、avg() over()：求平均
select  EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,avg(SALARY) over() from EMPLOYEES;--总和
select  EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,avg(SALARY) over(order by EMPLOYEE_ID) from EMPLOYEES;--按empno累计求和
select  EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,avg(SALARY) over(partition by DEPARTMENT_ID) from EMPLOYEES;--按照deptno分组求和
select  EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,avg(SALARY) over(partition by DEPARTMENT_ID order by EMPLOYEE_ID) from EMPLOYEES;--按照deptno分组并按empno累计求和
-- 5、min() over()；max() over()：求最小最大

select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,min(SALARY) over() 最小金额
      ,max(SALARY) over() 最大金额 from EMPLOYEES;--总最小（大）
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,min(SALARY) over(order by EMPLOYEE_ID) 最小金额
      ,max(SALARY) over(order by EMPLOYEE_ID) 最大金额 from EMPLOYEES;--按empno排序并一个个递增后的最小（大）
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,min(SALARY) over(partition by DEPARTMENT_ID) 最小金额
      ,max(SALARY) over(partition by DEPARTMENT_ID) 最大金额 from EMPLOYEES;--按deptno分组后的最小（大）
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,min(SALARY) over(partition by DEPARTMENT_ID order by EMPLOYEE_ID) 最小金额
      ,max(SALARY) over(partition by DEPARTMENT_ID order by EMPLOYEE_ID) 最大金额 from EMPLOYEES;--组内、递增累计后的最小（大）

7、rank() over()：跳跃排序；dense_rank()：连续排序；注：此分析函数同row_number() over()必须要加order by排序

--按SALARY 金额排名
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,rank() over(order by SALARY desc) 跳跃排序
      ,dense_rank() over(order by SALARY desc) 连续排序 from EMPLOYEES;

按SALARY 金额排名

--按DEPARTMENT_ID分组，组内、金额排名
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,rank() over(partition by DEPARTMENT_ID order by SALARY desc) 跳跃排序
      ,dense_rank() over(partition by DEPARTMENT_ID order by SALARY desc) 连续排序 from EMPLOYEES;

按DEPARTMENT_ID分组，组内、金额排名

8、ntile(n) over()：将数据等分成n组（不够等分的按顺序添加到每个组内）；注：必须要加order by

select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,ntile(3) over(order by EMPLOYEE_ID) from EMPLOYEES;--将数据等分3组（不够等分的按顺序添加到每个组内）

将数据等分3组（不够等分的按顺序添加到每个组内）

select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID,ntile(3) over(partition by DEPARTMENT_ID order by EMPLOYEE_ID) from EMPLOYEES;--组内再将数据均分3组

组内再将数据均分3组

9、first_value() over()：取对应第一条记录；last_value() over()：取对应最后一条记录（可加 ignore nulls 空值填充）向下（上）找最近的不为空的值还有：nth_value(value any, nth integer)：返回窗口框架中的指定值，如nth_value(salary,2),则表示返回字段salary的第二个窗口函数值

select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,first_value(SALARY ignore nulls) over() 整表sal的第一条记录
      ,last_value(SALARY ignore nulls) over()  整表sal的最后一条记录 from EMPLOYEES;

在这里插入图片描述

select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,first_value(FIRST_NAME) over(order by EMPLOYEE_ID) 第一条ename记录
      ,last_value(FIRST_NAME) over(order by EMPLOYEE_ID)  最后一条ename记录 from EMPLOYEES;--按empno顺序第一、最后一条数据

-按empno顺序第一、最后一条数据

select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,first_value(SALARY) over(partition by DEPARTMENT_ID) 第一条记录
      ,last_value(SALARY) over(partition by DEPARTMENT_ID)  最后一条记录 from EMPLOYEES;--部门组内数据第一、最后一条

部门组内数据第一、最后一条

select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,first_value(SALARY) over(partition by DEPARTMENT_ID order by EMPLOYEE_ID) 第一条记录
      ,last_value(SALARY) over(partition by DEPARTMENT_ID order by EMPLOYEE_ID)  最后一条记录 from EMPLOYEES;--部门组内、递增数据第一、最后一条

部门组内、递增数据第一、最后一条

10、keep (dense_rank first/last order by …)over():配合max()/min()取集合内第一或最后一条

--取集合内第一或最后一条
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,max(SALARY) keep(dense_rank first order by SALARY desc)over() 第一条记录
      ,max(SALARY) keep(dense_rank last  order by SALARY desc)over() 最后一条记录 from EMPLOYEES;

取集合内第一或最后一条

-- 组内排序后第一或最后一条	
select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,max(SALARY) keep(dense_rank first order by SALARY desc)over(partition by DEPARTMENT_ID) 第一条记录
      ,max(SALARY) keep(dense_rank last  order by SALARY desc)over(partition by DEPARTMENT_ID) 最后一条记录 from EMPLOYEES;

组内排序后第一或最后一条

11、lag(column_name,n,若首行无填充默认为null) over() ：取出前n行数据；lead() over() ：取出前(后)第n行数据；注：必须要加order by排序（11g中支持ignore nulls）

select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,lag(SALARY,1) over(order by EMPLOYEE_ID)  sal的上一条记录
      ,lead(SALARY,1) over(order by EMPLOYEE_ID) sal的下一条记录 from EMPLOYEES;--取出前(后)第1行数据

取出前(后)第1行数据

select EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,DEPARTMENT_ID
      ,lag(SALARY,2,0) over(partition by DEPARTMENT_ID order by EMPLOYEE_ID)  sal的上一条记录
      ,lead(SALARY,2,0) over(partition by DEPARTMENT_ID order by EMPLOYEE_ID) sal的下一条记录 from EMPLOYEES;--取出组内前(后)第2行数据

取出组内前(后)第2行数据

oracle函数的大全

地址： https://pan.baidu.com/s/1vQuVoe3EjUw3xPuoT57d4g

密码：trwn

NeoInHeart

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
从简单的数据表中学习oracle的窗口函数使用

oracle数据库创建及表的创建1、创建表空间：格式: create tablespace 表间名 datafile ‘数据文件名’ size 表空间大小--在oracle数据库中创建一个表空间类似于mysql数据库中创建的databasecreate tablespace data_test datafile 'e:\oracle\oradata\test\data_1.dbf' size 2000M;(*数据文件名包含全路径, 表空间大小 2000M 表是 2000兆)2、建好表空间后,
复制链接

扫一扫