重写Oracle的wm_concat函数,自定义分隔符、排序
oracle中,wm_concat函数是一个聚合函数,和mysql中的group_concat函数类似,不过group_concat函数比较强大,可以定义分隔符和排序,当然所谓强大是相对的,这里假使我们不知道oracle中的over函数,也不知道listagg函数。
我们先来看看wm_concat函数能实现什么功能,通俗点==>列传行,如果不明白,请看下面截图(可以看到分隔符默认为','顺序也是杂乱的)
所以,接下来,我们开始重写wm_concat函数(需要注意和需要说明的地方放在代码注释中...)
(1) 因为需要进行排序,首先自定义一个可变数组
1 2 |
|
(2)自定义排序函数、分隔符函数
1 2 3 4 5 6 7 8 9 10 11 12 |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
|
(3) 重定义oracle接口函数、以及接口函数的实现体(实现分隔符和排序的主要代码)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 |
|
(4)自定义聚集函数
1 2 3 |
|
至此,主要的代码已经全部奉上,看看运行效果,如下截图:
①看看调用的默认情况(分隔符默认是逗号,排序默认是升序,在初始化函数中如此定义的)
②自定义分隔符(利用分隔符函数将分隔符定义为*)
③降序排序
④去重,为了可以使用wm_concat自带的去重函数,所以在自定义分隔符和排序函数时,实质是实用了字符串处理(如果你觉得处理字符串麻烦,可以自定义 type... as object ,在使用的时候可以很方便,不会用的童鞋可以私下问)
转自:https://www.cnblogs.com/wangyong/p/6254872.html
同一个SQLID 完整的SQL语句,在分析AWR报告的时候可以直接查看完成的SQL就行SQL优化
select a.p, a.sql_id,a.r
from (select sql_id,
wm_concat(delimiter(sql_text,'*')) OVER(PARTITION BY sql_id ORDER BY t.PIECE) r,t.PIECE n,
max(t.PIECE) over(PARTITION BY sql_id ) p
/* row_number() OVER(PARTITION BY sql_id ORDER BY t.PIECE desc) p*/
from v$sqltext t
where sql_id = 'db56m4yjbn001'
/*order by t.PIECE desc*/) a
where a.n=a.p/*a.p=1*/;