oracle的sys_connect_by_path及有关分组分类聚合函数使用

由于业务需要对表中的数据进行行变列的处理，从网上查了下sys_connect_by_path的用法，备忘

=============Begin==============

表test 数据

id（varchar2）    mc（varchar2）

1                     111111

1                     222222

2                     111111

2                     222222

3                     111111

3                     222222

3                     333333

 

select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test;

 

利用分析函数，构造两列，做为连接的条件：按照id分组，RN-1等于PRIOR RN作为条件连接。
ID         MC                                                   RN_BY_ID         RN
---------- -------------------------------------------------- ---------- ----------
1          11111                                                       1          2
1          22222                                                       2          3
2          11111                                                       1          5
2          22222                                                       2          6
3          11111                                                       1          8
3          22222                                                       2          9
3          33333                                                       3         10

 

select id,ltrim(max(sys_connect_by_path(mc,';')),';') add_mc from (
select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test
)
start with rn_by_id = 1 connect by rn - 1 = prior rn
group by id
order by id;

 

另用sys_connect_by_path函数实现字符串的连接，把最左边的分号去掉，即得到我们想要的结果

（注意mc字段里不能包含';'字符，否则会出错）

ID         ADD_MC
---------- --------------------------------------------------------------------------------
1          11111;22222
2          11111;22222
3          11111;22222;33333

 

=============End==============

 

现有表T1：

a（varchar2）    b（varchar2）   c（varchar2）   d（varchar2）

aa01               b01                1001             d1

aa01               b01                1001             d2

aa01               b01                1001             d3

aa01               b03                1001             d1

aa01               b03                1001             d2

aa01               b03                1001             d3

aa01               b005               1001             d2

aa01               b005               1001             d1

aa01               b005               1001             d3

 

需要得到效果是按a，b，c 来分组，得到d的连接值

由于值不是数字，不能直接加，用聚合函数dense_rank()来构造分组连接条件：

 

SELECT    a,    b,    c,  max(substr(sys_connect_by_path(d, ', '), 3)) as dn
FROM    (SELECT       a,        b,        c,        d,
        dense_rank() over (order by a, b, c) + row_number() over (order by a, b, c)         as rnid,
        row_number() over (partition by a, b, c order by a, b, c)        as id
    FROM        T1 )         start with id = 1 connect by rnid - 1 = prior rnid
GROUP BY    a,b,c

 

a        b          c          dn

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

aa01  b01      1001    d1, d2, d3

aa01  b03      1001    d1, d2, d3

aa01  b003    1001    d1, d2, d3

=======================================

 

关于聚合函数 dense_rank() 及 rank()的说明：

  对给定的参数值在设定的排序查询中计算出其排序值。

  这些参数必须是常数或常值表达式，且必须和ORDER BY子句中的字段个数、位置、类型完全一致。

语法：
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
  expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
  [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)

 

两者的区别：

dence_rank在并列关系是，相关等级不会跳过。rank则跳过

参考：

表

   A        B            C
 a          liu          wang
 a          jin          shu
 a          cai          kai
 b          yang      du
 b          lin          ying
 b          yao        cai
 b          yang      99

当rank时为：

select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test m

 A          B             C          LIU
 a          cai          kai          1
 a          jin           shu        2
 a          liu           wang     3
 b          lin           ying        1
 b          yang      du           2
 b          yang      99           2
 b          yao        cai           4

而如果用dense_rank时为：

select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test m

 A          B             C          LIU
 a          cai          kai          1
 a          jin           shu        2
 a          liu           wang     3
 b          lin           ying        1
 b          yang      du           2
 b          yang      99           2
 b          yao        cai           3