专栏首页Hadoop数据仓库Oracle 聚合函数解决聚集连接字符串问题

Oracle 聚合函数解决聚集连接字符串问题

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wzy0623/article/details/53906139

需求: 给定数据表:wm_test code name 1 a 1 b 1 c 2 中 2 国 2 人

需要的结果(分隔符可以由参数输入): code name 1 a,b,c 2 中,国,人

分析:

这个问题在可以使用Oracle的wmsys.wm_concat 函数解决: select wt.code, wm_concat(wt.name) names from wm_test wt group by wt.code;

但是这有三个问题: 1. wmsys.wm_concat 是10g才有的,以前的版本无法使用 2. wmsys.wm_concat 是ORACLE内部函数,没有对外公布,也就是说,你可以使用,但是如果发生什么问题ORACLE概不负责。最显然的是ORACLE版本从10.2.0.4升级到10.2.0.5,只是一个小版本的变更,足以让你的系统出现问题。

解决方案: 1. 升级到Oracle 11g Release 2,此版本引入了LISTAGG 函数,使得聚集连接字符串变得很容易,并且允许使用我们指定连接串中的字段顺序。 2. 用自己定义的聚合函数替换wmsys.wm_concat

代码如下:

[sql] view plain copy

  1. -- 1. 建立测试表和数据:
  2. CREATE TABLE WM_TEST
  3. (
  4. CODE INTEGER,
  5. NAME VARCHAR2(20 BYTE)
  6. );
  7. Insert into WM_TEST (CODE, NAME) Values (1, 'a');
  8. Insert into WM_TEST (CODE, NAME) Values (1, 'b');
  9. Insert into WM_TEST (CODE, NAME) Values (1, 'c');
  10. Insert into WM_TEST (CODE, NAME) Values (2, '中');
  11. Insert into WM_TEST (CODE, NAME) Values (2, '国');
  12. Insert into WM_TEST (CODE, NAME) Values (2, '人');
  13. COMMIT;
  14. -- 2. 建立自定义聚合函数
  15. CREATE OR REPLACE TYPE ConcatObj AS OBJECT
  16. (
  17. fieldValue VARCHAR2 (4000),
  18. separator VARCHAR2 (100)
  19. )
  20. /
  21. CREATE OR REPLACE TYPE type_wm_concat
  22. AS OBJECT
  23. (
  24. l_join_str VARCHAR2 (32767 BYTE), -- 连接后的字符串
  25. l_flag VARCHAR2 (100 BYTE), -- 分隔符,默认值可在body中定义
  26. STATIC FUNCTION ODCIAggregateInitialize -- 初始化
  27. (sctx IN OUT type_wm_concat)
  28. RETURN NUMBER,
  29. MEMBER FUNCTION ODCIAggregateIterate -- 迭代器,处理每行数据
  30. (self IN OUT type_wm_concat,
  31. VALUE IN ConcatObj)
  32. RETURN NUMBER,
  33. MEMBER FUNCTION ODCIAggregateTerminate -- 迭代结束后处理代码
  34. (self IN OUT type_wm_concat,
  35. return_v OUT VARCHAR2,
  36. flags IN NUMBER)
  37. RETURN NUMBER,
  38. MEMBER FUNCTION ODCIAggregateMerge -- 结果合并
  39. (self IN OUT type_wm_concat,
  40. ctx2 IN type_wm_concat)
  41. RETURN NUMBER
  42. );
  43. /
  44. CREATE OR REPLACE TYPE BODY type_wm_concat
  45. IS
  46. STATIC FUNCTION ODCIAggregateInitialize -- 初始化
  47. (sctx IN OUT type_wm_concat)
  48. RETURN NUMBER
  49. IS
  50. BEGIN
  51. sctx := type_wm_concat (NULL, NULL);
  52. RETURN ODCIConst.success;
  53. END ODCIAggregateInitialize;
  54. MEMBER FUNCTION ODCIAggregateIterate -- 迭代器,处理每行数据
  55. (self IN OUT type_wm_concat,
  56. VALUE IN ConcatObj)
  57. RETURN NUMBER
  58. IS
  59. BEGIN
  60. IF self.l_join_str IS NOT NULL AND VALUE.fieldValue IS NOT NULL
  61. THEN
  62. self.l_join_str := self.l_join_str || self.l_flag || VALUE.fieldValue;
  63. ELSIF VALUE.fieldValue IS NOT NULL
  64. THEN
  65. self.l_join_str := VALUE.fieldValue;
  66. self.l_flag := VALUE.separator;
  67. END IF;
  68. RETURN ODCIConst.Success;
  69. END;
  70. MEMBER FUNCTION ODCIAggregateTerminate -- 迭代结束后处理代码
  71. (self IN OUT type_wm_concat,
  72. return_v OUT VARCHAR2,
  73. flags IN NUMBER)
  74. RETURN NUMBER
  75. IS
  76. BEGIN
  77. return_v := self.l_join_str;
  78. RETURN ODCIConst.Success;
  79. END;
  80. MEMBER FUNCTION ODCIAggregateMerge (self IN OUT type_wm_concat,
  81. ctx2 IN type_wm_concat)
  82. RETURN NUMBER
  83. IS
  84. BEGIN
  85. IF ctx2.l_join_str IS NOT NULL AND self.l_join_str IS NOT NULL
  86. THEN
  87. self.l_join_str := self.l_join_str || self.l_flag || ctx2.l_join_str;
  88. ELSIF ctx2.l_join_str IS NOT NULL
  89. THEN
  90. self.l_join_str := ctx2.l_join_str;
  91. END IF;
  92. RETURN ODCIConst.Success;
  93. END;
  94. END;
  95. /
  96. -- 3. 封装为一个普通的SQL函数:
  97. CREATE OR REPLACE FUNCTION my_wm_concat (pi_str ConcatObj)
  98. RETURN VARCHAR2
  99. PARALLEL_ENABLE
  100. AGGREGATE USING type_wm_concat;
  101. /
  102. -- 4. 测试:
  103. SELECT wt.code, my_wm_concat (ConcatObj (wt.name, '|+=')) names
  104. FROM wm_test wt
  105. GROUP BY wt.code;

code name 1 a|+=b|+=c 2 中|+=国|+=人

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle 自定义split 函数

    Oracle没有提供split函数,但可以自己建立一个函数实现此功能。比如“abc defg  hijkl   nmopqr     stuvw  xyz”,分...

    用户1148526
  • HAWQ取代传统数仓实践(一)——为什么选择HAWQ

            为了跟上所谓“大数据”技术的脚步,从两年前开始着手实践各种SQL-on-Hadoop技术,从最初的Hive,到SparkSQL,再到Impala...

    用户1148526
  • HAWQ技术解析(十八) —— 问题排查

    (原文地址:http://hawq.incubator.apache.org/docs/userguide/2.1.0.0-incubating/trouble...

    用户1148526
  • 理解snprintf()函数

    在编程中,需要关注snprintf()的两个问题:一是它的返回值,二是它的第二个参数。

    一见
  • 让你的笔记本更快一点——我的笔记本的性能测试和虚拟硬盘(把内存当成硬盘)的使用感觉

        以前是使用公司的笔记本,但是现在辞职没有本本用了,去外地的时候不太方便,于是一咬牙买了一个自己的本本(惭愧呀,人家一咬牙拿出几十万开个公司创业,而我只能...

    用户1174620
  • SDN产业联盟与ONOS战略合作

    4月22日,“2015中国SDN/NFV大会”在京举行。大会上SDN产业联盟与开源组织ONOS(Open Networking Operating System...

    SDNLAB
  • java 资深程序员第一课;jvm优化、了解jvm运行加载class变量过程

            1、首先运行程序,Demo1_car.java就会变为Demo1_car.class,将Demo1_car.class加入方法区,检查是否字节码...

    冯杰宁
  • 用Go自己实现配置文件热加载功能

    说到配置文件热加载,这个功能在很多框架中都提供了,如beego,实现的效果就是当你修改文件后,会把你修改后的配置重新加载到配置文件中,而不用重启程序,这个功能在...

    coders
  • Angularjs 初步使用总结

    Angularjs比较适合做SPA,所以在express中只需要把地址指导制定的html页面就好,其余的接口,全部都是面向数据即可。

    IMWeb前端团队
  • Angularjs 初步使用总结

    背景 在最近的一个管理后台的项目中,决定用angularjs去试一下水,后台采用express4.0的node来写,前端就直接用angularjs来实现。第一...

    IMWeb前端团队

扫码关注云+社区

领取腾讯云代金券