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