oracle connect by 和 分析函数总结

一、树查询(递归查询) ............................................................................................ 错误!未指定书签。

二、列转行sys_connect_by_path().................................................................... 错误!未指定书签。

2.分析函数总结........................................................................................................ 错误!未指定书签。

1.分析函数(OVER)................................................................................................... 错误!未指定书签。

2.分析函数2(Rank,Dense_rank,row_number) ......................................................... 错误!未指定书签。

3.分析函数3(Top/BottomN、First/Last、NTile) .................................................... 错误!未指定书签。

4.窗口函数................................................................................................................ 错误!未指定书签。

5.报表函数................................................................................................................ 错误!未指定书签。

1.connectby用法总结

一、树查询(递归查询)

1.作用

对于oracle进行简单树查询(递归查询)

列转行

2.基本语法

select...from？

where：过滤条件，用于对返回的所有记录进行过滤。

startwith：查询结果重起始根结点的限定条件。

connectby;：连接条件

1)例子：

select num1,num2

from table

startwith num2=1008

connectby num2=prior num1;

2)解释:

startwith：用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。

prior:位置很重要（自我总结，和父在一起则自底向上，即查父和子在一起则自顶向下查子）

例子

原始数据num1为父num2为子

看下面的图

返回当前节点的最顶端节点。

判断是否为叶子节点，是1，不是0。

伪列表示节点深度。

函数显示详细路径，并用“/”分隔。

二、列转行

这个函数使用之前必须先建立一个树，否则无用

sys_connect_by_path(字段名,2个字段之间的连接符号) with？

tmp_aas(

select'1'a,'0'pfromdual

unionall？

select'2','1'fromdual

unionall？

select'3','1'fromdual

unionall？

select'4','3'fromdual

unionall？

select'5','2'fromdual

unionall？

select'6','5'fromdual

)

--子全部显示根-->子level代表级别

selecta,p,sys_connect_by_path(a,'--'),levelfromtmp_a startwitha=1

connectbyp=priora

--2和2的所有下级去掉根-->子(开始就要去掉)

selecta,p,sys_connect_by_path(a,'--')fromtmp_a startwithp=1anda<>'2'

connectbyp=priora

--2的所有下级都去掉根-->子(connect时去掉)

selecta,p,sys_connect_by_path(a,'--')fromtmp_a startwitha=1

connectbyp=prioraandp<>'2'--去掉2的分枝

--2的下一级去掉根-->子(where中去掉)

selecta,p,sys_connect_by_path(a,'--')fromtmp_a wherep<>'2'

startwitha=1

connectbyp=priora

--显示最长的根-->子

with？

tmp_tabas(

select'中国's,nullbfromdual？

unionall？

select'广东's,'中国'bfromdual？

unionall？

select'湖南's,'中国'bfromdual？

unionall？

select'衡阳's,'湖南'bfromdual？

unionall？

select'广州's,'广东'bfromdual？

unionall？

select'衡东's,'衡阳'bfromdual？

)

selectmax(sys_connect_by_path(s,'/'))fromtmp_tab startwiths='湖南'

connectbypriors=b

2.分析函数总结

一、统计方面：

Sum()Over([Partitionby][Orderby])

Sum()Over([Partitionby][Orderby]？

Rows Between Preceding And Following)

？

Sum()Over([Partitionby][Orderby]

Rows Between Preceding And Current Row)

Sum()Over([Partitionby][Orderby]

Range Between Interval'''Day'Preceding

And Interval'''Day'Following)

二、排列方面：

Rank()Over([Partitionby][Orderby][NullsFirst/Last])

Dense_rank()Over([Patitionby][Orderby][NullsFirst/Last]) ？

Row_number()Over([Partitionby][Orderby][NullsFirst/Last]) ？

Ntile()Over([Partitionby][Orderby])

三、最大值/最小值查找方面：

Min()/Max()Keep(Dense_rankFirst/Last[Partitionby][Orderby])

四、首记录/末记录查找方面：

First_value/Last_value(Sum()Over([Patitionby][Orderby]

Rows Between Preceding And Following))

五、相邻记录之间比较方面：

Lag(Sum(),1)Over([Patitionby][Orderby])

1.分析函数(OVER)

一.分析函数语法:

FUNCTION_NAME(,...)

OVER

()

例:

sum(sal)over(partitionby deptno orderby ename)new_alias

sum：函数名

(sal)：参数0~3个参数可以是表达式

Over：关键字

partitionby：（可选）分区

orderby：（可选）LAG和LEAD需，AVG不需要，如果使用排序的开窗函数时，必须加

1)FUNCTION子句

26个分析函数,按功能分5类

分析函数分类

1.等级(ranking)函数:用于寻找前N种查询

2.开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上

3.制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列

（制表与开窗的区别：制表的OVER语句上少一个ORDERBY子句）

,LEAD函数:可在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常用用的.

,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值

2)PARTITION子句

分组

3)ORDERBY子句

分析函数中ORDERBY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDERBY时,默认的窗口是全部的分区。在Orderby子句后可以添加nullslast,如:orderbycommdescnullslast表示排序时忽略comm列为空的行.

二、分析函数简单实例：

按区域查找2001年度订单总额占区域订单总额20%以上的客户

【1】测试数据：

SQL>select*from orders_tmp;

CUST_NBRREGION_IDSALESPERSON_ID YEARMONTH TOT_ORDERSTOT_SALES

-------------------------------------------------------------------------- 1171120017212204

454200110237802

7672001233750

106820011221691

106720012342624

【2】测试语句：

SQL>select customer,

2region,

3sum cust_sales,

4sum(sum)over(partition by region_sales

5from orders_tmpo

6where=2001

7groupby,;

CUSTOMERREGIONCUST_SALESREGION_SALES

------------------------------------------

453780237802

76375068065

1066431568065

1171220412204

三、分析函数OVER解析：

四、

五、请注意上面的绿色高亮部分，groupby的意图很明显：将数据按区域ID，客户进行分组。

假如我们只需要统计每个区域每个客户的订单总额，那么我们只需要,就够了。但我们还想在每一行显示该客户所在区域的订单总额，这一点和前面的不同：需要在前面分组的基础上按区域累加。groupby和sum是无法做到这一点的(因为聚集操作的级别不一样，前者是对一个客户，后者是对一批客户)。

这就是over函数的作用了！它的作用是告诉SQL引擎：按区域对数据进行分区，然后累积每个区域每个客户的订单总额(sum(sum))。

最终语句

SQL>select all_sales.*,

2100*round(cust_sales/region_sales,2)||'%'Percent

3from(select customer,

4region,

5sum cust_sales,

6sum(sum)over(partition by region_sales

7from orders_tmpo

8where=2001

9groupby,all_sales

10where>*;

CUSTOMERREGIONCUST_SALESREGION_SALES PERCENT

----------------------------------------------------------------------------------

453780237802100%

106643156806594%

1171220412204100%

2.分析函数2(Rank,Dense_rank,row_number)

遇到相同的数据时用Rank,Dense_rank,row_number排名策略，他们之间的区别直接看例子SQL>select region_id,customer_id,sum(customer_sales)total,

2rank()over(orderby sum(customer_sales)desc)rank,

3dense_rank()over(orderby sum(customer_sales)desc)dense_rank,

4row_number()over(orderby sum(customer_sales)desc)row_number

5from user_order

6groupby region_id,customer_id;

REGION_IDCUSTOMER_IDTOTALRANKDENSE_RANKROW_NUMBER

-------------------------------------------------------------

？

8181253840111111

521224992121212

9231224992121213

9241224992121214

10301216858151315

3.分析函数3(Top/BottomN、First/Last、NTile)

一、Top/BottomN查询：

找出所有订单总额排名前3的大客户：

SQL>select*

SQL>from(select region_id,

SQL>customer_id,

SQL>sum(customer_sales)cust_total,

SQL>rank()over(orderby sum(customer_sales)desc NULLSLAST)rank

SQL>from user_order

SQL>groupby region_id,customer_id)

SQL>where rank<=3;

REGION_IDCUSTOMER_IDCUST_TOTALRANK

-----------------------------------------

92522327031

81719442812

71419297743

二、First/Last排名查询：

找出订单总额最多、最少的客户。

SQL>select min(customer_id)

2keep(dense_rankfirst orderby sum(customer_sales)desc)first,

3min(customer_id)

4keep(dense_ranklast orderby sum(customer_sales)desc)last

5from user_order

6groupby customer_id;

FIRSTLAST

--------------------

311

Min：函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。（去掉会出错）Keep：从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据，这就是keep的作用。告诉Oracle只保留符合keep条件的记录。

dense_rank：是告诉Oracle排列的策略，first/last则告诉最终筛选的条件。

三、按层次查询（NTile）

找出订单总额排名前1/5的客户。

SQL>select region_id,

2customer_id,

3ntile(5)over(orderby sum(customer_sales)desc)til

4from user_order

5groupby region_id,customer_id;

REGION_IDCUSTOMER_IDTILE

-------------------------------

10311

9251

10261

661？

8182

522

9233

693

7113

534

684

8164

675

10295

515

Ntil函数为各个记录在记录集中的排名计算比例，所有的记录分成5个等级，假如只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。

4.窗口函数

需要随着遍历记录集的每一条记录的同时进行统计。也即是说：统计不止发生一次，而是发生多次。统计不至发生在记录集形成后，而是发生在记录集形成的过程中。

一、窗口函数示例－全统计：

需求：列出每月的订单总额以及全年的订单总额

rowsbetween...precedingand...following。

SQL>select month,

2sum(tot_sales)month_sales,

3sum(sum(tot_sales))over(orderby month

4rows between unboundedpreceding and unboundedfollowing)total_sales

5from orders

6groupby month;

MONTH MONTH_SALESTOTAL_SALES

--------------------------------

16106976307766

24286766307766

36370316307766

45411466307766

55929356307766

65014856307766...（后面的剩了）unboundedprecedingandunbounedfollowing：针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。

Preceding前面如果是1，不是从第1条记录开始的意思，而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数，如同unbounded就是告诉oracle不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。

二、窗口函数进阶－滚动统计(累积/均值)：

需求：列出每月的订单总额以及截至到当前月的订单总额。也就是说3月份要显示当月的订单总额和1,2,3月份订单总额的和，依此类推。

需要在统计第N月的订单总额时，还要再统计这N个月来的订单总额之和。curreentrow

SQL>select month,

2sum(tot_sales)month_sales,

3sum(sum(tot_sales))over(orderby month

4rows between unboundedpreceding and current row)current_total_sales

5from orders

6groupby month;

MONTH MONTH_SALESCURRENT_TOTAL_SALES

----------------------------------------

1610697610697

24286761039373

36370311676404

三、窗口函数进阶－根据时间范围统计

统计了当天销售额和五天内的评价销售额：

select trunc(order_dt)day,

sum(sale_price)daily_sales,

avg(sum(sale_price))over(orderby trunc(order_dt)

rangebetweeninterval'2'day preceding？

andinterval'2'day following)five_day_avg

from cust_order

where sale_priceisnotnull？

andorder_dt betweento_date('01-jul-2001','dd-mon-yyyy')

and to_date('31-jul-2001','dd-mon-yyyy')

为了对指定范围进行统计，Oracle使用关键字range、interval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天，后2天范围内的记录，并统计其销售平均值。

四、窗口函数进阶－first_value/last_value：

first_value、last_value，用于在窗口记录集中查找第一条记录和最后一条记录。

需要显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值

select month,

first_value(sum(tot_sales))over(orderbymonth？

rowsbetween1preceding and1following)prev_month,

？

sum(tot_sales)monthly_sales,

？

last_value(sum(tot_sales))over(orderby month？

rowsbetween1preceding and1following)next_month,

？

avg(sum(tot_sales))over(orderby month？

rowsbetween1preceding and1following)rolling_avg

from orders

where year=2001？

andregion_id=6

groupby month

orderby month;

rowsbetween1precedingand1following:当前记录的前一条、后一条范围内查找并统计，而

first_value和last_value在这3条记录中至分别找出第一条、第三条记录

五、窗口函数进阶－比较相邻记录：

我们想每次显示当月的销售额和上个月的销售额，

leg函数类似于preceding和following子句，它能够通过和当前记录的相对位置而被应用，在比较同一个相邻的记录集内两条相邻记录的时候特别有用。

select month,？

sum(tot_sales)monthly_sales,

lag(sum(tot_sales),1)over(orderby month)prev_month_sales

from orders

where year=2001

and region_id=6

groupby month

orderby month;

5.报表函数

一、报表函数简介

sum(sum(tot_sales))over(orderby month rows between unboundedpreceding and unboun dedfollowing)

来统计全年的订单总额，这个函数会在记录集形成的过程中，每检索一条记录就执行一次，它总共执行了12次。这是非常费时的。实际上我们还有更简便的方法：

SQL>select month,

2sum(tot_sales)month_sales,

3sum(sum(tot_sales))over(orderby month

4rows between unboundedpreceding and unboundedfollowing)win_sales,

5sum(sum(tot_sales))over()rpt_sales

6from orders

7groupby month;

二、RATIO_TO_REPORT函数：

需求：列出上一年度每个月的销售总额、年底销售额以及每个月的销售额占全年总销售额的比例：方法①:

select all_sales.*,

100*round(cust_sales/region_sales,2)||'%'Percent

from(select customer,

region,

sum cust_sales,

sum(sum)over(partition by region_sales

from orders_tmpo

where=2001

groupby,all_sales

where>*;

方法②：

select region_id,salesperson_id,？

sum(tot_sales)sp_sales,

round(sum(tot_sales)/sum(sum(tot_sales))？

over(partition by region_id),2)percent_of_region

from orders

where year=2001

groupby region_id,salesperson_id

orderby region_id,salesperson_id;

方法③

select region_id,salesperson_id,？

sum(tot_sales)sp_sales,

round(ratio_to_report(sum(tot_sales))？

over(partition by region_id),2)sp_ratio

from orders

where year=2001

groupby region_id,salesperson_id

orderby region_id,salesperson_id;

Oracle提供的Ratio_to_report函数允许我们计算每条记录在其对应记录集或其子集中所占的比例。

oracle函数

许多软件公司都理解开发不依赖于特定数据库类型(例如Oracle、SQL Server、DB2)的应用程序的重要性，它可以让客户们选择自己习惯的平台。一般来说，软件开发人员都能够识别出他们的负责数据库维护的客户，和必须使用现有平台和个性化的客户。 关于Oracle和SQL Server之间的区别，已经有很多篇文章从企业的角度和数据库管理员的角度出发描写了两者之间的一般差别。在这篇文章里面，我将会从应用程序的角度向你描述SQL erver 和oracle平台之间的区别，并且将会讨论开发不依赖于数据库环境的应用程序的几种可能的方法。与此同时，我不会再论讨对于应用程序来说，两个平台之间显而易见的区别，例如表的分割和索引。 定义通用接口和语言有很少的几种通用语言和接口可以让应用程序不依赖于数据库，想来也可以以同样的方式应用在关系型数据库上面: ANSI是美国国家标准局定义的，这是一家志愿者成员的组织(用私人基金运转)，他们在有关设备和程序等广泛领域内开发了国家承认的标准。在数据库领域， ANSI定义了编写SQL命令的标准，假设命令可以运行在任何的数据库上，而不需要更改命令的语法。 ODBC是开放数据库连接(ODBC)接口，微软定义的，它可以让应用程序访问数据库管理系统(DBMS)中的数据，使用SQL作为访问数据的标准。 ODBC允许最大的互联性，这意味着一个单个的应用程序可以访问不同的数据库管理系统。然后，应用程序终端用户可以添加开放数据库连接(ODBC)数据库驱动来将应用程序与他们所选择的数据库管理系统链接上。 OLE DB 是ODBC的继承者，是一组允许例如基于VB、C++、Access等任何可以连接到类似SQL Server、Oracle、DB2、MySQL等后台的“前台”的软件组件。在许多种情况下，OLE DB组件都比原有的ODBC提供了好得多的性能。 JDBC(Java数据库连接)应用程序接口是Java编程语言和广泛范围的数据库，SQL数据库和其他表列数据源(例如电子表格或者普通文本文件)之间，进行不依赖于数据库的连接的行业标准。JDBD应用程序接口为基于SQL的数据库访问提供了调用级别的应用程序接口。真实世界中的通用接口不幸的是，并不是所有数据库级别的命令都是ANSI，每个数据库平台都有自己的扩展功能。对于ANSI或者通用接口，一般来说都代表着几本功能，因此也可能意味着丧失了性能方面的竞争力。对于小型数据库和小型应用程序来说，要维护对数据库的通用访问是简单的，但是当数据库和/或应用程序变得越来越大，越来越复杂，你就不得不向代码中添加功能。 SQL Server和Oracle的常用函数对比 ---------数学函数 1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual

Oracle中分析函数用法小结

Oracle中分析函数用法小结 一.分析函数适用场景: ○1需要对同样的数据进行不同级别的聚合操作 ○2需要在表内将多条数据和同一条数据进行多次的比较 ○3需要在排序完的结果集上进行额外的过滤操作 二.分析函数语法: FUNCTION_NAME(,...) OVER () 例: sum(sal) over (partition by deptno order by ename) new_alias sum就是函数名 (sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm) over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数 partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区 order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的. 1)FUNCTION子句 ORACLE提供了26个分析函数,按功能分5类 分析函数分类 等级(ranking)函数:用于寻找前N种查询 开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上 例: sum(t.sal) over (order by t.deptno,t.ename) running_total, sum(t.sal) over (partition by t.deptno order by t.ename) department_total 制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列 例: sum(t.sal) over () running_total2, sum(t.sal) over (partition by t.deptno) department_total2 制表函数与开窗函数的关键不同之处在于OVER语句上缺少一个ORDER BY子句! LAG,LEAD函数:这类函数允许在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常有用的. VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值 2)PARTITION子句 按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组 3)ORDER BY子句

Oracle 常见函数(一)——数值函数

Oracle常见数值函数 ----***特别说明***： x 可以是纯的数值，也可以是数值型表达式/* ABS(x)返回x绝对值 eg. */ selectabs(100),abs(-100) from dual; /* sign(x)判断x的正负，正数返回1，负数返回-1,0返回0； eg. */ selectsign(100),sign(-100),sign(0) from dual;

/* round(x[,n])对x进行四舍五入，保留n位小数，其中n采用其整数部分； 没有n时默认四舍五入到整数位，n为负数时，四舍五入保留小数点左边n位(补零)， eg. */ selectround(5555.6666, 2.1), round(5555.6666, -2.6), round(5555.6666) from dual; /* trunc(x)对x进行直接截取，保留n位小数，其中n采用其整数部分； 没有n时默认截取到整数位，n为负数时，截取保留小数点左边n位(补零)， eg. */ selecttrunc(5555.66666,2.1), trunc(5555.66666,-2.6), trunc(5555.033333) from dual; /* ceil(x)对x进行向上取整，返回不小于x的最小整数(可以是整数x本身)。

eg. */ selectceil(3.1), ceil(2.8+1.3), ceil(0) from dual; /* floor(x)对x进行向下取整，返回不大于x的最大整数(可以是整数x本身)。eg. */ selectfloor(3.1), floor(2.8+1.3), floor(0) from dual; /* mod(x,y)求x除以y的余数，x，y为数字型表达式。 eg. */ selectmod(23,8),mod(24,8) from dual;

Oracle列转行和行转列的几种用法

Oracle列转行和行转列的几种用法 栏到 栏主要讨论sys_connect_by_path的用法 1，具有分层关系 SQL > createtabledept(deptnononumber，deptname varchar2 (20)，mgrnononumber)； table created . SQL >插入deptvalues (1，“总部”，空)； 1 row created . SQL >插入deptvalues (2，’浙江分公司’，1)； 1 row created . SQL > insert into dept values(3，’杭州分公司’，2)；已创建 1行。 SQL >提交； 提交完成。 SQL >从部门连接中选择最大值(子串(sys_connect_by_path(deptname，’，’)，2))由先前部门连接= mgrno 最大值(SUBSTER(SYS _ CONNECT _ BY _ PATH(DEPTNAME)，’)，2) -总部，浙江分行，杭州分行 2，行-列转换 如果一个表的所有列都连接到一行，用逗号分隔:

SQL >选择最大值(SUBSTER(SYS _ CONNECT _ BY _ PATH(column _ name，’，’)，2)) MAX(SUBSTRA(SYS _ CONNECT _ BY _ PATH(COLUMN _ NAME，’，’)，2)) - DEPTNO，DEPTNAME，MGRNO 3，ListAgg(Oracle 11g) SQL >选择DEPTNO， 2 ListAgg(NAME，’；’) 3在组 4内(由搪瓷订购)搪瓷 5来自emp 6组由deptno 7由deptno 8 / DEPTNO搪瓷 - - 10 CLARK。国王；米勒 20亚当斯；福特。琼斯； SCOTT。史密斯 30艾伦；布莱克； JAMES；马丁； TURNER；下面的W ARD

Oracle 分析函数(Analytic Functions) 说明

Oracle 分析函数(Analytic Functions)说明一. Analytic Functions 说明 分析函数是oracle 8中引入的一个概念,为我们分析数据提供了一种简单高效的处理方式. 官方对分析函数的说明如下： Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The groupof rows is called a window and is defined bythe analytic_clause. For each row, a sliding window of rows is defined.The window determines the range of rows used to perform the calculations forthe current row. Window sizes can be based on either a physical number of rowsor a logical interval such as time. Analytic functions are the last set of operations performed in a query except for thefinal ORDER BY clause. All joins and all WHERE, GROUP BY,and HAVING clauses are completed before the analytic functions areprocessed. Therefore, analytic functions can appear only in the select listor ORDER BY clause. Analytic functions are commonly used to compute cumulative, moving, centered, andreporting aggregates. From：Analytic Functions https://www.sodocs.net/doc/d81981911.html,/cd/E11882_01/server.112/e26088/functions004.htm#S QLRF06174 分析函数是对一组查询结果进行运算，然后获得结果，从这个意义上，分析函数非常类似于聚合函数（Aggregate Function）。区别是在调用分析函数时，后面加上了开窗子句over()。 聚合函数是对一个查询结果中的每个分组进行运算，并且对每个分组产生一个运算结果。分析函数也是对一个查询结果中的每个分组进行运算，但每个分组对应的结果可以有多个。产生这个不同的原因是分析函数中有一个窗口的概念，一个窗口对应于一个分组中的若干行，分析函数每次对一个窗口进行运算。运算时窗口在查询结果或分组中从顶到底移动，对每一行数据生成一个窗口。 Oracle 聚合函数（Aggregate Functions）说明 https://www.sodocs.net/doc/d81981911.html,/tianlesoftware/article/details/7057249

oracle比较decode(case when) 、4种去重---和去重中的统计函数

oracle比较decode/case when 、4种去重和去重中的统计函数 一：decode 和case when decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default) decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。 select username,decode(lock_date,null,'unlocked','locked') status from t; ----------------如果lock_date是null就返回unlocked 如果不是null就返回locked select username,decode(lock_date,null,'unlocked') status from t; ----------------如果lock_date是null就返回unlocked 否则是空(因为没有定义) 例如有个学生表...... 行转列------- create table score2( name varchar2(10), Language number(3), Math number(3), English number(3)); insert into score2 values('Zhang',80,67,95); insert into score2 values('Li',79,84,62); insert into score2(name,Language) values('Chen',88); commit; Select * from score2; 显示成报表的格式....... select name, sum(decode(subject,'Language', grade,0)) "Language", sum(decode(subject,'Math', grade,0)) "Math", sum(decode(subject,'English', grade,0)) "English" from score group by name; NAME Language Math English -------------------- ---------- ---------- ---------- Zhang 80 92 76 Wang 73 0 0 Li 81 95 50 第二列，如果subject='Language',那么就显示成绩,否则显示为0 第三列，如果subject='Math',那么就显示成绩,否则显示为0 第四列，如果subject='English',那么就显示成绩，否则显示为0 Case when Case [selector] ---selector可以不设置 When id=1 THEN ‘id是1’; ELSE ‘没有id值’; END CASE; ---->如果ID=1 就显示id是1 否则显示没有id值 ~~~~~上面用case when显示................. select https://www.sodocs.net/doc/d81981911.html,, sum( case when t.subject = 'Language' then t.grade else 0 end ) dd, sum( case when t.subject = 'Math' then t.grade else 0 end ) Math,

Oracle函数详解(经典)

Oracle常用函数/过程说明主要介绍Oracle的系统函数、过程和包。 SQL常用函数： 数值函数： ABS Purpose 返回绝对值 Returns the absolute value of n. Example SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15 CEIL Purpose 取最小整数 Returns smallest integer greater than or equal to n. Example SELECT CEIL(15.7) "Ceiling" FROM DUAL;

Ceiling ---------- 16 * MOD Syntax MOD(m,n) Purpose 取余 Returns remainder of m divided by n. Returns m if n is 0. Example SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3 * ROUND Syntax ROUND(n[,m]) Purpose 取四舍五入信息 Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.

sqlserver到oracle数据无损迁移

sqlserver到oracle数据无损迁移 编者：liuli10@https://www.sodocs.net/doc/d81981911.html, 版本：V1.7 最后修订日期：2015-11-21

第一章简介 1.1数据迁移 随着时代发展数据越来越被重视，而很多时候，当系统需要更新换代的时候，升级后系统所是有的数据库与当前系统的数据库并不一致，此时不仅需要数据割接，最重要的是：如何能将老系统中的数据无损的割接到新系统、新数据库中。因此，结合项目实战经验，针对从windows平台下数据库sqlserver到linux平台下oracle数据库的数据无损迁移进行总结。 1.2数据库简介 一般此处会有很多数据库以及出品公司的历史以及发展历程，在编者看来然而并没有什么大用途，百度百科都可以搜索的到，因此本章结束，直接进入实战总结环节。

第二章sqlserver数据导出 2.1sqlserver数据导出命令 当然不可否认windows为sqlserver提供了强大的图形化平台，导出数据变得只需要点一点就能完成，然而这样的数据导出对于大批量有要求的操作，是极其劳神伤财的，因此，必须要通过命令行进行格式化导出，因此，这里介绍sqlserver 本机数据库导出命令。 2.1.1bcp命令以及参数介绍 https://www.sodocs.net/doc/d81981911.html,/liyanmingkong/article/details/6087674 https://www.sodocs.net/doc/d81981911.html,/uid-25472509-id-4304562.html https://www.sodocs.net/doc/d81981911.html,/link?url=WV2JJM4JHxR7Qct8rr_-499zPc3aP_7E5rOt5l yEnG_Mj_tE9_-ZN1JPE2Vc2wRpkO8QkNGNLVznDfMgniCOnxXhK5jQppNpZk8 Jo1x8o23 为了将文档尽可能精简，bcp命令的参数以及介绍请自行去以上任意网址查询。或者自行baidu或者google搜索。 2.2实战语句解析 实战语句为： bcp"select*from gwbnboss.dbo.ACCOUNT_BUSINESS"queryout "C:\Users\liuli9\Desktop\sqlserverdata_mov\textfile\ACCOUNT_BUSINESS.txt"-c -r"{#$&}"-t"{@#$}"-S"127.0.0.1"-U"数据库用户名"-P"密码" 最终导出的结果存在于 C:\Users\liuli9\Desktop\sqlserverdata_mov\textfile\ACCOUNT_BUSINESS.txt 文件中，当出现“{#$&}”时表示接下来是下一行数据，出现“{@#$}”时表示接下来是下一列数据。将查询结果集完整导出，不对数据做任何格式化或者修改操作，保证数据的原生无损。

ORACLE排序与分析函数

--已知：两种排名方式(分区和不分区):使用和不使用partition --两种计算方式(连续,不连续),对应函数：dense_rank,rank 语法： rank()over(order by排序字段顺序) rank()over(partition by分组字段order by排序字段顺序) 1.顺序：asc|desc名次与业务相关： 示例：找求优秀学员：成绩：降序迟到次数：升序 2.分区字段：根据什么字段进行分区。 问题：分区与分组有什么区别? ·分区只是将原始数据进行名次排列(记录数不变), ·分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意：聚合。rank()与dense_rank()：非连续排名与连续排名(都是简单排名) ·查询原始数据：学号,姓名,科目名,成绩 select*from t_score; S_ID S_NAME SUB_NAME SCORE 1张三语文80.00 2李四数学80.00 1张三数学0.00 2李四语文50.00 3张三丰语文10.00 3张三丰数学 3张三丰体育120.00 4杨过JAVA90.00 5mike c++80.00 3张三丰Oracle0.00 4杨过Oracle77.00 2李四Oracle77.00 ·查询各学生科目为Oracle排名(简单排名) select sc.s_id,sc.s_name,sub_name,sc.score,rank()over(order by score desc)名次from t_score sc where sub_name='Oracle'; S_ID S_NAME SUB_NAME SCORE名次 4杨过Oracle77.001 2李四Oracle77.001 3张三丰Oracle0.003 ·对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名) S_ID S_NAME SUB_NAME SCORE名次

ORACLE常用函数和一些项目中常用的SQL语句

Oracle笔记 实现中英文转换 Alter session set nls_language=‘ADMIN’ 从已知表中复制数据和结构 Create table test as select * from dept； 从已知表中复制数据和结构，但是不包括数据 Create table test as select * from dept where 1=2 插入数据 Insert into test select * from dept 二、运算符 +——*/可以在select 语句中使用 ||连接两个字段，select deptno ||dname from dept 比较运算付：> >= = !=< <= 逻辑运算符：not ，or 集合运算符：intersect，union，union all，minus， 使用ord er by 的时候必须使用位置序号，不能使用列名 Select *from emp intersect select * from emp where deptno=10； Select *from emp minus select * from emp where deptno=10； ---不包括重复行 Select *from emp where deptno=10 union select * from emp where deptno in（10，20）；----包括重复行 Select *from emp where deptno=10 union all select * from emp where deptno in（10，20）； 三、常用ORACLE函数

oracle列转行sql详细讲解

--当期时间贷款时间 SELECT DK_ID, max(substr(activeDate, 2)) activeDate FROM (SELECT DK_ID, sys_connect_by_path(activeDate, ',') activeDate FROM (SELECT DK_ID, activeDate, DK_ID || rn rchild, DK_ID || (rn - 1) rfather FROM (SELECT TEMP.DK_ID, --查询项目所在地树形结构全名 SELECT t.area_id, substr(sys_connect_by_path(t.area_name, '-'), 2) as allname , connect_by_root t.area_name as root, --是单一操作符，返回当前层的最顶层节点connect_by_isleaf as IsLeaf, --是伪列，判断当前层是否为叶子节点，1代表是，0代表否 level as lel --是伪列，显示当前节点层所处的层数 FROM dk_project_area_info t START WITH t.area_name = '项目所在地' CONNECT BY PRIOR t.area_id = t.area_pid SYS_CONNECT_BY_PATH 学习2008-09-08 10:59SELECT ename FROM scott.emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr; 得到结果为：KING JONES SCOTT ADAMS FORD SMITH BLAKE ALLEN WARD MARTIN TURNER JAMES

Oracle分析函数sum over介绍

分析函数sum over() 介绍 报送单位：审核人： 类型：业务应用 关键字：分析函数 1、引言 运维中，常常需要通过SQL语句对单行数据进行查询，同时又需要对结果集进行汇总，通常的方法是通过两个SQL语句分别进行查询汇总，这样效率低下。 2、现象描述 本节介绍一种ORACLE提供的全新的函数sum over()，该类函数称为分析函数，这类函数功能强大，可以通过一个SQL语句对数据进行遍历的同时又进行汇总，而且一张表只进行一次扫描，极大地提高SQL的执行效率。 3、处理过程 语法： FUNCTION_NAME(,,...) OVER() NAME：可以是SUM,AVG,MAX,MIN,COUNT等其它，这些函数单独使用称为聚集函数，与OVER子句一起使用使称为分析函数。在当分析函数使用时，SQL语句中不需要使用GROUP BY子句。

执行计划： 下图说明分析函数只对表进行一次扫描 4、举例说明 下面分别举例来说明分析函数的使用，原始数据如下表

示例1：查询单行数据同时对所有数据工资进行汇总求和 示例2：查询所有数据，同时对第各部门工资进行汇总，汇总范围取值为第一行所在部门至当前部门所有数据。

示例3：查询所有数据，并且每一行汇总值按ENAME排序后取第一行至当前行ENAME所在行。 示例4：查询所有数据，并且每一行汇总值按EMPNO排序后取第一行至当前行EMPNO所在行

示例5：查询所有数据，同时每行对各部门分别进行按EMPNO排序后从各组第一行至当前行汇总求和。 示例6：查询所有数据，同时每行对从当前上两行数据范围的工资进行汇总求和。 示例7：查询所有数据，同时每行对从当前向前一行数据向后两行数据范围的工资进行汇总求和。

oracle中常用函数大全

oracle中常用函数大全 1、数值型常用函数 函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual;

oracle 行转列sql

表结构和数据如下（表名Test）： NO V ALUE NAME 1 a 测试1 1 b 测试2 1 c 测试3 1 d 测试4 2 e 测试5 4 f 测试6 4 g 测试7 Sql语句： select No, ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value, ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name from (select No, Value, Name, rnFirst, lead(rnFirst) over(partition by No order by rnFirst) rnNext from (select a.No, a.Value, https://www.sodocs.net/doc/d81981911.html,, row_number() over(order by a.No, a.V alue desc) rnFirst from Test a) tmpTable1) tmpTable2 start with rnNext is null connect by rnNext = prior rnFirst group by No; 检索结果如下： NO V ALUE NAME 1 a;b;c;d 测试1;测试2;测试3;测试4 2 e 测试5 4 f;g 测试6;测试7 简单解释一下那个Sql吧： 1、最内层的Sql（即表tmpTable1），按No和Value排序，并列出行号：select a.No, a.Value, https://www.sodocs.net/doc/d81981911.html,, row_number() over(order by a.No, a.V alue desc) rnFirst

Oracle常用函数及使用案例(珍藏版)

Oracle常用函数及使用案例（珍藏版） 一：sql函数： lower（char）:将字符串转化为小写的格式。 upper（char）:将字符串转化为大写的格式。 length（char）：返回字符串的长度。 substr（char，m，n）：取字符串的字串。 案例1.将所有员工的名字按小写的方式显示 select lower(ename),sal from emp; 案例2.将所有员工的名字按大写的方式显示。 select upper(ename),sal from emp; 案例3.显示正好为五个字符的的员工的姓名。 select * from emp where length(ename)=5; 案例4.显示所有员工姓名的前三个字符。 select substr(ename,1,3) from emp;//从名字的第一个字符开始取，向后取三个字符。 案例5.以首字母为大写的方式显示所有员工的姓名。 （1）首字母大写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母小写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; 案例6.以首字母为小写的方式显示所有员工的姓名。（需要有较高的灵活度，细心分析和清晰思路） （1）首字母小写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母大写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 案例7.函数（替换）：replace(char1,search_string,replace_string); 显示所有员工的姓名，用“我要替换A”替代所有“A”。 select replace(ename,'A','我是老鼠')from emp; 案例8.以首字母为小写的方式显示所有员工的姓名。 select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp; 案例9.以首字母为大写的方式显示所有员工的姓名。 Select replace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp; 二：数学函数：(在财务中用的比较多) ronud（sal）用于四舍五默认取整； ronud（sal，1）用于四舍五留一位小数。 trunc（sal）取整，忽略小数。截去小数部分。 trunc（sal，1）截取；小数点留一位，之后的右边的省去。 trunc（sal，-1）截取；只留整数，个位数取零。 floor（sal）向下最接近取整；比如1.1值为1.

ORACLE关于动态SQL的使用

关于动态SQL的使用-----摘录 内容摘要：在PL/SQL开发过程中，使用SQL，PL/SQL可以实现大部份的需求，但是在某些特殊的情况下，在PL/SQL中使用标准的SQL语句或DML语句不能实现自己的需求，比如需要动态建表或某个不确定的操作需要动态执行。这就需要使用动态SQL来实现。本文通过几个实例来详细的讲解动态SQL的使用。 本文适宜读者范围：Oracle初级，中级 系统环境： OS：windows2000Professional(英文版) Oracle：8.1.7.1.0 正文： 一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。 首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。 Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行。下面就这两种情况分别进行说明： 一、本地动态SQL 本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。 1、本地动态SQL执行DDL语句： 需求：根据用户输入的表名及字段名等参数动态建表。 create or replace procedure proc_test ( table_name in varchar2,--表名 field1in varchar2,--字段名 datatype1in varchar2,--字段类型 field2in varchar2,--字段名 datatype2in varchar2--字段类型 )as str_sql varchar2(500); begin str_sql:=create table||table_name||(||field1||||datatype1||,||field2|| ||datatype2||); execute immediate str_sql;--动态执行DDL语句 exception when others then null; end; 以上是编译通过的存储过程代码。下面执行存储过程动态建表。

Oracle分析函数

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是 对于每个组返回多行，而聚合函数对于每个组只返回一行。 一、开窗函数 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。 例如： 1)over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数 2)over（partition by department_id）按照部门分区 3)over（order by salary range between 50 preceding and 150 following） 每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150 4)over（order by salary rows between 50 preceding and 150 following） 每行对应的数据窗口是之前50行，之后150行 5)over（order by salary rows between unbounded preceding and unbounded following） 每行对应的数据窗口是从第一行到最后一行，等效： 6) over（order by salary range between unbounded preceding and unbounded following） 其中： 第一行是unbounded preceding 当前行是current row 最后一行是unbounded following 二、分析函数的概念 分析函数是在整个SQL查询结束后(SQL语句中的ORDER BY的执行比较特殊)再进行的操作, 也就是说 SQL语句中的ORDER BY也会影响分析函数的执行结果。 分析函数中包含三个分析子句:分组(Partition By), 排序(Order By), 窗口(Window) 当省略窗口子句时: 1) 如果存在Order By则默认的窗口是unbounded preceding and current row 2) 如果同时省略Order By则默认的窗口是unbounded preceding and unbounded following 如果省略分组,则把全部记录当成一个组 a) 如果SQL语句中的Order By满足分析函数分析时要求的排序,那么SQL语句中的排序将先执行,分析 函数分析时就不必再排序 b) 如果SQL语句中的Order By不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分 析函数分析结束后执行排序 三、分析函数 1）AVG

oracle常用函数列表速查 (1)

Oracle常用函数列表速查 PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类： 单行函数 组函数 本文将讨论如何利用单行函数以及使用规则。 SQL中的单行函数 SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY 等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。 SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename) 单行函数也可以在其他语句中使用，如update的SET子句，INSERT的V ALUES子句，DELET 的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。 NULL和单行函数 在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。 下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿 column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2 不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子： update empset salary=(salary+bonus)*1.1 这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。 所以正确的语句是： update empset salary=(salary+nvl(bonus,0)*1.1