oracle connect by 和分析函数总结

合集下载

oracle_OLAP分析函数

CUME_DIST() OVER(ORDER BY DEPTNO, SAL DESC), --相对位置/总数 --(COUNT(*) over())**/
LAG(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC), -- 前一行的值 LEAD(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC), -- 后一行的值 NTILE(7) OVER(ORDER BY DEPTNO, SAL DESC), --将一个组分为"表达式"的散列表示 PERCENT_RANK() OVER(ORDER BY DEPTNO, SAL DESC), --对于一个组中给定的行来说，在计算那行的序号时，先减1，然后除以n-1（n为组
SELECT ENAME,SAL,HIREDATE,HIREDATE-100 WINDOWTOP, FIRST_VALUE(ENAME)
OVER(ORDER BY HIREDATE ASC
RANGE 100 PRECEDING) ENAME_PREC, FIRST_VALUE(HIREDATE)
OVER(ORDER BY HIREDATE ASC
范例2
• -- 两种不同的排序方式 • SELECT DEPTNO,ENAME,SAL, • DENSE_RANK() • OVER(PARTITION BY DEPTNO • ORDER BY SAL DESC)DR, • RANK() • OvER(PARTITION BY DEPTNO • ORDER BY SAL DESC) R • FROM SCOTT.EMP • ORDER BY DEPTNO,SAL DESC;

oracle 分组统计函数

oracle 分组统计函数Oracle是一种流行的关系型数据库管理系统，具有强大的分组统计函数，可以帮助用户轻松实现数据分析和汇总。

在本文中，我们将介绍几种常用的Oracle分组统计函数，并说明它们的用途和功能。

GROUP BY子句是SQL语句中用于对查询结果进行分组的重要部分。

在Oracle中，可以结合使用GROUP BY子句和聚合函数来实现数据的分组统计。

以下是几种常用的Oracle分组统计函数：1. COUNT函数：COUNT函数用于统计查询结果集中行的数量。

可以结合GROUP BY子句使用，以实现对分组数据的计数统计。

例如，可以使用COUNT(*)来统计每个分组中的行数，或者使用COUNT(column_name)来统计指定列中非空值的数量。

2. SUM函数：SUM函数用于计算指定列的合计值。

可以结合GROUP BY子句使用，以实现对分组数据的求和统计。

例如，可以使用SUM(column_name)来计算每个分组中指定列的合计值。

3. AVG函数：AVG函数用于计算指定列的平均值。

可以结合GROUP BY子句使用，以实现对分组数据的平均值统计。

例如，可以使用AVG(column_name)来计算每个分组中指定列的平均值。

4. MAX函数：MAX函数用于找出指定列的最大值。

可以结合GROUP BY子句使用，以实现对分组数据的最大值统计。

例如，可以使用MAX(column_name)来找出每个分组中指定列的最大值。

5. MIN函数：MIN函数用于找出指定列的最小值。

可以结合GROUP BY子句使用，以实现对分组数据的最小值统计。

例如，可以使用MIN(column_name)来找出每个分组中指定列的最小值。

除了上述常用的分组统计函数外，Oracle还提供了其他一些函数，如STDDEV、VARIANCE等，用于计算标准差和方差等统计指标。

这些函数可以帮助用户更全面地分析数据，发现数据的规律和趋势。

oracle合并列函数

oracle合并列函数Oracle是一个强大的关系型数据库管理系统，它提供了大量的SQL函数，其中包括一些合并列的函数。

这些函数可以帮助用户将数据从多个列中合并到一个列中，从而使数据更加便于管理和分析。

合并列函数有很多种，本文将介绍一些常用的合并列函数及其使用方法。

我们将为您提供详细的中文解释和相应的示例。

1. CONCAT函数CONCAT函数可以将两个或多个字符串相加，将它们合并成一个字符串。

语法如下：CONCAT(str1,str2,str3,...)其中，str1、str2、str3等都是要合并的字符串。

下面是一个简单的例子：SELECT CONCAT('Hello ','World') AS result FROM dual;运行结果如下：result-----------Hello World2. ||操作符Oracle还提供了一个连接操作符||，它可以将两个字符串连接成一个。

语法如下：string1 || string2下面是一个简单的例子：3. LISTAGG函数LISTAGG函数用于将多行数据合并为一行。

语法如下：LISTAGG(列名,分隔符) WITHIN GROUP (ORDER BY 排序列)其中，列名是要合并的列名，分隔符是分隔符，排序列是用于排序的列。

下面是一个例子：WM_CONCAT函数与LISTAGG函数类似，它也可以将多行数据合并为一行，但是它没有在Oracle 12cR1中被官方支持，尽管在某些社区中可能会使用。

如果您必须使用这个函数，请小心使用。

语法如下：WM_CONCAT(列名)XMLAGG函数用于将多个值转换为XML文档，这些值可以被合并到一个新列中。

语法如下：XMLAGG(Expr)John,Marie,Michael,Susan6. SYS_CONNECT_BY_PATH函数SYS_CONNECT_BY_PATH函数可以将多个值连接在一起，同时在它们之间添加分隔符。

Oracle分析函数-排序排列（rank、dense_rank、row_number、ntile）

Oracle分析函数-排序排列（rank、dense_rank、row_number、ntile）(1)rank函数返回⼀个唯⼀的值，除⾮遇到相同的数据时，此时所有相同数据的排名是⼀样的，同时会在最后⼀条相同记录和下⼀条不同记录的排名之间空出排名。

(2)dense_rank函数返回⼀个唯⼀的值，除⾮当碰到相同数据时，此时所有相同数据的排名都是⼀样的。

(3)row_number函数返回⼀个唯⼀的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

(4)ntile是要把查询得到的结果平均分为⼏组，如果不平均则分给第⼀组。

例如：create table s_score( s_id number(6),score number(4,2));insert into s_score values(001,98);insert into s_score values(002,66.5);insert into s_score values(003,99);insert into s_score values(004,98);insert into s_score values(005,98);insert into s_score values(006,80);selects_id,score,rank() over(order by score desc) rank --按照成绩排名，纯排名,dense_rank() over(order by score desc) dense_rank --按照成绩排名，相同成绩排名⼀致,row_number() over(order by score desc) row_number --按照成绩依次排名,ntile(3) over (order by score desc) group_s --按照分数划分成绩梯队from s_score;排名/排序的时候，有时候，我们会想到利⽤伪列row_num，利⽤row_num确实可以解决某些场景下的问题(但是相对也⽐较复杂)，⽽且有些场景下的问题却很难解决。

oracle常用的开窗函数使用技巧

oracle常用的开窗函数使用技巧在数据库操作中，常常需要对数据进行分组、排序、过滤、统计等操作，这时候开窗函数就成为了一种非常有用的技巧。

Oracle是一种支持开窗函数的数据库，他们可以被用于查询、分析、排序、聚合等操作。

下面将为大家介绍几种常见的开窗函数使用技巧。

一、聚合函数实现在数据库中，聚合函数是非常常见的操作，例如SUM、COUNT、AVG等。

在某些情况下，我们需要在查询中同时获得数据的总量或平均值，这时候开窗函数就可以派上用场了。

oracle递归查询start with connect by prior的用法

oracle递归查询start with connect by prior的用法在Oracle数据库中，"START WITH"和"CONNECT BY PRIOR"是用于执行递归查询的关键字。

这些关键字与"SELECT"语句一起使用，用于在以层次结构组织的数据中进行深度优先搜索。

具体用法如下所示：1. 使用"START WITH"关键字指定递归查询的起始条件。

例如，如果要从员工表中查询所有直接报告给经理ID为100的员工，可以这样写：```SELECT employee_id, employee_nameFROM employeeSTART WITH manager_id = 100;```2. 使用"CONNECT BY PRIOR"关键字指定递归查询的连接条件。

它指定了当前行与上一行之间的关系。

例如，可以将上述查询修改为查询经过多层级关系的员工：```SELECT employee_id, employee_nameFROM employeeSTART WITH manager_id = 100CONNECT BY PRIOR employee_id = manager_id;```在这个例子中，"PRIOR employee_id = manager_id"指定了下一层级的员工与上一层级的经理之间的连接关系。

3. 使用其他"WHERE"子句对查询结果进行筛选。

例如，可以添加"WHERE"子句限制只返回特定层级的员工：```SELECT employee_id, employee_nameFROM employeeSTART WITH manager_id = 100CONNECT BY PRIOR employee_id = manager_idWHERE LEVEL <= 3;```在这个例子中，"LEVEL"是递归查询中的一个伪列，表示当前行的层级。

oracle将字符串转列的函数

oracle将字符串转列的函数Oracle中的字符串转列函数可以通过使用正则表达式和字符串拆分来实现。

在Oracle中，可以使用REGEXP_SUBSTR函数来提取字符串中的特定部分，并使用CONNECT BY LEVEL子句来生成一个数字序列，然后使用SUBSTR函数将每个部分截取出来。

下面是一个示例：假设我们有一个包含以逗号分隔的字符串的表，如下所示：表名：string_table列名：string_column数据：'A,B,C,D,E,F'我们希望将字符串转为列，得到如下结果：列名：col1 col2 col3 col4 col5 col6数据：'A' 'B' 'C' 'D' 'E' 'F'为了实现这个目标，我们可以使用以下SQL语句：```SELECTSUBSTR(string_column, REGEXP_INSTR(string_column, '[^,]+', 1, LEVEL), REGEXP_INSTR(string_column, '[^,]+', 1, LEVEL + 1) - REGEXP_INSTR(string_column, '[^,]+', 1, LEVEL) - 1) AS colFROMstring_tableCONNECT BYLEVEL <= REGEXP_COUNT(string_column, '[^,]+')```在上面的SQL语句中，我们使用了REGEXP_INSTR函数来查找字符串中的逗号分隔符，并使用SUBSTR函数将每个部分截取出来。

通过使用CONNECT BY LEVEL子句，我们可以生成一个数字序列，从而将字符串拆分为多个部分。

执行上述SQL语句后，将得到如下结果：```COL---ABCDEF```这样，我们就成功地将字符串转为了列。

oracle字符串连接的方法

Oracle字符串连接的方法1. 概述字符串连接是在数据库中常见的操作之一，它用于将多个字符串拼接成一个字符串。

在Oracle数据库中，有多种方法可以实现字符串连接，本文将介绍Oracle中常用的字符串连接方法及其使用场景。

2. 使用”||“运算符进行字符串连接“||”运算符是Oracle中用于字符串连接的常用方法。

它可以将两个字符串连接成一个新的字符串。

2.1 语法string1 || string2其中，string1和string2是要连接的两个字符串。

2.2 示例SELECT 'Hello' || 'World' AS resultFROM dual;输出结果为：“HelloWorld”3. 使用CONCAT函数进行字符串连接CONCAT函数也是Oracle中常用的字符串连接方法之一。

它可以连接多个字符串，并返回连接后的结果。

3.1 语法CONCAT(string1, string2, ...)其中，string1、string2等是要连接的字符串。

3.2 示例SELECT CONCAT('Hello', ' ', 'World') AS resultFROM dual;输出结果为：“Hello World”4. 使用CONCAT_WS函数进行字符串连接CONCAT_WS函数是Oracle 12c版本中引入的新函数，它可以连接多个字符串，并使用指定的分隔符进行分隔。

4.1 语法CONCAT_WS(separator, string1, string2, ...)其中，separator是分隔符，string1、string2等是要连接的字符串。

4.2 示例SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Orange') AS resultFROM dual;输出结果为：“Apple, Banana, Orange”5. 使用LISTAGG函数进行字符串连接LISTAGG函数是Oracle中用于连接多个字符串并生成逗号分隔的列表的函数。

Oracle行转列、列转行的Sql语句总结

Oracle⾏转列、列转⾏的Sql语句总结多⾏转字符串这个⽐较简单，⽤||或concat函数可以实现SQL Code1 2select concat(id,username) str from app_user select id||username str from app_user字符串转多列实际上就是拆分字符串的问题，可以使⽤ substr、instr、regexp_substr函数⽅式字符串转多⾏使⽤union all函数等⽅式wm_concat函数⾸先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显⽰成⼀⾏,接下来上例⼦,看看这个神奇的函数如何应⽤准备测试数据 SQL Code1 2 3 4 5 6create table test(id number,name varchar2(20)); insert into test values(1,'a');insert into test values(1,'b');insert into test values(1,'c');insert into test values(2,'d');insert into test values(2,'e');效果1 : ⾏转列，默认逗号隔开SQL Code1select wm_concat(name) name from test;效果2: 把结果⾥的逗号替换成"|"SQL Code1select replace(wm_concat(name),',','|') from test;效果3: 按ID分组合并nameSQL Code1select id,wm_concat(name) name from test group by id;sql语句等同于下⾯的sql语句:SQL Code1 2 3 4 5 6-------- 适⽤范围：8i,9i,10g及以后版本（ MAX + DECODE ）select id,max(decode(rn, 1, name, null)) ||max(decode(rn, 2, ',' || name, null)) ||max(decode(rn, 3, ',' || name, null)) strfrom (select id,789101112131415161718192021222324252627282930313233343536 name,row_number () over(partition by id order by name) as rnfrom test) tgroup by idorder by 1;-------- 适⽤范围：8i,9i,10g 及以后版本（ ROW_NUMBER + LEAD ）select id, strfrom (select id,row_number () over(partition by id order by name) as rn,name || lead (',' || name, 1) over(partition by id order by name) ||lead (',' || name, 2) over(partition by id order by name) ||lead (',' || name, 3) over(partition by id order by name) as str from test)where rn = 1order by 1;-------- 适⽤范围：10g 及以后版本（ MODEL ）select id, substr (str, 2) strfrom test model return updated rows partition by (id) dimension by (row_number ()over(partition by id order by name) as rn) measures(cast (name as varchar2(20)) as str)rules upsert iterate (3) until(presentv(str [ iteration_number + 2 ], 1, 0) = 0)(str [ 0 ] = str [ 0 ] || ',' || str [ iteration_number + 1 ])order by 1;-------- 适⽤范围：8i,9i,10g 及以后版本（ MAX + DECODE ）select t.id id, max (substr (sys_connect_by_path(, ','), 2)) strfrom (select id, name, row_number () over(partition by id order by name) rnfrom test) tstart with rn = 1connect by rn = prior rn + 1and id = prior idgroup by t.id;懒⼈扩展⽤法:案例: 我要写⼀个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠⼿⼯写太⿇烦了,有没有什么简便的⽅法? 当然有了,看我如果应⽤wm_concat 来让这个需求变简单，假设我的APP_USER 表中有（id,username,password,age ）4个字段。

(完整版)ORACLE函数大全

ORACLE函数大全SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL〉 select ascii('A'）A,ascii(’a'） a，ascii('0’） zero,ascii(' ') space from dual;A A ZERO SPACE————-——-— -—---———- ---—----- ---————-—65 97 48 322.CHR给出整数,返回对应的字符;SQL〉 select chr(54740) zhao，chr(65） chr65 from dual；ZH C—— -赵 A3.CONCAT连接两个字符串；SQL> select concat('010—’，'88888888'）||'转23’高乾竞电话 from dual;高乾竞电话—-——-———-—--——-—010—88888888转234.INITCAP返回字符串并将字符串的第一个字母变为大写;SQL〉 select initcap（'smith’) upp from dual；UPP—————Smith5.INSTR（C1,C2，I，J)在一个字符串中搜索指定的字符，返回发现指定的字符的位置；C1 被搜索的字符串C2 希望搜索的字符串I 搜索的开始位置，默认为1J 出现的位置,默认为1SQL> select instr（’oracle traning’,’ra'，1,2） instring from dual；INSTRING—-—------96.LENGTH返回字符串的长度；SQL> select name，length(name)，addr，length(addr),sal,length(to_char(sal）) from gao.nchar_tst；NAME LENGTH(NAME） ADDR LENGTH(ADDR) SALLENGTH(TO_CHAR(SAL)）————-———---————-—- —--——---——----—- -———--—-—-—— ----———-————----—-——--—--—---高乾竞 3 北京市海锭区 6 9999.99 77。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

. . 1. connect by 用法总结 ................................................... 2 一、树查询(递归查询) ................................................. 2 二、列转行sys_connect_by_path() ..................................... 4 2.分析函数总结 ........................................................... 6 1.分析函数(OVER) ..................................................... 6 2.分析函数2(Rank, Dense_rank, row_number) ........................... 9 3.分析函数3(Top/Bottom N、First/Last、NTile) ........................ 9 4.窗口函数 .......................................................... 11 5.报表函数 .......................................................... 14 .

. 1.connect by 用法总结一、树查询(递归查询) 1.作用对于oracle进行简单树查询(递归查询) 列转行 2.基本语法 select ... from where ：过滤条件，用于对返回的所有记录进行过滤。

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

connect by ; ：连接条件

1)例子： select num1,num2 from table start with num2 = 1008 connect by num2 = prior num1 ; 2)解释:

start with：用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，

则表示所有满足查询条件的行作为根节点。 prior: 位置很重要（自我总结，和父在一起则自底向上，即查父和子在一起则自顶向下查子）例子原始数据 num1 为父 num2 为子 .

. 看下面的图 1. CONNECT_BY_ROOT 返回当前节点的最顶端节点。 2. CONNECT_BY_ISLEAF 判断是否为叶子节点，是1，不是0。 3. LEVEL 伪列表示节点深度。 4. SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔。 .

. 二、列转行sys_connect_by_path() 这个函数使用之前必须先建立一个树，否则无用 sys_connect_by_path(字段名, 2个字段之间的连接符号) with tmp_a as ( select '1' a,'0' p from dual union all select '2','1' from dual union all select '3','1' from dual union all select '4','3' from dual union all select '5','2' from dual union all select '6','5' from dual ) -- 子全部显示根-->子 level代表级别 select a,p,sys_connect_by_path(a,'--'),level from tmp_a start with a = 1 connect by p = prior a . . -- 2和2的所有下级去掉根-->子 (开始就要去掉) select a,p,sys_connect_by_path(a,'--') from tmp_a start with p = 1 and a <> '2' connect by p = prior a -- 2的所有下级都去掉根-->子 (connect 时去掉) select a,p,sys_connect_by_path(a,'--') from tmp_a start with a = 1 connect by p = prior a and p <> '2' --去掉2的分枝 -- 2的下一级去掉根-->子 (where 中去掉) select a,p,sys_connect_by_path(a,'--') from tmp_a where p <> '2' start with a = 1 connect by p = prior a --显示最长的根-->子 with tmp_tab as ( select '中国' s,null b from dual union all select '' s,'中国' b from dual union all select '' s,'中国' b from dual union all select '' s,'' b from dual union all select '' s,'' b from dual union all select '衡东' s,'' b from dual ) select max(sys_connect_by_path(s,'/')) from tmp_tab start with s = '' connect by prior s = b .

. 2.分析函数总结一、统计方面： Sum( ) Over ([Partition by ] [Order by ])

Sum( ) Over ([Partition by ] [Order by ] Rows Between Preceding And Following)

Sum( ) Over ([Partition by ] [Order by ] Rows Between Preceding And Current Row)

Sum( ) Over ([Partition by ] [Order by ] Range Between Interval ' ' 'Day' Preceding And Interval ' ' 'Day' Following )

二、排列方面： Rank() Over ([Partition by ] [Order by ] [Nulls First/Last])

Dense_rank() Over ([Patition by ] [Order by ] [Nulls First/Last])

Row_number() Over ([Partitionby ] [Order by ] [Nulls First/Last])

Ntile( ) Over ([Partition by ] [Order by ])

三、最大值/最小值查找方面： Min( )/Max( ) Keep (Dense_rank First/Last [Partition by ] [Order by ])

四、首记录/末记录查找方面： First_value / Last_value(Sum( ) Over ([Patition by ] [Order by ] Rows Between Preceding And Following ))

五、相邻记录之间比较方面： Lag(Sum( ), 1) Over([Patition by ] [Order by ])

1.分析函数(OVER) 一.分析函数语法: FUNCTION_NAME(,...) . . OVER () 例: sum(sal) over (partition by deptno order by ename) new_alias

sum：函数名 (sal)：参数 0~3个参数可以是表达式 Over：关键字 partition by ：（可选）分区 order by ：（可选）LAG和LEAD 需，AVG不需要，如果使用排序的开窗函数时，必须加 1)FUNCTION子句 26个分析函数,按功能分5类分析函数分类 1.等级(ranking)函数: 用于寻找前N种查询 2.开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上 3.制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列（制表与开窗的区别：制表的OVER语句上少一个ORDER BY子句） 4.LAG,LEAD函数: 可在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常用用的. 5.VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值 2)PARTITION子句分组 3)ORDER BY子句分析函数中ORDER BY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDER BY时,默认的窗口是全部的分区。在Order by子句后可以添加nulls last,如:order by comm desc nulls last表示排序时忽略comm列为空的行. 二、分析函数简单实例：按区域查找2001年度订单总额占区域订单总额20%以上的客户

【1】测试数据：

SQL> select * from orders_tmp; CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES ---------- ---------- -------------- ---------- ---------- ---------- ---------- 11 7 11 2001 7 2 12204 4 5 4 2001 10 2 37802