Oracle函数 --聚合函数中的语法within group

来源：互联网发布：怎么消除马赛克软件编辑：程序博客网时间：2024/06/03 16:37

Oracle的聚合函数一般与group by 联合使用，但一般通过group by 聚合

但某些聚合函数会后跟

WITHIN GROUP   (ORDER BY    expr [ DESC | ASC ]         [ NULLS { FIRST | LAST } ]    [, expr [ DESC | ASC ]            [ NULLS { FIRST | LAST } ]    ]...   )

) 指定在group 组内的顺序。

可理解为在组内的顺序，日后也许会有其他应用。

For example 1 :

同组字符串拼接函数（11gR2新增，与10g中 WMSYS.WM_CONCAT 相同）

LISTAGG(measure_expr [, 'delimiter'])  WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

（注： over（）为可选语法，在次函数中，与group by 作用相同，即 group by （）与over（）选一即可）

例一：合并同组的字符，连接方式以city倒叙排序

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 desc)from tempgroup by nation;例二：使用over()代替group by 语法

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,       listagg(city,',') within GROUP (order by city) over (partition by nation) rankfrom temp;

For example 2:

Rank() 函数--rank函数有聚合函数也可使用分析函数（二者不可同时使用）

--分析函数为选定的字段按指定顺序表名排序，可与其他字段一同出现；

--聚合函数将指定参数的在指定排序的结果集中可排顺序，函数只可单独使用，指定

例一: 使用rank查询参数在结果集排序中得位置（注：rank中得参数必须与within group（order by）中指定的参数相同（数量，顺序））

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        rank(500,'Bf') within GROUP (order by population,city desc) rankfrom temp;--参考表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,cityfrom temporder by population,city desc

0 0