分组聚合函数使用

1. MySQL 的分组合并函数GROUP_CONCAT

        group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由函
   数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。

       例：select 分组字段,group_concat(合并字段) from 表名 group by  分组字段；

参考：https://www.cnblogs.com/xd502djj/p/6093465.html

2. Oracle 的分组合并函数

    2.1 wm_concat   

        使用方法：select 分组字段,wm_concat(合并字段) from emp group by 分组字段;

        优点：效率高。

        缺点：

          （1）、返回最大字符数4000；

          （2）、行数据默认以逗号分隔，可以修改函数更改，但是函数一旦创建不能随意自定义分隔符；

          （3）、排序实现复杂且效率低；

         （4）、内部聚合混乱。比如：

    2.2 zh_concat

        该函数是在wm_concat基础上修改返回值类型得到，可以返回clob类型数据，内部实现同wm_concat。优缺点同wm_concat。

    2.3 listagg       

       11g新增函数，返回值varchar2，同样受4000字符数限制。但是可以排序，可以指定分隔符。

       使用方法：select 分组字段,listagg(合并字段,',') within group(order by 排序字段) from emp group by 分组字段

       优点：

         （1）、可排序

         （2）、可自定义分隔符

       缺点：

        （1）、仅11g之后版本可用

        （2）、返回最大字符数4000

     2.4 xmlagg   

       该方法通过将数据聚合成xml结构，再转换成varchar2或者clob类型。

       使用方法：

             2.4.1  select 分组字段,xmlagg(xmlparse(content 合并字段||',' wellformed) order by 排序字段).getstringVal() from emp group by 分组字段; // 返回的结果是字符串类型。

             2.4.2  select 分组字段,xmlagg(xmlparse(content 合并字段||',' wellformed) order by 排序字段).getclobval() from emp group by 分组字段; // 返回的结果是clob类型。          

      优点：

         （1）、可排序

         （2）、可返回clob类型容纳大数据量数据

         （3）、可自定义分隔符

         （4）、10g可用

      缺点：

        （1）、在不排序的情况下效率比wm_concat、zh_concat差

        （2）、在排序情况下效率比listagg差

        （3）、最终数据在后面或者前面会多一个分隔符，需要再做处理

   2.5 sys_connect_by_path

        借助connect by实现数据聚合。

 总结

       不同场景下使用不同方法（最佳选择）：

	10g	11g以上
排序（varchar2）	xmlagg	listagg
排序（clob）	xmlagg	xmlagg
不排序（varchar2）	wm_concat	wm_concat
不排序（clob）	zh_concat	zh_concat

  以上方法参考：https://zhengyunfei.iteye.com/blog/2411472

   2.6 多个函数组合 Start with connect by prior,SYS_CONNECT_BY_PATH, row_number() OVER

       select 分组字段,ltrim(max(sys_connect_by_path(合并字段,',')),',') AS VALUES from (
       select 分组字段,合并字段,row_number() over(partition by 分组字段,order by 分组字段) rn,
       from test
       )
       start with rn = 1 connect by rn - 1 = prior rn AND 分组字段=PRIOR 分组字段
       group by 分组字段
       order by 分组字段;

    2.6.1 Start with connect by prior语句

在SELECT命令中使用CONNECT BY和START WITH子句可以查询表中的树型结构关系。其命令格式如下： SELECT… … CONNECT BY {PRIOR列名1=列名2|列名1=PRIOR裂名2} [START WITH]； 其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIOR运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。 Start with...Connect By子句递归查询一般用于一个维护树形结构的应用。

    2.6.2 SYS_CONNECT_BY_PATH

SELECT ename  FROM scott.emp  START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr; 得到结果为：  KING    JONES    SCOTT    ADAMS    FORD    SMITH    BLAKE    ALLEN    WARD    MARTIN    TURNER    JAMES  而： SELECT SYS_CONNECT_BY_PATH(ename, '>') "Path"     FROM scott.emp     START WITH ename = 'KING'     CONNECT BY PRIOR empno = mgr;    --得到结果为：    >KING    >KING>JONES    >KING>JONES>SCOTT    >KING>JONES>SCOTT>ADAMS    >KING>JONES>FORD    >KING>JONES>FORD>SMITH    >KING>BLAKE    >KING>BLAKE>ALLEN    >KING>BLAKE>WARD    >KING>BLAKE>MARTIN    >KING>BLAKE>TURNER    >KING>BLAKE>JAMES    >KING>CLARK    >KING>CLARK>MILLER   综上所述：SYS_CONNECT_BY_PATH函数的第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！

 

      2.6.3 row_number() OVER

              row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号。

示例：  COL1   COL2     row_num  A      1700     1  A      1800     2  B      1085     1  B      1710     2

  参考：https://seandeng888.iteye.com/blog/2110590