Oracle常用函数总结

                                                                                  Oracle常用函数

                                                                                          今天主要参照以前一个同事的总结,将oracle在查询时常用到相关的函数进行归纳,这样可以大大节省我们的工作时间,尽快的查询统计到我们想要的结果,废话不多说,下面开始介绍总结的内容和使用方法。

                                                                                          首先讲一下sql语句的执行顺序,这有助于大家理解函数的使用情形:

                                                                                  • 执行顺序为引用:链接:http://www.cnblogs.com/qanholas/archive/2010/10/24/1859924.html
                                                                                    SQL 不同于与其他编程语言的最明显特征是处理代码的顺序。在大数编程语言中,代码按编码顺序被处理,但是在SQL语言中,第一个被处理的子句是FROM子句,尽管SELECT语句第一个出现,但是几乎总是最后被处理。每个步骤都会产生一个虚拟表,该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者(客户端应用程序或者外部查询)不可用。只是最后一步生成的表才会返回 给调用者。如果没有在查询中指定某一子句,将跳过相应的步骤。
                                                                                    (8) SELECT (9) DISTINCT (11) (Top Num) {所需查询字段}
                                                                                    (1) FROM [left_table]
                                                                                    (3) (关联函数left/right/inner) JOIN [right_table]
                                                                                    (2) ON {关联条件join_condition}
                                                                                    (4) WHERE {筛选条件where_condition}
                                                                                    (5) GROUP BY {分组列表group_by_list}
                                                                                    (6) WITH (CUBE | RollUP)
                                                                                    (7) HAVING {having_condition}
                                                                                    (10) ORDER BY {排序order_by_list} DESC/ASC
                                                                                    逻辑查询处理阶段简介:
                                                                                    FROM:对FROM子句中的前两个表执行笛卡尔积(Cartesian product)(交叉联接),生成虚拟表VT1
                                                                                    ON:对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2
                                                                                    OUTER(JOIN):如果指定了OUTER JOIN(相对于 CROSS JOIN 或 INNER JOIN), 保留表(preserved table:左外部联接把左表标记为保留表,右外部联接把右表标记为保留表,完全外部联接把两个表都标记为保留表)中未找到匹配的行将作为外部行添加到VT2,生成VT3。如果FROM子句包含两个以上的表,则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3,直到处理完所有的表为止。
                                                                                    WHERE:对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4
                                                                                    GROUP BY:按GROUP BY子句中的列列表对VT4中的行分组,生成VT5
                                                                                    CUBE|ROLLUP:把超组(Suppergroups)插入VT5,生成VT6
                                                                                    HAVING:对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7
                                                                                    SELECT:处理SELECT列表,产生VT8
                                                                                    DISTINCT:将重复的行从VT8中移除,产生VT9
                                                                                    ORDER BY:将VT9中的行按ORDER BY 子句中的列列表排序,生成游标**(VC10)
                                                                                    TOP:从
                                                                                    VC10的开始处选择指定数量或比例的行,生成表VT11**,并返回调用者。

                                                                                          现将自己经常使用到的函数做如下总结,对于难理解的函数将会举例说明。

                                                                                  1. 时间及日期处理函数

                                                                                  1.1 时间格式表示方法

                                                                                  这里写图片描述

                                                                                  1.2 日期和字符串之间的转换

                                                                                  这里写图片描述

                                                                                  1.3 时间及日期的计算方法

                                                                                  这里写图片描述

                                                                                  1.4 日期常见问题处理

                                                                                  这里写图片描述


                                                                                  2. 字符串函数及数字截取函数

                                                                                  2.1 字符串函数

                                                                                  这里写图片描述

                                                                                  2.2 数字截取函数

                                                                                  这里写图片描述


                                                                                  3. 统计相关函数

                                                                                  这里写图片描述


                                                                                  4. 分组、排序、判断语句及空值处理函数

                                                                                  这里写图片描述

                                                                                  这里需要注意:

                                                                                  • group by()函数进行分组的字段需与select后面查询的字段相等,可除统计函数统计的字段外;
                                                                                  • having()函数必须和group by()函数一起搭配使用,对汇总后的数据进行筛选;
                                                                                  • case when…then…end可以作为选择判断语句,也可以作为增加列的方法。
                                                                                  • greatest/least()函数不能与group by一起使用。

                                                                                  这里列举几个例子来说明其中几个函数的用法,如下:
                                                                                  信息表:

                                                                                  姓名 班级 语文 数学 英语
                                                                                  张三 一班 70 70 70
                                                                                  李四 一班 60 90 60
                                                                                  王五 二班 80 60 80
                                                                                  赵六 二班 70 90 70
                                                                                  赵六 二班 70 90 70
                                                                                  钱七 三班 80 70 80

                                                                                  4.1 case when… then … else… end判断函数

                                                                                  判断语句:
                                                                                  这里写图片描述
                                                                                  增加列名:
                                                                                  这里写图片描述

                                                                                  4.2 decode( )判断函数

                                                                                  含义解释:此部分解释引用[https://www.cnblogs.com/vinsonLu/p/3512526.html]
                                                                                  decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
                                                                                  该函数的含义如下:
                                                                                  IF 条件=值1 THEN
                                                                                      RETURN(翻译值1)
                                                                                  ELSIF 条件=值2 THEN
                                                                                      RETURN(翻译值2)
                                                                                      …
                                                                                  ELSIF 条件=值n THEN
                                                                                      RETURN(翻译值n)
                                                                                  ELSE
                                                                                      RETURN(缺省值)
                                                                                  END IF
                                                                                  decode(字段或字段的运算,值1,值2,值3)。

                                                                                  (1)使用decode判断字符串是否一样
                                                                                  这里写图片描述
                                                                                  (2)使用decode比较大小
                                                                                  这里写图片描述
                                                                                  (3)使用decode函数分段
                                                                                  成绩>=80为high scroe,成绩介于70-80之间为mid score,成绩低于60为low score
                                                                                  这里写图片描述
                                                                                  (4)利用decode实现表或者试图的行列转换
                                                                                  这里写图片描述
                                                                                  (5)使用decode函数来使用表达式来搜索字符串
                                                                                  decode (expression, search_1, result_1, search_2, result_2, …, search_n, result_n, default)
                                                                                  decode函数比较表达式和搜索字,如果匹配,返回结果;如果不匹配,返回default值;如果未定义default值,则返回空值。
                                                                                  这里写图片描述

                                                                                  4.3 nvl、nvl2、nullif、coalesce() 函数

                                                                                  (1)nvl (expr1,expr2)
                                                                                  若expr1为NULL,则返回expr2;
                                                                                  若expr1的值非NULL,则返回expr1

                                                                                  (2)nvl2 (expr1,expr2,expr3)
                                                                                  若第一个参数为NULL,则返回expr3;
                                                                                  若第一个参数为非NULL,则返回expr2

                                                                                  (3)nullif (expr1,expr2)
                                                                                  若两个参数值相等,则返回 *NULL;
                                                                                  若两个参数不等,则返回expr1

                                                                                  (4)coalesce (expr1,expr2,…,exprn)
                                                                                  返回表达式中第一个非空表达式,如果都为空则返回空值,
                                                                                  也就是:从左往右数,遇到第一个非null值,则返回该非null值,
                                                                                  所有表达式必须是相同类型,或者可以隐式转换为相同的类型,否则报错。

                                                                                  4.4 rollup、cube、grouping sets()函数

                                                                                  (1)rollup()函数(累计累加)
                                                                                  rollup是对group by的扩展,它只能出现在group by子句中,依赖于分组的列,对每个分组会生成汇总数据, rollup和group by联合一起使用,达到了按group by列顺序分组,并且实现小计和合计的功能。rollup分组还是有序的,先全部分组,然后对每个分组小计,最后合计。rollup中列的顺序不同,则统计的结果不同。因为它是按列从右递减分组的。*比如 group by rollup(A, B, C),首先会对(A、B、C)进行group by,然后对(A、B)进行group by,然后是(A)进行group by,最后对全表进行group by操作。*举个例子:
                                                                                  这里写图片描述
                                                                                  如果在实际查询中,有的小计或合计我们不需要,那么就要使用局部rollup,局部rollup就是将需要固定统计的列放在group by中,而不是放在rollup中。
                                                                                  这里写图片描述
                                                                                  注:与group by rollup(班级,姓名)相比:去掉了最后一行的汇总,因为每次汇总要么是班级,姓名,要么是班级,null ,班级是固定的。
                                                                                  另外,若我们只希望看到合计则可以这样写:
                                                                                  这里写图片描述
                                                                                  (2)cube()函数
                                                                                  如果使用group by cube(A,B,C),,则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),?,最后对全表进行GROUP BY操作,一共是2^3=8次grouping。同rollup一样,也可以用基本的group by加上结果集的union all写出一个与group by cube结果集相同的sql:
                                                                                  这里写图片描述
                                                                                  (3)grouping sets()函数
                                                                                  grouping sets就是对参数中的每个参数做grouping,也就是有几个参数做几次grouping;例如:
                                                                                  如果使用group by grouping sets(A,B,C),则对(A),(B),?进行group by;
                                                                                  如果使用group by grouping sets((A,B),C),则对(A,B),?进行group by;
                                                                                  甚至grouping by grouping set(A,A)都是语法允许的,也就是对(A)进行2次group by,grouping sets的参数允许重复。例如:
                                                                                  这里写图片描述
                                                                                  (4)grouping()函数
                                                                                  在以上例子中,是用rollup和cube函数都会对结果集产生null,这时候可用grouping函数来确认该记录是由哪个字段得出来的。grouping函数用法,带一个参数,参数为字段名,结果是根据该字段得出来的就返回1,反之返回0。例如:
                                                                                  这里写图片描述

                                                                                  4.5 总结

                                                                                  rollup (N+1个分组方案)
                                                                                  cube (2^N个分组方案)
                                                                                  grouping sets (自定义罗列出分组方案)


                                                                                  5. 进阶级函数

                                                                                  5.1 分析函数——开窗函数over( )

                                                                                  分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。分析函数用于复杂情况(比如:取同姓名同班级最新的记录,或数学分数最高的记录,或按天统计每天统计前面几天的总额),首先介绍一下over()函数的整体框架,如下图:
                                                                                  这里写图片描述

                                                                                  下面将举例说明以上函数的用法:
                                                                                  这里写图片描述
                                                                                  over简单实例:
                                                                                  over( partition by XXX order by YYY rows between ZZZ )与sql搭配使用:

                                                                                  • sql无排序,over()排序子句省略,作为max_sal_1;
                                                                                  • sql无排序,over()排序子句有,窗口省略,作为max_sal_2;
                                                                                  • sql无排序,over()排序子句有,窗口也有,窗口特意强调全组数据,作为max_sal_3;
                                                                                    这里写图片描述
                                                                                  • sql有排序(正序),over()排序子句无,先做sql排序再进行分析函数运算,作为max_sal_4;
                                                                                    这里写图片描述
                                                                                  • sql有排序(倒序),over()排序子句有,窗口子句无, 此时的运算是:sql先选数据但是不排序,而后排序子句先排序并进行分析函数处理(窗口默认为第一行到当前行),最后再进行sql排序。
                                                                                    这里写图片描述
                                                                                    (1)over()结合count()、sum()、max()、min()、avg()等统计函数使用方法
                                                                                    这里写图片描述
                                                                                    这里写图片描述
                                                                                    (2)row_number()、rank()、dense_rank()排序函数
                                                                                    这里写图片描述
                                                                                    (3)lag()、lead()函数
                                                                                    这里写图片描述
                                                                                    (4)first_value()、last_value()函数
                                                                                    这里写图片描述
                                                                                    这里写图片描述
                                                                                    (5)cume_dist()、percent_rank()、percentile_disc()、percentile_cont、ratio_to_report()、ntile()数值分布函数
                                                                                    这里写图片描述

                                                                                  5.2 树查询函数

                                                                                  实际工作中我们会遇到很多数据表是采用树形结构存储的现象,这时需要我们使用Oracle 树查询函数、递归查询(select…start with…connect by…prior),树查询有助于我们快速查找到想要的结果,下面主要介绍一下树查询语句的使用,首先介绍一下树查询的框架,如下图:
                                                                                  这里写图片描述
                                                                                  下面举例说明每个函数的使用:

                                                                                  数据案例引用[https://www.cnblogs.com/always-online/archive/2015/10/30/4923532.html]
                                                                                  生成的菜单层次结构如下:
                                                                                  这里写图片描述

                                                                                  (1)查找树中的所有顶级父节点(辈份最长的人)
                                                                                  假设这个树是个目录结构,那么第一个操作总是找出所有的顶级节点,再根据该节点找到其下属节点
                                                                                  这里写图片描述
                                                                                  (2)查找一个节点的直属子节点(所有儿子)
                                                                                  若查找的是直属子类节点就不用树型查询
                                                                                  这里写图片描述
                                                                                  (3)查找一个节点的所有直属子节点(所有后代)
                                                                                  这里写图片描述
                                                                                  (4)查找一个节点的直属父节点(父亲)
                                                                                  若查找的是节点的直属父节点就不用树型查询
                                                                                  这里写图片描述
                                                                                  (5)查找一个节点的所有直属父节点(祖宗)
                                                                                  这里写图片描述
                                                                                  (6)查询一个节点的兄弟节点(亲兄弟)
                                                                                  这里写图片描述
                                                                                  (7)查询与一个节点同级的节点(族兄弟)
                                                                                  这里写图片描述
                                                                                  这里使用两个技巧,一个是使用了level来标识每个节点在表中的级别,还有就是使用with语法模拟出了一张带有级别的临时表。
                                                                                  (8)名称要列出名称全部路径
                                                                                  这里常见的有两种情况,一种是从顶级列出,直到当前节点的名称(或者其它属性);一种是从当前节点列出,直到顶级节点的名称(或其它属性)
                                                                                  从顶部开始:
                                                                                  这里写图片描述
                                                                                  从当前节点开始:
                                                                                  这里写图片描述
                                                                                  (9)根据菜单组分类统计每个菜单包含子菜单的个数
                                                                                  这里写图片描述
                                                                                  (10)查询所有的叶子节点
                                                                                  这里写图片描述
                                                                                  (11)connect_by_root(name)函数、connect_by_root(id)函数
                                                                                  这里写图片描述
                                                                                  这里写图片描述
                                                                                  树查询暂时介绍到这里,后续会补充相关常用的其他查询技巧语句。

                                                                                  5.3 行列转换函数

                                                                                  在工作中经常会遇到需要将行转成列或列转成行,下面就介绍一下行列转换:
                                                                                  这里写图片描述
                                                                                  用到的两个表:
                                                                                  这里写图片描述
                                                                                  这里写图片描述
                                                                                  (1)行转列-case when
                                                                                  这里写图片描述
                                                                                  (2)行转列-pivot
                                                                                  这里写图片描述
                                                                                  (3)行转列-listagg
                                                                                  这里写图片描述
                                                                                  (4)列转行-union all
                                                                                  这里写图片描述
                                                                                  (5)列转行-unpivot
                                                                                  这里写图片描述

                                                                                  发布了42 篇原创文章 · 获赞 53 · 访问量 2万+
                                                                                  展开阅读全文

                                                                                  没有更多推荐了,返回首页

                                                                                  ??2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

                                                                                  分享到微信朋友圈

                                                                                  ×

                                                                                  扫一扫,手机浏览

                                                                                  加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】| 加拿大28预测计划【复制fh118.com打开】|