Oracle中的within,Oracle函數 --聚合函數中的文法within group

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 temp

group 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) rank

from 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) rank --Bf僅為一個參數，結果為Bf在population=500中可以排第幾

from 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,city

from temp

order by population,city desc