几周前团队的大神曾为大家整理和分享了多个PYTHON实用小技巧【这里是传送门：https://mp.weixin.qq.com/s/KOL9l_eG-JXqMH0p4a2Ymg】，笔者学习后颇受启发，结合自己近半年来与ORACLE相爱相杀的经历，决定向大家分享一下我心中的十个ORACLE必备小知识。
笔者虽是计算机专业出身，但对Oracle的学习仅限于大学时期老师在课堂上讲授的基础DML(DataManipulation Language，数据操作语言)和DDL(DataDefinition Language，数据定义语言), 因此在最开始做Data Request时，每天最头疼的莫过于要将复杂的业务逻辑转化成有条理的SQL语言，如今已过去快小半年，写SQL的本领虽不至于炉火纯青，但也算是有了一些小心得，所以本篇公众号就想把这些小心得分享给大家。假设我们有如下数据集，命名为temp_table，其中“编号”列为主键【此数据集为笔者虚构，如有雷同纯属巧合】
在orcacle中如下图所示：
一、 条件判断语句CASE WHEN 和 DECODE(1)CASE WHEN语句：语法：
如果条件1成立，那么返回返回值1；如果条件2成立，那么返回返回值2；……以此类推，如果所有条件都不满足，那么返回默认值。常见应用场景：(包含但不限于)· 当需要找出符合某个条件或某些条件的数据时；· 进行等值/范围转换时；· ……举例：在temp_table中找出出生地为北京或天津的女性，归为TYPE1；出生地为上海或重庆的男性，归为TYPE2；其余的归为OTHERS。
(2)DECODE()函数语法：
如果条件/列 = 列值1，那么返回返回值1；如果条件/列 = 列值2，那么返回返回值2；…..以此类推，如果所有条件都不满足，那么返回默认值。常见应用场景：(包含但不限于)· 一般用于等值判断。注意：DECODE只有Oracle才有，用法相对于CASEWHEN语句来说要简洁很多，但没有CASE WHEN 语句灵活性高。举例：对temp_table中的性别进行编码，若为女性则编码为0，男性编码为1。
二、分析函数ROW_NUMBER() OVER ()通过 row_number() over()函数可以对查询出的结果集添加一列序号(序号从1开始)，并通过这列序号对结果集进行再次过滤。常见应用场景：(包含但不限于)· 给每条记录的排序进行编号；· 给分组内的每条记录进行编号；· 查找每个分组内前N个或最后N个数据；· ……(1)ROW_NUMBER() OVER (ORDER BY )：语法：
将查询出的结果集按照排序列进行排序(默认为升序，可自行设置为降序排列desc)，并对结果编号。
举例：对temp_table中的女性按照出生日期进行排序并给出相应的序号。
有了序号我们就可以很轻松的筛选出最早出生的前N个女性的数据，这里假设N为3，即筛选出出生最早的前3个女性：
(2)ROW_NUMBER() OVER(PARTITION BY ORDER BY)语法：
将查询出的结果集先按照分组列进行分组，然后对每组按照排序列进行排序，并对结果编号。
举例：按性别分组，对temp_table中两个组群根据出生日期排序并给出相应的序号。
同样的，知道了序号，我们可以很容易的筛选出男性组群和女性组群中出生最早的前三个人的信息。
注意：在使用row_number() over()函数时候，over()中的分组以及排序的执行晚于where 、group by、order by 的执行。三、正则表达(模糊匹配)函数REGEXP_LIKE() REGEXP_LIKE()函数是ORACLE的特有函数，与LIKE的功能相似用法相同，常用于WHERE语句中作为筛选条件，但与Like相比最明显的高级之处在于REGEXP_LIKE()可以以非常简洁的形式实现对字符串进行多个值的模糊匹配。语法：
常用的通配符：| 通配符 | 作用 |
| ^ | 匹配输入字符串的开始位置；当在方括号中使用时表示排除该字符集合 |
| $ | 匹配输入字符串的结尾位置 |
| . | 匹配除换行符之外的任何单个字符 |
| ? | 匹配对应的子表达式零次或一次 |
| + | 匹配对应的子表达式一次或多次 |
| * | 匹配对应的子表达式零次或多次 |
| | | 与OR的作用相似 |
在temp_table表中，找出姓名中包含红、黄、绿、兰、小的数据。
四、处理缺失值函数NVL(), NVL2()NVL(), NVL2() 常用在SELECT语句中，用于对缺失值进行处理。(1)NVL()语法：
若指定列中的值为缺失值，则返回返回值。举例：将temp_table中出生地为空的数据用“未知”代替。
(2)NVL2()语法：
若指定列中的值不为缺失值，则返回返回值1，否则返回返回值2。举例：将temp_table中出生地为空的数据行标识为“未知”，否则标识为“已知”。
五、取整函数家族—CEIL(), FLOOR(), ROUND(), TRUNC()各个函数和其作用如下：| 函数 | 作用 |
| CEIL(数值N) | 取大于等于数值N 的最小整数 |
| FLOOR(数值N) | 取小于等于数值N的最大整数 |
| ROUND(数值N[,保留小数位数]) | 按指定的小数保留位数进行四舍五入，默认小数保留位数为0 |
| TRUNC(数值N[,保留小数位数]) | 按指定的小数保留位数进行直接截取，不四舍五入，默认小数保留位数为0 |
举例：
除此之外，TRUNC()函数也可以用于处理时间，语法为：
举例：
六、类型转换函数家族—TO_NUMBER(),TO_CHAR(), TO_DATE()(1)TO_NUMBER()函数该函数用于将字符类型(CHAR或VARCHAR2)的字符串转换成NUMBER类型的数值；需要注意的是，被转换的字符串必须是数值类型的格式，否则会报错。语法：
举例：将temp_table中的“编号”列从VARCHAR2类型转化为NUMBER类型。
(2)TO_CHAR()函数该函数用于将数值型或日期型的数据转换成字符型。
语法：
举例：根据temp_table中的“出生日期”列，创建字符型的“出生年月”列。
(3)TO_DATE()函数该函数用于将一个日期形式的字符串转换成为DATE类型。
语法：
举例：
常用的日期格式：| 表示形式 | 含义 |
| YYYY,YYY,YY,Y | 年份，分别代表四位年份、年份最后三位、最后二位、最后一位 |
| MM,MON,MONTH | 月份，分别代表数字形式的月份、英文简写月份、英文全写月份 |
| WW,W | 周，分别代表某年的第几周、某月的第几周 |
| DDD,DD,D | 天，分别代表某年第几天、某月第几天、某周第几天 |
| HH,HH24 | 小时，分别代表按12小时计、按24小时计 |
| MI | 分钟【注意， ORACLE中以MI代表分钟，MM代表月份】 |
| SS | 秒 |
在temp_table中，以2019年9月30日为时间点，计算每个人的年龄。
七、行列转换函数PIVOT()该函数用于将某一列中的某些特征值各自转换为一列。
语法：
举例：针对temp_table中的出生地进行行列转换。
常用的聚合函数：| 聚合函数 | 作用 |
| Count() | 返回数据的数量 |
| Max() | 返回给定数据中的最大值 |
| Min() | 返回给定数据中的最小值 |
| Avg() | 返回给定数据的平均值 |
| Sum() | 返回给定数据的总和 |
| Variance() | 返回给定数据的标准差 |
| Stddev() | 返回给定数据的方差 |
举例：
说起SUBSTR()就不得不提一下它的好兄弟INSTR()函数，INSTR()函数用于返回子字符串在源字符串中的位置，语法为：
其中：开始截取的位置默认从1开始，若为负数，则从右向左开始。子字符串第几次出现在源字符串中默认为第1次，不可为负数。举例：
SUBSTR()函数和INSTR() 函数一起使用时：
上面的例子目的是从源字符串“kpmg大数据挖掘”中把“kpmg”截取出来，从第1个字符开始，使用INSTR函数判断“大”字的位置，进而确定截取几个字符串。减1是因为通过INSTR ()获取的是源字符串中“大”字第一次出现的位置，再减1就得出了截取字符的个数。九、提高程序运行速度的两个方法(1)/*+driving_site(TableName)*/语句【TableName一般为较大表】该语句常用于使用DBLINK远程访问数据库时的优化，可以极大的提升SQL的运行效率。由于远程访问数据库时网络传输会耗费大量时间，当指定 /*+driving_site(BigTable)*/ 时, oracle会从较小的表中获取全部数据传输到较大的表(BigTable)所在的数据库来进行关联和拼接，从而减少网络传输的时间，提高程序运行的速度。语法：
(2)并行模式/*+parallel(TableName, N)*/语句当表的数量级超过千万且服务器存在多个CPU时，可以考虑使用并行模式。并行模式可以强制启动并行进程，在大表查询等操作中可以极大的缩短计算时间。但要注意的是，并行模式是以消耗资源为代价，所以不建议在系统超负荷运行时使用。并行模式会把待处理的数据集分成很多小的子数据集，并根据规定的进程数量N启动相应个数的并行进程同时处理这些子数据集，最后将所有的结果汇总作为最终结果返回给用户。语法：
十、子查询语句WITH AS()你有没有遇到过，需要建立很多中间表才能得到最终的目标结果的情况，但若实际情况不允许我们建立中间表时该怎么办呢？这时就轮到WITH AS()语句出场了！WITH AS()语句可以认为在真正开始查询前先预设了一个或几个临时表，在后续的查询过程中可以多次使用这个/这些临时表做进一步的分析处理。语法：
它的原理是把实际查询语句中会重复用到的SQL代码放入WITH AS 语句中，并为这段SQL代码取一个别名，查询语句可以通过调用这个别名来访问相应的SQL代码，这样对于大批量的SQL代码可以起到优化的作用并使代码看起来更有条理，增加了SQL 的易读性。常见应用场景：WITH AS 语句常与UNION ALL语句一起使用。因为UNIONALL的每个部分可能相同，但若每次都执行一遍这部分相同的语句会导致查询效率的低下，而使用WITH AS 语句则只需执行一遍即可。另外，若WITH AS 语句所定义的表名被调用两次以上，ORACLE的优化器会自动将WITH AS 语句所获取的数据放入一个临时表中，因此使用WITH AS 语句也可以提高程序的运行速度。注意：1. WITH AS 语句必须紧跟调用的SELECT语句;2. WITH AS 语句创建的临时表必须被调用，否则会报错。以上就是本次向大家分享的ORACLE中不能不知道的十个小知识，你都学会了吗？SQL(结构化查询语言)作为一门历史悠久、灵活性极强且功能强大的编程语言，还有很多潜在的闪光点值得我们在实际的应用过程中去不断的发掘和总结。如果你还有更多关于SQL或者ORACLE的小心得小技巧，欢迎在下面留言与我们分享。团队介绍：我们是毕马威旗下的专业数据挖掘团队，微信公众号(kpmgbigdata)每周六晚8点准时推送一篇原创数据科学文章。我们的作品都由项目经验丰富的博士或资深顾问精心准备，分享结合实际业务的理论应用和心得体会。欢迎大家关注我们的微信公众号，关注原创数据挖掘精品文章；您也可以在公众号中直接发送想说的话，与我们联系交流。
长按二维码即可关注！也请随手推荐我们给你的小伙伴 ↓↓↓↓
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
