分析関数の衝撃

PostgreSQLの分析関数の衝撃（7）
――window関数の変わった使用例

order by句でのwindow関数など

山岸賢治[著]

2010/02/15 14:00

ポスト

ダウンロード SourceCode (1.3 KB)

3. Oracleのkeep指定を模倣

　次は、Oracleのkeep指定を模倣するSQLです。サンプルを見てみましょう。

KeepDense

ID	SortKey1	SortKey2	Val
111	1	5	10
111	2	3	20
111	3	1	60
111	3	1	100
222	1	2	200
222	1	3	300
222	2	4	400
222	2	4	500
333	1	2	600
333	1	3	100

　OracleのSQLで使用できる分析関数でのkeep指定を模倣してみます。

模倣対象のOracleのSQL（分析関数でのkeep指定）

select ID,SortKey1,SortKey2,Val,
sum(Val) over(partition by ID) as SumVal1,
sum(Val) Keep(Dense_Rank Last order by SortKey1,SortKey2)
over(partition by ID) as SumVal2
  from KeepDense
order by ID,SortKey1,SortKey2,Val;

　答えは、Keep(Dense_Rank Last order by SortKey1,SortKey2)のorder by SortKey1,SortKey2の逆ソート（ascとdescを入れ替えたソート）である、order by SortKey1 desc,SortKey2 descを使ったdense_rank関数の結果を、case式でwindow関数の集計対象かを判定するのに使用した下記のSQLとなります。

　なお、array_agg関数は、sum関数の加算元をデバッグ用に表示したい時などに便利なので、例として使用してます。

case式でwindow関数の集計対象かを判定

select ID,SortKey1,SortKey2,Val,
sum(Val) over(partition by ID) as SumVal1,
sum(case rn when 1 then Val end)
over(partition by ID) as SumVal2,
array_agg(case rn when 1 then Val end)
over(partition by ID) as BaseOfSumVal2
from (select ID,SortKey1,SortKey2,Val,
      dense_rank()
      over(partition by ID
           order by SortKey1 desc,SortKey2 desc) as rn
      from KeepDense) a
order by ID,SortKey1,SortKey2,Val;

出力結果

ID	SortKey1	SortKey2	Val	SumVal1	SumVal2	BaseOfSumVal2
111	1	5	10	190	160	{60,100,NULL,NULL}
111	2	3	20	190	160	{60,100,NULL,NULL}
111	3	1	60	190	160	{60,100,NULL,NULL}
111	3	1	100	190	160	{60,100,NULL,NULL}
222	1	2	200	1400	900	{400,500,NULL,NULL}
222	1	3	300	1400	900	{400,500,NULL,NULL}
222	2	4	400	1400	900	{400,500,NULL,NULL}
222	2	4	500	1400	900	{400,500,NULL,NULL}
333	1	2	600	700	100	{100,NULL}
333	1	3	100	700	100	{100,NULL}

　SQLのイメージは下記となります。partition by IDに対応する赤線を引いてます。

SQLのイメージ

集約関数でのkeep指定を模倣

　次は、OracleのSQLで使用できる集約関数でのkeep指定を模倣してみます。

模倣対象のOracleのSQL（集約関数でのkeep指定）

select ID,sum(Val) as SumVal1,
sum(Val) Keep(Dense_Rank Last order by SortKey1,SortKey2)
as SumVal2
  from KeepDense
group by ID
order by ID;

　同じような考え方を使って、答えは下記となります。array_agg関数は、sum関数の加算元をデバッグ用に表示したい時などに便利なので、例として使用してます。

case式で集約関数の集計対象かを判定

select ID,sum(Val) as SumVal1,
sum(case rn when 1 then Val end) as SumVal2,
array_agg(case rn when 1 then Val end) as BaseOfSumVal2
from (select ID,SortKey1,SortKey2,Val,
      dense_rank()
      over(partition by ID
           order by SortKey1 desc,SortKey2 desc) as rn
      from KeepDense) a
group by ID
order by ID;

出力結果