分析関数 OVER (PARTITION BY ~ ORDER BY ~)


グループ毎の件数

select 社員マスタ.* ,count(*) over( partition by 課コード) as 課人数 from 社員マスタ

課コード毎の人数も出力する


グループ毎の最大値

select 社員マスタ.* ,max(年齢) over( partition by 課コード) as 課高齢 from 社員マスタ

課コード毎の最高年齢も出力する


グループ毎の合計数

select 社員マスタ.* ,sum(基本給) over( partition by 課コード) as 課基本給 from 社員マスタ

課コード毎の基本給合計も出力する


グループ毎のランク

select 社員マスタ.* ,rank() over( partition by 課コード order by 基本給 desc) as 課基本給ランク from 社員マスタ

課コード毎の基本給高額順位も出力する

基本給が同額の人がいた場合、rank関数は、1,1,3 と返す。

同種の関数で  dense_rank 関数は  1,1,2 と返す


n行前の参照
     n行前が存在しない場合は、nullを返却する

select 社員マスタ.* ,lag(基本給) over( order by 基本給) as 次基本給額 from 社員マスタ

自分の次に高い基本給も出力する(基本給順にならべて、自分の前のレコード)

 

select 社員マスタ.* ,lag(基本給,2) over( order by 基本給) as 次基本給額 from 社員マスタ

自分の次の次に高い基本給も出力する(基本給順にならべて、自分の2つ前のレコード)

select 社員マスタ.* ,lag(基本給) over(partition by 課コード order by 基本給) as 課内次基本給額 from 社員マスタ

自分課内で、次に高い基本給も出力する(課内で基本給順にならべて、自分の前のレコード)


n行後の参照

 select 社員マスタ.* ,lead(基本給) over( order by 基本給 ) as 次基本給額 from 社員マスタ

 


グループの先頭/末尾

LAST_VALUE( 項目 [ IGNORE NULLS ] )  IGNORE NULLS を付けると、nullでない先頭/末尾の項目を取得する

 select 社員マスタ.* ,first_value(社員コード) over(partition by 課コード order by 基本給 ) as 課低基本給額社員 from 社員マスタ

 select 社員マスタ.* ,last_value(社員コード) over(partition by 課コード order by 基本給 ) as 課高基本給額社員 from 社員マスタ

 

参考
ORACLE/オラクルSQLリファレンス(分析関数:COUNT)