​​​​​​​Oracle11 分析函数等高级函数使用说明

news/2024/10/11 13:59:08/文章来源:https://blog.csdn.net/m516387177/article/details/142184685

目录

1、Group by

2、Connect by

3、分析函数

4、Model

5、Listagg与自定义聚合函数

1、Group by

与GROUP BY 一起使用的关建字 GROUPING , ROLLUP,CUBE,结合这些特性的函数可以实现SQL的明细+分组统计


GROUPING 记录是对哪个字段进行统计
select  deptno,job,sum(sal),grouping(deptno),grouping(job) from emp  group by  rollup( deptno,job);

等价于

select deptno,job,sum(sal) from emp group by deptno,job

union all

select deptno,null,sum(sal) from emp group by deptno

union all

select null,null,sum(sal) from emp ;


关建字 CUBE

select  deptno,job,sum(sal),grouping (deptno),grouping(job) from emp  group by    cube( deptno,job);

 等价于

select deptno,job,sum(sal) from emp group by deptno,job

union all

select deptno,null,sum(sal) from emp group by deptno

union all

select null,null,sum(sal) from emp

union all

select null,job,sum(sal) from emp group by job

关建字 GROUP SETS  指定聚合方式

select  deptno,job,sum(sal),grouping (deptno),grouping(job) from emp  group by   grouping sets(deptno,job,null) ;

select  deptno,job,sum(sal),grouping (deptno),grouping(job) from emp  group by   grouping sets((deptno,job),null) ;

2、Connect by

CONNECT BY 语句特有的函数

SYS_CONNECT_BY_PATH   获取节点在整个树结构的路径

CONNECT_BY_ROOT,      获取节点规属的根节点

CONNECT_BY_ISLEAF,      判断该节点是否存在子节点

CONNECT_BY_ISCYCLE     该节点是否循环

LEVEL 伪列,用来指定节点在树结构里的层次

对 CONNECT BY 的结果集的每层进行排序

SELECT ……. FROM  TABLENAME

CONNECT BY PRIOR ID = F_ID

START WITH F_ID = 0

ORDER SIBLINGS BY ID DESC


无限记录的语句
select  level from dual connect by 1=1
显示20个记录结果集
select  level from dual connect by 1=1 and level<=20

显示一个树结构层次的
select  lpad(' ',(level-1)*10,' ')||ename,level from emp  connect by prior  empno=mgr  start with mgr is null


理解以下语句的执行
select  * from emp where empno=7369 connect by    empno=empno  ;

select * from emp  where empno=7369 connect by empno=empno and sys_guid() is not  null;

select * from emp  where empno=7369 connect by empno=empno and sys_guid() is    null;

select  * from emp where empno=7369 connect by prior   empno=empno;

select  * from emp where empno=7369 connect by prior   empno=empno and prior sys_guid() is null;

select  * from emp where empno=7369 connect by prior   empno=empno and   sys_guid() is null;

select  * from emp where empno=7369 connect by prior   empno=empno and prior sys_guid() is not null;

3、分析函数

关建字     分析函数 OVER (PARTITION BY …… ORDER BY ……)

PARTITION BY 分组

ORDER BY    排序

ROW_NUMBER ：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号,排行值相同,也是显示不间断流水号

RANK  ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置, 如果排序值相同,rank()值相同

DENSE_RANK ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置, 与rank相似,只是不会跳号

SUM       ：该函数计算组中表达式的累积和

MIN       ：在一个组中的数据窗口中查找表达式的最小值

MAX       ：在一个组中的数据窗口中查找表达式的最大值

AVG        ：用于计算一个组和数据窗口内表达式的平均值。

COUNT     ：对一组内发生的事情进行累积计数

FIRST      ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行

LAST      ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行

FIRST_VALUE ：返回组中数据窗口的第一个值

LAST_VALUE  ：返回组中数据窗口的最后一个值。

LAG         ：可以访问结果集中的其它行而不用进行自连接

LEAD         ：LEAD与LAG相反，LEAD可以访问组中当前行之后的行

例:

select empno,sal,

       row_number() over(order by sal) x,

       rank()       over(order by sal) y,

       dense_rank() over(order by sal) z

from emp where deptno=30;


select empno,ename,sal,deptno,

       row_number() over(partition by deptno order by sal) x,

       rank()       over(partition by deptno order by sal) y,

       dense_rank() over(partition by deptno order by sal) z

from emp;
 

执行以下SQL,看看查询后的结果集

1.

select empno, ename, deptno, sal,

       sum(sal) over (partition by deptno order by ename) x

from emp;  

--注意PARTITION BY, ORDER BY

2.

select empno, ename, deptno, sal,

       sum(sal) over (partition by deptno order by ename

                      rows between unbounded preceding and current row) x

from emp;  

--注意ROWS BETWEEN unbounded preceding AND current row  

--是指第一行至当前行的汇总 

3.

select empno, ename, deptno, sal,

       sum(sal) over (partition by deptno order by ename

                      rows between current row and unbounded following) x

from emp;  

--注意ROWS BETWEEN current row AND unbounded following  

--是指当前行到最后一行的汇总 

4.

select empno, ename, deptno, sal,

       sum(sal) over (partition by deptno order by ename

                      rows between 1 preceding and current row) x

from emp;  

--注意ROWS BETWEEN 1 preceding AND current row

--是指当前行的上一行(rownum-1)到当前行的汇总 

5.

select empno, ename, deptno, sal,

       sum(sal) over (partition by deptno order by ename

                      rows between 1 preceding and 2 following) x

from emp;  

--注意ROWS BETWEEN 1 preceding AND 1 following

--是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

4、Model

这个函数一般不太常见,但确是非常有用的一个函数
具体的官方资料可以参考

noname.htm

语法定义如下

--MODEL:MODEL语句的关键字，必须。

--DIMENSION BY: DIMENSION维度的意思,必须。

--MEASURES:指定作为数组的列,可以定义出许多有规则的伪列

--RULES:对数组的各列进行各种操作规则的定义。


 

执行以下SQL,看看结果集,理解model 函数

with t as (select  deptno,to_char(emp.hiredate,'yyyy') year,sum(sal) sal from emp group by deptno,to_char(emp.hiredate,'yyyy'))

select deptno,year,sal,p_sal

from t

model

dimension by (deptno,year)

measures (sal,0 p_sal)

rules

(

p_sal[any,any]=sal[cv(),cv(year)-1]

);

select   ename,sales,xxx from emp

model

dimension by (ename)

measures (sal sales,0 xxx)

rules

(

xxx[any]=sum(sales)[ cv(ename)='CLARK' AND (ename)='CLARK']

);

select   ename,sales,xxx from emp

model

dimension by (ename)

measures (sal sales,0 xxx)

rules

(

xxx[any]= sales[ ename='SMITH']

);

select   ename,sales,xxx from emp

model

dimension by (ename)

measures (sal sales,0 xxx)

rules

(

xxx[any]= sum(sales)[ cv(ename)='SMITH']

);

select   ename,sales,xxx from emp

model

dimension by (ename)

measures (sal sales,0 xxx)

rules

(

xxx[any]= sum(sales)[ cv(ename)='SMITH']

);

///

比较这两个SQL的结果

with t as (select  deptno,to_char(emp.hiredate,'yyyy') year,sum(sal) sal from emp group by deptno,to_char(emp.hiredate,'yyyy'))

select deptno,year,sal,p_sal

from t

model

dimension by (deptno,year)

measures (sal,0 p_sal)

rules

(

p_sal[any,any]=sum(sal)[cv(),cv() is not null ]

);

with t as (select  deptno,to_char(emp.hiredate,'yyyy') year,sum(sal) sal from emp group by deptno,to_char(emp.hiredate,'yyyy'))

select deptno,year,sal,p_sal

from t

model

dimension by (deptno,year)

measures (sal,0 p_sal)

rules

(

p_sal[any,any]=sum(sal)[any,cv() is not null ]

);

5、Listagg与自定义聚合函数


Listagg 函数是11g以上才出现的,针对行转列的函数(10g不支持改函数)

listagg函数的语法结构如下：
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

listagg虽然是聚合函数，但可以提供分析功能（比如可选的OVER()子句）。使用listagg中，下列中的元素是必须的：1>需要聚合的列或者表达式  2>WITHIN GROUP 关键词 3>分组中的ORDER BY子句

select deptno, listagg(ename, ',') within group(order by null)

  from emp

 group by deptno;

另外介绍一个 相对listagg 这个函数  的行转列的函数 regexp_string

 select regexp_substr('ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD',

                     '[^,]+',

                     1,

                     level)

  from dual

connect by regexp_count('ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD', '[^,]+') >=

           level

在不支持regexp_count的数据库环境下,可以使用 length ,  replace 这两个函数来实现它的效果

针对 11g以下的数据库,不能使用 listagg这个函数,可以进行自定义聚合函数来替代此函数

oracle自定义聚集函数接口简介

   a. static function ODCIAggregateInitialize(sctx IN OUTstring_agg_type) return number

   自定义聚集函数初始化设置,从这儿开始一个聚集函数

   b. member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2)  return number

    自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self 为当前聚集函数的指针,用来与前面的计算结果进行关联

   c. member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT  varchar2,flags IN number)  return number

     用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.

    d. member function OCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number) return number

     终止聚集函数的处理,返回聚集函数处理的结果.

下面代码 是实现 listagg 函数效果的 自定义函数

实现效果如下图所示

实现代码如下:

create or replace type cux_listagg as object (

    --定义变量

    result_string varchar2(4000),

    --自定义聚集函数初始化设置,从这儿开始一个聚集函数

    static function ODCIAggregateInitialize(cs_ctx In Out cux_listagg) return number,

    --自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作

    --self 为当前聚集函数的指针,用来与前面的计算结果进行关联

    member function ODCIAggregateIterate(self In Out cux_listagg,value in varchar2) return number,

    -- 用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.

    member function ODCIAggregateMerge(self In Out cux_listagg,ctx2 In Out cux_listagg) return number,

    --终止聚集函数的处理,返回聚集函数处理的结果.

    member function ODCIAggregateTerminate(self In Out cux_listagg,returnValue Out varchar2,flags in number) return number

)

/

create or replace type body cux_listagg is

  --自定义聚集函数初始化设置,从这儿开始一个聚集函数

  static function ODCIAggregateInitialize(cs_ctx IN OUT cux_listagg) return number

  is

  begin

      cs_ctx := cux_listagg(null);

      return ODCIConst.Success;

  end;

    --自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作

    --self 为当前聚集函数的指针,用来与前面的计算结果进行关联

  member function ODCIAggregateIterate(self IN OUT cux_listagg,

                                       value IN varchar2 )

  return number

  is

  begin

      self.result_string := self.result_string || ','|| value;

      return ODCIConst.Success;

  end;

 --终止聚集函数的处理,返回聚集函数处理的结果.

  member function ODCIAggregateTerminate(self IN Out cux_listagg,

                                         returnValue OUT varchar2,

                                         flags IN number)

  return number

  is

  begin

      returnValue := ltrim(rtrim(self.result_string,','),',');

      return ODCIConst.Success;

  end;

-- 用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.

    

  member function ODCIAggregateMerge(self IN OUT cux_listagg,

                                     ctx2 IN Out cux_listagg)

  return number

  is

  begin

      self.result_string := self.result_string || ',' || ctx2.result_string;

      return ODCIConst.Success;

  end;

end;

/

再对这个type进行函数的创建

CREATE or replace

FUNCTION f_row_column(input varchar2 )

RETURN varchar2

PARALLEL_ENABLE AGGREGATE USING cux_listagg;

/

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.ldbm.cn/p/443382.html

如若内容造成侵权/违法违规/事实不符，请联系编程新知网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

信息安全数学基础（7）最小公倍数

信息安全数学基础（7）最小公倍数

前言 在信息安全数学基础中&#xff0c;最小公倍数&#xff08;Least Common Multiple, LCM&#xff09;是一个重要的概念&#xff0c;它经常与最大公约数&#xff08;Greatest Common Divisor, GCD&#xff09;一起出现&#xff0c;两者在数论、密码学、模运算等领域都有广泛的…
阅读更多...
手把手教你：在微信小程序中加载map并实现拖拽添加标记定位

手把手教你：在微信小程序中加载map并实现拖拽添加标记定位

本文将为大家详细介绍如何在微信小程序中加载map组件&#xff0c;并实现拖拽标记定位功能。 实现步骤 1、首先&#xff0c;我们需要在项目的app.json文件中添加map组件的相关配置。如下所示&#xff1a; {"pages": ["pages/index/index"],"permiss…
阅读更多...
CesiumJS+SuperMap3D.js混用实现可视域分析 S3M图层加载 裁剪区域绘制

CesiumJS+SuperMap3D.js混用实现可视域分析 S3M图层加载 裁剪区域绘制

版本简介&#xff1a; cesium&#xff1a;1.99&#xff1b;Supermap3D&#xff1a;SuperMap iClient JavaScript 11i(2023)&#xff1b; 官方下载文档链家&#xff1a;SuperMap技术资源中心|为您提供全面的在线技术服务 示例参考&#xff1a;support.supermap.com.cn:8090/w…
阅读更多...
Axure RP实战：打造高效图形旋转验证码

Axure RP实战：打造高效图形旋转验证码

Axure RP实战&#xff1a;打造高效图形旋转验证码 在数字产品设计的海洋中&#xff0c;验证码环节往往是用户交互体验的细微之处&#xff0c;却承载着验证用户身份的重要任务。 传统的文本验证码虽然简单直接&#xff0c;但随着用户需求的提高和设计趋势的发展&#xff0c;它…
阅读更多...
智慧火灾应急救援：无人机、直升机航拍视角下的火灾应急救援检测数据集代码

智慧火灾应急救援：无人机、直升机航拍视角下的火灾应急救援检测数据集代码

智慧火灾应急救援&#xff1a;无人机、直升机航拍视角下的火灾应急救援检测数据集 引言 随着科技的发展&#xff0c;无人机、直升机等飞行器在火灾应急救援中的应用越来越广泛。这些飞行器不仅能快速到达火场&#xff0c;而且可以通过搭载的高清摄像机和其他传感器获取火场的…
阅读更多...
DFS：深搜+回溯+剪枝实战解决OJ问题

DFS：深搜+回溯+剪枝实战解决OJ问题

✨✨✨学习的道路很枯燥&#xff0c;希望我们能并肩走下来! 文章目录 目录 文章目录 前言 一 排列、子集问题 1.1 全排列I 1.2 子集I 1.3 找出所有子集的异或总和 1.4 全排列II 1.5 字母大小写全排列 1.6 优美的排列 二 组合问题 2.1 电话号码的数字组合 …
阅读更多...
linux驱动开发-arm汇编基础

linux驱动开发-arm汇编基础

目录 写在前面 1、Cortex-A7 处理器有 9 种处理模式 2、Cortex-A 寄存器组 通用寄存器 1、汇编语法 2、Cortex-A7 常用汇编指令 2.1 处理器内部数据传输指令 2.1.1 传输数据操作类型 1、MOV指令 2、MRS指令 3、MSR指令 2.2、存储器访问指令 2.2.1 LDR指令 2.2.2 …
阅读更多...
电气自动化入门02：三相交流电及其主要应用参数

电气自动化入门02：三相交流电及其主要应用参数

视频链接&#xff1a;1.2 电工知识&#xff1a;三相交流电及其主要应用参数_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1PJ41117PW?p3&vd_sourceb5775c3a4ea16a5306db9c7c1c1486b5 1.什么是交流电 交流电就是&#xff1a;大小和方向都随时间不断变化的电流 2…
阅读更多...
人工智能时代，程序员如何保持核心竞争力？

人工智能时代，程序员如何保持核心竞争力？

随着AIGC&#xff08;如ChatGPT、MidJourney、Claude等&#xff09;大语言模型的快速发展&#xff0c;程序员的工作模式正经历前所未有的变革。虽然AI辅助编程工具的普及为程序员提供了更高的工作效率&#xff0c;但也引发了对未来工作的深刻思考。面对这一趋势&#xff0c;程序…
阅读更多...
Idea 中的一些配置

Idea 中的一些配置

配置 javap jdk 自带的 javap 可以用来查看字节码信息。 配置过程&#xff1a; 打开设置&#xff0c;定位到 Tools&#xff0c;External Tools新建项&#xff0c;Program 中填 javap 的路径Argument 中填 -c $FileClass$Working directory 中填 $OutputPath$ Argument 中也…
阅读更多...
计算机三级网络技术总结（三）

计算机三级网络技术总结（三）

宽带&#xff08;bandwidth&#xff09;单位是kbpspos framing sdh / pos framing sonetpos flag s1s0 2 / pos flag s1s0 0CRC 32network <目的网络的ip地址><子网掩码的反码>area 0area 0 range<子网地址><子网掩码>ip route <目的网络地址>&l…
阅读更多...
前端 + 接口请求实现 vue 动态路由

前端 + 接口请求实现 vue 动态路由

前端 接口请求实现 vue 动态路由 在 Vue 应用中&#xff0c;通过前端结合后端接口请求来实现动态路由是一种常见且有效的权限控制方案。这种方法允许前端根据用户的角色和权限&#xff0c;动态生成和加载路由&#xff0c;而不是在应用启动时就固定所有的路由配置。 实现原理…
阅读更多...
2024.9.16 day 1 pytorch安装及环境配置

2024.9.16 day 1 pytorch安装及环境配置

一、配置pytorch环境&#xff0c;安装pytorch 1.查看python版本 python --version 2.在anaconda命令中创建pytorch环境 conda create -n pytorch python3.12(python版本&#xff09; 3.pytorch安装 pytorch首页 PyTorchhttps://pytorch.org/ os为windows推荐package选择…
阅读更多...
【Java数据结构】二叉树

【Java数据结构】二叉树

目录 树树的特征树的概念 二叉树两种特殊的二叉树二叉树的性质二叉树的基本操作4 种遍历二叉树的方式判断一棵树是不是完全二叉树获取二叉树总共的节点个数获取叶子节点的个数获取第 k 层的节点个数获取二叉树的高度检测值为 value 的元素是否存在 二叉树基本操作完整代码 树 …
阅读更多...
【机器学习】--- 自监督学习

【机器学习】--- 自监督学习

1. 引言 机器学习近年来的发展迅猛&#xff0c;许多领域都在不断产生新的突破。在监督学习和无监督学习之外&#xff0c;自监督学习&#xff08;Self-Supervised Learning, SSL&#xff09;作为一种新兴的学习范式&#xff0c;逐渐成为机器学习研究的热门话题之一。自监督学习…
阅读更多...
Vite - 依赖预购建

Vite - 依赖预购建

目录 1&#xff0c;目的1.1&#xff0c;CommonJS 和 UMD 模块的兼容性1.2&#xff0c;性能 2&#xff0c;作用范围和配置2.1&#xff0c;作用范围2.2&#xff0c;自定义配置 4&#xff0c;缓存实现过程5&#xff0c;注意点 官方文档 1&#xff0c;目的 主要是下面2点&#xf…
阅读更多...
Expectation disarray Analysts’ growth forecast anomaly in China

Expectation disarray Analysts’ growth forecast anomaly in China

Expectation disarray: Analysts’ growth forecast anomaly in China 论文阅读 文章目录 Expectation disarray: Analysts’ growth forecast anomaly in China 论文阅读 Abstract中美市场的差异如何调和中美市场的相似性和差异为什么美国分析师预测导致负向收益 MethodologyD…
阅读更多...
2、vectorCast集成测试常用功能

2、vectorCast集成测试常用功能

一、什么是软件集成测试 软件集成测试主要来源于A-SPICE和ISO26262这两个汽车行业内的标准规范。 用途与范围 纯软件模块的功能测试，不涉及和硬件相关的功能测试 模块/组件内部的功能测试(module/component test) 模块与模块之间的接口测试 模块/组件的划分需参考softwa…
阅读更多...
1.使用 IDEA 过程中的英语积累 - File 菜单（每一次重点积累 5 个单词）

1.使用 IDEA 过程中的英语积累 - File 菜单（每一次重点积累 5 个单词）

前言 学习可以不局限于传统的书籍和课堂&#xff0c;各种生活的元素也都可以做为我们的学习对象&#xff0c;本文将利用 IDEA 页面上的各种英文元素来做英语的积累&#xff0c;如此做有 3 大利 这些软件在我们工作中是时时刻刻接触的&#xff0c;借此做英语积累再合适不过&…
阅读更多...
MATLAB系列03：分支语句和编程设计

MATLAB系列03：分支语句和编程设计

MATLAB系列03&#xff1a;分支语句和编程设计 3. 分支语句和编程设计3.1 自上而下的编程方法简介3.2 伪代码的应用3.3 关系运算符和逻辑运算符3.3.1 关系运算符3.3.2 小心和~运算符3.3.3 逻辑运算符3.3.4 逻辑函数 3.4 选择结构3.4.1 if结构3.4.2 switch结构3.4.3 try/catch结构…
阅读更多...
推荐文章
最新文章

Copyright @ 2022~2023