Oracle Database 11g SQL开发指南学习笔记：高级查询(五)
ok', 1, 600 from dual
union all
select 2013, 7, 'Book', 1, 700 from dual
union all
select 2013, 8, 'Book', 1, 800 from dual
union all
select 2013, 9, 'Book', 1, 900 from dual
union all
select 2013, 10, 'Book', 1, 1000 from dual
union all
select 2013, 11, 'Book', 1, null from dual
union all
select 2013, 12, 'Book', 1, 1000 from dual
union all
select 2013, 1, 'Magazine', 1, 100 from dual
union all
select 2013, 2, 'Magazine', 2, 200 from dual
union all
select 2013, 3, 'Magazine', 1, 300 from dual
union all
select 2013, 4, 'Magazine', 2, 400 from dual
union all
select 2013, 5, 'Magazine', 1, 500 from dual
union all
select 2013, 6, 'Magazine', 2, 600 from dual
union all
select 2013, 7, 'Magazine', 1, 700 from dual
union all
select 2013, 8, 'Magazine', 2, 800 from dual
union all
select 2013, 9, 'Magazine', 1, null from dual
union all
select 2013, 10, 'Magazine', 2, 1000 from dual
union all
select 2013, 11, 'Magazine', 2, null from dual
union all
select 2013, 12, 'Magazine', 1, 800 from dual
);
commit;
--1.排名函数
--1.1在降序排列时，默认会把空值排到第一，而在升序排列时会把空值排到最后
select year,
month,
amount,
--在值相等的情况下，名次会留下空位
rank() over(order by amount desc) as rank,
--不会留有空位，是密集的
dense_rank() over(order by amount desc) as dense_rank,
--行号，类似于rownum伪列，但按照某个字段排序后再编号
row_number() over(order by amount desc) as row_number,
--按照返回的记录显示行号，与row_number显示的行号不同
rownum,
--按照字段排序，根据记录条数/分片数=12/4 = 3，也就是每个片有3条记录，一共4片
ntile(4) over(order by amount desc) as ntile
from t
where type_name = 'Magazine';
--1.2通过nulls first和nulls last来空值null在排序时，显示的位置
select year,
month,
amount,
emp_id,
--在值相等的情况下，名次会留下空位
rank() over(order by amount desc nulls last) as rank,
--不会留有空位，是密集的
dense_rank() ov
er(order by amount desc nulls last) as dense_rank,
--行号，类似于rownum伪列，但按照某个字段排序后再编号
row_number() over(order by amount desc nulls last) as row_number,
--按照返回的记录显示行号，与row_number显示的行号不同
rownum,
--按照emp_id分组，这里每组有6条记录，再按字段排序，
--根据记录条数/分片数=6/4 = 1.5，前两个分片每片有2条记录,后2个分片每个1条记录
ntile(5) over(partition by emp_id
order by amount desc nulls last
) as ntile
from t
where type_name = 'Magazine'
order by emp_id,ntile;
--2.百分点函数、反百分点函数、假想评级分布函数
--百分点函数
select v,
--rank排名
rank() over(order by v desc) as rank,
--rank排名号/总的记录数
--如果有多个值相同，那么取相同值中最大的row_number/总的记录数
cume_dist() over(order by v desc) as cume_dist,
--(rank排名号-1)/(总的记录数 - 1)
--如果有多个值相同，那么取相同值中最小的row_number/总的记录数
percent_rank() over(order by v desc) as percent_rank
from
(
select 600 as v from dual
union all
select 400 as v from dual
union all
select 100 as v from dual
union all
select 300 as v from dual
union all
select 300 as v from dual
)
--反百分点函数、假想评级分布函数
select --反百分点函数
--在每个分组中检查累积分布的数值，直到找到大于或等于参数的值，与percent_disc相反
percentile_disc(0.8) within group (order by v desc) as percentile_disc,
--在每个分组中检查百分比排名的值，直到找到大于或等于参数的值，与percent_rank相反
percentile_cont(0.5) within group (order by v desc) as percentile_count,
--假想评级分布函数
--假设v的值为350，那么返回rank排名
rank(350) within group (order by v desc) as rank,
--假设v的值为350，那么返回percent_rank的百分比
percent_rank(350) within group (order by v desc) as percent_rank
from
(
select 600 as v from dual
union all
select 400 as v from dual
union all
select 100 as v from dual
union all
select 300 as v from dual
union all