1.Lag与Lead

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。

create table t (a number, b number);

insert into t values (1,2);

insert into t values (2,4);

commit;

select a,b,lag(a,1) over (order by a) previous,lead(a,1) over (order by a) past from t;

A B PREVIOUS PAST

--------------- --------------- --------------- ---------------

1 2 2

2 4 1

也可以执行下面语句，当返回值为NULL时给一个默认值

select a,b,lag(a,1,-1) over (order by a) previous,lead(a,1, -2) over (order by a) past from t;

A B PREVIOUS PAST

--------------- --------------- --------------- ---------------

1 2 -1 2

2 4 1 -2

2. Extract
extract 函数可以从日期格式的值中单独提取年月日值。此处需要注意两点：

1. 只能从date类型的数值中提取year,month,day。
2. 只能从带有time zone类型的timestamp数值中提取timezone_hour, timezone_minute。

例如：

extract(year from date '2010-02-08') 将返回2003;
extract(month from date '2010-02-08') 将返回2;
extract(day from date '2010-02-08') 将返回8;

3. Ntile

ntile是一个分析函数，它可以将选取的记录根据提供的表达式分为几组数据，每组数据的编号是从1开始，一直到指定的表达式为止，因此指定的表达式一定为正数；

如果指定的表达式大于选取记录的行数，则每组数据的编号则等于行号，同时剩余的表达式则为NULL。

如下的例子中根据where条件我们可以得到6条记录，然后我们想将此6条记录分为4个组，按照6/4的比例，所以前两组中平均每组会有2条记录；此时会剩余2条记录，因为一共需要分成4组，并且前面已经分配了2组，所以在后面的2组中平均每组就只能有1条数据（(6-2×2)/2）。

SELECT last_name, salary, NTILE(4) OVER (ORDER BY salary DESC)
   AS quartile FROM employees
   WHERE department_id = 100;

LAST_NAME SALARY QUARTILE
------------------------- ---------- ----------
Greenberg 12000 1
Faviet    9000 1
Chen    8200 2
Urman 7800 2
Sciarra 7700 3
Popp 6900 4

ITPub博客

Oracle函数小结

最新文章