工作中用到一段比较复杂的SQL查询脚本,使用了listagg()函数实现了具有多个值的字段的填充(即,列表聚合,list aggregation(我猜的))。
说简单点,listagg()函数可以实现多列记录聚合为一条记录,从而实现数据的压缩、致密化(data densification)。
以下内容转载自http://dacoolbaby.iteye.com/blog/1698957,SQL脚本做了部分优化,增加了输出结果。
-------------------------------------------------------------------------
这是一个Oracle的列转行函数:LISTAGG()
先看示例代码:
- with temp as(
- select 'China' nation ,'Guangzhou' city from dual union all
- select 'China' nation ,'Shanghai' city from dual union all
- select 'China' nation ,'Beijing' city from dual union all
- select 'USA' nation ,'New York' city from dual union all
- select 'USA' nation ,'Bostom' city from dual union all
- select 'Japan' nation ,'Tokyo' city from dual
- )
- select nation,listagg(city,',') within GROUP (order by city) as Cities
- from temp
- group by nation
运行结果:
- with temp as(
- select 'China' nation ,'Guangzhou' city from dual union all
- select 'China' nation ,'Shanghai' city from dual union all
- select 'China' nation ,'Beijing' city from dual union all
- select 'USA' nation ,'New York' city from dual union all
- select 'USA' nation ,'Bostom' city from dual union all
- select 'Japan' nation ,'Tokyo' city from dual
- )
- select nation,listagg(city,',') within GROUP (order by city)
- from temp
- group by nation
这是最基础的用法:
LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX),
用法就像聚合函数一样,通过Group by语句,把每个Group的一个字段,拼接起来,非常方便。
同样是聚合函数,还有一个高级用法:
就是over(partition by XXX)
也就是说,在你不使用Group by语句时候,也可以使用LISTAGG函数:
- with temp as(
- select 500 population, 'China' nation ,'Guangzhou' city from dual union all
- select 1500 population, 'China' nation ,'Shanghai' city from dual union all
- select 500 population, 'China' nation ,'Beijing' city from dual union all
- select 1000 population, 'USA' nation ,'New York' city from dual union all
- select 500 population, 'USA' nation ,'Bostom' city from dual union all
- select 500 population, 'Japan' nation ,'Tokyo' city from dual
- )
- select population,
- nation,
- city,
- listagg(city,',') within GROUP (order by city) over (partition by nation) rank
- from temp
总结:LISTAGG()把它当作SUM()函数来使用就可以了。