Note04--聚合函数

Oracle：
1. 最大最小函数
max和min是用来取某个字段的最大最小值，并不是说如果你有几个数，然后你直接从它们中取出最大或者最小，由于我不知道greatest和least这两个函数，最后我实现的是通过case去比较的，如果A大于B就取A，否则取B，现在就可以直接用greatest(A, B)来实现了。
1、coalescue
返回表达式列表中第一个非空的值，列表必须都是相同类型，也可以是一个表的同一行、不同列的值进行比较。

SELECT coalesce(1,NULL,2) FROM dual; -- 返回1
SELECT coalesce(NULL,2,1) FROM dual; -- 返回2
SELECT coalesce(t.empno,t.mgr) FROM scott.emp t; -- 效果类似 NVL( t.empno, t.mgr )

2、greatest
返回表达式列表中最大值，列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。当列表中有一个值为null，那么就返回null，这个地方需要注意，如果需要当值为null的时候，可以考虑用nvl转为0来进行比较。

SELECT greatest(1,3,2) FROM dual; -- 返回3
SELECT greatest('A','B','C') FROM dual; -- 返回C
SELECT greatest(NULL,'B','C') FROM dual; -- 返回null
SELECT greatest(t.empno,t.mgr) FROM scott.emp t; -- 返回empno和mgr 较大值

3、least
返回表达式列表中最小值，列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。当列表中有一个值为null，那么就返回null，这个地方需要注意，如果需要当值为null的时候，可以考虑用nvl转为0来进行比较。

SELECT least(1,3,2) FROM dual; -- 返回1
SELECT least('A','B','C') FROM dual; -- 返回A
SELECT least(NULL,'B','C') FROM dual; -- 返回null
SELECT least(t.empno,t.mgr) FROM scott.emp t; -- 返回empno和mgr 较小值

对于max和min函数，如果处理的列中有null值，那么max和min会忽略null值，但是，如果在该列中，所有行的值都是null，那么max、min的返回值就是null。

1. wm_concat
   If you are not running 11g Release 2 or above, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you. It is actually an example of a user defined aggregate function described below, but Oracle have done all the work for you.
   Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output asWM_CONCAT is both documented and supported by Oracle.
   WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). If this concerns you, use a User-Defined Aggregate Function described below.

Also, WM_CONCAT has been removed from 12c onward, so you can’t pick this option.
COLUMN employees FORMAT A50
SELECT deptno, wm_concat(ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES

---

    10 CLARK,KING,MILLER
    20 SMITH,FORD,ADAMS,SCOTT,JONES
    30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

3 rows selected.
5. LISTAGG
The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.
COLUMN employees FORMAT A50
SELECT deptno, LISTAGG(ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES

---

    10 CLARK,KING,MILLER
    20 ADAMS,FORD,JONES,SCOTT,SMITH
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

5. NVL和NVL2
NVL ( expr1 , expr2 ): If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
NVL2 ( expr1 , expr2 , expr3 ): If expr1 is null, then NVL2 returns expr3. If expr1 is not null, then NVL2 returns expr2