oracle求最大值的函数吗_Oracle over函数的学习笔记五 最大最小值函数

求最大最小值函数：

FIRST

功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行(可能多行，因为值可能相等)，因此完整的语法需要在开始处加上一个集合函数以从中取出记录SAMPLE：下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最高的值。

LAST功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行(可能多行，因为值可能相等)，因此完整的语法需要在开始处加上一个集合函数以从中取出记录

SELECT last_name lname, department_id depid, salary

,MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)   OVER (PARTITION BY department_id) "Worst"

,MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)   OVER (PARTITION BY department_id) "Best"

FROM employees

WHERE department_id in (20,80)

ORDER BY department_id, salary;

LNAME  DEPID  SALARY Worst  Best

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

Fay 20 6000 6000 13000

Hartstein 20 13000  6000 13000

Kumar 80 6100 6100 14000

Banda 80 6200 6100 14000

Johnson 80 6200 6100 14000

Ande  80 6400 6100 14000

Lee 80 6800 6100 14000

Tuvault  80  7000 6100 14000

Sewall 80 7000 6100 14000

Marvins  80 7200 6100 14000

Bates 80 7300 6100 14000

……

FIRST_VALUE功能描述：返回组中数据窗口的第一个值。SAMPLE：下面例子计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字

SELECT department_id depid, last_name lname, salary

,FIRST_VALUE(last_name) OVER (PARTITION BY department_id ORDER BY salary ASC) AS lowest_sal

FROM employees

WHERE department_id in(20,30);

DEPID LNAME SALARY LOWEST_SAL

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

20Fay 6000 Fay

20Hartstein  13000 Fay

30Colmenares 2500 Colmenares

30Himuro 2600 Colmenares

30Tobias 2800 Colmenares

30Baida 2900 Colmenares

30Khoo 3100 Colmenares

30Raphaely 11000Colmenares

LAST_VALUE功能描述：返回组中数据窗口的最后一个值。SAMPLE：下面例子计算按部门分区按薪水排序的数据窗口的最后一个值对应的名字，如果薪水的最后一个值有多个，则从多个对应的名字中取缺省排序的最后一个名字

SELECT department_id depid, last_name lname, salary

,FIRST_VALUE(last_name) OVER (PARTITION BY department_id ORDER BY salary ASC) AS lowest_nam

,LAST_VALUE(last_name)

OVER(PARTITION BY department_id ORDER BY salary) AS highest_nam

,LAST_VALUE(salary)

OVER(PARTITION BY department_id ORDER BY salary) AS highest_sal

FROM employees

WHERE department_id in(20,30);

DEPID LNAMESALARY LOWEST_NAMHIGHEST_NAMHIGHEST_SAL

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

20 Fay6000 FayFay6000

20 Hartstein13000 FayHartstein13000

30 Colmenares2500 ColmenaresColmenares2500

30 Himuro2600ColmenaresHimuro2600

30 Baida2800ColmenaresTobias2800

30 Tobias2800 ColmenaresTobias2800

30 Khoo3100 ColmenaresKhoo3100

30 Raphaely11000 ColmenaresRaphaely11000

在同样的salary情况下就能看出来。

****last_value()函数的默认的窗口是范围是rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，在进行比较的时候从当前窗口组中的第一行开始向后进行比较，所以会出现上边的结果。

MAX和MIN功能描述：在一个组中的数据窗口中查找表达式的最大值/最小值。

max, min函数如果不加order子句，窗口为每个partition分组内的数据记录集合，如果加了order子句，则默认窗口为rows between unbounded preceding and current row.

SAMPLE：下面例子中dept_max返回当前行所在部门的最大薪水值

SELECT department_id depid, last_name lname, salary,

MAX(salary) OVER (PARTITION BY department_id) AS dept_max

,MIN(salary) OVER (PARTITION BY department_id) AS dept_min

FROM employees WHERE department_id in (10,20,30);

DEPID LNAME SALARY DEPT_MAXDEPT_MAX

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

10 Whalen 4400 44004400

20Hartstein 13000 130006000

20 Fay6000 130006000

30 Raphaely 11000 110002500

30 Khoo 3100 110002500

30 Baida 2900 110002500

30 Tobias2800 110002500

30 Himuro 2600 110002500

30 Colmenares 2500 110002500