利用arrary_agg方便的实现相同键值的文本拼接

今天一个客户问怎样把表中相同键值对应的文本按照一定顺序拼接起来。如果使用SQL实现将非常麻烦，并且效率低下。GP4.1以后提供了一个函数array_agg可以方便快捷，高效的实现该功能

比如原始查询是

test1=# select n_regionkey,n_name from nation;
 n_regionkey |          n_name
-------------+---------------------------
           3 | UNITED KINGDOM
           1 | BRAZIL
           2 | VIETNAM
           4 | IRAN
           4 | SAUDI ARABIA
           4 | JORDAN
           0 | ALGERIA
           4 | EGYPT
           4 | IRAQ
           0 | ETHIOPIA
           1 | ARGENTINA
           3 | ROMANIA
           2 | CHINA
           2 | JAPAN
           0 | MOROCCO
           2 | INDONESIA
           1 | UNITED STATES
           0 | MOZAMBIQUE
           3 | RUSSIA
           3 | GERMANY
           1 | CANADA
           1 | PERU
           0 | KENYA
           2 | INDIA
           3 | FRANCE

我们要把相同地区的代码的国家，按照字母顺序拼接在一起，放在一行当中，并且显示地区代码。通过arrary_agg可以很方便的实现该功能

test1=# select n_regionkey,array_agg(trim(n_name) order by n_name) from nation group by n_regionkey;
 n_regionkey |                    array_agg
-------------+--------------------------------------------------
           2 | {CHINA,INDIA,INDONESIA,JAPAN,VIETNAM}
           4 | {EGYPT,IRAN,IRAQ,JORDAN,"SAUDI ARABIA"}
           1 | {ARGENTINA,BRAZIL,CANADA,PERU,"UNITED STATES"}
           3 | {FRANCE,GERMANY,ROMANIA,RUSSIA,"UNITED KINGDOM"}
           0 | {ALGERIA,ETHIOPIA,KENYA,MOROCCO,MOZAMBIQUE}

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25548387/viewspace-713073/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25548387/viewspace-713073/