Oracle中复杂数据处理

利用聚合函数统计数据

求最大值-max（）

max()可应用数值型和字符型和日期型（实质也是数值型）

select max(employee_age) max_age from employees

max()应用字符型时，会按照字母表有前之后的顺序进行排序，如果含有英文名，则英文名永远排在中文名之前

求最小值-min()

求平均值-avg（）

avg（）只能用于数值型

select avg(empaoyee_age) avg_age from employees

求和-sum（）

统计记录数-count（）

统计单列

select count(employee_name) count_name from employees

当统计的这列的某条数据为空时，count（）不进行计数

统计所有列

count（*）、count（1）都是统计所有列。即使所有列值都为空，Oracle仍然进行计数

Oracle中的常用技巧

多值判断-decode()

语法：
decode(表达式，比较值1，结果值1，比较值2，结果值2，…，默认值)

decode（）处理过程类似于解码过程，第一个参数表达式往往为变量或数据表中的列；其后的参数总以‘比较值-结果值’对的形式出现，表达式与‘比较值’进行比较，如果相等，返回相应的‘结果值’；如果所有比较都不，那么返回默认值

select employee_name,decode(sign(instr(employee_position,'工程师'))，1，‘工程师’，‘行政管理部’) department from employees

sign(instr(employee_position,‘工程师’))获取工程师的位置并判断其正负性

空值处理-nvl（）

nvl（）判断表达式是否为空，如果为空则返回新值，不为空，返回原值

nvl(表达式，新值/表达式)

select nvl(sum(salary),0) total_salary from employees

结果集的行号-rownum（）

select rownum from employees

rownum和order by 子句

首先利用内嵌视图获得排序之后的结果，然后对这个结果进行rownum（）

select t.*,rownum from (select e.employee_id,employee_name from employee e order by e.employee_name) t where rownum<=3

rownum与比较运算符‘=’

select * from employees where rownum=1;

返回正确结果

select * from employees where rownum=4;

查询失败

因为rownum总是从1开始，那么rownum的返回值永远无法大于1。当Oracle遍历第一条数据时，rownum=1，但是whererownum=4，那么这条记录不再捕获范围内，当遍历第二条时，因为已捕获为0，所以rownum仍为1，以此类推，无论Oracle遍历多少数据，rownum总是为1，无法满足rownum=4。此时可以通过内嵌视图来解决

rownum与比较运算符‘>’

select * from employees where rownum=N;

当n>=1时，返回空，<1时，返回所有数据，原因与上一个同理

强制转换数据类型-cast（）

cast(原数据 as 新的数据类型)

典型应用：利用一个已有表创建一个新表

create table t_salary as select cast(s.salary as varchar2(20)) salary_id,cast(employee_id as varchar2(20)) employee_id from salary s

数学运算（±*/）

无论操作数是何种数据类型，都将首先转换为数值型，然后才参与运算，当其中一个操作树null时，运算结果也为null。注意开发中要用nvl（）来处理null的数据

逻辑运算

只要其中一个操作数为null，运算结果一定为假。即使null=null和null<>null，结果仍为假

按位运算

按位与

bitand（）

如果数值参数不为整数，Oracle总是先将其转换为整数-转换规则为直接截取整数部分

列的乘积

因为标准sql并未对乘积提供专门的函数，但是乘积运算可以转换为指数运算eln18+eln19+eln15=e(ln12+ln19+ln15 ),sql如下：

select exp(ln(18)+ln(19)+ln(15)) result from dual

结果5130

select exp(ln(data)) result from 表名

这种情况只针对全部是正数的情况，其他情况不想写

Oracle中的特殊判式

between-范围测试

select * from employees where employee_age between 26 and 35

去年龄在26到35之间的数据，包含26和35

in-集合成员测试

in判式用于判断某个值或表达式的值是否处于某个集合之内

select employee_name from employees where employee_id in (select distinct employee_id from salary)

like-模糊匹配

匹配任意字符串-‘%’

select employee_name from employees where employee_name like '种%'

匹配单个字符-‘_’

select employee_name from employees where employee_name like '种_'

结果：钟强

多个’_'可以同时使用，以指定判式的字符串长度

select employee_name from employees where employee_name like '种__'

结果：钟晓平

判断匹配‘%’的值，需要转义

select 1 result from dual where '10%' like '%\%' escape '\'

结果：1

escape ''指定转义字符‘\’，like '%%'第一个%表示通配符，第二个%，表示原义字符%

is null -空值判断

Oracle不能用=判断是否为空，

is not null 判断不为空

exists-存在性判断

exists的操作对象是结果集，当结果集中的记录数大于0时，返回真，否则返回假

select * from employee e where exists(select 1 from salary s where s.employee_id=e.employee_id) 

all,some,any-集合判断

获得年龄大于所有工程师的员工信息

select * from employees e where employee_age>all(select employee_age from employees where employee_position='工程师')

获取年龄大于任一位工程师的年龄，用some或any都可以。

select * from employees e where employee_age>any(select employee_age from employees where employee_position='工程师')

Oracle中的分析函数与窗口函数

排名函数

rank（）的使用

按照年龄大小升序排列，每位员工的排名情况

select employee_name,employee_age,rank() over(order by employee_age) employee_position from employees

rank()排名具有跳跃性，，比如2个人的排名并列第二，那么下一个人的排名就是第四，rank（）返回的排名也就是实际排名

dense_rank()的使用

dense_rank（）排名没有跳跃性，其他和rank（）相同

row_number()的使用

row_number（）只是单纯的返回行号，没有排名功能

分区窗口

分区窗口是指与当前记录拥有相同的分区标准的所有记录，创建分区窗口的语法如下：

partition by 列名

partition by语句首先根据列名获取当前记录的列值，接着获取具有相同列值的所有记录，并将该记录集合作为当前记录窗口

select employee_name,employee_position,employee_age,avg(employee_age) over(partition by employee_position) avg_age from employees

获得员工姓名和年龄的同时，也获得该职位员工的平均年龄，可以使用分区窗口

在分区窗口中，同样可以进行排序，例如，在获得员工信息的同时，获得同职位中年龄大小排序的位置

select employee_name,employee_position,employee_age,dense——rank（） over(partition by employee_position order by employee_age) position from employees

窗口子句

rows子句

rows的使用前提为，窗口已经用order by 排序

over(order by 列名 rows between 位移量 preceding and 位移量 following)

利用preceding向前追溯，利用following向后追溯

获取当前年龄和相邻的2位员工的年龄的总和

select employee_name,employee_position,employee_age, sum(employee_age) over(order by employee_age rows between 1 preceding and 1 following) sum_age from employees

range子句

rows子句以相对位置获取记录，而range子句以相对列值作为筛选记录

over(order by 列名 range between 差值 preceding and 差值 following)

利用preceding向前追溯，利用following向后追溯，追溯的范围为当前列值±差值

select employee_name,employee_position,employee_age, count（1） over(order by employee_age range between 1 preceding and 1 following) count from employees order by employee_age

获得与当前员工年龄相差1岁之内的员工数目

current row和unbounded

除了可以用确定的数值来限定窗口之外，还可以使用current row来指定当前记录，使用unbounded代替数值，表示不受限制的窗口范围

排序之后获取第一条记录至当前记录的窗口大小

select employee_name,employee_position,employee_age, count(1) over(order by employee_age rows between unbounded perceding and current row) count from employees order by employee_age

当然也可以对rows子句的前后位移均不进行限制。这样每条记录所获得的窗口均为表中的所有记录

select employee_name,employee_position,employee_age, count(1) over(order by employee_age rows between unbounded perceding and undounded following) count from employees order by employee_age

常用分析函数

first_value()

first_value()返回已排序窗口中第一条记录相关的信息

获得按照同龄人中，按姓名排序最前的员工

select employee_name,employee_age, first_value(employee_name) over(partition by employee_age order by employee_name) first_name from employees 

last_value()

返回分区中最后一条记录

select employee_name,employee_age, first_value(employee_name) over(partition by employee_age order by employee_name rows between unbounded preceding and undounded following) first_name from employees 

lag()

lag()以当前记录为坐标，按照特定位移向上搜索，并尝试捕获记录，语法如下:

lag(列名或列的表达式，位移，默认值)

列名或列的表达式针对捕获的记录，位移表示从当前行开始的偏移量，如果未捕获，则返回默认值

获取按姓名排序的前一位员工的信息

select employee_name,employee_age,lag(employee_name,1,'N/A') over(order by employee_age) lag_name lag(employee_age,1,null) over(order by employee_age) lag_age from employees

lead()

lead()和lag（）一样，只不过lead（）是想下偏移