SQL（Oracle）

１. coalesce　

coalesce函数可以将空值转换为实际值。

　SELECT coalesce(name,'noname') FROM user;

相比于nvl，coalesc支持多参数，按照参数排序，优先返回第一个不为空的值。

SELECT coalesce(name,nick_name,'noname') FROM user;

如果要使用nvl的话就会变成下面这样：

SELECT nvl(name,nvl(nick_name,'noname')) FROM user;

2. OREDE BY 数字 ASC

结果集排序时，除了写出具体的字段名，也可以用数字代替

SELECT name,age FROM user order by 2 ASC;
SELECT name,age FROM user order by age ASC;

上面的语句是等效的。这种写法非常适用于取值不定的时候，比如淘宝搜索结果排序，可以按价格，销量等排序

3. NULLS FIRST,NULLS LAST

在Oracle当中空值默认排序在后面，想要排到前面可以使用 NULLS FIRST，NULLS LAST。

SELECT name，age FROM user ORDER BY 2  NULLS FIRST；

4. ratio_to_report

进行报表开发时，经常需要行数据占总数百分比，使用ratio_to_report（）时括号内最多只能有一个函数方法

SELECT
  sex,
  count,
  round(ratio_to_report(sum(count))
                                  OVER () * 100, 3) AS per
FROM (SELECT count(1) count,decode(sex, 1, '男', 2, '女', '未知') sex 
FROM person GROUP BY sex) GROUP BY count, sex ORDER BY per DESC

| SEX | COUNT | PER |
| - | - | - |
| 男 | 37215 | 61.128 |
| 女 | 23663 | 38.868 |
| 未知 | 2 | 0.003 |

5.ROW_NUMBER、RANK、DENSE_RANK

这三个函数可以配合PARTITION BY 将主查询返回的数据分组进行分析。
以学生考试成绩为例

| ID | COURSE | SCORE |
| - | - | - |
| 101 | 数学 | 89 |
| 101 | 英语 | 92 |
| 102 | 数学 | 89 |
| 102 | 英语 | 81 |
| 103 | 数学 | 87 |
| 103 | 英语 | 88 |
| 查询出各科成绩前三名的记录 | | |

select * from (
 select id,course,score , 
row_number() OVER (PARTITION BY course ORDER BY score DESC ) AS row_number ,
rank() OVER (PARTITION BY course ORDER BY score DESC ) AS rank, 
dense_rank()OVER (PARTITION BY course ORDER BY score DESC ) AS dense_rank 
FROM exam
) WHERE rank<=3;

当使用ROW_NUMBER 排名是1，2，3即使成绩相同，排名可能不一样。
当使用RANK 排名是1，1，3即使成绩相同，成绩相同的排名相同，下一位发生了跳跃，出现占位的情况
当使用DENSE_RANK 排名 是1，1，2 成绩相同排名相同，但不发生占位情况，

6.numtodsinterval，numtoyminterval

这个函数可以获取相应单位的时间,小数会被计算成整数后，再做计算：
numtodsinterval(x,c),x是一个数字,c是一个字符串,
表明x的单位,这个函数把x转为interval day to second数据类型
常用的单位有 ('day','hour','minute','second')

select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;

numtoyminterval与numtodsinterval函数类似,将x转为interval year to month数据类型

select sysdate,sysdate+numtoyminterval(3,'year') as res from dual;

常用的单位有'year','month'

未完待续...