"Oracle数据库基础知识与开窗函数详解" 在Oracle数据库中,开窗函数是一种强大的工具,它允许我们在单个SQL查询中对数据进行复杂的分析和聚合操作,而无需使用子查询或临时表。以下是对标题和描述中提及的知识点的详细解释: ### 1. `SUM() OVER (PARTITION BY ... ORDER BY ...)` `SUM() OVER (ORDER BY ...)` 是开窗函数的一种用法,用于计算基于指定排序顺序的累计和。例如: - **1.1** `SUM(v2) OVER (ORDER BY v2)` 按照`v2`的值升序排序,逐行累加`v2`的值,从第一行开始累积到当前行。 - **1.2** `SUM(v2) OVER (PARTITION BY v1 ORDER BY v2)` 首先按`v1`分组,然后在每个组内按照`v2`的值进行排序并累加。 - **1.3** `SUM(v2) OVER (PARTITION BY v1)` 在`v1`分组内从第一行到最后一行累加`v2`。 - **1.4** `DISTINCT v1, (SELECT SUM(v2) OVER (PARTITION BY v1))` 使用`DISTINCT`去除`v1`列的重复数据,同时计算每个唯一`v1`值的`v2`之和。 - **1.5** 反向累加只需将`ORDER BY`后的列改为降序即可,如`ORDER BY v2 DESC`。 ### 2. `ROW_NUMBER() OVER (ORDER BY ...)` `ROW_NUMBER() OVER (ORDER BY ...)` 为查询结果中的每一行分配一个唯一的序列号,根据指定的列进行排序: - **2.1** `ROW_NUMBER() OVER (ORDER BY col_1[,col_2])` 根据`col_1`和可选的`col_2`列升序排列,并为每一行赋予一个不同的序列号。 - **2.2** `ROW_NUMBER() OVER (PARTITION BY col_n[,col_m] ORDER BY col_1[,col_2])` 先按`col_n`和`col_m`分组,然后在每个组内按照`col_1`和`col_2`排序,返回排序后的行号。 ### 3. `ROWNUM` `ROWNUM` 是Oracle中的一个伪列,它自动为查询返回的每一行分配一个从1开始的连续整数。但是,`ROWNUM`不能与表名关联,并且只能与小于或等于运算符(`<`, `<=`)一起使用,用于限制返回的行数。 ### 4. `ROUND(XXX, 2)` `ROUND(XXX, 2)` 函数用于四舍五入数字`XXX`到指定的小数位数,这里是两位。如果省略第二个参数,则默认四舍五入到最接近的整数。 ### 5. `LISTAGG(field, ',') WITHIN GROUP (ORDER BY field)` `LISTAGG()` 函数将列的值合并成一个字符串,每个值之间由指定的分隔符分隔: - **例1** `LISTAGG(a, '--') WITHIN GROUP (ORDER BY a) GROUP BY b` 根据`b`分组,将每个组内`a`列的值按照`a`的顺序连接在一起,分隔符为'--'。 - **例2** `LISTAGG(a, '--') WITHIN GROUP (ORDER BY a) OVER (PARTITION BY b)` 根据`b`分组,但不进行合并,而是保持每个组内`a`列的值按顺序连接,分隔符同样为'--'。 ### 6. 其他开窗函数 Oracle还提供了其他开窗函数,如`RANK()`, `DENSE_RANK()`, `LEAD()`, `LAG()`, `FIRST_VALUE()`, `LAST_VALUE()` 等,它们在数据分析和报表生成时非常有用。 理解并熟练运用这些开窗函数,能极大地提高你在处理复杂数据查询时的效率,帮助你编写出更加精炼和高效的SQL语句。
1.1 select v1,v2,sum(v2)over(order by v2)
按照v2排序,累计求v2的和,从第一行开始累积到当前行为止
1.2 select v1,v2,sum(v2) over(partition by v1 order by v2)
先根据v1进行分组,在组内累计求和
1.3 select v1,v2,sum(v2) over(partition by v1)
根据v1进行分组 然后求和 从组内的第一条开始一直累计到组内的最后一条为止
1.4 select distinct v1,v2 ( select v1,sum(v2) over(partition by v1))
distinct 去除v1列重复的数据
1.5 也可以逆序累加 只需要 order by desc 即可
2.row_number() over 用法
2.1 row_number() over (order by col_1[,col_2 ...])
按照col_1[,col_2 ...]排序,返回排序后的结果集,并且为每一行返回一个不相同的值。
2.2 row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
先按照col_n[,col_m ...进行分组,再在每个分组中按照col_1[,col_2 ...]进行排序(升序),最后返回排好序后的结果集
3.rownum ----
对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,
这个伪字段可以用于限制查询返回的总行数,且rownum不能以任何表的名称作为前缀。只能与< ,<= 连用
4.ROUND(XXX,2) 截取两位小数
5.listagg(field,',') within group(order by field) 列转行函数 field:要转行的列 第二个参数:分割符
例:select b,listagg(a,'--') within group(order by a)
from aa
group by b
根据b进行分组,把组内a列的数据放在一行 分组显示
例2:也可以不分组
select c, b,listagg(a,'--') within group(order by a) over(partition by b)
from aa
根据b进行分组,把组内a列的数据放在一行 不会分组显示
6.select SEQ_COM_LOG.nextval from dual (orcal中的一个万能虚拟表) SEQ_COM_LOG.nextval 得到序列的下一个值
7.今天:trunc(sysdate,'dd')
下载后可阅读完整内容,剩余2页未读,立即下载
- 粉丝: 6
- 资源: 3
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- 李兴华Java基础教程:从入门到精通
- U盘与硬盘启动安装教程:从菜鸟到专家
- C++面试宝典:动态内存管理与继承解析
- C++ STL源码深度解析:专家级剖析与关键技术
- C/C++调用DOS命令实战指南
- 神经网络补偿的多传感器航迹融合技术
- GIS中的大地坐标系与椭球体解析
- 海思Hi3515 H.264编解码处理器用户手册
- Oracle基础练习题与解答
- 谷歌地球3D建筑筛选新流程详解
- CFO与CIO携手:数据管理与企业增值的战略
- Eclipse IDE基础教程:从入门到精通
- Shell脚本专家宝典:全面学习与资源指南
- Tomcat安装指南:附带JDK配置步骤
- NA3003A电子水准仪数据格式解析与转换研究
- 自动化专业英语词汇精华:必备术语集锦