ORACLE中OVER函数的用法
oracle over函数详解今天在javaeye上看到一道面试题，很多人都用over函数解决的特意查了一下它的用法SQL> select deptno,ename,sal2 from emp3 order by deptno;DEPTNO ENAME SAL---------- ---------- ----------10 CLARK 2450KING 5000MILLER 130020 SMITH 800ADAMS 1100FORD 3000SCOTT 3000JONES 297530 ALLEN 1600BLAKE 2850MARTIN 1250JAMES 950TURNER 1500WARD 1250已选择14行。
2.先来一个简单的，注意over(...)条件的不同，使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,注意over (order by ename)如果没有order by 子句，求和就不是“连续”的，放在一起，体会一下不同之处：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 emp6 /DEPTNO ENAME SAL 连续求和总和份额(%)---------- ---------- ---------- ---------- ---------- ----------20 ADAMS 1100 1100 29025 3.7930 ALLEN 1600 2700 29025 5.5130 BLAKE 2850 5550 29025 9.8210 CLARK 2450 8000 29025 8.4420 FORD 3000 11000 29025 10.3430 JAMES 950 11950 29025 3.2720 JONES 2975 14925 29025 10.2510 KING 5000 19925 29025 17.2330 MARTIN 1250 21175 29025 4.3110 MILLER 1300 22475 29025 4.4820 SCOTT 3000 25475 29025 10.3420 SMITH 800 26275 29025 2.7630 TURNER 1500 27775 29025 5.1730 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> 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 emp9 /DEPTNO ENAME SAL 部门连续求和部门总和部门份额(%) 连续求和总和总份额(%)------ ------ ----- ------------ ---------- ----------- ---------- ------ ---------- 10 CLARK 2450 2450 8750 28 2450 29025 8.44KING 5000 7450 8750 57.14 7450 29025 17.23MILLER 1300 8750 8750 14.86 8750 29025 4 .4820 ADAMS 1100 1100 10875 10.11 9850 29025 3.79FORD 3000 4100 10875 27.59 12850 29025 10.34JONES 2975 7075 10875 27.36 15825 29025 10.25SCOTT 3000 10075 10875 27.59 18825 29025 10.34SMITH 800 10875 10875 7.36 19625 29025 2.7630 ALLEN 1600 1600 9400 17.02 21225 29025 5.51BLAKE 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 290254.31TURNER 1500 8150 9400 15.96 27775 290255.17WARD 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) sum3 from emp;DEPTNO ENAME SAL DEPT_SUM SUM---------- ---------- ---------- ---------- ----------10 MILLER 1300 1300 1300CLARK 2450 3750 3750KING 5000 8750 875020 SMITH 800 800 9550ADAMS 1100 1900 10650JONES 2975 4875 13625SCOTT 3000 10875 19625FORD 3000 10875 1962530 JAMES 950 950 20575WARD 1250 3450 23075MARTIN 1250 3450 23075TURNER 1500 4950 24575ALLEN 1600 6550 26175BLAKE 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) sum4 from emp;DEPTNO ENAME SAL DEPT_SUM SUM---------- ---------- ---------- ---------- ----------30 BLAKE 2850 2850 2850ALLEN 1600 4450 4450TURNER 1500 5950 5950WARD 1250 8450 8450MARTIN 1250 8450 8450JAMES 950 9400 940020 SCOTT 3000 6000 15400FORD 3000 6000 15400JONES 2975 8975 18375ADAMS 1100 10075 19475SMITH 800 10875 2027510 KING 5000 5000 25275CLARK 2450 7450 27725MILLER 1300 8750 29025已选择14行。
6.体会：在"... from emp;"后面不要加order by 子句，使用的分析函数的(partition bydeptno 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) sum3 from emp4 order by deptno desc;DEPTNO ENAME SAL DEPT_SUM SUM---------- ---------- ---------- ---------- ----------30 JAMES 950 950 20575WARD 1250 3450 23075MARTIN 1250 3450 23075TURNER 1500 4950 24575ALLEN 1600 6550 26175BLAKE 2850 9400 2902520 SMITH 800 800 9550ADAMS 1100 1900 10650JONES 2975 4875 13625SCOTT 3000 10875 19625FORD 3000 10875 1962510 MILLER 1300 1300 1300CLARK 2450 3750 3750KING 5000 8750 8750已选择14行row_number() over ([partition by col1] order by col2) ) as 别名表示根据col1分组，在分组部根据 col2排序而这个“别名”的值就表示每组部排序后的顺序编号（组连续的唯一的）,[partition by col1] 可省略。
myql数据库，sql横排转竖排以及竖排转横排，oracle的over函数的使用
myql数据库，sql横排转竖排以及竖排转横排，oracle的over函数的使⽤⼀、引⾔ 前些⽇⼦遇到了⼀个sql语句的横排转竖排以及竖排转横排的问题，现在该总结⼀下，具体问题如下：这⾥的第⼆题和第三题和下⾯所讲述的学⽣的成绩表是相同的，这⾥给⼤家留⼀下⼀个念想，⼤家可以⾃⼰做做上⾯的笔试题。
我主要针对的是第⼆题和第三题来做讲解，第⼀题相信⼤家都会做，这⾥就不赘述了，直接进⼊正题！⼆、问题详解 1、我们先来说说第⼆题， （1）⾸先我们先创建⼀个表，⽤实际来说话，新建⼀个tb表，DROP TABLE tb;CREATE TABLE tb(name varchar(10),subject VARCHAR(10),score NUMERIC);INSERT INTO tb(name,SUBJECT,score) VALUES('张三','语⽂',74);INSERT INTO tb(name,SUBJECT,score) VALUES('张三','数学',83);insert into tb(Name , Subject , score) values('张三' ,'物理' , 93);insert into tb(Name , Subject , score) values('李四' , '语⽂' , 74);insert into tb(Name , Subject , score) values('李四' , '数学' , 84);insert into tb(Name , Subject , score) values('李四' , '物理' , 94);SELECT*FROM tb;（2）最初的查询结果如图所⽰（3）下⾯我们开始竖排转横排SELECT NAME 姓名,MAX(CASE SUBJECT WHEN'语⽂'THEN score ELSE0END) 语⽂,MAX(CASE SUBJECT WHEN'数学'THEN score ELSE0END) 数学,MAX(CASE SUBJECT WHEN'物理'THEN score ELSE0END) 物理FROM tb GROUP BY NAME结果是：（4）进⼀步的拓展，假如我们想要的结果为下图SELECT NAME 姓名,MAX(CASE SUBJECT WHEN'语⽂'THEN score ELSE0END) 语⽂,MAX(CASE SUBJECT WHEN'数学'THEN score ELSE0END) 数学,MAX(CASE SUBJECT WHEN'物理'THEN score ELSE0END) 物理,SUM(score) AS总分,AVG(score) AS平均分FROM tb GROUP BY NAME2、下⾯来讨论⼀下横排转竖排的问题（1）⾸先创建表tb1，CREATE TABLE tb1(姓名VARCHAR(10),语⽂ NUMERIC,数学 NUMERIC,物理 NUMERIC);insert into tb1(姓名 , 语⽂ , 数学 , 物理) values('张三',74,83,93);insert into tb1(姓名 , 语⽂ , 数学 , 物理) values('李四',74,84,94);SELECT*FROM tb1;如图所⽰：（2）横排转竖排⽅法⼀：select姓名as name,'语⽂'as subject,语⽂as score from tb1unionselect姓名as name,'数学'as subject,数学as score from tb1unionselect姓名as name,'物理'as subject,物理as score from tb1order by name⽅法⼆：SELECT*FROM (SELECT姓名as NAME,'语⽂'AS SUBJECT, 语⽂AS score from tb1 UNION SELECT姓名AS NAME,'数学'AS SUBJECT , 数学AS score from tb1 UNION SELECT姓名AS NAME,'物理'AS SUBJECT, 物理AS score FROM tb1)t ORDER BY NAME结果为：3、下⾯讨论⼀下第三题（1）创建表tb2CREATE TABLE tb2(YEAR NUMBER,salary NUMBER);INSERT INTO tb2(YEAR,salary) VALUES(2000,1000);INSERT INTO tb2(YEAR,salary) VALUES(2001,2000);INSERT INTO tb2(YEAR,salary) VALUES(2002,3000);INSERT INTO tb2(YEAR,salary) VALUES(2003,4000);SELECT*FROM tb2;如图：（2）利⽤over函数完成所需要求，select year,sum(salary) over(order by salary) from tb2考察开窗函数的，。
Oracle分析函数1
分析函数(OVER) (1)分析函数2(Rank, Dense_rank, row_number) .............................................................................. 错误！未定义书签。
分析函数3(Top/Bottom N、First/Last、NTile) .......................................................................... 错误！未定义书签。
窗口函数....................................................................................................................................... 错误！未定义书签。
报表函数....................................................................................................................................... 错误！未定义书签。
分析函数总结............................................................................................................................... 错误！未定义书签。
26个分析函数.............................................................................................................................. 错误！未定义书签。
oracle over partition by用法
oracle over partition by用法在Oracle 数据库中，`OVER` 子句与`PARTITION BY` 子句一起使用，通常用于在SQL 窗口函数中定义分区。
`PARTITION BY` 子句用于将结果集划分为不同的分区，然后窗口函数将在每个分区内独立执行。
以下是一个简单的例子，演示了如何在Oracle 中使用`OVER PARTITION BY`：假设有一个名为`sales` 的表，包含`product_id`、`sales_date` 和`revenue` 列。
我们想要计算每个产品的销售总额，并在每个产品内进行分区：```sqlSELECTproduct_id,sales_date,revenue,SUM(revenue) OVER (PARTITION BY product_id ORDER BY sales_date) AS running_total FROMsales;```在这个查询中，`SUM(revenue) OVER (PARTITION BY product_id ORDER BY sales_date)` 表示计算每个产品的销售总额，同时在每个产品内按照销售日期排序。
`PARTITION BY` 子句将结果集划分为不同的分区，每个分区都有相同的`product_id`。
然后，`SUM` 窗口函数计算了每个分区内的销售总额，并在每个分区内按照`sales_date` 进行排序。
这样，对于每个产品，你都会得到一个包含销售日期、销售额和在该日期之前的销售总额的结果集。
总的来说，`OVER PARTITION BY` 是在窗口函数中使用的一种强大的功能，用于在结果集中定义分区，以便对每个分区应用窗口函数。
oracle lagover函数用法
oracle lagover函数用法一、概述Oracle数据库中的Lagover函数是一种用于获取滞后值（Lag）和领先值（Lead）的函数，它可以帮助您在查询中获取数据之间的差异和关系。
Lagover函数可以根据指定的列或表达式，在给定的窗口区域内返回滞后值和领先值的结果。
二、Lagover函数的语法Lagover函数的语法如下：```scssLagover(expression, partition_by_clause, order_by_clause, lead_lag)```参数说明：* `expression`：要比较的列或表达式。
* `partition_by_clause`：可选参数，用于指定根据哪个列或表达式对数据进行分区。
* `order_by_clause`：可选参数，用于指定数据的排序顺序。
* `lead_lag`：指定要返回的滞后值和领先值的数量，可以是正数（表示滞后）或负数（表示领先）。
三、使用示例下面是一些使用Lagover函数的示例，展示了如何使用它来获取数据之间的差异和关系。
1. 获取某一列的滞后值：```sqlSELECT lag(column_name) OVER (ORDER BY column_name) AS lag_valueFROM table_name;```这将返回指定列的上一行值，即滞后值。
2. 获取某一列的领先值：```sqlSELECT lead(column_name) OVER (ORDER BY column_name) AS lead_valueFROM table_name;```这将返回指定列的下-一行值，即领先值。
3. 使用partition_by_clause对数据进行分区：```sqlSELECT lag(column_name) OVER (PARTITION BYpartition_column ORDER BY column_name) AS lag_value FROM table_name;```这将根据指定的分区列对数据进行分区，并返回每个分区内的滞后值。
Oracle查询中OVER（PARTITIONBY..）用法
Oracle查询中OVER（PARTITIONBY..）⽤法为了⽅便⼤家学习和测试，所有的例⼦都是在Oracle⾃带⽤户Scott下建⽴的。
注：标题中的红⾊order by是说明在使⽤该⽅法的时候必须要带上order by。
⼀、rank()/dense_rank() over(partition by ...order by ...)现在客户有这样⼀个需求，查询每个部门⼯资最⾼的雇员的信息，相信有⼀定oracle应⽤知识的同学都能写出下⾯的SQL语句：select e.ename, e.job, e.sal, e.deptnofrom scott.emp e,(select e.deptno, max(e.sal) sal from scott.emp e group by e.deptno) mewhere e.deptno = me.deptnoand e.sal = me.sal;在满⾜客户需求的同时，⼤家应该习惯性的思考⼀下是否还有别的⽅法。
这个是肯定的，就是使⽤本⼩节标题中rank()over(partition by...)或dense_rank() over(partition by...)语法，SQL分别如下：select e.ename, e.job, e.sal, e.deptnofrom (select e.ename,e.job,e.sal,e.deptno,rank() over(partition by e.deptno order by e.sal desc) rankfrom scott.emp e) ewhere e.rank = 1;select e.ename, e.job, e.sal, e.deptnofrom (select e.ename,e.job,e.sal,e.deptno,dense_rank() over(partition by e.deptno order by e.sal desc) rankfrom scott.emp e) ewhere e.rank = 1;为什么会得出跟上⾯的语句⼀样的结果呢？这⾥补充讲解⼀下rank()/dense_rank() over(partition by e.deptno order by e.sal desc)语法。
oracle中over函数用法
oracle中over函数用法（实用版）目录1.Oracle 中 over 函数的概述2.over 函数的基本语法与参数3.over 函数的使用场景与实例4.over 函数与其他分析函数的配合使用5.总结正文一、Oracle 中 over 函数的概述Oracle 中的 over 函数是一种分析函数，用于对查询结果进行分区和排序。
它可以让我们在查询成绩时，按照不同的条件对数据进行汇总和分析，从而得到更加精确和具体的结果。
二、over 函数的基本语法与参数over 函数的基本语法如下：```over(partition, by, expr2, order, by, expr3)```其中，各个参数的含义如下：- partition：用于对结果进行分区的条件，可以是一个表分区或者一个列；- by：指定分区的顺序，可以是升序（ASC）或降序（DESC）；- expr2：指定分区内的排序条件，可以是一个列或者一个表达式；- order：指定排序的顺序，可以是升序（ASC）或降序（DESC）；- by：指定排序的列名；- expr3：可选参数，用于指定在每个分区内需要计算的聚合函数，如 sum、avg、count 等。
三、over 函数的使用场景与实例over 函数通常与 rownumber()、rank() 和 denserank、lag() 和lead() 等分析函数配合使用，以实现更加复杂的查询需求。
以下是一些常见的使用场景与实例：1.按照班级统计每个班级的总分和平均分：```select over(partition, by, t.class) sum(t.score) astotal_score, over(partition, by, t.class) avg(t.score) as average_scorefrom tscore t, ts_student swhere t.student_id = s.idorder by s.class;```2.按照时间分区，统计每个时间段内的总销售额：```select to_char(order_date, "YYYY-MM-DD") as sales_date, over(partition, by, to_char(order_date, "YYYY-MM-DD"))sum(sales_amount) as total_salesfrom salesorder by order_date;```3.计算每个学生的成绩排名：```select student_id, over(partition, by, rank()) rank_score from (select student_id, score, rownumber() over(order by score) as rankfrom exams) t;```四、over 函数与其他分析函数的配合使用over 函数可以与其他分析函数相互配合，以实现更加复杂的数据分析需求。
Oracle开发之分析函数简介Over用法
Oracle开发之分析函数简介Over⽤法⼀、Oracle分析函数简介：在⽇常的⽣产环境中，我们接触得⽐较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者⾄少说对响应的时间多长有⼀定的要求；其次这些系统的业务逻辑⼀般⽐较复杂，可能需要经过多次的运算。
⽐如我们经常接触到的电⼦商城。
在这些系统之外，还有⼀种称之为OLAP的系统(即Online Aanalyse Process)，这些系统⼀般⽤于系统决策使⽤。
通常和数据仓库、数据分析、数据挖掘等概念联系在⼀起。
这些系统的特点是数据量⼤，对实时响应的要求不⾼或者根本不关注这⽅⾯的要求，以查询、统计操作为主。
我们来看看下⾯的⼏个典型例⼦：①查找上⼀年度各个销售区域排名前10的员⼯②按区域查找上⼀年度订单总额占区域订单总额20%以上的客户③查找上⼀年度销售最差的部门所在的区域④查找上⼀年度销售最好和最差的产品我们看看上⾯的⼏个例⼦就可以感觉到这⼏个查询和我们⽇常遇到的查询有些不同，具体有：①需要对同样的数据进⾏不同级别的聚合操作②需要在表内将多条数据和同⼀条数据进⾏多次的⽐较③需要在排序完的结果集上进⾏额外的过滤操作⼆、Oracle分析函数简单实例：下⾯我们通过⼀个实际的例⼦：按区域查找上⼀年度订单总额占区域订单总额20%以上的客户，来看看分析函数的应⽤。
【1】测试环境：复制代码代码如下:SQL> desc orders_tmp;Name Null? Type----------------------- -------- ----------------CUST_NBR NOT NULL NUMBER(5)REGION_ID NOT NULL NUMBER(5)SALESPERSON_ID NOT NULL NUMBER(5)YEAR NOT NULL NUMBER(4)MONTH NOT NULL NUMBER(2)TOT_ORDERS NOT NULL NUMBER(7)TOT_SALES NOT NULL NUMBER(11,2)【2】测试数据：复制代码代码如下:SQL> select * from orders_tmp;CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES---------- ---------- -------------- ---------- ---------- ---------- ----------11 7 11 2001 7 2 122044 5 4 2001 10 2 378027 6 7 2001 2 3 375010 6 8 2001 1 2 2169110 6 7 2001 2 3 4262415 7 12 2000 5 6 2412 7 9 2000 6 2 506581 52 20003 2 444941 5 1 2000 92 748642 5 4 20003 2 350602 5 4 2000 4 4 64542 5 1 2000 10 4 355804 5 4 2000 12 2 3919013 rows selected.【3】测试语句：复制代码代码如下:SQL> select o.cust_nbr customer,o.region_id region,sum(o.tot_sales) cust_sales,sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales from orders_tmp owhere o.year = 2001group by o.region_id, o.cust_nbr;CUSTOMER REGION CUST_SALES REGION_SALES---------- ---------- ---------- ------------4 5 37802 378027 6 3750 6806510 6 64315 6806511 7 12204 12204三、分析函数OVER解析：请注意上⾯的绿⾊⾼亮部分，group by的意图很明显：将数据按区域ID，客户进⾏分组，那么Over这⼀部分有什么⽤呢？假如我们只需要统计每个区域每个客户的订单总额，那么我们只需要group by o.region_id,o.cust_nbr就够了。
oracle中的keep和over的区别
oracle中的keep和over的区别看到很多人对于keep不理解，这里解释一下！Returns the row ranked first using DENSE_RANK2种取值：DENSE_RANK FIRSTDENSE_RANK LAST在keep (DENSE_RANK first ORDER BY sl) 结果集中再取max、min的例子。
SQL> select * from test;ID MC SL-------------------- -------------------- -------------------1 111 11 222 11 333 21 555 31 666 32 111 12 222 12 333 22 555 29 rows selectedSQL>SQL> select id,mc,sl,2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partit ion by id),3 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partit ion by id)4 from test5 ;ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)K EEP(DENSE_RANKLASTORDE-------------------- -------------------- ------------------- ------------------------------ ------------------------------1 111 1 111 6661 222 1 111 666133****1666155****1666166****16662 111 1 111 5552 222 1 111 5552 333 2 111 5552 555 2 111 5559 rows selectedSQL>不要混淆keep内（first、last）外（min、max或者其他）：min是可以对应last的max是可以对应first的SQL> select id,mc,sl,2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partit ion by id),3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partit ion by id),4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partiti on by id),5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partit ion by id)6 from test7 ;ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)K EEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTO RDE MAX(MC)KEEP(DENSE_RANKLASTORDE-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------1 111 1 111 222 555 6661 222 1 111 222 555 6661 3332 111 222 555 6661 555 3 111 222 555 6661 666 3 111 222 555 6662 111 1 111 222 333 5552 222 1 111 222 333 5552 333 2 111 222 333 5552 555 2 111 222 333 5559 rows selectedSQL> select id,mc,sl,2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partit ion by id),3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partit ion by id),4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partiti on by id),5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partit ion by id)6 from test7 ;ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)K EEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTO RDE MAX(MC)KEEP(DENSE_RANKLASTORDE-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------1 111 1 111 222 555 6661 222 1 111 222 555 6661 3332 111 222 555 6661 555 3 111 222 555 6661 666 3 111 222 555 6662 111 1 111 222 333 5552 222 1 111 222 333 5552 333 2 111 222 333 5552 555 2 111 222 333 555min(mc) keep (DENSE_RANK first ORDER BY sl) over(partitio n by id)：id等于1的数量最小的（DENSE_RANK first ）为1 111 11 222 1在这个结果中取min(mc) 就是111max(mc) keep (DENSE_RANK first ORDER BY sl) over(partiti on by id)取max(mc) 就是222；min(mc) keep (DENSE_RANK last ORDER BY sl) over(partitio n by id)：id等于1的数量最大的（DENSE_RANK first ）为1 555 31 666 3在这个结果中取min(mc) 就是222，取max(mc)就是666oracle分析函数oracle分析函数zhouwf0726 | 25 七月, 2006 12:51oracle分析函数--SQL*PLUS环境--1、GROUP BY子句--CREATE TEST TABLE AND INSERT TEST DATA.create table students(id number(15,0),area varchar2(10),stu_type varchar2(2),score number(20,2));insert into students values(1, '111', 'g', 80 );insert into students values(1, '111', 'j', 80 );insert into students values(1, '222', 'g', 89 );insert into students values(1, '222', 'g', 68 );insert into students values(2, '111', 'g', 80 );insert into students values(2, '111', 'j', 70 );insert into students values(2, '222', 'g', 60 );insert into students values(2, '222', 'j', 65 );insert into students values(3, '111', 'g', 75 );insert into students values(3, '111', 'j', 58 );insert into students values(3, '222', 'g', 58 );insert into students values(3, '222', 'j', 90 );insert into students values(4, '111', 'g', 89 );insert into students values(4, '111', 'j', 90 );insert into students values(4, '222', 'g', 90 );insert into students values(4, '222', 'j', 89 ); commit;col score format 999999999999.99--A、GROUPING SETSselect id,area,stu_type,sum(score) scorefrom studentsgroup by grouping sets((id,area,stu_type),(id,area),id) order by id,area,stu_type;/*--------理解grouping setsselect a, b, c, sum( d ) from tgroup by grouping sets ( a, b, c )等效于select * from (select a, null, null, sum( d ) from t group by a union allselect null, b, null, sum( d ) from t group by b union allselect null, null, c, sum( d ) from t group by c )*/--B、ROLLUPselect id,area,stu_type,sum(score) score from studentsgroup by rollup(id,area,stu_type)order by id,area,stu_type;/*--------理解rollupselect a, b, c, sum( d )from tgroup by rollup(a, b, c);等效于select * from (select a, b, c, sum( d ) from t group by a, b, cunion allselect a, b, null, sum( d ) from t group by a, b union allselect a, null, null, sum( d ) from t group by a union allselect null, null, null, sum( d ) from t)*/--C、CUBEselect id,area,stu_type,sum(score) score from studentsgroup by cube(id,area,stu_type)order by id,area,stu_type;/*--------理解cubeselect a, b, c, sum( d ) from tgroup by cube( a, b, c)等效于select a, b, c, sum( d ) from tgroup by grouping sets(( a, b, c ),( a, b ), ( a ), ( b, c ),( b ), ( a, c ), ( c ),() )*/--D、GROUPING/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/select decode(grouping(id),1,'all id',id) id,decode(grouping(area),1,'all area',to_char(area)) area,decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_typ e,sum(score) scorefrom studentsgroup by cube(id,area,stu_type)order by id,area,stu_type;--2、OVER()函数的使用--1、RANK()、DENSE_RANK() 的、ROW_NUMBER()、CUME_DIST()、MAX()、AVG()break on id skip 1select id,area,score from students order by id,area,score des c;select id,rank() over(partition by id order by score desc) rk,s core from students;--允许并列名次、名次不间断select id,dense_rank() over(partition by id order by score de sc) rk,score from students;--即使SCORE相同，ROW_NUMBER()结果也是不同select id,row_number() over(partition by ID order by SCORE desc) rn,score from students;select cume_dist() over(order by id) a, --该组最大row_number/所有记录row_numberrow_number() over (order by id) rn,id,area,score from stude nts;select id,max(score) over(partition by id order by score desc ) as mx,score from students;select id,area,avg(score) over(partition by id order by area) as avg,score from students; --注意有无order by的区别--按照ID求AVGselect id,avg(score) over(partition by id order by score desc rows between unbounded precedingand unbounded following ) as ag,score from students;--2、SUM()select id,area,score from students order by id,area,score des c;select id,area,score,sum(score) over (order by id,area) 连续求和, --按照OVER后边内容汇总求和sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)100*round(score/sum(score) over (),4) "份额(%)"from students;select id,area,score,sum(score) over (partition by id order by area ) 连id续求和, --按照id内容汇总求和sum(score) over (partition by id) id总和, --各id的分数总和100*round(score/sum(score) over (partition by id),4) "id份额(%)",sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)100*round(score/sum(score) over (),4) "份额(%)"from students;--4、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据select id,lag(score,1,0) over(order by id) lg,score from stude nts;select id,lead(score,1,0) over(order by id) lg,score from stud ents;--5、FIRST_VALUE()、LAST_VALUE()select id,first_value(score) over(order by id) fv,score from stu dents;select id,last_value(score) over(order by id) fv,score from stu dents;<A href=""></A>-------------------------------------------------再次理解分析函数！/************************************************************** *******************************<A href=""></A>问题提出：一个高级SQL语句问题假设有一张表，A和B字段都是NUMBER，A B1 22 33 44有这样一些数据现在想用一条SQL语句，查询出这样的数据1－》2－》3—》4就是说，A和B的数据表示一种连接的关系，现在想通过A的一个值，去查询A所对应的B值，直到B为NULL为止，不知道这个SQL语句怎么写？请教高手！谢谢*************************************************************** ******************************/--以下是利用分析函数的一个简单解答：--start with connect by可以参考CREATE TABLE TEST(COL1 NUMBER(18,0),COL2 NUMBER(18 ,0));INSERT INTO TEST VALUES(1,2);INSERT INTO TEST VALUES(2,3);INSERT INTO TEST VALUES(3,4);INSERT INTO TEST VALUES(4,NULL);INSERT INTO TEST VALUES(5,6);INSERT INTO TEST VALUES(6,7);INSERT INTO TEST VALUES(7,8);INSERT INTO TEST VALUES(8,NULL);INSERT INTO TEST VALUES(9,10);INSERT INTO TEST VALUES(10,NULL);INSERT INTO TEST VALUES(11,12);INSERT INTO TEST VALUES(12,13);INSERT INTO TEST VALUES(13,14);INSERT INTO TEST VALUES(14,NULL);CREATE TABLE TEST(COL1 NUMBER(18,0),COL2 NUMBER(18 ,0));INSERT INTO TEST VALUES(1,2);INSERT INTO TEST VALUES(2,3);INSERT INTO TEST VALUES(3,4);INSERT INTO TEST VALUES(4,NULL);INSERT INTO TEST VALUES(5,6);INSERT INTO TEST VALUES(6,7);INSERT INTO TEST VALUES(7,8);INSERT INTO TEST VALUES(8,NULL);INSERT INTO TEST VALUES(9,10);INSERT INTO TEST VALUES(10,NULL);select max(col) from(select SUBSTR(col,1,CASE WHEN INSTR(col,'->')>0 THEN IN STR(col,'->') - 1 ELSE LENGTH(col) END) FLAG,col from( select ltrim(sys_connect_by_path(col1,'->'),'->') col from ( select col1,col2,CASE WHEN LAG(COL2,1,NULL) OVER(ORDE R BY ROWNUM) IS NULL THEN 1 ELSE 0 END FLAG from test)start with flag=1 connect by col1=prior col2))group by flag。
oracleover（）的使用和需要特别注意的地方
oracleover（）的使用和需要特别注意的地方STUDENT表数据。
SQL> select * from students;ID CLASS NAME AGE COURSE SCORE---------- ---------- ---------- ---------- ---------- ---------- 325 三班张1 23 英语 67326 二班李2 26 语文 47327 三班刘2 22 数学 87328 四班常1 22 自然 99329 一班张3 23 英语 77330 三班黄1 24 数学 97331 三班田1 28 数学 87332 四班达1 22 自然 97333 三班叶1 26 英语 67334 四班胖1 26 语文 94335 三班虎1 28 数学 77ID CLASS NAME AGE COURSE SCORE---------- ---------- ---------- ---------- ---------- ---------- 336 四班水1 22 自然 93337 一班韬1 23 英语 62338 二班李1 26 语文 97339 三班辜1 28 数学 85340 一班喻1 22 自然 92341 四班杨1 23 英语 61342 二班凯1 26 语文 95343 三班子1 24 数学 84344 四班万1 22 自然 97345 一班丹1 23 英语 68346 二班小1 26 语文 93ID CLASS NAME AGE COURSE SCORE---------- ---------- ---------- ---------- ---------- ---------- 347 三班白1 25 数学 82348 四班钟1 22 自然 94349 一班宇1 23 英语 62350 三班帅1 24 语文 92351 三班男1 23 数学 87352 四班我1 22 自然 94353 一班你1 23 英语 57354 一班阿1 26 语文 87355 三班刘1 24 数学 67356 四班常1 21 自然 96357 二班秋1 23 英语 77ID CLASS NAME AGE COURSE SCORE---------- ---------- ---------- ---------- ---------- ---------- 358 二班饿2 26 语文 87359 二班胡2 22 数学 77360 二班可1 22 自然 6936 rows selected.STUDENT表结构。
Oracle开窗函数over（）（转）
Oracle开窗函数over（）（转）copy⽂链接：/yjjm1990/article/details/7524167#，/database/201402/281473.html格式： 可以开窗的函数(..) over(..) over中防⽌分组的条件和分组的排序，不过分组使⽤的不再是GROUP BY⽽是PARTITION BY，表⽰开窗-- 建表CREATE table tb_sc(uName varchar2(10),uCourse varchar2(10),Uscore varchar2(10));-- 插⼊数据INSERT INTO tb_sc VALUES('张三','语⽂','80');INSERT INTO tb_sc VALUES('张三','数学','95');INSERT INTO tb_sc VALUES('李四','语⽂','90');INSERT INTO tb_sc VALUES('李四','数学','70');INSERT INTO tb_sc VALUES('王五','语⽂','90');INSERT INTO tb_sc VALUES('王五','数学','90');-- 查询所有SELECT * FROM tb_sc;-- 查询每名学⽣的平均分（展⽰姓名、平均分）Select uName,AVG(uScore)FROM tb_scGROUP BY uName;-- 查询每名同学的平均分并降序排列（展⽰姓名、平均分）SELECT uName,AVG(uScore)FROM tb_scGROUP BY uNameORDER BY uName DESC;-- 查询平均分数⾼于85分的学⽣（展⽰姓名、平均分）SELECT uName,AVG(uScore)FROM tb_scGROUP BY uNameHAVING AVG(uScore)>85;-- 查询不为张三且平均分⾼于85的学⽣（展⽰姓名、平均分）SELECT uName,AVG(uScore)FROM tb_scGROUP BY uNameHAVING uName != '张三' AND AVG(uScore) >85;-- 查询所有学⽣的信息并将每个学⽣的各科成绩降序SELECT t.*,ROW_NUMBER() OVER(PARTITION BY t.uName ORDER BY core DESC) RMFROM tb_sc t;-- 查询每个学⽣考得最好的科⽬并展⽰该科⽬的成绩SELECT *FROM(SELECT t.*,row_number() OVER(PARTITION BY t.uName ORDER BY core DESC) rmFROM tb_sc t )WHERE rm=1;-- 注：row_number() over(oartition by 分组字段 order by 排序字段)常⽤于查询所有分组并将各个窗体进⾏排序-- 在开窗函数出现之前存在着很多⽤SQL语句很难解决的问题，很多都要通过复杂的相关⼦查询或者存储过程来完成。