设计
设计规范
为了达到数据库设计规范化的要求，一般来说，需要符合以下五个要求。
要求一：表中应该避免可为空的列，虽然表中允许空列，但是，空字段是一种比较特殊的数据类型。
要求二：表不应该有重复的值或者列。
要求三：表中记录应该有一个唯一的标识符，在数据库表设计的时候，应该养成一个好习惯，用一个ID号来唯一的标识行记录，而不要通过名字、编号等字段来对纪录进行区分。
要求四：数据库对象要有统一的前缀名，一个比较复杂的应用系统，其对应的数据库表往往以千计。
要求五：尽量只存储单一实体类型的数据，这里将的实体类型跟数据类型不是一回事，要注意区分。这里讲的实体类型是指所需要描述对象的本身。
数据库对象包括表、视图、索引、同义词、簇、触发器、函数、过程、包、数据库链、快照等（表空间、回滚段、角色、用户）。
数据库通过对表的操作来管理存储在其中的数据。
字段类型的选择
字段类型选择尽量避免查询时隐式转换导致无法使用索引。
建议选择： NUMBER（m，n）
VARCHAR2(n) n=10，20，50，100，200，500，1000，2000，4000
DATE CLOB RAW 2000 字节 BLOB 应该只在长度超过 2000 字节才用。
慎重选择 TIMESTAMP 类型，这个类型代价比较高，做查询时往往要进行处理。
不建议选择 CHAR 类型。 不建议选择 LONG RAW 类型
字段的顺序
靠近记录开始的地方字段定位速度会明显快于记录尾的字段。
常用的字段放在前面
所以一般规则是将访问频繁字段放在前面。为了利用这点，select 只应选择需要的字段。
额外的字段需要：
1、更多时间定位字段。
2、服务器和客户端更多内存来保存。
3、更多时间网络传输。
末尾的 null 值不保存，因此建议 null 字段放表末端。这样实际存储物理字段数量和相 应行平均大小也可能降低。
逆范式设计
为了大批量数据查询性能，牺牲单条数据插入，修改性能，通过增加冗余，来减少表 连接。
主从表在主表保存子表汇总值 例如：订单表保存订单总金额，不再查询订单清单表。
主从表在主表保存子表最新值 例如：产品信息表中保存最新产品价格，不再关联产品历史价格表。
多层主从表跨层冗余 例如：省，市，区，街道，经常查询市，街道，在街道表中直接保存市。
在事实表不仅保存代码 ID，同时保存代码值 例如：很多地方有操作人员字段，不仅保存员工 ID，还保存员工姓名。防止多个 人员字段，多次关联员工表。
索引设计
索引字段建议非空，如果有空值，就设置一个缺省值。
使用索引要注意的方面：
1、不要在索引上使用函数
2、不要选择低效率字段作为索引
3、尽可能减少复合索引的使用
4、尽量减少索引字段的更新
如何避免索引被抑制问题
导致不必要的全表扫描的一个重要原因是：虽然在相关字段建立了索引，但因如下些 因素导致索引被抑制。
· 在字段前增加了函数。将导致索引无法使用，应尽量将字段前的函数进行转换。
例如：
to_char (C. Total-date ) between : in_begEmgime and : in_endgime
应修改为：
C. Total_date between to_date ( : in_begin_time,'YYYY. MM. DD') and to_date ( : il_end_time,'YYYY. MM. DD')
再比如，车牌号： 省市区简称地级市名称字母+数字字母组合
将ON T1.BRAPREFIX || T1.BRANUM = T2.BRAPREFIX || T2.BRANUM
改为T1.BRAPREFIX = T2.BRAPREFIX and T1.BRANUM = t2.BRANUM
· 字段类型设计问题。如果因字段类型设计不合理，将迫使应用程序使用函数，从 而导致索引无法使用。例如日期数据以字符类型进行表达，将被迫导致 SQL 语句使格的截 取。Substr()，trim()等函数。错误地使用 char 字段，也将导致使用 trim()函数进行空格的截 取。
· 将字段嵌入表达式之中。也将导致索引索引无法使用，应尽量将字段从表达式中 进行剥离。例如：
and (sysdate - to_date(a. Efftt,'yyyy m mddhh24miss’ ) ) * 24 * 60 <=15
应修改为： and a. Efftt >= to_char ( ( sysdate-1 / 96 ), ‘yyymmddhh24miss') ;
4.函数索引设计规范 如果可能，应尽量避免创建函数索引，因为函数索引将导致 DML 操作资源消耗增加。
is null 索引设计
对于需要查询的包含 NULL 的字段，我们建议设置缺省值，然后建索引。 但是对于已有的程序，在无法修改 SQL 情况下，我们可以通过建复合索引进行优化。
CREATE TABLE test AS SELECT * FROM dba_objects;
UPDATE test SET created = NULL WHERE owner = 'SCOTT';
COMMIT;
SELECT * FROM test WHERE created IS NULL;
is null 的值非常少,如果能够创建一个 index,取到列 null 的值,那效率将非常高.但是有 oracle index 只是的人都知道,B树 index 是不包含 null 列,因此一般性 index 无法满足该需求. 这里思考创建含常数的复合 index,而且把包含 null 字段放在前面,因为后面的常数一定存在, 因此字段中含有 null 的记录也就包含在该复合 index 中.
创建含常数复合 index：
CREATE INDEX ind_test_created ON test (created, 0) ONLINE;
分区
通常下列情况下推荐使用分区
1、表的大小超过 2GB。
2、表中包含历史数据，新的数据被增加到新的分区中。
3、表中数据要分布在不同的存储设备上。
Oracle 分区提供三种基础数据分配方法，通过这些方法控制如何将数据真正放入各种 单独的分区：范围，哈希，列表。
自动创建分区
根据年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
根据月: INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY'))
据时分秒: NUMTODSINTERVAL( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})
create table t_range (id number not null PRIMARY KEY, test_date date)
partition by range (test_date) interval (numtoyMinterval (1,'MONTH'))
(
partition p_2014_01_01 values less than (to_date('2014-01-01', 'yyyy-mm-dd'))
);
insert into t_range(id,test_date) values (1,sysdate);
alter table t_range rename partition SYS_P21 to p_2017_02_01;
散列分区
散列分区是根据字段的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等。
还是刚才那个表，只不过把范围分区改换为散列分区
create table t_test_part(
id number,
pro_num varchar2(40),
app_type varchar2(40)
)
partition by hash(pro_num)
(
partition part_01,
partition part_02,
partition part_03,
partition part_04,
partition part_05,
partition part_06,
partition part_07,
partition part_08
)
测试分部
declare
i number;
begin
for i in 1..100 loop
insert into t_test_part
(id, pro_num, app_type)
values
(i, to_char(i), '类型'||to_char(i));
end loop;
commit;
end;
select * from T_TEST_PART partition( part_01);
select * from T_TEST_PART partition( part_02);
实际结论见下：
1. 数据随机插入
Hash分区一般是在分区键值无法确定的情况下，使用的一种分区策略，Oracle按照hash 算法把数据插入用户指定的分区键中，它是随机的插入到某个区中，不受人为的干预。
2. 分区大小
分区的初始大小受建表初始化参数的影响（即initial、next的影响），不指定时受建库时表空间参数的影响。实际分配数据后的大小，受分区字段值的影响。分区字段值相同时，所有数据只能插入到一个分区；分区字段值不同时，数据随机插入不同的分区。
根据实验的结果并不是平均分配，也不是每个区都分配。如上面的表，最少的一个区只有0.1M，最大的一个区是33M，严重的不均匀。也许随着时间和数据量的不断增加，数据会趋于均衡，估计实验是很难验证出来的。
3. Local和Global索引
分区索引分为Local和Global索引，Local索引和表分区是一一对应的，Global索引又分为Global非分区索引和Global分区索引。Global非分区索引，可以与表分区对应，也可以不对应；但是当Global分区索引与表分区的表空间对应时，则Global分区索引就是个Local索引。
4. 分区定值查询时，Local索引与Global索引没有任何区别，但是范围查询时，Global索引的partition start 和 partition stop是相同的；Local索引的partition start 和 partition stop 就不同了，上面的那个表是从1到8（和范围的大小有关）。根据这个情况，如果实际应用中定值查询多，就用Local索引了，反之，则用Global索引。如果根本没法分，那就要权衡了。
5. 分区与不分区的区别
就我的测试用例来说，两者没有本质区别，时间都相当。 15G的数据，分区的SQL语句COST是不分区时SQL语句COST的几千倍，不分区的COST还更低。但是两者的执行时间没有任何区别。在实际使用分区时，性能只是要考虑的一个方面，易管理性、可用性方面分区还是有很大优势的，不能仅看一面而忽略了整体。
如：
1）.一个分区的丢失或损坏不会影响其余的分区，损坏的分区可以单独恢复。
2）.分区把一个大段分为更多的小片段，可以降低争用。
数据库设计规范检查
字段名相同类型长度不同
字段名相同，类型长度不同的数量：
SELECT column_name,
data_type ||
DECODE(DECODE(data_type, 'NUMBER', NVL(data_precision, 0), 1),
0,
NULL,
'(') ||
DECODE(data_type, 'NUMBER', data_precision, data_length) ||
DECODE(DECODE(data_type, 'NUMBER', NVL(data_precision, 0), 1),
0,
NULL,
')') data_type_length,
COUNT(*)
FROM USER_TAB_COLUMNS
WHERE column_name IN
(SELECT column_name
FROM USER_TAB_COLUMNS
GROUP BY column_name
HAVING MIN(DECODE(data_type, 'NUMBER', NVL(data_precision, data_length), data_length)) < MAX(DECODE(data_type, 'NUMBER', NVL(data_precision, data_length), data_length)))
GROUP BY column_name,
data_type ||
DECODE(DECODE(data_type, 'NUMBER', NVL(data_precision, 0), 1),
0,
NULL,
'(') ||
DECODE(data_type, 'NUMBER', data_precision, data_length) ||
DECODE(DECODE(data_type, 'NUMBER', NVL(data_precision, 0), 1),
0,
NULL,
')')
ORDER BY column_name;
字段名相同，类型长度不同的具体表名，字段类型长度信息：
SELECT column_name,
table_name || ' ' || data_type ||
DECODE(DECODE(data_type, 'NUMBER', NVL(data_precision, 0), 1),
0,
NULL,
'(')
|| DECODE(data_type, 'NUMBER', data_precision, data_length) ||
DECODE(DECODE(data_type, 'NUMBER', NVL(data_precision, 0), 1),
0,
NULL,
')') "Characteristics"
FROM USER_TAB_COLUMNS
WHERE column_name IN
(SELECT column_name
FROM USER_TAB_COLUMNS
GROUP BY column_name
HAVING MIN(DECODE(data_type, 'NUMBER', NVL(data_precision, data_length), data_length)) < MAX(DECODE(data_type, 'NUMBER', NVL(data_precision, data_length), data_length)))
ORDER BY column_name;
没有主键的表
SELECT table_name FROM user_tables MINUS SELECT table_name FROM user_constraints WHERE constraint_type = 'P';
没有唯一约束或索引的表:
SELECT table_name FROM user_all_tables MINUS SELECT table_name FROM user_constraints WHERE constraint_type = 'U' MINUS SELECT table_name
FROM user_indexes WHERE uniqueness = 'UNIQUE';
没有建索引的外键
SELECT c.constraint_name, c.table_name, cc.column_name, c.status
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_type = 'R'
AND c.constraint_name = cc.constraint_name
AND NOT EXISTS
(SELECT 'x'
FROM user_ind_columns ic
WHERE cc.table_name = ic.table_name
AND cc.column_name = ic.column_name
AND cc.position = ic.column_position
AND NOT EXISTS
(SELECT index_name
FROM user_indexes i
WHERE i.index_Name = ic.index_name
AND (i.status = 'UNUSABLE' OR
i.partitioned = 'YES' AND EXISTS
(SELECT 'x'
FROM user_ind_partitions ip
WHERE status = 'UNUSABLE'
AND ip.index_Name = i.index_name
UNION ALL
SELECT 'x'
FROM user_ind_subpartitions isp
WHERE status =
'UNUSABLE'
AND isp.index_Name = i.index_name))))
ORDER BY 1;
或：
SELECT a.constraint_name cons_name,
a.table_name tab_name,
b.column_name cons_column,
NVL(c.column_name, '***No Index***') ind_column
FROM user_constraints a
JOIN user_cons_columns b
ON a.constraint_name = b.constraint_name
LEFT OUTER JOIN user_ind_columns c
ON b.column_name = c.column_name
AND b.table_name = c.table_name
WHERE constraint_type = 'R'
ORDER BY 2, 1;
字段数过多的表
字段数超过 50 的表：
SELECT TABLE_NAME, C_NUM
FROM (SELECT TABLE_NAME, COUNT(*) C_NUM
FROM USER_TAB_COLUMNS
GROUP BY TABLE_NAME)
WHERE C_NUM >= 50
ORDER BY C_NUM DESC;
字段数过多的索引
字段数超过 5 的索引：
SELECT TABLE_NAME, INDEX_NAME, C_NUM
FROM (SELECT TABLE_NAME, INDEX_NAME, COUNT(*) C_NUM
FROM user_ind_columns
GROUP BY TABLE_NAME, INDEX_NAME)
WHERE C_NUM >= 5 ORDER BY C_NUM DESC;
字段可为空的索引
SELECT a.TABLE_NAME, INDEX_NAME, a.COLUMN_NAME FROM user_ind_columns a, USER_TAB_COLUMNS b WHERE a.TABLE_NAME = b.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME AND b.NULLABLE = 'Y' ORDER BY a.TABLE_NAME, INDEX_NAME
2.16.7 非空没有默认值的字段
SELECT table_name, column_name, data_type FROM USER_TAB_COLUMNS WHERE nullable = 'N' AND data_default IS NULL;
修改默认值：
SELECT 'alter table ' || a.table_name || ' modify ' || column_name ||
' default ' || CASE data_type
WHEN 'VARCHAR2' THEN
''' '''
WHEN 'NVARCHAR2' THEN
''' '''
WHEN 'CHAR' THEN
''' '''
WHEN 'NUMBER' THEN
'-1'
WHEN 'DATE' THEN
'TO_DATE(''1900-01-01'',''YYYY-MM-DD'')'
WHEN 'TIMESTAMP(6)'
THEN
'to_timestamp(''1900-01-01'',''YYYY-MM-DD'')'
ELSE
''
END || ';'
FROM USER_TAB_COLUMNS a, USER_tables b
WHERE a.table_name = b.table_name
AND a.table_name NOT LIKE 'R%'
AND nullable = 'N'
AND data_default IS NULL
AND data_type NOT IN ('CLOB', 'RAW', 'BFILE', 'BLOB');
重复的索引列
查看有哪些索引含有重复的字段, 从而让索引更加合理化:
SELECT /*+ rule */
a.table_owner,
a.table_name,
a.index_owner,
a.index_name,
column_name_list,
column_name_list_dup,
dup duplicate_indexes,
i.uniqueness,
i.partitioned,
i.leaf_blocks,
i.distinct_keys,
i.num_rows,
i.clustering_factor
FROM (SELECT table_owner,
table_name,
index_owner,
index_name,
column_name_list_dup,
dup,
MAX(dup) OVER(PARTITION BY table_owner, table_name, index_name) dup_mx
FROM (SELECT table_owner,
table_name,
index_owner,
index_name,
SUBSTR(SYS_CONNECT_BY_PATH(column_name, ','), 2) column_name_list_dup,
dup
FROM (SELECT index_owner,
index_name,
table_owner,
table_name,
column_name,
COUNT(1) OVER(PARTITION BY index_owner, index_name) cnt,
ROW_NUMBER() OVER(PARTITION BY index_owner, index_name ORDER BY column_position) AS seq,
COUNT(1) OVER(PARTITION BY table_owner, table_name, column_name, column_position) AS dup
FROM sys.dba_ind_columns
WHERE index_owner = 'SCOTT')
WHERE dup != 1
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq
AND PRIOR index_owner = index_owner
AND PRIOR index_name = index_name)) a,
(SELECT table_owner,
table_name,
index_owner,
index_name,
SUBSTR(SYS_CONNECT_BY_PATH(column_name, ','), 2) column_name_list
FROM (SELECT index_owner,
index_name,
table_owner,
table_name,
column_name,
COUNT(1) OVER(PARTITION BY index_owner, index_name) cnt,
ROW_NUMBER() OVER(PARTITION BY index_owner, index_name ORDER BY column_position) AS seq
FROM sys.dba_ind_columns
WHERE index_owner = 'SCOTT')
WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq
AND PRIOR index_owner = index_owner
AND PRIOR index_name = index_name) b,
dba_indexes i
WHERE a.dup = a.dup_mx
AND a.index_owner = b.index_owner
AND a.index_name = b.index_name
AND a.index_owner = i.owner
AND a.index_name = i.index_name
ORDER BY a.table_owner, a.table_name, column_name_lis
常用函数
字符函数
-1： ASCII
返回字符串的ASCII值
SELECT ASCII('A') FROM DUAL;
SELECT ASCII('a') FROM DUAL;
--2: CHR
返回整数所对应的ASCII字符
SELECT CHR('65') FROM DUAL;
SELECT CHR(400) FROM DUAL; --如果超出ACII值，则返回空
--3: CONCAT 函数
连接字符串A和字符串B
SELECT CONCAT('您好', '欢迎来到ORACLE世界') AS TEXT FROM DUAL;
--3.1 如果要连接表里面的两个字段可以用||
SELECT TYPECODE || '____' || TYPE_NAME AS "TYPE" FROM USER_TYPES;
--4: INITCAP
返回字符串并将字符串的第一个字母变为大写;
--Q 是否每个单词的第一个字母都会变成大些？ 全部单词的首字母大写
SELECT INITCAP('your didn''t try your best') FROM DUAL;
--5: LENGTH
返回字符串的长度;
--Q:返回表某条数据某个列实际长度，如果该表没有数据，返回0
SELECT LENGTH(TYPE_NAME) FROM USER_TYPES
--6： LOWER
返回字符串,并将所有的字符小写
SELECT LOWER('AbcDedf Gbad') FROM DUAL;
--7： UPPER
返回字符串,并将所有的字符大写
SELECT UPPER('abcdEf') FROM DUAL;
--8: LTRIM(X,[TRIM_STRING])
LTRIM 删除左边出现的字符串, 默认为空字符串
SELECT LTRIM(' hello world!') FROM DUAL;
SELECT LTRIM('hello, world', 'hello') FROM DUAL;
--9: RTRIM(X, [TRIM_STRING])
RTRIM 删除右边出现的字符串TRIM_STRING,默认为空字符串。
SELECT RTRIM('hello world! ') FROM DUAL;
--10： SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SELECT SUBSTR('you are right!, come on', 3, 30) FROM DUAL;
--11: INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SELECT INSTR('HELLO WORLD! WELCOME', 'WORLD', 1) FROM DUAL;
--12: RPAD
RPAD 在列的右边粘贴字符
--注意长度值并不是粘贴字符的长度，而是整个字符串的长度，如果长度小于原始字符串
--SELECT RPAD('HELLO', 4, '*') FROM DUAL; 的值为HELL
SELECT RPAD('HELLO', 10, '*') FROM DUAL;
SELECT RPAD('HELLO', 10, 'E') FROM DUAL;
--13: LPAD
LPAD 在列的左边粘贴字符
--N: 注意的问题同上
SELECT LPAD('WELCOME', 20, 'HELLO') FROM DUAL;
--14: REPLACE('string','s1','s2')
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SELECT REPLACE('HE LOVE YOU', 'HE' ,'I') FROM DUAL;
--15: SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
--16.TRIM('s' from 'string')
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符
SELECT TRIM('Y' FROM 'YOU') FROM DUAL;
--17： NVL(X, VALUE)
如果X是空值，返回VALUE，否则返回X
SELECT NVL(NULL, '2') FROM DUAL;
SELECT NVL('33', '2') FROM DUAL;
--18: NVL2(X, VALUE1, VALUE2)
如果X是空值，返回VALUE1， 否则返回VALUE2
SELECT NVL2(NULL, '3', '4') FROM DUAL;
--19: NANVI(X, VALUE)
如果X不是数字，那么返回VALUE，否则返回X
SELECT NANVI('3DF', '34') FROM DUAL;
Oracle COALESCE函数语法为COALESCE(表达式1,表达式2,...,表达式n)，n>=2,此表达式的功能为返回第一个不为空的表达式，如果都为空则返回空值
数字函数
1，abs(n) 返回n的绝对值
[plain] view plain copy
1. SQL> select abs(-1),abs(1) from dual;
2.
3. ABS(-1) ABS(1)
4. ---------- ----------
5. 1 1
2，ceil(n) 返回大于等于n的最小整数
[plain] view plain copy
1. SQL> select ceil(10),ceil(10.5),ceil(-10.5) from dual;
2.
3. CEIL(10) CEIL(10.5) CEIL(-10.5)
4. ---------- ---------- -----------
5. 10 11 -10
3，floor(n) 返回小于等于n的最大整数
[plain] view plain copy
1. SQL> select floor(10),floor(10.5),floor(-10.5) from dual;
2.
3. FLOOR(10) FLOOR(10.5) FLOOR(-10.5)
4. ---------- ----------- ------------
5. 10 10 -11
4，greatest(expr[,expr...])返回参数列表的最大值，可用于数字，字符串，日期
[plain] view plain copy
1. SQL> select greatest(1,2,3) from dual;
2.
3. GREATEST(1,2,3)
4. ---------------
5. 3
6.
7. SQL> select greatest('a','b') from dual;
8.
9. GR
10. --
11. b
12.
13. SQL> select greatest(to_date('2011','yyyy'),to_date('2012','yyyy')) from dual;
14.
15. GREATEST(TO_DA
16. --------------
17. 01-11月-12
如果参数列表为混合类型，Oracle在选择最大值之前将选择第一个参数类型作为基准类型并试图将其他参数转换为第一个参数的类型
[plain] view plain copy
1. SQL> select greatest(1,'abc') from dual;
2. select greatest(1,'abc') from dual
3. *
4. ERROR at line 1:
5. ORA-01722: invalid number
6.
7.
8. SQL> select greatest(1,'111') from dual;
9.
10. GREATEST(1,'111')
11. -----------------
12. 111
5，least(expr[,expr...]) 选择参数列表最小值，和greatest函数相反，参数规则相同
6，mod(m,n) m除以n的余数
[plain] view plain copy
1. SQL> select mod(11,10) from dual;
2.
3. MOD(11,10)
4. ----------
5. 1
7，power(m,n) m的n次方，m为非0数字，如果m为正数，n可以为正数或负数，如果m为负数，n必须为正整数
[plain] view plain copy
1. SQL> select power(2,2),power(-1,2),power(1,0) from dual;
2.
3. POWER(2,2) POWER(-1,2) POWER(1,0)
4. ---------- ----------- ----------
5. 4 1 1
6.
7. SQL> select power(-2,0.5) from dual;
8. select power(-2,0.5) from dual
9. *
10. ERROR at line 1:
11. ORA-01428: argument '-2' is out of range
8，round(m,n) 对十进制数字m，根据n进行四舍五入计算
[plain] view plain copy
1. SQL> select round(123.5),round(123.46,1),round(126.45,-1) from dual;
2.
3. ROUND(123.5) ROUND(123.46,1) ROUND(126.45,-1)
4. ------------ --------------- ----------------
5. 124 123.5 130
9，sign(n) 返回n的符号，n为负数返回-1，n为0返回0，n为正数返回1
[plain] view plain copy
1. SQL> select sign(-11),sign(0),sign(99) from dual;
2.
3. SIGN(-11) SIGN(0) SIGN(99)
4. ---------- ---------- ----------
5. -1 0 1
10，sqrt(n) n的平方根，n不能为负数
[plain] view plain copy
1. SQL> select sqrt(4),sqrt(0),sqrt(80) from dual;
2.
3. SQRT(4) SQRT(0) SQRT(80)
4. ---------- ---------- ----------
5. 2 0 8.94427191
6.
7. SQL> select sqrt(-3) from dual;
8. select sqrt(-3) from dual
9. *
10. ERROR at line 1:
11. ORA-01428: argument '-3' is out of range
11，trunc(m[,n]) 根据指定的位数n截取数字m（只截取，不进行四舍五入）
[plain] view plain copy
1. SQL> select trunc(999.99),trunc(999.99,1),trunc(999.99,-2) from dual;
2.
3. TRUNC(999.99) TRUNC(999.99,1) TRUNC(999.99,-2)
4. ------------- --------------- ----------------
5. 999 999.9 900
Round和Trunc
一、Oracle中的Round和Trunc：
如同对数字进行四舍五入和按位截取一样，Oracle对时间日期也提供了这两种功能。但比起对数字进行四舍五入和截取比较复杂：这是因为时间日期是有格式的。下面看看这两个函数的定义和用途：
ROUND(date [, format])
TRUNC(date [, format])
Round函数对日期进行“四舍五入”，Trunc函数对日期进行截取。如果我们不指定格式的话，Round会返回一个最接近date参数的日期，而Trunc函数只会简单的截取时分秒部分，返回年月日部分。
二、Round和Trunc函数示例：
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') now_date,
2 to_char(Round(sysdate),'yyyy-mm-dd hh24:mi:ss') round_date,
3 to_char(Trunc(sysdate),'yyyy-mm-dd hh24:mi:ss') trunc_date
4 from dual;
NOW_DATE ROUND_DATE TRUNC_DATE
-------------------------------------- -------------------------------------- ----------------------
2008-06-30 14:52:13 2008-07-01 00:00:00 2008-06-30 00:00:00
这是一个典型的例子，由于我们没有指定round和trunc函数的格式，所以Oracle默认采用了按日期时间的格式，该例子中当前的时间是下午14:52分，已经超过了12:00 AM这个中界线，所以Round返回07-01日而非06-30日。而Trunc不管三七二十一直接截取前面日期部分返回。
另外一个值得注意的地方是这两个函数返回的时分秒都是00:00:00，即一天的开始时间(对于12小时制的返回的是12:00:00 AM)。
三、指定格式的Round和Trunc函数示例：
如果我们对Round函数和Trunc函数指定了格式，事情就变得有点复杂了，不过核心思想还是不变：Round是四舍五入，Trunc是截取。举个例子来说，假如我们以年为格式，则现在Oracle的判断是基于年来判断，超过一年的一半(即6月30日)，Round函数则返回下一年了，Trunc函数依然返回当前年。
SQL> select sysdate "Now date",
2 Round(sysdate, 'yyyy') Round_year,
3 Trunc(sysdate, 'yyyy') Trunc_year
4 from dual;
Now date ROUND_YEAR TRUNC_YEAR
---------- ---------- ----------
30-6月 -08 01-1月 -08 01-1月 -08
关于这两个函数可用的格式非常多，但日常应用中用得比较多的基本上就这几个，以Round函数为例：
select Round(sysdate, 'Q') Rnd_Q,
Round(sysdate, 'Month') Rnd_Month,
Round(sysdate, 'WW') Rnd_Week,
Round(sysdate, 'W') Rnd_Week_again,
Round(sysdate, 'DDD') Rnd_day,
Round(sysdate, 'DD') Rnd_day_again,
Round(sysdate, 'DAY') Rnd_day_of_week,
Round(sysdate, 'D') Rnd_day_of_week_again,
Round(sysdate, 'HH12') Rnd_hour_12,
Round(sysdate, 'HH24') Rnd_hour_24,
Round(sysdate, 'MI') Rnd_minute
from dual
日期函数
目录
=========================================
1.Oracle的日期函数
2.日期加减
3.月份加减
4.年份加减
5.求每月的最后一天
6.求每月的第一天
7.求下一个星期几
入门知识：
①Oracle中的日期时间存储：
oracle数据库中存放时间格式的数据，是以oracle特定的格式存贮的，占7个字节，与查询时显示的时间格式无关。不存贮秒以下的时间单位。
②Oracle中的日期时间显示：
通常，客户端与数据库建立起连接后，oracle就会给一个缺省的时间格式数据的显示形式，与所使用的字符集有关。一般显示年月日，而不显示时分秒。
③Oracle中的日期时间插入：
向表中插入数据时，如果不使用转换函数，则时间字段的格式必须遵从会话环境的时间格式，否则不能插入。
④Oracle中的日期时间格式修改：
a.SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
b.册表\hkey_local_machine\software\oracle\home0主键中增加一个字串(8i版本)，字串名为nls_date_format，字串的值为你希望定义的时间格式
前者只对当前会话有效，也即是一旦你关闭了SQL*PLUS窗口或重新打开一个SQL*PLUS窗口，日期时间格式依然采用本地字符集对应的日期时间格式。后者对所有客户端应用有效。当两者同时应用时，以alter session的修改为准。
一、Oracle的日期函数:
Oracle从8i开始就提供了大量的日期函数，这些日期函数包括对日期进行加减、转换、截取等功能。下面是Oracle提供的日期函数一览表
Function Use
ADD_MONTHS Adds months to a date
LAST_DAY Computes the last day of the month
MONTHS_BETWEEN Determines the number of months between two dates
NEW_TIME Translates a time to a new time zone
NEXT_DAY Returns the date of the next specified weekday
ROUND Rounds a date/time value to a specified element
SYSDATE Returns the current date and time
TO_CHAR Converts dates to strings
TO_DATE Converts strings and numbers to dates
TRUNC Truncates a date/time value to a specific element
二、日期加减：
在Oralce中，对日期进行加减操作的默认单位是天，也就是说如果我们向当前日期加1的话是加上一天，而不是一秒或一小时。那么对一天中的一段时间进行加减要怎么做呢？很简单！只需将它们转化为以天为单位即可。
【1】为当前时间加上30分钟：
SQL> select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') now_date,
2 to_char(sysdate+(30/24/60), 'yyyy-mm-dd hh:mi:ss') new_date
3 from dual;
NOW_DATE NEW_DATE
-------------------------------------- --------------------------------------
2008-06-30 10:47:31 2008-06-30 11:17:31
SQL>
我们看到了在绿色高亮处使用30/24/60将分钟转换成天。另外一个要注意的地方是：SQL*PLUS环境下默认的日期格式：NLS_DATE_FORMAT是DD-MM-YYYY，也即是不包含时、分、秒，所以我们这里必须采用to_char的方式指定输入的日期格式。
除此之外也可以通过在SQL*PLUS中执行下列语句修改默认的日期输出格式，这样的话就不需要通过to_char来转换了，直接输出就行。
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
【2】为当前时间减去30分钟：
SQL> select to_char(sysdate+(-30/24/60),'yyyy-mm-dd hh:mi:ss') new_date from dual;
NEW_DATE
--------------------------------------
2008-06-30 10:24:59
只需要加上一个负数即可以了。
三、月份加减：
月份的加减和日期加减相比要难了很多，因为每个月份的天数并不是固定的，可能是31,30,29,28。如果采用上面的方法将月份转换成实际天数将不可避免地出现多个判断，幸亏Oracle为我们提供了一个add_months函数，这个函数会自动判断月份的天数。看看下面的例子：
【1】为当前时间加上6个月：
SQL> select add_months(sysdate, 6) from dual;
ADD_MONTHS
----------
31-12月-08
【2】为当前时间减去6个月：
SQL> select add_months(sysdate, -6) from dual;
ADD_MONTHS
----------
31-12月-07
【3】求两个日期相差的月数：
通常情况下两个时间相减将得到以天数为单位的结果，可是有时我们更希望得到以月为单位的结果，如果手动转换这太麻烦了，所以Oracle又提供了一个函数，这个函数就是months_between。
SQL> select months_between(sysdate,
2 to_date('2008-01-01 01:00:00', 'yyyy-mm-dd hh:mi:ss')) result
3 from dual;
RESULT
----------
5.94928203
months_between函数有2个参数，第一个参数是结束日期，第二个参数是开始日期，Oracle用第一个参数减去第二个参数得到月份数。所以结果有可能会是负数的。
四、年份加减：
Oracle并不直接提供对年份进行加减的函数，不过有了add_months和months_between函数，我们照样可以做到。
【1】为当前日期加上2年：
SQL> select add_months(sysdate, 2*12) two_years_later
2 from dual;
TWO_YEARS_
----------
30-6月 -10
【2】求两个日期相差几年：
SQL> select months_between(sysdate,
2 to_date('2006-06-30', 'yyyy-mm-dd')) / 12 years_between
3 from dual;
YEARS_BETWEEN
-------------
2
直接将两个日期相减，然后除以365天并不准确，但是不管一年有多少天它总是只有12个月，所以利用这一点我们可以先求出两个日期相差的月数，再除以12就得出相差的年数了
五、求每月的最后一天：
SQL> select last_day(add_months(sysdate,2)) last_day
2 from dual;
LAST_DAY
----------
31-8月 -08
六、求每月的第一天：
Oracle提供了last_day让我们能够求出所在月份的最后一天，但没有对应的first_day函数，如果有这方面的需求，只需要稍微动一下脑筋，利用last_day函数即可。例如下面的SQL语句就是求出下个月的第一天：
SQL> select last_day(sysdate)+1 fisrt_day
2 from dual;
FISRT_DAY
----------
01-7月 -08
在这里我们将“每月的第一天”转换成“上个月最后一天的下一天”，问题就解决了！
七、求下一个星期几：
有时候我们会碰上“下个星期五是几号啊？”这样常见的问题。Oracle为此提供了一个函数：next_day，它的语法是这样的：next_day(date, string)。其中第一个参数date告诉Oracle从什么时候开始算起，第二个参数string则告诉Oracle要取的工作日。
下面我们看看如何得到下个星期五的日期：
select next_day(sysdate,'星期五') "下周五" from dual;
例如我要查下个周三是什么时候，则函数是这样写的：next_day(sysdate, 4)。
分析函数（over）：
一、统计方面：
Sum( ) Over ([Partition by ] [Order by ])
Sum( ) Over ([Partition by ] [Order by ]
Rows Between Preceding And Following)
Sum( ) Over ([Partition by ] [Order by ]
Rows Between Preceding And Current Row)
Sum( ) Over ([Partition by ] [Order by ]
Range Between Interval ' ' 'Day' Preceding
And Interval ' ' 'Day' Following )
二、排列方面：
Rank() Over ([Partition by ] [Order by ] [Nulls First/Last])
Dense_rank() Over ([Patition by ] [Order by ] [Nulls First/Last])
Row_number() Over ([Partitionby ] [Order by ] [Nulls First/Last])
Ntile( ) Over ([Partition by ] [Order by ])
具体请参考《Oracle开发专题之：分析函数2》
三、最大值/最小值查找方面：
Min( )/Max( ) Keep (Dense_rank First/Last [Partition by ] [Order by ])
具体请参考《Oracle开发专题之：分析函数3》
四、首记录/末记录查找方面：
First_value / Last_value(Sum( ) Over ([Patition by ] [Order by ]
Rows Between Preceding And Following ))
具体请参考《Oracle开发专题之：窗口函数》
五、相邻记录之间比较方面：
Lag(Sum( ), 1) Over([Patition by ] [Order by ])
报表函数
1.报表函数简介
2.RATIO_TO_REPORT函数
一、报表函数简介：
回顾一下前面《Oracle开发专题之：窗口函数》中关于全统计一节，我们使用了Oracle提供的：
sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following)
来统计全年的订单总额，这个函数会在记录集形成的过程中，每检索一条记录就执行一次，它总共执行了12次。这是非常费时的。实际上我们还有更简便的方法：
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and unbounded following) win_sales,
5 sum(sum(tot_sales)) over() rpt_sales
6 from orders
7 group by month;
MONTH MONTH_SALES WINDOW_SALES REPORT_SALES
---------- ----------- ------------ ------------
1 610697 6307766 6307766
2 428676 6307766 6307766
3 637031 6307766 6307766
4 541146 6307766 6307766
5 592935 6307766 6307766
6 501485 6307766 6307766
7 606914 6307766 6307766
8 460520 6307766 6307766
9 392898 6307766 6307766
10 510117 6307766 6307766
11 532889 6307766 6307766
12 492458 6307766 6307766
已选择12行。
over函数的空括号表示该记录集的所有记录都应该被列入统计的范围，如果使用了partition by则先分区，再依次统计各个分区。
二、RATIO_TO_REPORT函数：
报表函数特(窗口函数)特别适合于报表中需要同时显示详细数据和统计数据的情况。例如在销售报告中经常会出现这样的需求：列出上一年度每个月的销售总额、年底销售额以及每个月的销售额占全年总销售额的比例：
方法①:
select all_sales.*,
100 * round(cust_sales / region_sales, 2) || '%' Percent
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.2;
这是一种笨方法也是最易懂的方法。
方法②：
select region_id, salesperson_id,
sum(tot_sales) sp_sales,
round(sum(tot_sales) / sum(sum(tot_sales))
over (partition by region_id), 2) percent_of_region
from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;
方法③
select region_id, salesperson_id,
sum(tot_sales) sp_sales,
round(ratio_to_report(sum(tot_sales))
over (partition by region_id), 2) sp_ratio
from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;
Oracle提供的Ratio_to_report函数允许我们计算每条记录在其对应记录集或其子集中所占的比例。
窗口函数
一、窗口函数简介：
到目前为止，我们所学习的分析函数在计算/统计一段时间内的数据时特别有用，但是假如计算/统计需要随着遍历记录集的每一条记录而进行呢？举些例子来说：
①列出每月的订单总额以及全年的订单总额
②列出每月的订单总额以及截至到当前月的订单总额
③列出上个月、当月、下一月的订单总额以及全年的订单总额
④列出每天的营业额及一周来的总营业额
⑤列出每天的营业额及一周来每天的平均营业额
仔细回顾一下前面我们介绍到的分析函数，我们会发现这些需求和前面有一些不同：前面我们介绍的分析函数用于计算/统计一个明确的阶段/记录集，而这里有部分需求例如2，需要随着遍历记录集的每一条记录的同时进行统计。
也即是说：统计不止发生一次，而是发生多次。统计不至发生在记录集形成后，而是发生在记录集形成的过程中。
这就是我们这次要介绍的窗口函数的应用了。它适用于以下几个场合：
①通过指定一批记录：例如从当前记录开始直至某个部分的最后一条记录结束
②通过指定一个时间间隔：例如在交易日之前的前30天
③通过指定一个范围值：例如所有占到当前交易量总额5%的记录
二、窗口函数示例－全统计：
下面我们以需求：列出每月的订单总额以及全年的订单总额为例，来看看窗口函数的应用。
【1】测试环境：
SQL> desc orders;
名称 是否为空? 类型
----------------------- -------- ----------------
MONTH NUMBER(2)
TOT_SALES NUMBER
SQL>
【2】测试数据：
SQL> select * from orders;
MONTH TOT_SALES
---------- ----------
1 610697
2 428676
3 637031
4 541146
5 592935
6 501485
7 606914
8 460520
9 392898
10 510117
11 532889
12 492458
已选择12行。
【3】测试语句：
回忆一下前面《Oracle开发专题之：分析函数(OVER)》一文中，我们使用了sum(sum(tot_sales)) over (partition by region_id) 来统计每个分区的订单总额。现在我们要统计的不单是每个分区，而是所有分区，partition by region_id在这里不起作用了。
Oracle为这种情况提供了一个子句：rows between ... preceding and ... following。从字面上猜测它的意思是：在XXX之前和XXX之后的所有记录，实际情况如何让我们通过示例来验证：
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over (order by month
4 rows between unbounded preceding and unbounded following) total_sales
5 from orders
6 group by month;
MONTH MONTH_SALES TOTAL_SALES
---------- ----------- -----------
1 610697 6307766
2 428676 6307766
3 637031 6307766
4 541146 6307766
5 592935 6307766
6 501485 6307766
7 606914 6307766
8 460520 6307766
9 392898 6307766
10 510117 6307766
11 532889 6307766
12 492458 6307766
已选择12行。
绿色高亮处的代码在这里发挥了关键作用，它告诉oracle统计从第一条记录开始至最后一条记录的每月销售额。这个统计在记录集形成的过程中执行了12次，这时相当费时的！但至少我们解决了问题。
unbounded preceding and unbouned following的意思针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢？看看下面的结果：
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over (order by month
4 rows between 1 preceding and unbounded following) all_sales
5 from orders
6 group by month;
MONTH MONTH_SALES ALL_SALES
---------- ----------- ----------
1 610697 6307766
2 428676 6307766
3 637031 5697069
4 541146 5268393
5 592935 4631362
6 501485 4090216
7 606914 3497281
8 460520 2995796
9 392898 2388882
10 510117 1928362
11 532889 1535464
12 492458 1025347
已选择12行。
很明显这个语句错了。实际1在这里不是从第1条记录开始的意思，而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数，如同unbounded就是告诉oracle不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。
三、窗口函数进阶－滚动统计(累积/均值)：
考虑前面提到的第2个需求：列出每月的订单总额以及截至到当前月的订单总额。也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和，依此类推。
很明显这个需求需要在统计第N月的订单总额时，还要再统计这N个月来的订单总额之和。想想上面的语句，假如我们能够把and unbounded following换成代表当前月份的逻辑多好啊！很幸运的是Oracle考虑到了我们这个需求，为此我们只需要将语句稍微改成: curreent row就可以了。
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and current row) current_total_sales
5 from orders
6 group by month;
MONTH MONTH_SALES CURRENT_TOTAL_SALES
---------- ----------- -------------------
1 610697 610697
2 428676 1039373
3 637031 1676404
4 541146 2217550
5 592935 2810485
6 501485 3311970
7 606914 3918884
8 460520 4379404
9 392898 4772302
10 510117 5282419
11 532889 5815308
12 492458 6307766
已选择12行。
现在我们能得到滚动的销售总额了！下面这个统计结果看起来更加完美，它展现了所有我们需要的数据：
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and current row) current_total_sales,
5 sum(sum(tot_sales)) over(order by month
6 rows between unbounded preceding and unbounded following) total_sales
7 from orders
8 group by month;
MONTH MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES
---------- ----------- ------------------- -----------
1 610697 610697 6307766
2 428676 1039373 6307766
3 637031 1676404 6307766
4 541146 2217550 6307766
5 592935 2810485 6307766
6 501485 3311970 6307766
7 606914 3918884 6307766
8 460520 4379404 6307766
9 392898 4772302 6307766
10 510117 5282419 6307766
11 532889 5815308 6307766
12 492458 6307766 6307766
已选择12行。
在一些销售报表中我们会时常看到求平均值的需求，有时可能是针对全年的数据求平均值，有时会是针对截至到当前的所有数据求平均值。很简单，只需要将：
sum(sum(tot_sales))换成avg(sum(tot_sales))即可。
四、窗口函数进阶－根据时间范围统计：
前面我们说过，窗口函数不单适用于指定记录集进行统计，而且也能适用于指定范围进行统计的情况，例如下面这个SQL语句就统计了当天销售额和五天内的评价销售额：
select trunc(order_dt) day,
sum(sale_price) daily_sales,
avg(sum(sale_price)) over (order by trunc(order_dt)
range between interval '2' day preceding
and interval '2' day following) five_day_avg
from cust_order
where sale_price is not null
and order_dt between to_date('01-jul-2001','dd-mon-yyyy')
and to_date('31-jul-2001','dd-mon-yyyy')
为了对指定范围进行统计，Oracle使用关键字range、interval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天，后2天范围内的记录，并统计其销售平均值。
五、窗口函数进阶－first_value/last_value：
Oracle提供了2个额外的函数：first_value、last_value，用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值，这两个函数就可以派上用场了。
select month,
first_value(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) prev_month,
sum(tot_sales) monthly_sales,
last_value(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) next_month,
avg(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) rolling_avg
from orders
where year = 2001
and region_id = 6
group by month
order by month;
首先我们来看:rows between 1 preceding and 1 following告诉Oracle在当前记录的前一条、后一条范围内查找并统计，而first_value和last_value在这3条记录中至分别找出第一条、第三条记录，这样我们就轻松地得到相邻三个月的销售记录及平均值了！
六、窗口函数进阶－比较相邻记录：
通过第五部分的学习，我们知道了如何利用窗口函数来显示相邻的记录，现在假如我们想每次显示当月的销售额和上个月的销售额，应该怎么做呢？
从第五部分的介绍我们可以知道，利用first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following))就可以做到了，其实Oracle还有一个更简单的方式让我们来比较2条记录，它就是lag函数。
leg函数类似于preceding和following子句，它能够通过和当前记录的相对位置而被应用，在比较同一个相邻的记录集内两条相邻记录的时候特别有用。
select month,
sum(tot_sales) monthly_sales,
lag(sum(tot_sales), 1) over (order by month) prev_month_sales
from orders
where year = 2001
and region_id = 6
group by month
order by month;
lag(sum(tot_sales),1)中的1表示以1月为基准。
分析函数3(Top/Bottom N、First/Last、NTile)
目录
===============================================
1.带空值的排列
2.Top/Bottom N查询
3.First/Last排名查询
4.按层次查询
一、带空值的排列：
在前面《Oracle开发专题之：分析函数2(Rank、Dense_rank、row_number)》一文中，我们已经知道了如何为一批记录进行全排列、分组排列。假如被排列的数据中含有空值呢？
SQL> select region_id, customer_id,
sum(customer_sales) cust_sales,
sum(sum(customer_sales)) over(partition by region_id) ran_total,
rank() over(partition by region_id
order by sum(customer_sales) desc) rank
from user_order
group by region_id, customer_id;
REGION_ID CUSTOMER_ID CUST_SALES RAN_TOTAL RANK
---------- ----------- ---------- ---------- ----------
10 31 6238901 1
10 26 1808949 6238901 2
10 27 1322747 6238901 3
10 30 1216858 6238901 4
10 28 986964 6238901 5
10 29 903383 6238901 6
我们看到这里有一条记录的CUST_TOTAL字段值为NULL，但居然排在第一名了！显然这不符合情理。所以我们重新调整完善一下我们的排名策略，看看下面的语句：
SQL> select region_id, customer_id,
2 sum(customer_sales) cust_total,
3 sum(sum(customer_sales)) over(partition by region_id) reg_total,
4 rank() over(partition by region_id
order by sum(customer_sales) desc NULLS LAST) rank
5 from user_order
6 group by region_id, customer_id;
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
---------- ----------- ---------- ---------- ----------
10 26 1808949 6238901 1
10 27 1322747 6238901 2
10 30 1216858 6238901 3
10 28 986964 6238901 4
10 29 903383 6238901 5
10 31 6238901 6
绿色高亮处，NULLS LAST/FIRST告诉Oracle让空值排名最后后第一。
注意是NULLS，不是NULL。
二、Top/Bottom N查询：
在日常的工作生产中，我们经常碰到这样的查询：找出排名前5位的订单客户、找出排名前10位的销售人员等等。现在这个对我们来说已经是很简单的问题了。下面我们用一个实际的例子来演示：
【1】找出所有订单总额排名前3的大客户：
SQL> select *
SQL> from (select region_id,
SQL> customer_id,
SQL> sum(customer_sales) cust_total,
SQL> rank() over(order by sum(customer_sales) desc NULLS LAST) rank
SQL> from user_order
SQL> group by region_id, customer_id)
SQL> where rank <= 3;
REGION_ID CUSTOMER_ID CUST_TOTAL RANK
---------- ----------- ---------- ----------
9 25 2232703 1
8 17 1944281 2
7 14 1929774 3
SQL>
【2】找出每个区域订单总额排名前3的大客户：
SQL> select *
2 from (select region_id,
3 customer_id,
4 sum(customer_sales) cust_total,
5 sum(sum(customer_sales)) over(partition by region_id) reg_total,
6 rank() over(partition by region_id
order by sum(customer_sales) desc NULLS LAST) rank
7 from user_order
8 group by region_id, customer_id)
9 where rank <= 3;
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
---------- ----------- ---------- ---------- ----------
5 4 1878275 5585641 1
5 2 1224992 5585641 2
5 5 1169926 5585641 3
6 6 1788836 6307766 1
6 9 1208959 6307766 2
6 10 1196748 6307766 3
7 14 1929774 6868495 1
7 13 1310434 6868495 2
7 15 1255591 6868495 3
8 17 1944281 6854731 1
8 20 1413722 6854731 2
8 18 1253840 6854731 3
9 25 2232703 6739374 1
9 23 1224992 6739374 2
9 24 1224992 6739374 2
10 26 1808949 6238901 1
10 27 1322747 6238901 2
10 30 1216858 6238901 3
18 rows selected.
三、First/Last排名查询：
想象一下下面的情形：找出订单总额最多、最少的客户。按照前面我们学到的知识，这个至少需要2个查询。第一个查询按照订单总额降序排列以期拿到第一名，第二个查询按照订单总额升序排列以期拿到最后一名。是不是很烦？因为Rank函数只告诉我们排名的结果，却无法自动替我们从中筛选结果。
幸好Oracle为我们在排列函数之外提供了两个额外的函数：first、last函数，专门用来解决这种问题。还是用实例说话：
SQL> select min(customer_id)
2 keep (dense_rank first order by sum(customer_sales) desc) first,
3 min(customer_id)
4 keep (dense_rank last order by sum(customer_sales) desc) last
5 from user_order
6 group by customer_id;
FIRST LAST
---------- ----------
31 1
这里有几个看起来比较疑惑的地方：
①为什么这里要用min函数
②Keep这个东西是干什么的
③fist/last是干什么的
④dense_rank和dense_rank()有什么不同，能换成rank吗？
首先解答一下第一个问题：min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢？
SQL> select keep (dense_rank first order by sum(customer_sales) desc) first,
2 keep (dense_rank last order by sum(customer_sales) desc) last
3 from user_order
4 group by customer_id;
select keep (dense_rank first order by sum(customer_sales) desc) first,
*
ERROR at line 1:
ORA-00907: missing right parenthesis
接下来看看第2个问题：keep是干什么用的？从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据，这就是keep的作用。告诉Oracle只保留符合keep条件的记录。
那么什么才是符合条件的记录呢？这就是第3个问题了。dense_rank是告诉Oracle排列的策略，first/last则告诉最终筛选的条件。
第4个问题：如果我们把dense_rank换成rank呢？
SQL> select min(region_id)
2 keep(rank first order by sum(customer_sales) desc) first,
3 min(region_id)
4 keep(rank last order by sum(customer_sales) desc) last
5 from user_order
6 group by region_id;
select min(region_id)
*
ERROR at line 1:
ORA-02000: missing DENSE_RANK
四、按层次查询：
现在我们已经见识了如何通过Oracle的分析函数来获取Top/Bottom N，第一个，最后一个记录。有时我们会收到类似下面这样的需求：找出订单总额排名前1/5的客户。
很熟悉是不？我们马上会想到第二点中提到的方法，可是rank函数只为我们做好了排名，并不知道每个排名在总排名中的相对位置，这时候就引入了另外一个分析函数NTile，下面我们就以上面的需求为例来讲解一下：
SQL> select region_id,
2 customer_id,
3 ntile(5) over(order by sum(customer_sales) desc) til
4 from user_order
5 group by region_id, customer_id;
REGION_ID CUSTOMER_ID TILE
---------- ----------- ----------
10 31 1
9 25 1
10 26 1
6 6 1
8 18 2
5 2 2
9 23 3
6 9 3
7 11 3
5 3 4
6 8 4
8 16 4
6 7 5
10 29 5
5 1 5
Ntil函数为各个记录在记录集中的排名计算比例，我们看到所有的记录被分成5个等级，那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。
