在一些实际使用中用可能会遇到要求对表中的数据进行分组并组内连接的情况。在Mysql中有group_concat()函数可以实现相关的功能，Oracle中也有类似的函数可以实现这个功能。在SAP HANA数据库中，没有可以直接调用的内置函数。但我们可以自定义procedure，来针对具体的表来实现。同时，也可以使用视图中的level hierarchy来实现。
即表中的数据为：
| COUNTRY | REGION |
|---|---|
UK | England |
| UK | Scotland |
| USA | New York |
| USA | Florida |
希望按COUNTRY列来分组，并得到查询结果：
| COUNTRY | REGION |
|---|---|
| UK | England,Scotland |
| USA | New York,Florida |
在SAP HANA的三种视图中，支持根据视图中数据的分布情况建立层级（Hierarchy）。可以支持两种类型的层级，分别为：Level Hierarchy 和Parent-child Hierarchy。Level hierarchy包含一个或多个层级，属性层层向上递进向上组成一种多对一的关系，直到最上层。常见的level hierarchy的例子是一个地址的层次，包含大洲，国家，州等等。Parent-child hierarchy使用父属性来在源表数据间定义树状层级结构。这里的父属性相当于描述表中一个自参考关系或者自连接关系。
在这篇文章中，分别使用procedure和level hierarchy两种方式来实现组内连接的功能。分别介绍如何实现，以及进行性能等的对比。
构造数据
droptable LEVEL_HIER_COUNTRY_REGION;
CREATE COLUMN TABLE LEVEL_HIER_COUNTRY_REGION (
"COUNTRY" VARCHAR(100),
"REGION" VARCHAR(100));
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Baden-Wuerttemberg');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Bayern');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Bremen');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Brandenburg');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Rheinland-Pfalz');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Hessen');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Saarland');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Nordrhein-Westfalen');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Niedersachen');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Hamburg');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Schleswig-Holstein');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Mecklenburg-Vorpommern');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Sachsen-Anhalt');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Berlin');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Thueringen');
insert into LEVEL_HIER_COUNTRY_REGION values('Germany','Sachsen');
insert into LEVEL_HIER_COUNTRY_REGION values('UK','England');
insert into LEVEL_HIER_COUNTRY_REGION values('UK','Wales');
insert into LEVEL_HIER_COUNTRY_REGION values('UK','Scotland');
insert into LEVEL_HIER_COUNTRY_REGION values('UK','Northern Ireland');
insert into LEVEL_HIER_COUNTRY_REGION values('USA','New York');
insert into LEVEL_HIER_COUNTRY_REGION values('USA','New Jersey');
insert into LEVEL_HIER_COUNTRY_REGION values('USA','Massachusetts');
insert into LEVEL_HIER_COUNTRY_REGION values('USA','Florida');
insert into LEVEL_HIER_COUNTRY_REGION values('USA','Texas');
insert into LEVEL_HIER_COUNTRY_REGION values('USA','Alabama');
insert into LEVEL_HIER_COUNTRY_REGION values('USA','Alaska');
insert into LEVEL_HIER_COUNTRY_REGION values('USA','California');
insert into LEVEL_HIER_COUNTRY_REGION values('USA','Illinois');
insert into LEVEL_HIER_COUNTRY_REGION values('USA','Nevada');
insert into LEVEL_HIER_COUNTRY_REGION values('Brazil','Pernambuco');
insert into LEVEL_HIER_COUNTRY_REGION values('Brazil','Sao Paulo');
insert into LEVEL_HIER_COUNTRY_REGION values('Brazil','Santa Catarina');
insert into LEVEL_HIER_COUNTRY_REGION values('Brazil','Rio Grande do Sul');
select * from LEVEL_HIER_COUNTRY_REGION;
使用Procedure
droptype country_regions;
create type country_regions as table
("COUNTRY" VARCHAR(100),"RS" VARCHAR(500));
drop procedure group_concat_pro;
create procedure group_concat_pro(in placeholder varchar(10),out cr country_regions )
language sqlscript
READS SQL DATA WITH RESULT VIEW group_concat_view AS
index_i integer;
index_j integer;
arr_countrys varchar(100) array;
cty varchar(100);
arr_regions varchar(100) array;
tmp_regions varchar(500);
begin
tab_countrys=select distinct country from LEVEL_HIER_COUNTRY_REGION;
arr_countrys:=array_agg(:tab_countrys.country);
regions=select null as country,null as rs from dummy;
for index_i in 1..cardinality(:arr_countrys) do
cty:=:arr_countrys[:index_i];
tab_regions=select region from LEVEL_HIER_COUNTRY_REGION where country=:cty;
arr_regions:=array_agg(:tab_regions.region);
tmp_regions:='';
for index_j in 1..cardinality(:arr_regions) do
tmp_regions:=:tmp_regions||:placeholder||:arr_regions[:index_j];
end for;
regions=select * from :regions where country is not null
union
select :cty as country,substr_after(:tmp_regions,:placeholder) as rs from dummy;
end for;
cr=select country,rs from :regions;
end;
则指定使用逗号分隔，调用存储过程结果为：
call group_concat_pro(',',?);
或
select * from group_concat_view WITH PARAMETERS ('placeholder' = ('$$placeholder$$', ','));
使用Level Hierarchy
建立属性视图，使其包含表中的所有列。并创建level hierarchy如图，node style选择“Name Path”，并且注意Level的级别与对应的列名。同时，可以选择分组和组内的排序方式。
验证并激活这个属性视图。则在_SYS_BIC 的schema中，在Column Views下会产生两个列视图，如图：
则，yuan/LEVEL_HIRE_VIEW/hier/group_concat则是对于建立的level hierarchy的内容。
可以查看视图的内容：
其中，LEVEL列来区分对应的层次，PARENTS和CHILDERN列包含对应的父子关系。QUERY_NODE,RESULT_NODE根据选择的Node Style的不同，显示不同。QUERY_NODE_NAME,RESULT_NODE_NAME指结点对应的列名。这里正是利用了level hierarchy视图中所展示的父子关系，来实现组内连接的功能的。
使用replace（）函数来去掉CHILDEREN列中多余内容，同样也可以使用replace（）函数来改变连接所用的分隔符。
则查询语句为：
select query_node_name,replace(replace(children,'['||query_node_name||'].[',''),']','')
from "_SYS_BIC"."yuan/LEVEL_HIRE_VIEW/hier/group_concat"
wherelevel=1;
得到查询结果：
对比
在一个数据量2K+的一张CITY_DISTRICT表上，分别使用procedure和level hierarchy的方式实现，其查询性能如下图所示。
在此场景下level hierarchy的性能明显优于procedure的性能。
另附测试数据和代码。
想获取更多SAP HANA学习资料或有任何疑问，请关注新浪微博@HANAGeek！我们欢迎你的加入！转载本文章请注明作者和出处<http://scn.sap.com/community/chinese/hana/blog/2014/08/19/%E4%BD%BF%E7%94%A8%E8%A7%86%E5%9B%BE%E4%B8... >，请勿用于任何商业用途。
- SAP Managed Tags:
2 Comments
